Writing "Null" to a database

[Papa_Don]

Group, I've got several textboxes within a form that are set to link to a data column that are set to accept a "null" (blank) value. However, these columns are formated to receive numeric data.

When the user bypasses entering anything within those textboxes (as they should do), what is the correct way to handle the reporting?

I've written it this way (I've not tested it yet) and am wondering if this is acceptable:

        If txbSpecialCost.Text = "" Then
            Dim spclcost As String = ""
        Else
            Dim spclcost As Integer = Convert.ToInt32(txbSpecialCost.Text)
        End If

Hmmm.... This prompts a second question: Is there a way to determine if the characters within "txbSpecialCost" are Alph or Numeric characters? I'm thinking I need to add a message box to redirect them if they enter anything other than a numeric value.

Thanks for helping me with these two questions.

Don

[deceptikon]

When the user bypasses entering anything within those textboxes (as they should do), what is the correct way to handle the reporting?

If null values are acceptable in the database then you can write DBNull.Value when the string is empty.

Is there a way to determine if the characters within "txbSpecialCost" are Alph or Numeric characters?

Yes, though if you're looking to validate numeric input I'd suggest either a NumericUpDown control rather than TextBoxes, or validate by trying to convert to the numeric type with something like Int32.TryParse().

[Papa_Don]

Does "int32.TryParse()" bring back a True/False answer?

Regarding "DBNull.Value", do I write that as

If txbSpecialCost.Text = "" Then
            Dim spclcost As Integer = DBNull.Value

Thanks for your help.

Don

[deceptikon]

Does "int32.TryParse()" bring back a True/False answer?

http://msdn.microsoft.com/en-us/library/system.int32.tryparse.aspx

Regarding "DBNull.Value", do I write that as

No, you use it when actually writing to the database. Your title suggests that you're writing to a database, but you're focusing on what looks like validation code that runs first.

[Papa_Don]

Deceptikon,

Yes, I am writing to the database. But I do need to be prepared for 3 entries into the textbox that could hold the value to be written. In this instance, the database column is set to accept numerical data only, but it will accept "nothing". So I need to plan for that "(textbox1.text = "")" and write it to the datatable as "null", or plan for the errant alpha character that was entered in error and, lastly, a real numerical entry that will be written to the file. To do this I want to take the "Textbox1.Text" and write it as an integer as (in this case) to a variable called "spclcost". The value of "spclcost will be written to the file. That needs to be either a number or DBNull.Value.

So what is the correct syntax to assign DBNull.Value to the variable "spclcost"?

Thanks again for the help.

Don

[deceptikon]

So what is the correct syntax to assign DBNull.Value to the variable "spclcost"?

You don't do it that way. If the string is empty when you write to the database, use DBNull.Value instead of spclcost.

[Papa_Don]

deceptikon,

Sorry to appear to be hardheaded. I'm not grasping the meaning of using DBNull.Value. I'm new to coding, so this is rather foreign to me. Ultimately I need to finish this "If/Then" statement to ensure that, if "txbSpecialCost.Text" is empty, then the value of "spclcost" will also be empty. I then want to write the value of this variable to the database as either a numeric value or a null value - if that's possible.

If IsNumeric(txbSpecialCost.Text) = True Then
            Dim spclcost As Integer = Convert.ToInt32(txbSpecialCost.Text)
        Else
            ' I need to say spclcost = DBNull.Value.  But how do I do that?

Can you help me finish this?

Again, sorry to be a pest. I need to ensure that "spclcost" will be accepted by the database column that is assigned to accept a numeric value.

Don

[deceptikon]

' I need to say spclcost = DBNull.Value. But how do I do that?

You don't do that. You use either spclcost or DBNull.Value when assigning the database parameter. For example:

Dim parameter As New SqlParameter("SpecialCost", SqlDbType.Int, 0)

If IsNumeric(txbSpecialCost.Text) Then
    parameter.Value = Convert.ToInt32(txbSpecialCost.Text)
Else
    parameter.Value = DBNull.Value
End If

Then add the parameter to your SqlCommand object's Parameters collection. spclcost never gets assigned DBNull.Value unless you want to define it as type Object, but that would confuse the intention of the code in my opinion.

[Papa_Don]

OK. Now this makes sense. I see what you're doing here. If you will, explain what "SpecialCost" is, What a "SqlDbType.Int" is and what is the purpose of the zero in the parentheses. "SpecialCost" really confuses me as I don't know where this came from.

Thanks a bunch. This is begining to make some sense.

Don

[deceptikon]

If you will, explain what "SpecialCost" is, What a "SqlDbType.Int" is and what is the purpose of the zero in the parentheses

Judging by your questions, I'm guessing you aren't using parameterized queries. The first parameter is the column name of your table. The second is the type of the table, and the third is the maximum length allowed for the value. I didn't know the setup of your table, so I made up a column name: SpecialCost. Since the type of the parameter is a fixed size, the size argument is ignored, but it's still required in the constructor call. So I just use 0 since it's the most obvious dummy default.

[Papa_Don]

Deceptikon,

I wasn't going to use paramerterized queries..... I thought it was just an "extra step". However I just learned there's a good reason to use them. I think I will.

Thanks for the explainations. It's becoming much clearer to me!

Don

[deceptikon]

I wasn't going to use paramerterized queries..... I thought it was just an "extra step".

They are an extra step, but a valuable one nonetheless. ;)

[gian88r]

In the past I've used a similar approach but used minus numbers for the NULL values - that way you can you treat anything less than zero as NULL in one if statement.

This assumes that your ID's in the table are always positive though :)

[RvSon]

Did you get your question solved?