SQL and VB.NET 2005

How would I insert values using a command string where one of the values is null but the field in the DB will accept null?

For example:

VB Code:

USE MyDB
INSERT INTO MyTable (Name,Description,Notes)
VALUES ('myName','myDescription',' ')

The last Value is null but I keep getting a truncated error when executing the command. Now keep in mind, not all of my notes will be null values.


Thanks in advance!!

I believe you can use dbnull

VB Code:

USE MyDB
INSERT INTO MyTable (Name,Description,Notes)
If myNotes = "" then
   VALUES ('myName','myDescription','DBNull.Value')
Else
   VALUES ('myName','myDescription','myNotes')
End If

SQL Server isn't going to know what DBNull.Value means.... That's a .NET thing....

If you want to insert a NULL ... use the NULL keyword....
It would help to know if you want the solution in VB, or in SQL... you've mixed your example, and the one above makes the problem worse.

-tg

Sorry about that. I am assigning the command to a string and using the SQLCommand.ExecuteNonQuery method to work the database on this issue. I know it is not the most elegant way to do it but I am the type that needs to know how something works first before I can excel at it.

Would I use NULL from VB or is that an SQL keyword?

This is what I get:

Message "String or binary data would be truncated.

Let's try this; post the code that creates the SQL string, and the code that is executing it.

-tg

[Highlight=VB]

Dim CommandString As String
Dim SQLCmd as SQLClient.SQLCommand

CommandString = "USE MyDB " & _
" INSERT INTO myTable " & _
" ( Name, Description, Notes ) " & _
" VALUES ('Jerry','A Kewl D00d',' ')"

SQLCmd = New SqlClient.SqlCommand(CommandString, mySQLConnection)
SQLCmd.ExecuteNonQuery()


[\VBCODE]

This is how I do it.

Note: You may need to create a function for determining when to pass Null to the DB. I have a function call EmptyToNull that checks the parameter passed in to see if it is an empty string and returns back DBNull.Value.)

[Highlight=VB]
Dim CommandString As String
Dim SQLCmd as SQLClient.SQLCommand

CommandString = "USE MyDB " & _
" INSERT INTO myTable " & _
" ( Name, Description, Notes ) " & _
" VALUES (?,?,?)"

SQLCmd = New SqlClient.SqlCommand(CommandString, mySQLConnection)
SQLCmd.Parameters.Add(New SqlParameter("@Name", SqlDbType.VarChar)).Value = "Jerry"
SQLCmd.Parameters.Add(New SqlParameter("@Description", SqlDbType.VarChar)).Value = "A Kewl D00d"
SQLCmd.Parameters.Add(New SqlParameter("@Notes", SqlDbType.VarChar)).Value = DBNull.Value
SQLCmd.ExecuteNonQuery()
[\VBCODE]

Ok, n00b13 here, what does the "@" mean in front of the Name/Description/Notes variable names?

That is the way to declare variables in SQL Server. You preface it with an @ symbol.

I keep getting errors on the ?'s. Am I supposed to leave those blank?

You're doing too much in your command string..... Dump the USe myDB part... you don't need that. The database will be specified based on the connectionString in the connection.

-tg

I am still getting the "Incorrect Syntax near ?" error, even without the USE command.

Anymore thoughts?

Ok, I figured out to put the variable names in the VALUES statement and now that part works. I am still back to the same problem as before where it tells me that the string or binary data will be truncated. What does this mean and how can I get past it? Will I need to just make a lengthy statement of if...then in order to insert data dynamically accorrding to non-null values?

Thanks again for all the help!

If you want to insert a null value, then just don't specify it as one of the parameters:

Code:

---

USE MyDB
INSERT INTO MyTable (Name,Description)
VALUES ('myName','myDescription')

---

That will insert the values into Name and Description, and nulls into all of the other fields in the table.

In relation to the truncation warning, how is the table setup. What is the data type for each field that you have? I'm guessing you have set some very short field length or something?

All of my fields are set at varChar. I thought this would make sure to shorten or lengthen the field according to the stored values. The above code will work but I need to have it enter the myNotes into the Notes field of the same table when myNotes are present.

For example, I have 3 people, John, Joe, and Mary. They all have a name and description but only Joe has notes. How would I be able to input all 3 in the same statement without picking out myNotes in a seperate case statement?

I hope I explained this ok.

Thanks

While varchars will store only the text you send it (as opposed to char fields which pad with spaces)... varchar fields do have a limit. Depending on what you set the length to when defining the table structure, you might be exceeding the length. This will generate the error you are seeing.

-tg

What data type should I be using? I am not sending strings of more then 25 characters at the moment. At any given time, my strings will be no more then 50 characters or so. The maximum an SQL field will take is 255 if I remember correctly and there is no way I can exceed that with my current values.

Well, I take that back. It looks like my CREATE statement of this table turned it into a varChar(1) datatype. I am guessing I need to expand that to 255 when I declare them huh? Very sorry for the confusions, I am trying to get up to speed on SQL Server.

Thanks to ALL who helped me out!!!

You could also try the ntext data type

BTW: You can have more than 255 for your varchar fields... We've got some that are 4k... depends on what you want to put into it. The make byte size for any given row in SQL server is 8k.

-tg

What about ntext?

the only advantage ntext has is the ability to store unicode text. Since unicode takes two bytes per char, it actualy cuts the capacity in half, since it still cannot exceed 8k per row.

-tg