DBNull problem

6 Jul 2006 1:29 AM

Liverpool fan

Hi all and thanks to those who respond to this.
I have a data maintenance form which I am using to update data in my
database. In the update routine I use the following code;

ssql = "UPDATE Email_Usage SET [date] = @Date," & _
                                          "pagesgenerated =
@PagesGenerated," & _
                                          "pagesemailed =
@PagesEmailed," & _
                                          "customersemailed =
@CustomersEmailed " & _
                   "WHERE id = @ID"
Dim params(4) As SqlClient.SqlParameter
params(0) = New SqlClient.SqlParameter("@Date", dtDate)
params(1) = New SqlClient.SqlParameter("@PagesGenerated",
dr("pagesgenerated"))
params(2) = New SqlClient.SqlParameter("@PagesEmailed",
dr("pagesemailed"))
params(3) = New SqlClient.SqlParameter("@CustomersEmailed",
dr("customersemailed"))
params(4) = New SqlClient.SqlParameter("@ID", dr("id"))

dr is a DataRow

All is well when I supply values for fields on the form. If the user
does not enter a value for Customers Emailed, I write DBNull.Value into
dr instead. The problem is that when I execute the query I get an error
that I must declare the variable @CustomersEmailed. Why will it not
accept DBNull.Value ?

6 Jul 2006 3:22 AM

Steven Nagy

Is it possible that you need a space after the last parameter is
assigned?

Currently your third parameter is 'customersemailed' but there is no
space before your WHERE statement. Could this mean that when the final
sql query is constructed, there is no space between the third parameter
and the WHERE clause?

I only responded because I support LFC as well. :)

Liverpool fan wrote:
Show quoteHide quote

> Hi all and thanks to those who respond to this.
> I have a data maintenance form which I am using to update data in my
> database. In the update routine I use the following code;
>
> ssql = "UPDATE Email_Usage SET [date] = @Date," & _
>                                           "pagesgenerated =
> @PagesGenerated," & _
>                                           "pagesemailed =
> @PagesEmailed," & _
>                                           "customersemailed =
> @CustomersEmailed " & _
>                    "WHERE id = @ID"
> Dim params(4) As SqlClient.SqlParameter
> params(0) = New SqlClient.SqlParameter("@Date", dtDate)
> params(1) = New SqlClient.SqlParameter("@PagesGenerated",
> dr("pagesgenerated"))
> params(2) = New SqlClient.SqlParameter("@PagesEmailed",
> dr("pagesemailed"))
> params(3) = New SqlClient.SqlParameter("@CustomersEmailed",
> dr("customersemailed"))
> params(4) = New SqlClient.SqlParameter("@ID", dr("id"))
>
> dr is a DataRow
>
> All is well when I supply values for fields on the form. If the user
> does not enter a value for Customers Emailed, I write DBNull.Value into
> dr instead. The problem is that when I execute the query I get an error
> that I must declare the variable @CustomersEmailed. Why will it not
> accept DBNull.Value ?

6 Jul 2006 5:46 AM

Liverpool fan

This gets more and more curious.

If I change DBNull.Value to SQLInt32.Null then it works, however I get
a casting error when trying to insert the latter into the field in the
data row. The column in the data row will accept DBNull.Value but not
SQLInt32.Null, and the parameters accept the latter as a value but not
the former. Any ideas?

Thanks for the post Steven. If you look closely you will see there is a
space after the last parameter.

Do you think Stevie G sgould be the new England captain then. I do. He
plays with the kind of passion that the team is lacking I think.

6 Jul 2006 10:49 PM

Steven Nagy

Sorry I didn't see the space. Wearing my glasses this time I promise.
Not sure about your curious findings. But in knowing that now can you
you organise to place the correct null value where required? We use a
custom data layer and in the background it uses the SqlInt32.Null
syntax for adding null parameters. But when wanting a value in a
datarow to be null, it simply doesn't assign a value.. . (not assigning
a null value, just don't assign anything). Not sure if this helps your
cause.

RE: England
At least Gerrard would never break down and cry like Becks. He has
captain experience, but why would you want to captain a big group of
girls? He would be a good captain but I don't think he should do it.
Coming from Australia, I can be objective about England's international
team. I think captaining is not England's biggest problem, its the
manager. Luckily he's gone now. I mean, WHO subs on a non-penalty taker
in the 120th minute of the game!!!!