Error sql parameter SqlDbType.Float won't accept null value, AND/OR variable as DOUBLE won't accept

  • Thread starter Thread starter JennL
  • Start date Start date
J

JennL

I'm using a grid and when a user edits the grid, if they leave a field
null I get a system.formatException error (string not in correct
format).

The stored procedure allows nulls for this float field.


Here is my code:

'***********8I get the data here
'ERROR1: when I try to get strAct as Double and a NULL value is
entered, it returns an error
'*****************************
Dim strAct as String
=CType(objArgs.Item.FindControl("txtAct"),TextBox).Text
Dim strCons as Double =CType(objArgs.Item.FindControl("txtCons"),
TextBox).Text

'***** here's a snippet from the sp **********
Dim Cmd As New SqlCommand("put_sales", Conn)
Cmd.CommandType = CommandType.StoredProcedure
'Create and add a parameter to Parameters collection for the stored
procedure.
Cmd.Parameters.Add(New SqlParameter("@ccs_id",
SqlDbType.Int))Cmd.Parameters.Add(New SqlParameter("@act",
SqlDbType.Float))
Cmd.Parameters.Add(New SqlParameter("@cons", SqlDbType.Float))
Cmd.Parameters.Add(New SqlParameter("@mode", SqlDbType.Char))

'Assign the search value to the parameter.
Cmd.Parameters("@ccs_id").Value = ccs_id
Cmd.Parameters("@act").Value = strAct
Cmd.Parameters("@cons").Value = strCons
Cmd.Parameters("@mode").Value = "U"

Conn.Open()
Cmd.Prepare()
' Execute the prepared SQL statement

Cmd.ExecuteNonQuery()
Conn.Close()

'******and a snippet from the stored procedure:
create procedure dbo.put_sales (
@ccs_id int,
@act float=NULL,
@cons float=NULL,
@mode char(1)
)
AS
BEGIN....

'********************888
I'm stumped. Please help!
 
where do I do this? When a user leaves a field blank, I grab the contents of
the field (which is NULL if empty, right?). Then when I try to add to
parameter as FLoat (which is what it should be) then it gives me an error
since it is null. (?)



Miha Markic said:
Instead of assigning null you should assign DBNull.Value

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

JennL said:
I'm using a grid and when a user edits the grid, if they leave a field
null I get a system.formatException error (string not in correct
format).

The stored procedure allows nulls for this float field.


Here is my code:

'***********8I get the data here
'ERROR1: when I try to get strAct as Double and a NULL value is
entered, it returns an error
'*****************************
Dim strAct as String
=CType(objArgs.Item.FindControl("txtAct"),TextBox).Text
Dim strCons as Double =CType(objArgs.Item.FindControl("txtCons"),
TextBox).Text

'***** here's a snippet from the sp **********
Dim Cmd As New SqlCommand("put_sales", Conn)
Cmd.CommandType = CommandType.StoredProcedure
'Create and add a parameter to Parameters collection for the stored
procedure.
Cmd.Parameters.Add(New SqlParameter("@ccs_id",
SqlDbType.Int))Cmd.Parameters.Add(New SqlParameter("@act",
SqlDbType.Float))
Cmd.Parameters.Add(New SqlParameter("@cons", SqlDbType.Float))
Cmd.Parameters.Add(New SqlParameter("@mode", SqlDbType.Char))

'Assign the search value to the parameter.
Cmd.Parameters("@ccs_id").Value = ccs_id
Cmd.Parameters("@act").Value = strAct
Cmd.Parameters("@cons").Value = strCons
Cmd.Parameters("@mode").Value = "U"

Conn.Open()
Cmd.Prepare()
' Execute the prepared SQL statement

Cmd.ExecuteNonQuery()
Conn.Close()

'******and a snippet from the stored procedure:
create procedure dbo.put_sales (
@ccs_id int,
@act float=NULL,
@cons float=NULL,
@mode char(1)
)
AS
BEGIN....

'********************888
I'm stumped. Please help!
Click to expand...
Click to expand...
 
U¿ytkownik "JennL said:
where do I do this? When a user leaves a field blank, I grab the contents
of
the field (which is NULL if empty, right?). Then when I try to add to
parameter as FLoat (which is what it should be) then it gives me an error
since it is null. (?)
Click to expand...

Empty field => field.Text = ""
"" <> DBNull.Value

Grzegorz
 
Found the fix:
Double doesn't want NULL as value, so had to set it equal to string.
Dim strAct as String =CType(objArgs.Item.FindControl("txtAct"),TextBox).Text

Then, if NOT null, convert, otherwise use NULL
if strAct <> "" then
Cmd.Parameters("@act").Value =System.Convert.ToDouble(strActual)
else
Cmd.Parameters("@actl").Value = DBNull.Value
end if



JennL said:
where do I do this? When a user leaves a field blank, I grab the contents of
the field (which is NULL if empty, right?). Then when I try to add to
parameter as FLoat (which is what it should be) then it gives me an error
since it is null. (?)



Miha Markic said:
Instead of assigning null you should assign DBNull.Value

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

JennL said:
I'm using a grid and when a user edits the grid, if they leave a field
null I get a system.formatException error (string not in correct
format).

The stored procedure allows nulls for this float field.


Here is my code:

'***********8I get the data here
'ERROR1: when I try to get strAct as Double and a NULL value is
entered, it returns an error
'*****************************
Dim strAct as String
=CType(objArgs.Item.FindControl("txtAct"),TextBox).Text
Dim strCons as Double =CType(objArgs.Item.FindControl("txtCons"),
TextBox).Text

'***** here's a snippet from the sp **********
Dim Cmd As New SqlCommand("put_sales", Conn)
Cmd.CommandType = CommandType.StoredProcedure
'Create and add a parameter to Parameters collection for the stored
procedure.
Cmd.Parameters.Add(New SqlParameter("@ccs_id",
SqlDbType.Int))Cmd.Parameters.Add(New SqlParameter("@act",
SqlDbType.Float))
Cmd.Parameters.Add(New SqlParameter("@cons", SqlDbType.Float))
Cmd.Parameters.Add(New SqlParameter("@mode", SqlDbType.Char))

'Assign the search value to the parameter.
Cmd.Parameters("@ccs_id").Value = ccs_id
Cmd.Parameters("@act").Value = strAct
Cmd.Parameters("@cons").Value = strCons
Cmd.Parameters("@mode").Value = "U"

Conn.Open()
Cmd.Prepare()
' Execute the prepared SQL statement

Cmd.ExecuteNonQuery()
Conn.Close()

'******and a snippet from the stored procedure:
create procedure dbo.put_sales (
@ccs_id int,
@act float=NULL,
@cons float=NULL,
@mode char(1)
)
AS
BEGIN....

'********************888
I'm stumped. Please help!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top