1,105,534 Community Members

Writing "Null" to a database

Member Avatar
doncwilson_1
Junior Poster
188 posts since Feb 2013
Reputation Points: 14 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 2 [?]
 
0
 

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

Member Avatar
deceptikon
Eternally Awesome
4,690 posts since Jan 2012
Reputation Points: 1,341 [?]
Q&As Helped to Solve: 688 [?]
Skill Endorsements: 104 [?]
Administrator
Featured
 
1
 

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().

Member Avatar
doncwilson_1
Junior Poster
188 posts since Feb 2013
Reputation Points: 14 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 2 [?]
 
0
 

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

Member Avatar
deceptikon
Eternally Awesome
4,690 posts since Jan 2012
Reputation Points: 1,341 [?]
Q&As Helped to Solve: 688 [?]
Skill Endorsements: 104 [?]
Administrator
Featured
 
1
 

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.

Member Avatar
doncwilson_1
Junior Poster
188 posts since Feb 2013
Reputation Points: 14 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 2 [?]
 
0
 

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

Member Avatar
deceptikon
Eternally Awesome
4,690 posts since Jan 2012
Reputation Points: 1,341 [?]
Q&As Helped to Solve: 688 [?]
Skill Endorsements: 104 [?]
Administrator
Featured
 
1
 

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.

Member Avatar
doncwilson_1
Junior Poster
188 posts since Feb 2013
Reputation Points: 14 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 2 [?]
 
0
 

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

Member Avatar
deceptikon
Eternally Awesome
4,690 posts since Jan 2012
Reputation Points: 1,341 [?]
Q&As Helped to Solve: 688 [?]
Skill Endorsements: 104 [?]
Administrator
Featured
 
1
 

' 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.

Member Avatar
doncwilson_1
Junior Poster
188 posts since Feb 2013
Reputation Points: 14 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 2 [?]
 
0
 

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

Member Avatar
deceptikon
Eternally Awesome
4,690 posts since Jan 2012
Reputation Points: 1,341 [?]
Q&As Helped to Solve: 688 [?]
Skill Endorsements: 104 [?]
Administrator
Featured
 
1
 

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.

Member Avatar
doncwilson_1
Junior Poster
188 posts since Feb 2013
Reputation Points: 14 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 2 [?]
 
0
 

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

Question Answered as of 1 Year Ago by deceptikon
Member Avatar
deceptikon
Eternally Awesome
4,690 posts since Jan 2012
Reputation Points: 1,341 [?]
Q&As Helped to Solve: 688 [?]
Skill Endorsements: 104 [?]
Administrator
Featured
 
0
 

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. ;)

Member Avatar
gian88r
Newbie Poster
10 posts since Mar 2013
Reputation Points: 0 [?]
Q&As Helped to Solve: 1 [?]
Skill Endorsements: 3 [?]
 
0
 

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 :)

Member Avatar
RvSon
Junior Poster in Training
72 posts since Aug 2010
Reputation Points: -3 [?]
Q&As Helped to Solve: 8 [?]
Skill Endorsements: 0 [?]
 
0
 

Did you get your question solved?

You
This question has already been solved: Start a new discussion instead
Post:
Start New Discussion
Tags Related to this Article