?
Solved

Problem inputting text box values into a SQL Database when the text box is left blank

Posted on 2007-10-18
7
Medium Priority
?
273 Views
Last Modified: 2010-04-14
I am trying to input values from a set of text boxes into my SQL database.  I have three fields which are decimal fields in my database.  If I put a numeric value in the text boxes for these fields then the insert works fine.  The problem I am running into is when the text boxes are left blank.  I get an invalid input string error message.  Any ideas on how i can just have the database insert a null when these text boxes are blank.  My fields in my DB do allow null values.  Thanks.
0
Comment
Question by:clearcarbonconsulting
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 18

Expert Comment

by:UnifiedIS
ID: 20102492
Use a stringbuilder to create your sql statement
conditionally add the line for your textbox
dim sb as new system.text.stringbuilder
sb.append("INSERT INTO table (Columns) VALUES(")
if textbox.text.trim.length>0 then
   sb.append(textbox.text.trim)
else
   sb.append("NULL")
endif
sb.append(")")
0
 
LVL 12

Expert Comment

by:Praveen Kumar
ID: 20102592
If Not IsNumeric(txtValue.Text.Trim) Then txtValue.Text=0
If Not IsNumeric(txtValue2.Text.Trim) Then txtValue2.Text=0
If Not IsNumeric(txtValue3.Text.Trim) Then txtValue3.Text=0

Then try to insert the values into table.

     
   
0
 

Author Comment

by:clearcarbonconsulting
ID: 20102636
I am using a stored procedure to do my insert.  So i don't think a string building would work.  I also don't want to insert 0's, I want the value to be NULL.  Any other ideas?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 48

Expert Comment

by:jpaulino
ID: 20102663
You can check if your textbox is empty or not and then use it

Dim myValue1 As String
If txtValue1.Text = "" then
  myValue1  = "NULL"
Else
 myValue1  = txtValue1.Text
End If

Dim myValue2 As String
If txtValue2.Text = "" then
  myValue2  = "NULL"
Else
 myValue2  = txtValue1.Text
End If

Dim SQL As String = "INSERT INTO mytable (name, value1, value2) VALUES ('" & txtName.Text & "'," & myValue1 & "," & myValue2 & ")

jpaulino
0
 

Author Comment

by:clearcarbonconsulting
ID: 20102709
I still get the error.  SQL still sees "null" as an nvarchar value.  it doesn't recognize it as a real null value.
0
 
LVL 48

Accepted Solution

by:
jpaulino earned 150 total points
ID: 20102759
Use DbNull.Value to insert null into storaged procedures

If(txtName.Text = "", DbNull.Value;txtName.Text)
0
 
LVL 18

Expert Comment

by:UnifiedIS
ID: 20102995
Are you passing the textbox value to the stored procedure?
IF LEN(@text) = 0
begin
'insert with null
end
if LEN(@text) >0
begin
'insert with value
end

On another note, you are asking a question about a stored procedure in .net zones and you haven't included any code for your stored procedure.  The more you give the better the help will be.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Simulator games are perfect for generating sample realistic data streams, especially for learning data analysis. It is even useful for demoing offerings such as Azure stream analytics, PowerBI etc.
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question