Inserting System Date In Table
Hi!
I would like to insert a system date in a table when I'm inserting a row. Any help would be appreciated.
Thanks
George
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
Count Table Recs = To System Date
I need to count the records in a table with a datetime field equal to system datetime. It looks like it is trying to match time also since time is in field too. I just want to match date only. My sql is below. Does anyone have some suggestions on how to handle this? Date from code DateTime todaydt = DateTime.Now; Table Data format [cal_str_tm] [datetime] not null, Data in cal_str_tm field - 3/27/2008 9:43:16 PM Thanks, Ron ALTER PROCEDURE SP_DpCount ( @todaydt datetime ) AS SELECT @total = COUNT(cal_int_id) from dpcalldtl where (cal_callstat != 'COMPLETED' and cal_str_tm = @todaydt) RETURN
View Replies !
View Related
Problem Inserting Integers And Date In A Sql Server 2005 Datatable Row And Selecting It Afterwards Based On The Date
Hi, I have soma ado.net code that inserts 7 parameters in a database ( a date, 6 integers). I also use a self incrementing ID but the date is set as primary key because for each series of 6 numbers of a certain date there may only be 1 entry. Moreover only 1 entry of 6 integers is possible for 2 days of the week, (tue and fr). I manage to insert a row of data in the database, where the date is set as smalldatetime and displays as follows: 1/05/2007 0:00:00 in the table. I want to retrieve the series of numbers for a certain date that has been entered (without taking in account the hours and seconds). A where clause seems to be needed but I don’t know the syntax or don’t find the right function I use the following code to insert the row : command.Parameters.Add(new SqlParameter("@Date", SqlDbType.DateTime, 40, "LDate")); command.Parameters[6].Value = DateTime.Today.ToString(); command.ExecuteNonQuery(); and the following code to get the row back (to put in arraylist): “SELECT C1, C2, C3, C4, C5, C6 FROM Series WHERE (LDate = Today())� WHERE LDate = '" + DateTime.Today.ToString() + "'" Which is the correct syntax? Is there a better way to insert and select based on the date? I don’t get any error messages and the code executes fine but I only get an empty datatable in my dataset (the table isn’t looped for rows I noticed while debugging). Today’s date is in the database but isn’t found by my tsql code I think. Any help would be greatly appreciated! Grtz Pascal
View Replies !
View Related
Inserting Data Into Two Tables (Getting ID From Table 1 And Inserting Into Table 2)
I am trying to insert data into two different tables. I will insert into Table 2 based on an id I get from the Select Statement from Table1. Insert Table1(Title,Description,Link,Whatever)Values(@title,@description,@link,@Whatever)Select WhateverID from Table1 Where Description = @DescriptionInsert into Table2(CategoryID,WhateverID)Values(@CategoryID,@WhateverID) This statement is not working. What should I do? Should I use a stored procedure?? I am writing in C#. Can someone please help!!
View Replies !
View Related
System.Data.SqlClient.SqlException - When Inserting Image
Hi All, I have a form (asp website with the default.aspx and default.aspx.cs) which I use to connect to the SQL Server 2005 database and insert, update,get and delete data to and from it. In the table I have a column called Picture to insert a photo (usually bmp files) which I declared as a varbinary(max). ID is the primary key and is an int (i tried it to be numeric & varchar). I am trying to insert data into the table using a form with this syntax: SqlConnection enter_conn = new SqlConnection("user id=;" + "password=;server=servername;" + "Trusted_Connection=yes;" + "database=Member Profiles; " + "connection timeout=30"); enter_conn.Open(); string insertSql = "Insert dbo.Details(ID,Last_Name,First_Name,Middle_Initial,Project, Organization,Manager,Current_Skills,Biography,Hobbies, Picture) Select 1001, 'ABC','XYZ','R',' Website Development','ACT',LKM','ASP.Net, C#.Net, SQL Server 2005, SharePoint, Java, ',' developing the new website for ACT using SharePoint and SQL Server 2005', ' Singing, Listening to Music, Dancing, Cooking, Swimming', BulkColumn from Openrowset( Bulk 'C:\photos\rose.bmp', Single_Blob) as Picture"; SqlCommand myCommand = new SqlCommand(insertSql, enter_conn); int i = myCommand.ExecuteNonQuery(); I have no data in the table and am doing the first insert. Though the data gets inserted I am getting this exception: System.Data.SqlClient.SqlException: Violation of PRIMARY KEY constraint 'PK_Details'. Cannot insert duplicate key in object 'dbo.Details'. The statement has been terminated. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at _Default.Enter_Click(Object sender, EventArgs e) in c:Documents and SettingskrkondaXMy DocumentsVisual Studio 2005WebSitesMember ProfilesDefault.aspx.cs:line 82 Can someone please suggest anything???? I appreciate a quick response please!!!! Thanks, Kavya
View Replies !
View Related
Problem Inserting Date
Hello The date format in SQL Server 2000 is dd-MM-yyyy. I am writing the following code in the buttons click event. I am using a textbox and button. Dim conn as SqlConnection=new SqlConnection(connection string) Dim ins as string="insert into Sample(dval) values(' " & TextBox1.Text & " ')" Dim cmd as SQlCommand=new SqlCommand(sel,conn) conn.open() cmd.ExecuteNonQuerry() conn.close() When i am inserting the date 05-03-2007 in the textbox and clicking the button it is inserting date 03-05-2007 rather than 05-03-2007. What changes should i make in the code? Rathish
View Replies !
View Related
Inserting Empty Date
undefined I am using VB6 and SQL7. I am asking a user to enter a date if applicable - specifically a product manufactured date. I then take that date, using the dateadd function, query a table for a specific code, and calculate an expiration date based on the particular code. The problem - if the user does not enter a manufactured date, which is OK, SQL inserts 01/01/1900 - which I do not want. How do I handle? I am inserting the date, if there is one, from a flexgrid. I am declaring the variable as variant. The SQL field is DateTime. Ex: sSQL = "Insert into RecI(manufacdate) values ('" & (flxRec.TextMatrix(ctr,14)) & "')" Please Help!
View Replies !
View Related
Inserting Date And Time
I am wanting to populate a datetime field with data retrieved from 4 dropdowns month, day, year and hour 08:00 - 18:00. I am having trouble joining them together in a format that the sql server recognises.
View Replies !
View Related
Inserting Date Into String
Hi again, I am having troubles inserting a datetime value in a table to a string. what iw ant to do is have it be sent in an email. its an attendance email. here is the code i have right now: select @summaryreport = isnull (@summaryreport + '; ',''+ char(13))+ '<BR>' +'Instructor '+instructor + ' Student ' + Fname + ' ' + Lname + ' - ' + classname +' ' + classdate from #tabledata i get this as an error Msg 241, Level 16, State 1, Line 92 Conversion failed when converting datetime from character string.
View Replies !
View Related
Inserting The Date As Defaultvalue In A Sqldatasource Parameter
Hi Guys. I am trying to insert the date as the default value into the DatePosted parameter in the sqldatasource object. I have put have the following below but it doesn't work. I have also tried <asp:Parameter Name="DatePosted" Type="DateTime" DefaultValue="<%= Date.Now %>" /> <asp:Parameter Name="DatePosted" Type="DateTime" DefaultValue="<%= now() %>" /> I know the solution is probably simple and I look like an idiot, but excuse me because I am very knew and fragile at this lol... any help would be great :). Mike.
View Replies !
View Related
Problem With Inserting Date In A Datetime Field
Hi, I have a problem when I insert a date in a datetime field in a MSSQLServer. That's my problem: if the server is in english version, I have to insert date with this code: DateTime.Today.ToString("MM/dd/yyyy") instead if the server is in italian version, I have to insert date with this code: DateTime.Today.ToString("dd/MM/yyyy") Is there a way to insert a date in standard way, without knowing the server version? bye and thanks in advance
View Replies !
View Related
Change Format Of Date Wghen Inserting Into Sql Express
I have a webform that has 2 calendars that i use to insert a dateFrom and dateTo into a sql database table that has the type smalldatetime. When i insert into the database i get yyyy-mm-dd hh:mm:ss but i just want the yyyy-mm-dd not the time. how can i do this with my asp.net c# code? I also has a datetime.Now() that does the same thing.1 string dateNow = DateTime.Now.ToShortDateString(); 2 3 string myConnectionString = @"Data Source=SRVWEB02SQLEXPRESS;Initial Catalog=strukton_se;User ID=user;Password=secret"; 4 5 SqlConnection myConnection = new SqlConnection(myConnectionString); 6 string myInsertQuery = "INSERT INTO jobs (name, description, contact, datePosted, dateFrom, dateTo) Values(@name, @description, @contact, @datePosted, @dateFrom, @dateTo)"; 7 SqlCommand myCommand = new SqlCommand(myInsertQuery); 8 myCommand.Parameters.AddWithValue("name", TextBoxName.Text); 9 myCommand.Parameters.AddWithValue("description", TextBoxDescription.Text.Replace(" ", "<br/>")); 10 myCommand.Parameters.AddWithValue("contact", TextBoxContact.Text.Replace(" ", "<br/>")); 11 myCommand.Parameters.AddWithValue("datePosted", dateNow); 12 myCommand.Parameters.AddWithValue("dateFrom", Calendar1.SelectedDate.ToShortDateString()); 13 myCommand.Parameters.AddWithValue("dateTo", Calendar2.SelectedDate.ToShortDateString()); 14 15 ButtonSubmit.Enabled = false; 16 17 myCommand.Connection = myConnection; 18 myConnection.Open(); 19 myCommand.ExecuteNonQuery(); 20 myCommand.Connection.Close(); Thanks.
View Replies !
View Related
Inserting The Current Date And Time Into SQL Server Database
I need an SQL string that inserts the current date into a database. So far I have tried: SQL = "INSERT INTO X (START_DATE) VALUES ('" & Date.Now & "')" mycomm = New SqlCommand(sql, myconn) mycomm.ExecuteNonQuery() However, there is a problem with the SQL command. The problem is related to the date. Is there a way of programatically inserting the current date/time into the SQL database? Language used is VB.
View Replies !
View Related
SQL Statement For Inserting Date && Auto-increment Fields
I have a (1) date field and (2) an auto-incrementing ID field that always throw me errors when I'm doing a programmatic insert. (1) After doing many searches on the subject, I don't think I'm using the correct syntax for the date and can't find any suggestion that works. Would appreciate your knowledge on correct SQL syntax for inserting a "today's date" field. (2) I'm using the following code to create a new auto-incrementing ID for each record but it seems that there should be a smoother method to force the field's value to auto-increment. Any ideas? Private objCmd As SqlCommand Private strConn As New SqlConnection(ConfigurationManager.AppSettings("conn"))...objCmd = New SqlCommand("select max(ClientID) from tblClients", strConn)Dim intClientID As Int16 = objCmd.ExecuteNonQuery + 1
View Replies !
View Related
Inserting NULL Values On Date Fields Trhough DAL
I am using a DAL and i want to insert a new row where one of the columns is DATE and it can be 'NULL'. I am assigning SqlTypes.SqlDateTime.Null. But when the date is saved in the database, i get the minvalue (1/01/1900) . Is there a way to put the NULL value in the database using DAL????how can i put an empty date in the database? THANK YOU!!!
View Replies !
View Related
Problem In Inserting Date In Sql Server 7 Through Insert Query
hello myself avinashi am developing on application having vb 6 as front end and sql server 7as back end.when i use insert query to insert data in table then the date value ofthat query is going as 01/01/1900my query is as followsStrSql = "Insert IntoSalesVoucher(TransactionID,VoucherNo,VoucherDate,D ebitTo,CreditTo,TotalAmt,Discount,ModAmt,ModWt,Oth er,Othertype,TaxPerc,TaxAmt,NetAmt,Advance,Narrati on,Haste)"StrSql = StrSql & " Values(" & txtTransactionID.text & "," &txtChallanno.text & ",'" & Format(txtChallanDate.Value, "dd/mm/yyyy") &"'," & AccCode & ",'" & IIf((Category = "Gold"), 36, 38) & "',"StrSql = StrSql & vsAmountDesc.ValueMatrix(RowAmountArr(0),2)& "," & vsAmountDesc.ValueMatrix(RowAmountArr(2), 2) & "," &val(txtModTotal.caption) & "," & val(TxtModWt.caption) & ","StrSql = StrSql & vsAmountDesc.ValueMatrix(RowAmountArr(1),2)& ",'" & vsAmountDesc.TextMatrix(RowAmountArr(1), 1) & "','" &vsAmountDesc.TextMatrix(RowAmountArr(4), 1) & "'," &vsAmountDesc.ValueMatrix(RowAmountArr(4), 2) & ","StrSql = StrSql & vsAmountDesc.ValueMatrix(RowAmountArr(3),2)+ val(txtModTotal.caption) & "," & val(txtAdvance.text) & ",'-'," &IIf(Trim(txtHaste.text) <> "", RetriveAccountCode(Trim(txtHaste.text)),0)& ")"and its output isInsert IntoSalesVoucher(TransactionID,VoucherNo,VoucherDate,D ebitTo,CreditTo,TotalAmt,Discount,ModAmt,ModWt,Oth er,Othertype,TaxPerc,TaxAmt,NetAmt,Advance,Narrati on,Haste)Values(18,1831,'07/04/2004',150,'36',11000,0,0,0,-10,'','1.00',109.9,11100,0,'-',0)in above query though i used cdate to voucherdate value still it save indatabase as 01/01/1900 though here it shows right dateplz help me its a very big issue for me & i really just fed of thisproblem
View Replies !
View Related
System Date
Oracle named the system date as sysdate. Can anyone tell me what Microsoft named the system date? Any help would be greatly appreciated. Thanks in advance.
View Replies !
View Related
Why Date Columns Default To 1900 After Inserting Dates In SQL Server Everywhere.?
I have created a sample Database for the school project, After executing the query below, the Date column is supposed to have the dates I have entered before, However the dates shown are 1900. Any idea why is this happening? I appreciate your help. Thank you. Query: Drop table AccountReceivable GO --BEGIN TRANSACTION Create table AccountReceivable ( AccountRecID int identity (1,1) not null, PatientID int not null, PresentCharges int default 0 not null, PaymentMade money default 0 not null, PreviousBalance money default 0 not null, BalanceDue money default 0 not null, LastPaymentDate datetime not null, PresentDate datetime default GetDate() not null ) GO ALTER TABLE AccountReceivable ADD CONSTRAINT PK_AccountRecID Primary Key (AccountRecID) GO ALTER TABLE AccountReceivable ADD CONSTRAINT FK_PatientID_PatientID FOREIGN KEY (PatientID) REFERENCES PATIENT (PatientID) GO --COMMIT --query to find delinquent accounts --DATEDIFF (d, LastPaymentDate, PresentDate) --Populate the Accounts Table DELETE AccountReceivable GO INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate ) VALUES (913235,451.34,50,0,401.34,4/7/2006,DEFAULT) GO INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate) VALUES (918035,109,109,0,0,3/6/2006,DEFAULT) GO INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate) VALUES (914235,279,89,0,190,5/9/2005,5/9/2005) GO INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate) VALUES (914235,0,90,190,100,5/9/2005,DEFAULT) GO INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate) VALUES (912224,67.90,67.90,0,0,2/2/2006,DEFAULT) GO INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate) VALUES (900814,678.32,78.32,0,600,4/6/2006,4/6/2006) GO INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate) VALUES (900814,0,500,600,100,4/6/2006,4/16/2006) GO INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate) VALUES (900814,0,100,100,0,4/16/2006,DEFAULT) GO INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate) VALUES (913010,203,0,100,303,2/6/2006,DEFAULT) GO INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate) VALUES (913010,0,80,303,223,8/3/2006,DEFAULT) GO INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate) VALUES (913230,1030.89,1030.89,0,0,4/16/2006,DEFAULT) GO INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate) VALUES (918035,78,60,0,18,7/1/2006,DEFAULT) GO INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate) VALUES (941235,902,502,0,400,8/15/2006,DEFAULT) GO INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate) VALUES (941235,0,200,400,200,8/15/2006,DEFAULT) GO INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate) VALUES (952235,134,24,0,110,4/18/2006,DEFAULT) GO INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate) VALUES (952235,0,20,110,90,4/18/2006,DEFAULT) GO INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate) VALUES (921635,257.87,57.87,0,200,5/27/2006,DEFAULT) GO INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate) VALUES (921635,0,20,200,180,6/27/2006,DEFAULT) GO INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate) VALUES (915235,1204,200,0,1004,3/15/2006,DEFAULT) GO INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate) VALUES (915235,0,100,1004,904,4/27/2006,DEFAULT) GO INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate) VALUES (900035,578,178,0,400,7/10/2006,DEFAULT) GO INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate) VALUES (900035,0,100,400,300,7/19/2006,DEFAULT) GO INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate) VALUES (913241,157,0,0,157,5/12/2006,DEFAULT) GO INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate) VALUES (913241,0,57,157,100,5/16/2006,DEFAULT) GO --sample query select PatientID,PresentCharges,LastPAymentDate,PresentDate from AccountReceivable GO --result PatientID PresentCharges LastPaymentDate PresentDate ----------- -------------- ----------------------- ----------------------- 913235 451 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297 918035 109 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297 914235 279 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000 914235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297 912224 67 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297 900814 678 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000 900814 0 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000 900814 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313 913010 203 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313 913010 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313 913230 1030 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313 918035 78 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313 941235 902 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327 941235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327 952235 134 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327 952235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327 921635 257 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327 921635 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327 915235 1204 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327 915235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327 900035 578 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343 900035 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343 913241 157 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343 913241 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343 (24 row(s) affected)
View Replies !
View Related
Error Inserting Image Into SQL Server2000 Table From Pocket PC Application Only When Using Stored Procedure In Table Adapter Wiz
My Pocket PC application exports signature as an image. Everything is fine when choose Use SQL statements in TableAdapter Configuration Wizard. main.ds.MailsSignature.Clear(); main.ds.MailsSignature.AcceptChanges(); string[] signFiles = Directory.GetFiles(Settings.signDirectory); foreach (string signFile in signFiles) { mailsSignatureRow = main.ds.MailsSignature.NewMailsSignatureRow(); mailsSignatureRow.Singnature = GetImageBytes(signFile); //return byte[] array of the image. main.ds.MailsSignature.Rows.Add(mailsSignatureRow); } mailsSignatureTableAdapter.Update(main.ds.MailsSignature); But now I am getting error "General Network Error. Check your network documentation" after specifying Use existing stored procedure in TableAdpater Configuration Wizard. ALTER PROCEDURE dbo.Insert_MailSignature( @Singnature image ) AS SET NOCOUNT OFF; INSERT INTO MailsSignature (Singnature) VALUES (@Singnature); SELECT Id, Singnature FROM MailsSignature WHERE (Id = SCOPE_IDENTITY()) For testing I created a desktop application and found that the same Code, same(Use existing stored procedure in TableAdpater Configuration Wizard) and same stored procedure is working fine in inserting image into the table. Is there any limitation in CF? Regards, Professor Corrie.
View Replies !
View Related
Inserting Distinct Data From One Table In Another Table, How?!?Really Urgent And Needing Help!!!
Hi, I have a table in which I will insert several redundant data. Don't ask why, is Integration services, it only reads data and inserts it in a SQL table. THis way, I have a SQL table with several lines repeating them selves. What I want to do is create a procedure that reads the distinct data and inserts it in another table, but my problem is that I am not able to select data line by line on the original table to save it in local variables and insert it on the another table, I just can select the last line. I've tried a while cycle but no succeed. Here is my code: create proc insertLocalizationASdeclare @idAp int, @macAp varchar(20), @floorAp varchar(2), @building varchar(30), @department varchar(30)select @idAp = idAp from OLTPLocalization where idAp not in (select idAp from dimLocalization)select @macAp=macAp,@floorAp=floorAp,@building=building,@department=department from OLTPLocalizationif (@idAp <> null)beginInsert into dimLocalization VALUES(@idAp,@macAp,@floorAp,@building,@department)endGO This only inserts the last line in the "oltpLocalization" table. O the other hand, like this:create proc aaaaasdeclare @idAp as int, @macAp as varchar(50), @floorAp as int, @building as varchar(50), @department as varchar(50)while exists (select distinct(idAp) from OLTPLocalization)begin select @idAp =idAp from OLTPLocalization where idAp not in (select idAp from dimLocalization) select @macAp = macAp from OLTPLocalization where idAp = @idAp select @building = building from OLTPLocalization where idAp = @idAp select @department = department from OLTPLocalization where idAP = @idApif (@idAp <> null)begin insert into dimLocalization values(@idAp,@macAp,@floorAp,@building,@department)endendgo this retrieves every distinct idAp in each increment on the while statement. The interess of the while is really selecting each different line in the OLTPLocalization table. I did not find any foreach or for each statement, is there any way to select distinct line by line in a sql table and save each column result in variables, to then insert them in another table? I've also thought about web service, that reads the distinct data from the oltpLocalization into a dataset, and then inserts this data into the dimLocalization table. Is there anything I can do?Any guess?Really needing a hand here!Thanks a lot!
View Replies !
View Related
Calculating Value From Two Separate Rows In The Same Table And Inserting As New Row In The Same Table
Code Block Hi, I'm working on a database for a financial client and part of what i need to do is calculate a value from two separate rows in the same table and insert the result in the same table as a new row. I have a way of doing so but i consider it to be extremely inelegant and i'm hoping there's a better way of doing it. A description of the existing database schema (which i have control over) will help in explaining the problem: Table Name: metrics_ladder id security_id metric_id value 1 3 80 125.45 2 3 81 548.45 3 3 82 145.14 4 3 83 123.32 6 4 80 453.75 7 4 81 234.23 8 4 82 675.42 . . . Table Name: metric_details id metric_id metric_type_id metric_name 1 80 2 Fiscal Enterprise Value Historic Year 1 2 81 2 Fiscal Enterprise Value Current Fiscal Year 3 82 2 Fiscal Enterprise value Forward Fiscal year 1 4 83 2 Fiscal Enterprise Value Forward Fiscal Year 2 5 101 3 Calendar Enterprise value Historic Year 1 6 102 3 Calendar Enterprise Value Current Fiscal Year 5 103 3 Calendar Enterprise value Forward Year 1 6 104 3 Calendar Enterprise Value Forward Year 2 Table Name: metric_type_details id metric_type_id metric_type_name 1 1 Raw 2 2 Fiscal 3 3 Calendar 4 4 Calculated The problem scenario is the following: Because a certain number of the securities have a fiscal year end that is different to the calendar end in addition to having fiscal data (such as fiscal enterprise value and fiscal earnings etc...) for each security i also need to store calendarised data. What this means is that if security with security_id = 3 has a fiscal year end of October then using rows with ids = 1, 2, 3 and 4 from the metrics_ladder table i need to calculate metrics with metric_id = 83, 84, 85 and 86 (as described in the metric_details table) and insert the following 4 new records into metrics_ladder: id security_id metric_id value 1 3 101 <calculated value> 2 3 102 <calculated value> 3 3 103 <calculated value> 4 3 104 <calculated value> Metric with metric_id = 101 (Calendar Enterprise value Historic Year 1) will be calculated by taking 10/12 of the value for metric_id 80 plus 2/12 of the value for metric_id 81. Similarly, metric_id 102 will be equal to 10/12 of the value for metric_id 81 plus 2/12 of the value for metric_id 82, metric_id 103 will be equal to 10/12 of the value for metric_id 82 plus 2/12 of the value for metric_id 83 and finally metric_id 104 will be NULL (determined by business requirements as there is no data for forward year 3 to use). As i could think of no better way of doing this (and hence the reason for this thread) I am currently achieving this by pivoting the relevant data from the metrics_ladder so that the required data for each security is in one row, storing the result in a new column then unpivoting again to store the result in the metrics_ladder table. So the above data in nmetrics_ladder becomes: security_id 80 81 82 83 101 102 ----------- -- -- -- -- -- -- 3 125.45 548.45 145.14 123.32 <calculated value> <calculated value> 4 ... . . . which is then unpivoted. The SQL that achieves this is more or less as follows: ********* START SQL ********* declare @calendar_averages table (security_id int, [101] decimal(38,19), [102] decimal(38,19), [103] decimal(38,19), [104] decimal(38,19),etc...) -- Dummy year variable to make it easier to use MONTH() function -- to convert 3 letter month to number. i.e. JAN -> 1, DEC -> 12 etc... DECLARE @DUMMY_YEAR VARCHAR(4) SET @DUMMY_YEAR = 1900; with temp(security_id, metric_id, value) as ( select ml.security_id, ml.metric_id, ml.value from metrics_ladder ml where ml.metric_id in (80,81,82,83,84,85,86,87,88,etc...) -- only consider securities with fiscal year end not equal to december and ml.security_id in (select security_id from company_details where fiscal_year_end <> 'dec') ) insert into @calendar_averages select temppivot.security_id -- Net Income ,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[80]) +((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[81]) as [101] ,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[81]) +((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[82]) as [102] ,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[82]) +((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[83]) as [103] ,NULL as [104] -- Share Holders Equity ,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[84]) +((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[85]) as [105] ,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[85]) +((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[86]) as [106] ,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[86]) +((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[87]) as [107] ,NULL as [108] -- Capex -- Sales -- Accounts payable etc... .. .. from temp pivot ( sum(value) for metric_id in ([80],[81],[82],[83],[84],[85],[86],[87],[88],etc...) ) as temppivot inner join company_details cd on temppivot.security_id = cd.security_id ********* END SQL ********* The result then needs to be unpivoted and stored in metrics_ladder. And FINALLY, the question! Is there a more elegant way of achieving this??? I have complete control over the database schema so if creating mapping tables or anything along those lines would help it is possible. Also, is SQL not really suited for such operations and would it therefore be better done in C#/VB.NET. Many thanks (if you've read this far!) M.
View Replies !
View Related
How To Get The System Date And Time Into Database
Hi, I m using ASP.NET with C#. I m having one field which should store the datetime of the system. The datetime should be automatically stored for that entry when the user submits that record on the click event.Then it should display the date time on the gridview from database.I m using sqlserver 2005 and i have created the stored procedure for the insert command.This is the sample sp what should be written here to insert system date time automatically when the user submits the asp.net form ?Is there any code for writing directly in stored procedure or asp.net coding page... ALTER PROCEDURE [dbo].[StoredProcedure1]@salesid INT OUTPUT,@salesdate datetime,@customername varchar(20)ASBEGINSET NOCOUNT ONBEGIN INSERT INTO sales (customername) VALUES (@customername) SELECT @companyid = SCOPE_IDENTITY()END SET NOCOUNT OFFEND Thanxs in advance...
View Replies !
View Related
DTS File Name To Include System Date/Time
We have an application group that wants to pull date from SQL Server and write it to text file on the server. They want the file format to be 12100_YYYMMDDHHMM.fr1 for one set of data, 12100_YYYMMDDHHMM.fr2 for a second set...and so on. The '12100' is fixed, but the rest of the file name will always have to include the system date/time. Is there an easy way to do this within a DTS package (when writing to the output file)? I would really appreciate help on this. Thank you.
View Replies !
View Related
Query 8 Days Ago Based On System Date
Hi, I'm trying to query an SQL table column with date values to show 8 Days ago results. I've started with this query: SELECT ficheiro, erro, descritivo_erro, contrato, DO, movimento, data, descritivo, tipo_movimento, desconto, montante, comissao, IVA FROM status_day WHERE (YEAR(data) = YEAR(GETDATE())) AND (MONTH(data) = MONTH(GETDATE())) AND (DAY(data) = DAY(GETDATE()) -8) ORDER BY descritivo_erro, contrato The problem is that the text in red will have some problems when the month changes - If I want the 8 days ago results from January and the system date is 1st of February the query will not return any values. I read something about DATESERIAL but is wasn't conclusive on how to use it with system date. Please help me out with this query.
View Replies !
View Related
Inserting Data Into A Table Referencing PK From Another Table
How do i insert data into multiple tables. Lets say i have 2 tables: Schedules and Event Schedules data is entered into the Schedules Table first then now i need to insert Event table's data by refrencing the (PK ID) from the schedules table. How do i insert data into Event table referencing the (PK ID) from Schedules Table ? Fields inside each of the tables can be found below: Event Table (PK,FK) ScheduleID EventTitle AccountManager Presenter EventStatus Comment Schedule Table (PK) ID AletrnateID name UserID UserName StartTime EndTime ReserveSource Status StatusRetry NextStatusDateTime StatusRemarks
View Replies !
View Related
Selecting Rows From SQL Server Dbs With A Where Clause Using The System Date.
Hello i currently have a website that has an SQL server 2005 dbs that stores appointments. I would like to do a select statement in my sqldatasource that selects all the records that have an 'appointmentDate' more than 2 weeks after the current date (ie the system date). I am stuck on the SQL statement i need to produce to achieve this. I was thinking along the lines of SELECT * FROM appointments WHERE appointmentDate > System.Date + 14; However this is clearly not the right SQL statement. Any help would be appreciated. Many thanks, James.
View Replies !
View Related
Problem With Custom Reservation System, Date Querying
Hi, I'm having issues trying to get a query working the way I want, it maybe that i'm overly complicating things though. What I have done so far is have 2 seperate tables one holding details about the item to be booked out with an ID the second linking to the Item via the ID and also having the startdate and the enddate of the booking, thus an item will have multiple rows in the bookings table for multiple bookings. What I want to have is a "quick" booking method where a user enters the startdate they would like and the enddate, a drop down is then filtered (via a query) returning only the items that are avalible. The issue i'm having is that because my bookings have multiple rows for each item, for what maybe true in the rules for an item in 1 row maybe false alittle later - i which case the returned data i am getting is incorrect! Hopefully I have made sense, and maybe someone can help? my querry for the filter so far: - SELECT DISTINCT DeviceDetails.Device_ID, DeviceDetails.Device_Name FROM DeviceDetails LEFT OUTER JOIN BookingDetails ON BookingDetails.Device_ID = DeviceDetails.Device_ID WHERE (BookingDetails.Bookout_Date IS NULL) OR (BookingDetails.Bookout_Date >= GETDATE()) AND (@Dateout <= BookingDetails.Bookin_Date) AND (BookingDetails.Bookout_Date >= @Datein) OR (BookingDetails.Bookout_Date >= GETDATE()) AND (@Dateout >= BookingDetails.Bookin_Date) AND (BookingDetails.Bookout_Date <= @Datein)
View Replies !
View Related
Error 30311: Value Of Type 'System.DBNull' Cannot Be Converted To 'Date'
I'm creating an ISP for extractig data from a text file and put it in a database. One of the fields in my textfile contains the value '0' or a date. If it's '0' it should be converted to the Null Value. The column in which it has to be saved is of type smalldatetime. This is the code of my script where I want to check the value of the field in my textfile and convert it to a date or to Null. Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer) If Row.cdDateIn = "" Or Row.cdDateIn= "0" Then Row.cdDateInCorr = CDate(DBNull.Value) ElseIf Row.cdDateIn_IsNull Then Row.cdDateInCorr = CDate(DBNull.Value) Else Row.cdDateInCorr = CDate(Row.cdDateIn) End If End Sub The error that I get is: Validation error. Extract FinCD: Conversion of cdDateIn [753]: Eroor 30311: Value of type 'System.DBNull' cannot ben converted to 'Date'. How do I resolve this problem ?
View Replies !
View Related
Weird One. An Internal Error Occurred On The Report Server. (System Date?)
Hi guys, We've had Reporting Services running in a production environ. for 6 months fine, but from Saturday every report now causes the following error (in both the Report Manager and Soap calls): An internal error occurred on the report server. See the error log for more details. (rsInternalError) Get Online Help Specified argument was out of the range of valid values. Parameter name: date Now, before you jump to conclusions - this error is occurring on reports with both parameters and no parameters (ie in reports that have no "date" parameter in the report). The next bit of info is the weird bit... It was working on Friday (25/March/2006) - so as a test, i switched the servers clock back to Friday - and BINGO... it worked. Then I changed it to Saturday (26th March) and it doesnt work. In fact for the next 7 days - the service will not work until April 2nd 2006 - (when I changed the systems date to the 2nd it worked again.) Moving forward, it looks like its working fine. Does anyone have any suggestions? This is in a production environment, so obviously changing the sytsem date as a quick fix workaround wont suffice. Thanks in advance. Grey
View Replies !
View Related
Inserting Data In A Table From Another Table
I have a pb when i transfer data from a table named INCIDENT to a table GI_INCIDENTS.... In the table INCIDENT i have startdate,enddate,starttime,endtime And in the table GI_INCIDENTS i have startdate and enddate -->format yyyy/MM/dd hh:mm:ss.ttt.. INCIDENT is a migrated table from access...Then with a query i transfered datas to GI_INCIDENTS... The pb is in INCIDENT Table, date of beginning incident is (2003/06/18 ) but when i execute my insert query,in the table GI_INCIDENTS, date of beginning incident is (2003/06/06)... So i have 2 days delay in the all colums... INCIDENT-->enddate (2006/11/30) GI_INCIDENT-->enddate(2006/11/28) I don't understand the fact... The query: Insert into GI_INCIDENTS(GIIN_ID,GIIN_STA_INCIDENT,STARTDATE,ENDDATE,GIIN_TYPE,GIIN_RESUME,GIIN_DESCRIPTION,GIIN_IMPACT_ANTENNE,GIIN_INITIATEUR) select NUMINCIDENT,CODETAT,STARTDATE+ ' ' + STARTTIME, ENDDATE + ' ' + ENDTIME,CODETYPE,NATURE,DESINCIDENT,CODBLOQ,NUMEXPL From INCIDENT set IDENTITY_INSERT GI_INCIDENTS off Thanks a lot for your help..
View Replies !
View Related
Inserting Into A Sql Table
VWD 2005 Express. Visual Basic. Sql Server 2005. How may I insert a record into a table from code behind (I am doing this without any entries to a form). The connection name is GoodNews_Extranet. The table is Login. The fields I want to write are UserId and dtTime. Thanks for any help. The insert command is: INSERT INTO [Login] ([SystemUserId], [dtTime]) VALUES (@SystemUserId, @dtTime) I will provide the @ parameters programmatically. I just need to know the commands I should use in Visual Basic to actually execute the INSERT command. Thanks for the help.
View Replies !
View Related
Inserting Into More Than 1 Table
Hey all, Quick question for ya. If I wanted to insert 1 value into 1 table and another value into another table, how would I do that with closing connections and reopening and whatnot? I am using Visual Web Dev and SQL server 2005 express. I have been trying to mess around with it but I can't figure it out. Here is the situation. I want to insert a GroupName and GroupDescription that a user fills in in 2 text boxes named GroupNametxt and GroupDescriptiontxt. This data will go into the "Group" table. I then also want to Insert the data UserID (taken from the aspnet ID table), and GroupName from the Grouptxt.text into a GroupMembership table. How would I do all this in 1 button click? I know some basic TSQL but I don't know how to handle the opening and closing of connections and whatnot. Thanks, Chris
View Replies !
View Related
Help Inserting XML Into MS SQL Table.
I've got a string value in C# that contains: "<?xml version="1.0"?><?qbxml version="2.0"?><QBXML><QBXMLMsgsRq onError="stopOnError"><JournalEntryAddRq requestID="1"><JournalEntryAdd><TxnDate>2003-11-18</TxnDate><JournalDebitLine><AccountRef> <FullName>1200</FullName> </AccountRef> <Amount>160.06</Amount> <Memo>613663</Memo> <ClassRef> <FullName>Buffalo</FullName> </ClassRef></JournalDebitLine><JournalCreditLine><AccountRef> <FullName>5687</FullName> </AccountRef> <Amount>160.06</Amount> <Memo>613663</Memo> <ClassRef> <FullName>Buffalo</FullName> </ClassRef></JournalCreditLine><JournalDebitLine><AccountRef> <FullName>1200</FullName> </AccountRef> <Amount>133.85</Amount> <Memo>300931</Memo> <ClassRef> <FullName>Buffalo</FullName> </ClassRef></JournalDebitLine><JournalCreditLine><AccountRef> <FullName>5687</FullName> </AccountRef> <Amount>133.85</Amount> <Memo>300931</Memo> <ClassRef> <FullName>Buffalo</FullName> </ClassRef></JournalCreditLine></Journ alEntryAdd></JournalEntryAddRq></QBXMLMsgsRq></QBXML>" I'm passing that string to an insert stored procedure that hits a varchar field (varchar 5000). problem is, nothing is showing up in the database. there are no errors, but the field I have shows nothign in it after the insert. if i run a test and set my string to "this is a test" it goes into the table from the SP_insert no problem. So i'm figuring, somehow the formatting of the XML string in C# isn't kosher with MS SQL. the above text is the exact string value from VS.NET command output (? mystring). I'm really in need of help here guys. I'm trying to log this XML result into a log table for reference if there are ever any problems I can look in the log table @ the xml. but i can't get it to insert. Help!
View Replies !
View Related
Inserting Into Table
I have the 3 tables below. I want to be able to add more than one item to a quote. I thought I had designed the tables so that I could have more than one item in a quote....? I've tried to do this: insert into has_quote(date_of_quote,service_desk_contact,category,cat_ref) values('09/12/2002', 'me',2,'C92') where quote_id = '1' but it doesn't work. Am I totally missing something here? Code: CREATE TABLE Item ( cat_ref VARCHAR(5)PRIMARY KEY, descrip VARCHAR(50), date_added SMALLDATETIME, date_last_pricecheck SMALLDATETIME, cat_type VARCHAR(20), contract VARCHAR(10), cost_price SMALLMONEY, installation_charge SMALLMONEY, commercial_markup SMALLMONEY, supplier_name VARCHAR(20), supplier_phone VARCHAR(20), notes VARCHAR(500)) CREATE TABLE has_quote ( quote_id INT IDENTITY (1,1) PRIMARY KEY, date_of_quote SMALLDATETIME, service_desk_contact VARCHAR(20), category INTEGER, cat_ref VARCHAR(5)FOREIGN KEY REFERENCES Item(cat_ref), first_name VARCHAR(10), surname VARCHAR(10), customer_phone VARCHAR(20), FOREIGN KEY (first_name, surname, customer_phone) REFERENCES Customer(first_name, surname, customer_phone) ON DELETE CASCADE ON UPDATE CASCADE) CREATE TABLE Customer ( first_name VARCHAR(10), surname VARCHAR(10), customer_phone VARCHAR(20), contract VARCHAR(10), location VARCHAR(20), email VARCHAR(50), cust_id INT IDENTITY (1,1), PRIMARY KEY (first_name, surname, customer_phone))
View Replies !
View Related
Inserting From One Table To Another
I am trying to insert data into a table from another where not exists. This will be a table that writes from one table to another if there has been an update from the original table. Both tables have the same field and table names. This is what I have so far: USE cana_01imis DECLARE @Gen_Tables.Code AS VARCHAR(60) DECLARE @Gen_Tables.DESCRIPTION AS VARCHAR(255) DECLARE cur CURSOR FAST_FORWARD FOR SELECT Gen_Tables.Code, Gen_Tables.DESCRIPTION FROM dbo.cana_01imis.gen_tables WHERE NOT IN (SELECT Gen_Tables.Code FROM Gen_Tables) FETCH NEXT FROM cur INTO @Gen_Tables.Code, @Gen_Tables.DESCRIPTION WHILE @@FETCH_STATUS = 0 BEGIN --SELECT Gen_Tables.Code, Gen_Tables.DESCRIPTION from gen_tables DECLARE @DNNGen_Tables AS VARCHAR(60) SET @DNNGen_Tables.code = @Gen_Tables.Code DECLARE @DNNGen_Tables.Code DESCRIPTION AS VARCHAR(255) SET @DNNGen_Tables.Code DESCRIPTION = @Gen_Tables.description -- then your insert statement goes here INSERT INTO cana_01dnn.gen_tables VALUES (Gen_Tables.Code,Gen_Tables.DESCRIPTION) FETCH NEXT FROM cur INTO @Gen_Tables.Code,@Gen_Tables.Code END CLOSE cur DEALLOCATE cur
View Replies !
View Related
Inserting A Value From Another Table
Hi, I am using MSSQL and I want to do something like this: insert into people values (newid(), *value from names table* ) but I can't work out how to select the value from one table and insert it into another table in a single query. Using a subquery or combining with a select has not worked. Is this a job for a datacursor or is there a simpler way? Thanks.
View Replies !
View Related
Inserting Into A Table
Hi, i am new to SQL. I have this problem here, i need a little help. How do i insert data into a table only when there is no such record in the existing table? I have this: INSERT INTO ptable ( symptoms, weight, solutions ) VALUES ( '"+str+"', "+percent+", '"+str3+"' );
View Replies !
View Related
|
TRACKER
