How to insert NULL values in a table in VB.net

[Dino M. Buljubasic]

I'd like to be able to insert some values in a table. The values are text
box fields. If a text box field is empty, then NULL should be inserted in a
table field.

e.g.

strSQL = "INSERT INTO calOwners(first_name, last_name, " & _
"username, password, " & _
"email_address) " & _
"VALUES ('" & txtFirst.Text & "', '" & _
txtLast.Text & "', '" & _
txtUserName.Text & "', '" & _
TtxtPassword.Text & "', '" & _
txtEmail.Text & ")"

if any of txt fields is empty, instead of empty string I would like to
insert NULL value.

How to do that?

Thank you,
Dino

 

[Bernie Yaeger]

Hi Dino,

irow("email_address") = DBNull.Value

HTH,

Bernie Yaeger

 

[Dino M. Buljubasic]

Hi Bernie,

thanks for your reply.
I get error "Line 1, incorect sintax near ','

Here is what I am doing:

if txtEmail.text <> string.Empty then
strSQL = "INSERT INTO calOwners(first_name, last_name, " & _
"username, password, " & _
"email_address) " & _
"VALUES ('" & txtFirst.Text & "', '" & _
txtLast.Text & "', '" & _
txtUserName.Text & "', '" & _
txtPassword.Text & "', '" & _
txtEmail.Text & ")"
else
strSQL = "INSERT INTO calOwners(first_name, last_name, " & _
"username, password, " & _
"email_address) " & _
"VALUES ('" & txtFirst.Text & "', '" & _
txtLast.Text & "', '" & _
txtUserName.Text & "', '" & _
txtPassword.Text & "', " & _
DBNull.Value & ")"
end if

What am I doing wrong?

Thank you,
Dino

--


-------------------------------------------------------------------------
FIGHT BACK AGAINST SPAM!
Download Spam Inspector, the Award Winning Anti-Spam Filter
http://mail.giantcompany.com

Hi Dino,

irow("email_address") = DBNull.Value

HTH,

Bernie Yaeger

I'd like to be able to insert some values in a table. The values are text
box fields. If a text box field is empty, then NULL should be inserted

in

 

[Bob Grommes]

Dino,

Bernie is talking about assinging database null to fields in a DataRow, or
to parameters in a parameterized SQL statement.

What you are doing is building an SQL string.

In this case you want to end up with syntax like:

INSERT INTO calOwners ... VALUES ('Joe','Smith','JSmith','Secret',NULL)

Since EMail appears to be the only field that can be null, I would do it
something like this:

if txtEmail.text <> string.Empty then
strEmail = "'" & txtEmail.text & "'"
else
strEmail = "NULL"
end if

strSQL = "INSERT INTO calOwners(first_name, last_name, " & _
"username, password, " & _
"email_address) " & _
"VALUES ('" & txtFirst.Text & "', '" & _
txtLast.Text & "', '" & _
txtUserName.Text & "', '" & _
txtPassword.Text & "'," & _
strEmail & ")"

It's just string-building excerize -- either you need the text value inside
apostrophes, or you need the constant NULL, without the apostrophes.

--Bob

 

[Dino M. Buljubasic]

Thanks Bob,

I think I tried that. I'll try it again but I think that will insert NULL
value as a string.
Anyways, I'll give it a try again.

Regards, Dino

 

[Dino M. Buljubasic]

Thanks Bob,

I tried it and it works just fine. Thanks for your help.

Dino

 

[Bob Grommes]

Yes ... the key is to omit the quotes around NULL, you only need them if
you're actually putting a string in there.

--Bob