Solved

C Sharp / MS SQL - Parameterised Query problem

Posted on 2011-02-16
3
494 Views
Last Modified: 2012-05-11
Hi,

I have an SQL insert statement that i am using a parameterised query to pass the values within a program i am writing in C#.

the SQL string looks like this:

sql = "Insert Into equities company_name,mnemonic,sedol,code1,code2,primary_analyst,secondary_analyst, sales,sector_id,index_id,created_dttm,mod_dttm,act_inact) ";
                sql = sql + "values (@company_name,@mnemonic,@sedol,@code1,@code2,@analyst1,@analyst2,@sales,@sector,@index,@now,@now,'A')";

my problem is, when i pass a null value for @analyst2 or @sales which are numeric fields in the database, SOMEWHERE its being assumed that these input values should be smalldatetime values. If i pass an int as the value for these values, it correctly assumes these fields to be ints... below i have pasted what is being executed against the SQL Server from a trace using SQL Server profiler. The first one you can see that null values are being passed in and @analyst2 and @sales are thought to be smalldatetime fields, whereas in the second one, ints have have been passed in and the fields have been assumed correctly to be numerics and all works fine. I would like someone to help me shed some light why/where these types get assumed to be of an incorrect type...

EXAMPLE 1 (incorrect value types as smalldatetime)
-----------------------------------------------------------------------------------------------------------------
exec sp_executesql N'Insert Into equities (company_name,mnemonic,sedol,code1,code2,primary_analyst,secondary_analyst, sales,sector_id,index_id,created_dttm,mod_dttm,act_inact) values (@company_name,@mnemonic,@sedol,@code1,@code2,@analyst1,@analyst2,@sales,@sector,@index,@now,@now,''A'')',N'@company_name varchar(7),@mnemonic varchar(4),@sedol varchar(8000),@code1 varchar(8000),@code2 varchar(8000),@analyst1 int,@analyst2 smalldatetime,@sales smalldatetime,@sector int,@index int,@now smalldatetime',@company_name='Tes''t 2',@mnemonic='TST2',@sedol='',@code1='',@code2='',@analyst1=10,@analyst2=NULL,@sales=NULL,@sector=14,@index=5,@now='2011-02-15 18:06:00:000'

EXAMPLE 2 (Correctly assumed types when value is passed)
---------------------------------------------------------------------------------------------------------------
exec sp_executesql N'Insert Into equities (company_name,mnemonic,sedol,code1,code2,primary_analyst,secondary_analyst, sales,sector_id,index_id,created_dttm,mod_dttm,act_inact) values (@company_name,@mnemonic,@sedol,@code1,@code2,@analyst1,@analyst2,@sales,@sector,@index,@now,@now,''A'')',N'@company_name varchar(7),@mnemonic varchar(4),@sedol varchar(9),@code1 varchar(7),@code2 varchar(8),@analyst1 int,@analyst2 int,@sales int,@sector int,@index int,@now smalldatetime',@company_name='Tes''t 8',@mnemonic='TST8',@sedol='dfde34333',@code1='23fgfeg',@code2='dgdr4563',@analyst1=10,@analyst2=12,@sales=11,@sector=17,@index=6,@now='2011-02-16 07:59:00:000'
0
Comment
Question by:alnunn
3 Comments
 
LVL 23

Accepted Solution

by:
wdosanjos earned 250 total points
ID: 34905654
How do you set those parameter values on C#?
0
 
LVL 2

Assisted Solution

by:HSIHSA
HSIHSA earned 250 total points
ID: 34905721
http://www.bigresource.com/MS_SQL-Insert-null-value-in-column-with-int-datatype-SGwVXwG1.html#4mQhAeMD

go through the link

you need to pass System.Data.SqlTypes.SqlInt32.Null
0
 
LVL 1

Author Comment

by:alnunn
ID: 34905802
I have found the error of my ways! I just couldnt see it. It was a typo where i set the values and types in some wrapper functions of mine...

Sorry for the bother!
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now