Sending NULL to stored procedure? 1

[song2siren]

Hi

I'm trying to send values from an ASP.NET page to a stored procedure which inserts them into a SQL database. I've got a list of parameters as follows;

myCommand.Parameters.Add( "@FirstName", frmFirstName.text )
myCommand.Parameters.Add( "@Surname", frmFirstName.text )
etc...

However, I need some kind of function which will test all of these for NULL values and then send a NULL value to the stored procedure. At the moment the fields in my SQL database are blank where there is no value, rather than showing <NULL>.

I'm sure this is fairly simple - any help would be much appreciated.

Thanks

 

[DaZZleD]

SqlParameter mySQLParam = new SqlParameter ("@FirstName", varchar, 32);
mySQLParam.Value = DBNull.Value;

myCommand.Parameters.Add(mySQLParam);

--------------------------
"two wrongs don't make a right, but three lefts do" - the unknown sage

 

[song2siren]

Hi dazzled

Sorry, I don't really understand this. I need to set each empty parameter to null and allow this to be passed to the stored procedure. Maybe I should modify the stored procedure somehow? Or use some kind of function, a bit like this:

Public Function EmptyIf(ByVal value As Object) As String
If IsDBNull(value) Then Return ""
Return value.ToString
End Function

However, I'm not sure how to call this in from the parameters.

Thanks again.

 

[DaZZleD]

try something like

mySQLParam.Value = computeValue(stringValue);

where

Public Function computeValue (ByVal value As String) As Object
      If (value.length == 0) Then Return DBNull.Value
      Else return value;
End Function

from what you said you want to insret NULL where the string is empty (has 0 length). if I misunderstood, post back and correct me.

--------------------------
"two wrongs don't make a right, but three lefts do" - the unknown sage

 

[song2siren]

Yes, that's right. My code looks like this:

Sub SubmitBtn_Click(ByVal sender As Object, ByVal e As ImageClickEventArgs)

Dim cart As myStoreComponents.ShoppingCartDB = New myStoreComponents.ShoppingCartDB()

Dim myConnection As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("connString"))
Dim myCommand As SqlCommand = New SqlCommand("OrdersAdd", myConnection)

myCommand.CommandType = CommandType.StoredProcedure

myCommand.Parameters.Add( "@FirstName", frmFirstName.text )
myCommand.Parameters.Add( "@Surname", frmSurname.text )
etc...

myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()

End Sub

So, I'm not sure how to test if any of these parameters are empty, and, if so, send a NULL value to the stored procedure.

Sorry if I'm missing something really obvious here.

 

[DaZZleD]

use my function and instead of:

myCommand.Parameters.Add( "@FirstName", frmFirstName.text )
myCommand.Parameters.Add( "@Surname", frmSurname.text )

use

myCommand.Parameters.Add( "@FirstName", computeValue(frmFirstName.text) )
myCommand.Parameters.Add( "@Surname", computeValue(frmSurname.text) )

--------------------------
"two wrongs don't make a right, but three lefts do" - the unknown sage

 

[song2siren]

Yeah, just tried this but go an 'Expression Expected' error on this line:

If (value.length == 0) Then Return DBNull.Value

Thanks again

 

[DaZZleD]

sorry about this... replace == with = (CSharp's fault)

--------------------------
"two wrongs don't make a right, but three lefts do" - the unknown sage

 

[crazyboybert]

You can achieve this from within your stored procedure without writing any additional .NET code. Within a stored procedure a input parameter can havea default value, this doesn't have to be, but can be, null. You set default values for parameters like this

CREATE PROCEDURE SomeProcedure

@param1 int,
@param2 varchar(20),
@param3 varchar(10) = null,
@param4 int

AS

-- Your query

GO

Now you can keep your .NET code as is and if the value for the variable being used to populate the parameter is null (nothing in VB.NET) then the stored procedure would use the default value, null in this case.

The downside is it will not convert String.Empty into null/nothing so if as it looks like your populating your parameter collection straight from form values then you would still require some sort of helper method to identify empty strings and replace with null.

However, this is really useful when using a business object to populate the parameter collection rather than binding direct to control values. Say you have a customer class with id, name and phone properties you could bind the customer objects properties to the parameters regardless of wether they had a value or not and the stored procedure would handle the properties which were null/nothing.

I know this doesn't make much difference to your immediate problem but is a handy technique so I figured it was worth mentioning.

Rob


Every gun that is made, every warship launched, every rocket fired, signifies in the final sense a theft from those who hunger and are not fed, those who are cold and are not clothed - Eisenhower 1953

 

[song2siren]

Thanks for the tip crazyboybert - seems like a very efficient way to deal with the problem.

 

[hamking01]

I'm trying to do the same thing except I'm inserting null values from a datagrid into an Access stored query. I've used Dazzled's function:

Public Function computeValue (ByVal value As String) As Object
If (value.length = 0) Then
Return DBNull.Value
Else
return value
End If
End Function

But for some reason it still won't work. Could someone tell me what the problem is? Below is the relevant code:

Sub SelInsert (sender as object, e as DataGridCommandEventArgs)
Dim C20 = computeValue(e.Item.Cells(3).Text)
Dim paraC20 As New OleDbparameter("C20", &_ OleDbType.Integer)
paraC20.value = C20
cmd.Parameters.Add(paraC20)