• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 295
  • Last Modified:

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

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
clearcarbonconsulting
Asked:
clearcarbonconsulting
  • 2
  • 2
  • 2
  • +1
1 Solution
 
UnifiedISCommented:
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
 
Praveen KumarArchitectCommented:
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
 
clearcarbonconsultingAuthor Commented:
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
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
Jorge PaulinoIT Pro/DeveloperCommented:
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
 
clearcarbonconsultingAuthor Commented:
I still get the error.  SQL still sees "null" as an nvarchar value.  it doesn't recognize it as a real null value.
0
 
Jorge PaulinoIT Pro/DeveloperCommented:
Use DbNull.Value to insert null into storaged procedures

If(txtName.Text = "", DbNull.Value;txtName.Text)
0
 
UnifiedISCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now