Passing Null Values to a SP doesn't work for me!!!

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

Passing Null Values to a SP doesn't work for me!!!

Author

Topic

iancuct
Yak Posting Veteran

73 Posts

Posted - 2006-11-15 : 17:43:09

private sub Example
Dim Parameters(12) As SqlParameter
Dim dr As SqlDataReader

Parameters(0) = (New SqlParameter("@invoice_num", NonEmpty(SI_invoice_num.Text)))
Parameters(1) = (New SqlParameter("@ro_task_num", NonEmpty("")))
Parameters(2) = (New SqlParameter("@po_task_num", NonEmpty("")))
dr = SqlHelper.ExecuteReader(ConfigurationSettings.AppSettings("con_Claims"), CommandType.StoredProcedure, "usp_JOS_SearchInvoices", Parameters)

Me.gridInvoices.Visible = True
Me.gridInvoices.DataSource = dr
Me.gridInvoices.DataBind()

dr.Close()
dr = Nothing
End Sub

THIS WILL WORK

However The following will not work!!!

-----------------------------------------------

...
Parameters.Add(New SqlParameter("@invoice_num", DbType.String,NonEmpty(SI_invoice_num.text)))
Parameters.Add(New SqlParameter("@ro_task_num", DbType.Int32,NonEmpty(SI_ro_task_num.text)))
Parameters.Add(New SqlParameter("@po_task_num", DbType.Int32,NonEmpty(SI_po_task_num.text)))
Parameters.Add(New SqlParameter("@invoice_date", DbType.DateTime,NonEmpty(SI_invoice_date.text)))
....

It will give the following Error:

Cast from type 'DBNull' to type 'Integer' is not valid.

Doing the following will also work!!!
Parameters(0) = (New SqlParameter("@invoice_num", DbType.Int32))
Parameters(0).Value=NonEmpty("") or DBnull.Value

Do I Really have to do this on 2 lines!!! or leave the type out?? why doesn't this work
all in one line, maybe is just my computer. Can anyone please help?
If i leave the type out, like in the first example, is there a performance penalty?

function that returns dbnull.value in case the object or string is nothing ("")
----------------------
Public Function NonEmpty(ByVal x As Object)
If (x Is DBNull.Value) Or (x Is Nothing) Then
Return DBNull.Value
Else
If ((x.GetType.Name = "String")) Then
If ((Trim(x) = "") Or x = " <Add New>" Or x = "All Ports" Or x = "All Years") Then
Return DBNull.Value
Else
Return x
End If
Else
Return x
End If
End If
End Function
-----------------

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-15 : 21:42:36

What are you trying to do with this? The third parameter to the SqlParameter constructor is an Integer value that indicates the length of the value (used only for variable length types like varchar), so your code really doesn't make much sense.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2006-11-15 : 22:35:16

Post your stored procedure

You can setup your procedure parameter to have a default of null so if the value is null you just don't pass the parameter.

As far as I know you can't convert a DBNull to an Integer.

iancuct
Yak Posting Veteran

73 Posts

Posted - 2006-11-16 : 13:54:36

quote:
Originally posted by snSQL

What are you trying to do with this? The third parameter to the SqlParameter constructor is an Integer value that indicates the length of the value (used only for variable length types like varchar), so your code really doesn't make much sense.

Thanks, you are right the third value is the variable length its not the value.

The overloaded constructor for SqlParameter takes the value parameter only in 2 locations

as location 2 (when u call with paramter name, and value)
as location 10 (when all paramters are called)
-----------------------------

its never used in location 3... :(

i thought that if i just put it there like in my example it will just know that its the value , but it doens't ... it expects a different parameter in location 3.

i tried using this notation but it does't work

new SqlParamter("@variable",sqltype.int32,,,,,,,,value)

I guess i have to do it in 2 lines.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-16 : 14:06:54

quote:
i thought that if i just put it there like in my example it will just know that its the value , but it doens't ...

Just as well - have you seen Terminator, or The Matrix!!!

iancuct
Yak Posting Veteran

73 Posts

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

Yes, why?

for some reason this won't work either... although i think it should.

Parameters(1) = (New SqlParameter("@ro_task_num", DbType.Int32)).Value(dbnull.value)
or
Parameters(1) = (New SqlParameter("@ro_task_num", DbType.Int32)).Value=dbnull.value

I think it should work because there is a constructur that takes 2 paramters (name, type)
and when executed by itself it works. but if at the end of the line you add .Value()
it doesn't

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-16 : 15:33:02

quote:
Yes, why?

That's what happens when machines start thinking for themselves, which is what you wanted to have happen

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-16 : 15:35:12

quote:
for some reason this won't work either... although i think it should.

Parameters(1) = (New SqlParameter("@ro_task_num", DbType.Int32)).Value(dbnull.value)
or
Parameters(1) = (New SqlParameter("@ro_task_num", DbType.Int32)).Value=dbnull.value

I think it should work because there is a constructur that takes 2 paramters (name, type)
and when executed by itself it works. but if at the end of the line you add .Value()
it doesn't

The problem is then you are assigning the Value to the Parameter collection, not the Parameter.
I know hard-core programmers used to get a kick out writing their entire application in one line of code, and I don't want to encourage inefficient code, but don't you think you could have created 500 parameters in two lines of code by now?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources