Inserting empty values as string to sql table

Question

I have a project which has a Class which has some members as string which equal "". My teacher had shown examples with Class members declared as String = "NULL". I try to insert to SQL and I get this message:

Error insertion... Error converting data type varchar to numeric.

I know many will suggest to use parameterized values for inserting but I like to finish the way I started this.

I have tried to cast query string Object values with CDec, CInt, Cbit and still no clue how to do it right because I got Exceptions for trying to cast an "".

Also, I have changed to default values of members like this;

Dim NumberOfPackage As String = ""

instead of;

Dim NumberOfPackage As String = "NULL"

Some Columns in SQL table had ALLOW NULL checkBox set to NOT allowed, I changed the design of the Table to make it easier for insertion.

'NULL' is different to a true NULL value. Are you trying to insert the text 'NULL' or an actual NULL value? NULL is the absence of a value — Rich Benner
– Rich Benner, Commented May 31, 2019 at 15:53
The error "Error converting data type varchar to numeric" means you are converting your string to a number somewhere. You should keep it all strings or all numbers. — the_lotus
– the_lotus, Commented May 31, 2019 at 15:56
If you are not using an ORM correct your code and use parameters. Just because you have created a bunch of wrong code, now that you know better correct it. SQL injection is not to be toyed with. — Mary
– Mary, Commented Jun 2, 2019 at 1:32

John Deligiannis · Accepted Answer · 2019-05-31 19:48:08Z

1

The field in database have been set to not null? I think that dbnull.value do what you want

answered May 31, 2019 at 19:48

John Deligiannis

531 silver badge9 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

djv · Accepted Answer · 2019-05-31 20:03:30Z

0

I think I understand what is happening. Your variable NumberOfPackage contains numbers but since you want to insert NULL sometime, you made it a string.

NumberOfPackage = "23"
NumberOfPackage = "NULL"

"INSERT INTO table (column) VALUES (" & NumberOfPackage & ");"

But since you use a string, you might be trying to do

"INSERT INTO table (column) VALUES ('" & NumberOfPackage & "');"

Which would cause the error on NULL since your column is a number and it is trying to do

"INSERT INTO table (column) VALUES ('NULL');"

Stick with my first example (without the ') if you really want to concatenate strings but everything would be much easier if you used parameters. By using parameters, it would be easier to keep NumberOfPackage as a decimal or a decimal? and do proper math with it.

edited May 31, 2019 at 20:03

djv

15.8k7 gold badges53 silver badges81 bronze badges

answered May 31, 2019 at 17:13

the_lotus

12.8k3 gold badges39 silver badges55 bronze badges

2 Comments

Mr. Tripodi Over a year ago

As far as I am concerned, if he does not want to take the time to learn and use parameters than it's not worth our time to extend on his substandard code.

djv Over a year ago

Maybe some peoples' time is less valuable than yours? But seriously, I have never seen the need for parameters - personally I'd rather use ORM

djv · Accepted Answer · 2019-05-31 20:02:56Z

0

To insert a NULL value, it must be without the apostrophes like @RichBenner and @the_lotus state:

Dim myCommand As String 
myCommand = "INSERT INTO dbo.Foo (Foo_Text) VALUES (NULL);"
'The query for an empty string would look like this
myCommand = "INSERT INTO dbo.Foo (Foo_Text) VALUES ('');"

edited May 31, 2019 at 20:02

djv

15.8k7 gold badges53 silver badges81 bronze badges

answered May 31, 2019 at 17:03

Christoph

3,7092 gold badges24 silver badges33 bronze badges

Comments

Mary · Accepted Answer · 2019-06-02 02:23:34Z

col1 in my Builder database is datatype int and nulls are allowed. This is how it is done with Parameters. TextBox1 will hold the number of packages and TextBox2 the name. I used the object datatype so it could hold either DBNull.Value or an Integer.

Private Sub InsertRecord()
    Dim myValue As Object
    Dim myInt As Integer
    If Integer.TryParse(TextBox1.Text, myInt) Then
        myValue = myInt
    Else
        myValue = DBNull.Value            
    End If

    Using cn As New SqlConnection(My.Settings.BuildersConnectio)
        Using cmd As New SqlCommand("Insert Into Builders (BuilderName, col1) VALUES (@Name, @NoOfPackages);", cn)
            cmd.Parameters.Add("@Name", SqlDbType.VarChar, 100).Value = TextBox2.Text
            cmd.Parameters.Add("NoOfPackages", SqlDbType.Int).Value = myValue
            cn.Open()
            cmd.ExecuteNonQuery()
        End Using
    End Using
End Sub

Collectives™ on Stack Overflow

Inserting empty values as string to sql table

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related