April 21, 2008 at 4:26 am
An insert statement was not inserting all the data into a table. Found it very strange as the other fields in the row were inserted. I ran SQL profiler and found that sql statement had all the fields in the insert statement but some of the fields were not inserted. Below is the sql statement which is created dyanmically by a asp.net C# class. The columns which are not inserted are 'totaltax' and 'totalamount' ...while the 'shipto_name' etc...were inserted.
there were not errors thrown. The sql from the code cannot be shown here as it is dynamically built referencing C# class files.
It works fine on another test database which uses the same dlls. The only difference i found was the difference in date formats..
@totalamount=1625.62,
@totaltax=125.62
are not inserted into the database.
Below is the statement copied from SQL profiler.
exec sp_executesql N'INSERT INTO salesorder
(billto_city, billto_country, billto_line1, billto_line2, billto_name,
billto_postalcode,
billto_stateorprovince, billto_telephone, contactid, CreatedOn,
customerid, customeridtype,
DeletionStateCode, discountamount,
discountpercentage, ModifiedOn, name, ordernumber,
pricelevelid, salesorderId, shipto_city, shipto_country,
shipto_line1, shipto_line2,
shipto_name, shipto_postalcode, shipto_stateorprovince,
shipto_telephone, StateCode, submitdate, totalamount,
totallineitemamount, totaltax
) VALUES
(@billto_city, @billto_country, @billto_line1, @billto_line2,
@billto_name, @billto_postalcode, @billto_stateorprovince,
@billto_telephone, @contactid, @CreatedOn, @customerid,
@customeridtype, @DeletionStateCode, @discountamount,
@discountpercentage,
@ModifiedOn, @name, @ordernumber, @pricelevelid, @salesorderId,
@shipto_city, @shipto_country, @shipto_line1, @shipto_line2,
@shipto_name,
@shipto_postalcode, @shipto_stateorprovince, @shipto_telephone,
@StateCode, @submitdate, @totalamount, @totallineitemamount,
@totaltax)',N'@billto_city nvarchar(8),
@billto_country nvarchar(13),@billto_line1 nvarchar(3),
@billto_line2 nvarchar(4),@billto_name nvarchar(15),
@billto_postalcode nvarchar(5),
@billto_stateorprovince nvarchar(8),
@billto_telephone nvarchar(3),@contactid
uniqueidentifier,@CreatedOn datetime,
@customerid uniqueidentifier,@customeridtype int,
@DeletionStateCode int,@discountamount
decimal(1,0),@discountpercentage decimal(1,0),
@ModifiedOn datetime,@name nvarchar(33),
@ordernumber nvarchar(18),@pricelevelid
uniqueidentifier,@salesorderId uniqueidentifier,
@shipto_city nvarchar(8),@shipto_country nvarchar(13),@shipto_line1
nvarchar(3),@shipto_line2 nvarchar(4),@shipto_name nvarchar(15),
@shipto_postalcode nvarchar(5),@shipto_stateorprovince
nvarchar(8),@shipto_telephone nvarchar(3),@StateCode int,
@submitdate datetime,@totalamount decimal(6,2),
@totallineitemamount decimal(6,2),
@totaltax decimal(5,2)',
@billto_city=N'New York',
@billto_country=N'United States',
@billto_line1=N'454',
@billto_line2=N'Road',
@billto_name=N'Hillary Clinton',
@billto_postalcode=N'10001',
@billto_stateorprovince=N'New York',
@billto_telephone=N'124',
@contactid='8DAFE298-3A25-42EE-B208-0B79DE653B61',
@CreatedOn=''2008-04-18 13:37:12:013'',
@customerid='8DAFE298-3A25-42EE-B208-0B79DE653B61',
@customeridtype=2,@DeletionStateCode=0,@discountamount=0,
@discountpercentage=0,
@ModifiedOn=''2008-04-18 13:37:12:013'',
@name=N'E-Commerce Order (Before billing)',
@ordernumber=N'BRKV-CC-OKRW5764YS',
@pricelevelid='B74DB28B-AA8F-DC11-B289-000423B63B71',
@salesorderId='9CD0E11A-5A6D-4584-BC3E-4292EBA6ED24',
@shipto_city=N'New York',
@shipto_country=N'United States',
@shipto_line1=N'454',
@shipto_line2=N'Road',
@shipto_name=N'Hillary Clinton',
@shipto_postalcode=N'10001',
@shipto_stateorprovince=N'New York',
@shipto_telephone=N'124',
@StateCode=0,
@submitdate=''2008-04-18 14:37:10:140'',
@totalamount=1625.62,
@totallineitemamount=1500.00,
@totaltax=125.62
thanks
April 21, 2008 at 4:39 am
I don't understand. If the difference is in date formats, why have you highlighted values that appear to represent sums of money? Please show us what the query is inserting into the table, and what it ought to be inserting.
John
April 21, 2008 at 4:41 am
thanks for reply.
The problem is not the date format,
@totalamount=1625.62,
@totaltax=125.62
are not inserted into the database.
April 21, 2008 at 4:52 am
OK. Did you get any error message? What was in the columns that were not inserted to? Are there any triggers on the table? What is the data type of the columns in question? What happens if you paste that code into Management Studio and run it from there?
John
April 21, 2008 at 5:45 am
There are no triggers. The columns are money columns.
I noticed that the difference between the working database and my database is the date format.
working: @submitdate='Apr 18 2008 2:16:02:107PM',
Not working: @submitdate=''2008-04-18 14:37:10:140'',
when i run the insert statement directly in management studio i get an error:
Msg 102, Level 15, State 1, Line 24
Incorrect syntax near '2008'.
when i remove the extra single quotes around the dates ..the row is inserted...
Why is the date format differnent? why are they additional single quotes? Is this the reason for the fields after the submitdate are not getting inserted?? Any suggestions
April 21, 2008 at 6:16 am
It looks as if it is the date that's the problem, then. Have you tried changing the double quotes to single quotes?
John
April 21, 2008 at 7:03 am
OK, I found the problem. It is with the dates.
When i change the date formats from :''2008-04-18 14:37:10:140''
To:'Apr 18 2008 1:16:03:090PM'
The record is inserted successfully. How do i make sure that the date formats are maintained and the extra single quotes are removed? Although the quotes look like double quotes they are actually two single quotes...
Othwise i get an error when i run the query in management studio : cannot convert varchar to datetime.
April 21, 2008 at 7:07 am
You'll need to speak to whoever wrote the asp.net C# code in order to get the dynamic SQL generated in the way you expect it.
John
April 21, 2008 at 8:48 am
Is this what you were looking for?Yes No
nirisan:
How do i change the date format for the user created in the database to English - UK from English US as i think problem lies there.
IT is similar to the post in the link. Once you get on to the link the problem is discussed but there is no solution.
I have a similar problem as the code gets the date from the user login accessing the site which is created in SQL2005.
http://www.devnewsgroups.net/group/microsoft.public.dotnet.framework.adonet/topic16289.aspx
April 21, 2008 at 8:55 am
The big question in my mind is why a datetime parameter is being passed as nvarchar.... You REALLY don't want to do that. Bind the datetime parameter to be a datetime parameter, and you won't have conversion issues.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 22, 2008 at 2:17 am
How do i change the date format for the user created in the database to English - UK from English US as i think problem lies there.
IT is similar to the post in the link. Once you get on to the link the problem is discussed but there is no solution.
I have a similar problem as the code gets the date from the user login accessing the site which is created in SQL2005.
http://www.devnewsgroups.net/group/microsoft.public.dotnet.framework.adonet/topic16289.aspx
April 22, 2008 at 2:27 am
Have a look at the CONVERT topic in Books Online. This gives you all sorts of date formats.
John
April 22, 2008 at 4:58 am
As the SQL statement is dynamically created in the Asp.net C# code behind file, So i am not able to control the format by the backend.
At the moment the code is:
DateTime OrderDate;
crmOrder.submitdate = CommerceOrder.OrderDate;
Present Output with Two Single quotes: ''2008-04-18 14:37:10:140''
Required format and output with one single quote: 'Apr 18 2008 1:16:03:090PM'
Though the present output looks like double quotes they are actually 2 single quotes.
April 22, 2008 at 5:07 am
If your C# code is generating syntactically invalid SQL and you can't change that, then I'd say there's nothing you can do.
John
April 22, 2008 at 5:55 am
As the SQL statement is dynamically created in the Asp.net C# code behind file, So i am not able to control the format by the backend. Im not changing it to varchar. While testing i removed the extra single quotes and management studio threw error that cannot convert from Datetime to varchar
At the moment the code is:
DateTime OrderDate;
crmOrder.submitdate = CommerceOrder.OrderDate;
Present Output with Two Single quotes: ''2008-04-18 14:37:10:140''
Required format and output with one single quote: 'Apr 18 2008 1:16:03:090PM'
Though the present output looks like double quotes they are actually 2 single quotes.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply