Can t pass an empty string to a SqlParameter

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

Can t pass an empty string to a SqlParameter

Author

Topic

rtutus
Aged Yak Warrior

522 Posts

Posted - 2006-01-13 : 01:49:33

I have this code:
pm = oleDBInsNeAcc.Parameters.Add(New SqlParameter("@AccAccFNam", SqlDbType.Char))
pm.Value = objCust.prpContFrsNam
where objCust.prpContFrsNam is of type String

When I don t fill the property objCust.prpContFrsNam (being therefore merly an empty string) and run the sqlcommand.executenonquery() I get the error :

Prepared statment '(@AccAccFNam,......,@' expects parameter @AccAccFNam, which was not supplied.

So please, how do I pass empty strings to parameters for a SqlServer database without gettingthe error

THanks.

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2006-01-13 : 02:45:30

is it an empty string or is it null?
have you tried pm.Value = "" ?

Duane.

"It's a thankless job, but I've got a lot of Karma to burn off."

rtutus
Aged Yak Warrior

522 Posts

Posted - 2006-01-13 : 08:55:33

it s empty string ""

thanks for help

Billkamm
Posting Yak Master

124 Posts

Posted - 2006-01-13 : 09:13:52

SqlDbType.Char cannot be an empty string. The SQL Server type Char is a fixed size. If it is char of size 5 then you would need to pass 5 spaces.

If that field allows NULL values then you would want to pass Convert.DbNull instead of an empty string.

If you do not want a fixed size string then you should be using the varchar datatype in your database.

rtutus
Aged Yak Warrior

522 Posts

Posted - 2006-01-13 : 09:19:39

it s empty string ""

thanks for help

rtutus
Aged Yak Warrior

522 Posts

Posted - 2006-01-13 : 09:23:28

1/ so can I use another type in my paramaterer code (Parameters.Add(New SqlParameter("@AccAccFNam", SqlDbType.Char)) besides SqlDbType.Char to fix this problem. how do i write that line of code then.
2/ how do i convet the empty string "" to a null vale, i m using vb.net

Thnks a lot

Billkamm
Posting Yak Master

124 Posts

Posted - 2006-01-13 : 09:33:24

Well first of all your parameter has to match whatever @AccAccFNam is declared as in the database. If it is a char in the database then the parameter has to be char. If you would like to change this to a variable length string you must do so on the database side first then use SqlDbType.VarChar

Once it is changed to varchar you can use empty strings. If you would like to keep it as char and instead pass a NULL value to the database then this would be the code:


pm = oleDBInsNeAcc.Parameters.Add(New SqlParameter("@AccAccFNam", SqlDbType.Char))
If objCust.prpContFrsNam = String.Empty Then
    pm.Value = Convert.DbNull
Else
    pm.Value = objCust.prpContFrsNam
End If

But also remember with the type char, the data must be the exact length of what the variable is declared in the database. If it is declared as char size 5 you may also want to check the length of the string like so:


If objCust.prpContFrsNam.Length = 5 Then
    pm.Value = objCust.prpContFrsNam
ElseIf objCust.prpContFrsNam = String.Empty Then
    pm.Value = Convert.DbNull
Else
    ' The data is not valid.  Do something about it here
End If

But based on your variable name it appears to be a First Name at which point the varchar solution would be the best option.

rtutus
Aged Yak Warrior

522 Posts

Posted - 2006-01-13 : 15:22:36

Thanks a lot the code above worked perfectly, now I can pass the Null value whenever I have an empty string.

However I still can t send an empty string "" either when using Varchar or Char in the DB type definition.
I get the same error : Prepared statment '(@AccAccFNam,......,@' expects parameter @AccAccFNam, which was not supplied. And that happens weither I use : ......New SqlParameter("@AccAccFNam", SqlDbType.VarChar, 50)) or .....New SqlParameter("@AccAccFNam", SqlDbType.VarChar, 50)).

The only time when I don t get an error is when I declare my variable as varianttype. Is that what u meant by your first sentence. Do u think the only way to pass an empty string to DB d be to declare a varianttype.

Thanks a lot amigo. Anyway, my code now seems to work thanks to your help.
Gracias

jhermiz

3564 Posts

Posted - 2006-01-13 : 16:11:14

quote:
Originally posted by rtutus

Thanks a lot the code above worked perfectly, now I can pass the Null value whenever I have an empty string.

However I still can t send an empty string "" either when using Varchar or Char in the DB type definition.
I get the same error : Prepared statment '(@AccAccFNam,......,@' expects parameter @AccAccFNam, which was not supplied. And that happens weither I use : ......New SqlParameter("@AccAccFNam", SqlDbType.VarChar, 50)) or .....New SqlParameter("@AccAccFNam", SqlDbType.VarChar, 50)).

The only time when I don t get an error is when I declare my variable as varianttype. Is that what u meant by your first sentence. Do u think the only way to pass an empty string to DB d be to declare a varianttype.

Thanks a lot amigo. Anyway, my code now seems to work thanks to your help.
Gracias

This is because you did not pass it an empty string...
String.Empty you can use as a string property and send that over to the db.

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]

rtutus
Aged Yak Warrior

522 Posts

Posted - 2006-01-13 : 18:59:06

Cool It s all working now, u guys re champs
muchas gracias and have a FFFAAAABBBBBBulous week end and happy coding

rtutus
Aged Yak Warrior

522 Posts

Posted - 2006-01-14 : 10:40:19

In my code I have to do this:

If objCust.prpContFrsNam = String.Empty Then pm.Value = String.Empty

Just putting: pm.Value=objCust.prpContFrsNam
fails and gives the same error in the case of an empty string. that s not normal, just putting pm.Value=objCust.prpContFrsNam should be enough and should take care of the case when the value is empty.string

it seems like if pm.Value=objCust.prpContFrsNam doesn t do the job when the tring s empty

thanks a lot

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources