[RESOLVED] how to enter null value ?

[hlsc1983]

if textbox is not enabled , a null value must be entered in a database.

i tried this but there is an error " value of type DBnull cannot be converted to string"

If pretTxt.Enabled = False Then pretTxt.Text = DBNull .value

and i am trying to do this so that i can insert something like this.

 If pretTxt.Enabled = False Then pretTxt.Text = DBNull .value

        Dim cmd As New SqlCommand("insert into Marks_master (subject_id, student_id, T_PA_marks, T_ET_marks, P_PA_marks,P_ET_marks) VALUES(@si,@stdid, @tet ,@tpa, @pet, @ppa)", con)
        cmd.Parameters.AddWithValue("si", subjectLbl.Text)
        cmd.Parameters.AddWithValue("stdid", studentidTxt.Text)
        cmd.Parameters.AddWithValue("tet", thetTxt.Text)
        cmd.Parameters.AddWithValue("tpa", thpaTxt.Text)
        cmd.Parameters.AddWithValue("pet", pretTxt.Text)
        cmd.Parameters.AddWithValue("ppa", prpaTxt.Text)

       Try
            cmd.ExecuteNonQuery()
            MessageBox.Show("Record added")
        Catch ex As Exception
            MessageBox.Show("Error while adding record")
        End Try
        con.Close()

if textbox is not enabled then null value must be inserted in the table.

[omundodogabriel]

on vb.net, a null value is Nothing.
Example:

SomeVar = Nothing

Althrough im not sure if I understood what youre trying to do. What is DBNull?

[techgnome]

if textbox is not enabled , a null value must be entered in a database.

i tried this but there is an error " value of type DBnull cannot be converted to string"

If pretTxt.Enabled = False Then pretTxt.Text = DBNull .value

and i am trying to do this so that i can insert something like this.

 If pretTxt.Enabled = False Then pretTxt.Text = DBNull .value

        Dim cmd As New SqlCommand("insert into Marks_master (subject_id, student_id, T_PA_marks, T_ET_marks, P_PA_marks,P_ET_marks) VALUES(@si,@stdid, @tet ,@tpa, @pet, @ppa)", con)
        cmd.Parameters.AddWithValue("si", subjectLbl.Text)
        cmd.Parameters.AddWithValue("stdid", studentidTxt.Text)
        cmd.Parameters.AddWithValue("tet", thetTxt.Text)
        cmd.Parameters.AddWithValue("tpa", thpaTxt.Text)
        cmd.Parameters.AddWithValue("pet", pretTxt.Text)
        cmd.Parameters.AddWithValue("ppa", prpaTxt.Text)

       Try
            cmd.ExecuteNonQuery()
            MessageBox.Show("Record added")
        Catch ex As Exception
            MessageBox.Show("Error while adding record")
        End Try
        con.Close()

if textbox is not enabled then null value must be inserted in the table.

Two ways to deal with this... 1) use .Parameters.Add rather than .AddWithValue, but this means creating the parameter, setting its type and size, etc., then setting the value ... or 2) after adding the parameters, before executing the SQL, you update the parameter value.

cmd.Parameters("pet").Value = If(preText.Enabled, pretTxt.Text, DBNull.Value))

I use a similar approach to ensure that the parameter datatype gets set right, then go back and set NULLs as needed.

in looking back over that, I guess it's a two step either way.

on vb.net, a null value is Nothing.
Example:

SomeVar = Nothing

Althrough im not sure if I understood what youre trying to do. What is DBNull?

Null and Nothing are not the same thing. Nothing means just that, it is nothing... Null on the other hand means unknown. It's used in databases to represent the lack of a value. Some people think it means the same as an empty value, but it's more than that... because in some cases a blank could be a valid value... and distinctly different from a null value. DBNull is .NET's implementation of the NULL concept... I'm not sure if it's a structure or a class, but it is shared, so you can't declare something "as DBNull" but if you want to compare someting from the database to see if it is null or not, you use DBNull.Value.

-tg

[kebo]

I'm not sure if it's a structure or a class

It's a non inheritable class, with the Value field as a shared member.

[hlsc1983]

Two ways to deal with this... 1) use .Parameters.Add rather than .AddWithValue, but this means creating the parameter, setting its type and size, etc., then setting the value ... or 2) after adding the parameters, before executing the SQL, you update the parameter value.

cmd.Parameters("pet").Value = If(preText.Enabled, pretTxt.Text, DBNull.Value))

I use a similar approach to ensure that the parameter datatype gets set right, then go back and set NULLs as needed.

in looking back over that, I guess it's a two step either way.



Null and Nothing are not the same thing. Nothing means just that, it is nothing... Null on the other hand means unknown. It's used in databases to represent the lack of a value. Some people think it means the same as an empty value, but it's more than that... because in some cases a blank could be a valid value... and distinctly different from a null value. DBNull is .NET's implementation of the NULL concept... I'm not sure if it's a structure or a class, but it is shared, so you can't declare something "as DBNull" but if you want to compare someting from the database to see if it is null or not, you use DBNull.Value.

-tg

k i am using the this:

 
        Dim cmd As New SqlCommand("insert into Marks_master (subject_id, student_id, T_PA_marks, T_ET_marks, P_PA_marks,P_ET_marks) VALUES(@si,@stdid, @tet ,@tpa, @pet, @ppa)", con)
        cmd.Parameters.AddWithValue("si", subjectLbl.Text)
        cmd.Parameters.AddWithValue("stdid", studentidTxt.Text)
        cmd.Parameters.AddWithValue("tet", thetTxt.Text)
        cmd.Parameters.AddWithValue("tpa", thpaTxt.Text)
        cmd.Parameters.AddWithValue("pet", pretTxt.Text)
        cmd.Parameters.AddWithValue("ppa", prpaTxt.Text)
        cmd.Parameters("pet").Value = If(pretTxt.Enabled, pretTxt.Text, DBNull.Value)

and geeting this error

Cannot infer a common type for the second and third operands of the 'If' operator. One must have a widening conversion to the other.

[techgnome]

Ungh... ok... well then try this:

        cmd.Parameters.AddWithValue("pet", pretTxt.Text)
        cmd.Parameters.AddWithValue("ppa", prpaTxt.Text)
        if not pretTxt.Enabled Then
            cmd.Parameters("pet").Value = DBNull.Value
        End If

You'd think there was an easier way to deal with this... oh well...

And I'd swear the pevious example works... I may have to go back and see just exactlky what I did.

-tg

[omundodogabriel]

Null and Nothing are not the same thing. Nothing means just that, it is nothing... Null on the other hand means unknown. It's used in databases to represent the lack of a value. Some people think it means the same as an empty value, but it's more than that... because in some cases a blank could be a valid value... and distinctly different from a null value. DBNull is .NET's implementation of the NULL concept... I'm not sure if it's a structure or a class, but it is shared, so you can't declare something "as DBNull" but if you want to compare someting from the database to see if it is null or not, you use DBNull.Value.

gee, I always thought that the Nothing in vb,net was the equivalent to null in c#.

[techgnome]

There's null and there's Null (or DBNull) ... in this case, it's taken in the context of Null, from a database - thus the name DBNull ...

In the C-faimilies null is a representation of char(0), and used for a variety of things - two most notable - releasing pointers (setting a pointer to null effectively clears out the address... this is where it is similar to Nothing in VB) and for terminating strings ala "null-terminated string".

It's all about the context.

-tg

[hlsc1983]

It works...you are a life saver.!!