Update a record and insert NULL in db

Posted on 2014-04-03
Last Modified: 2014-04-06
I need to send an update string to a record and if the field is blank, it should write NULL to the db.  I has datetime fields and int fields and everytime I update the sql it will put 00:00 for my datetime and 0 for my int.  I do not have the whole update string with me, but this is the layout i use:

Update Communications set  Dispatched = '" + txtdispatched.text + "' where Item = '" + txtitem.text + "'"

Any help would be greatly appreciated.  Thanks in advance.

I am using VB.NET not C++
Question by:russell12
  • 3
  • 2

Accepted Solution

GowthamNatarajan earned 250 total points
ID: 39977224
If you want to update the value as null in DB, You can use
Update Communications set  Dispatched = DbNull.Value where Item = '" + txtitem.text + "'"
Pleas try this
Dim con As New SqlConnection("Your Connection")
Dim cmd As New SqlCommand() With { _
      Key .CommandText = "Update Communications set  Dispatched = @text where Item = @item", _
      Key .Connection = con_
If  txtdispatched.text  IsNot Nothing Then
cmd.Parameters.Add(New SqlParameter("@text", txtdispatched.text))
cmd.Parameters.Add(New SqlParameter("@text", DbNull.Value))
End If
cmd.Parameters.Add(New SqlParameter("@item", txtitem.text))


Expert Comment

ID: 39977269
Set Allow Null Values for the both the fields in the table.
For both the fields..

Author Comment

ID: 39977298
I am glad you gave me this response.  My insert command is set up exactly like this and I was wanting to do it this way on an update command, just didnt know the proper syntax.  I will test this when I get back in town on Sunday and post back and let you know.

It is set in the db to allow null values.  When I insert a new record, it will wite dbnull.  I have in my insert command if txtdispatched.text="" then dispatch = dbnull else dispatch = txtdispatch.text and it works great with no flaws.  I just couldnt figure out how to get the dbnull to pass on an update query.  Thanks for your comment, it is appreciated and is a easy think to overlook.
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.


Author Comment

ID: 39981465
I have tried to manipulate this code multiple times and the error I am getting is :

A first chance exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll

Additional information: Incorrect syntax near ')'.

I know its connecting to db because of the insert command works.  I am missing something just cant find it.  Any help would be greatly appreciated!!

The code is:
            Dim con As SqlConnection
            Dim cmd As SqlCommand
            Dim Sqlstr As String

            con = New SqlConnection("Data Source='" + sqlseverlocation + "';Initial Catalog='" + sqltable + "';Persist Security Info=True;User ID='" + sqluname + "';Password='" + sqlpass + "'")

            'cmd = New SqlCommand("UPDATE Communications SET Unit='" + txtUnit.Text + "', location='" + txtLocation.Text + "', [Activity Code]='" + txtActivityCode.Text + "', tag='" + txtTag.Text + "', state='" + txtState.Text + "', [case #]='" + txtCaseNumber.Text + "', Month='" + txtmonth.Text + "', Day='" + txtday.Text + "', Year='" + txtyear.Text + "', Received='" + txtReceived.Text + "' WHERE Item='" + txtItem.Text + "'", con)
            Sqlstr = "UPDATE Communications SET Unit=@Unit, location=@Location, [Activity Code]=@ActivityCode, tag=@Tag, state=@State, [case #]=@CaseNumber, Month=@month, Day=@day, Year=@year, Received=@Received, Dispatched=@Dispatched, Arrived=@Arrived, Custody=@Custody, EnrouteHosp=@EnrouteHosp, ArriveHosp=@ArriveHosp, Completed=@Completed, Method=@Method, [Reported By]=@ReportedBy, Address=@Address, City=@City, Phone=@Phone, Status=@Status, Agency=@Agency, [Duty Officers]=@Officer, Shift=@Shift, [Starting Mileage]=@startmiles), [Ending Mileage]=@endmiles, [total daily miles]=@totalmiles, Remarks=@Remarks WHERE Item=@item"
            cmd = New SqlCommand(Sqlstr, con)

            cmd.Parameters.Add(New SqlParameter("@Unit", txtUnit.Text))
            cmd.Parameters.Add(New SqlParameter("@location", txtLocation.Text))
            cmd.Parameters.Add(New SqlParameter("@ActivityCode", txtActivityCode.Text))
            cmd.Parameters.Add(New SqlParameter("@tag", txtTag.Text))
            cmd.Parameters.Add(New SqlParameter("@state", txtState.Text))
            cmd.Parameters.Add(New SqlParameter("@CaseNumber", txtCaseNumber.Text))
            cmd.Parameters.Add(New SqlParameter("@Month", txtmonth.Text))
            cmd.Parameters.Add(New SqlParameter("@Day", txtday.Text))
            cmd.Parameters.Add(New SqlParameter("@Year", txtyear.Text))
            cmd.Parameters.Add(New SqlParameter("@Received", DateTime.Now.ToString(txtReceived.Text)))

            If txtDispatched.Text = "" Then
                cmd.Parameters.Add(New SqlParameter("@Dispatched", DBNull.Value))
                cmd.Parameters.Add(New SqlParameter("@Dispatched", DateTime.Now.ToString(txtDispatched.Text)))
            End If

            If txtOnScene.Text = "" Then
                cmd.Parameters.Add(New SqlParameter("@Arrived", DBNull.Value))
                cmd.Parameters.Add(New SqlParameter("@Arrived", DateTime.Now.ToString(txtOnScene.Text)))
            End If

            If txtCustody.Text = "" Then
                cmd.Parameters.Add(New SqlParameter("@Custody", DBNull.Value))
                cmd.Parameters.Add(New SqlParameter("@Custody", DateTime.Now.ToString(txtCustody.Text)))
            End If

            If txtEnrouteHosp.Text = "" Then
                cmd.Parameters.Add(New SqlParameter("@EnrouteHosp", DBNull.Value))
                cmd.Parameters.Add(New SqlParameter("@EnrouteHosp", DateTime.Now.ToString(txtEnrouteHosp.Text)))
            End If

            If txtArriveHosp.Text = "" Then
                cmd.Parameters.Add(New SqlParameter("@ArriveHosp", DBNull.Value))
                cmd.Parameters.Add(New SqlParameter("@ArriveHosp", DateTime.Now.ToString(txtArriveHosp.Text)))
            End If

            If txtCompleted.Text = "" Then
                cmd.Parameters.Add(New SqlParameter("@Completed", DBNull.Value))
                cmd.Parameters.Add(New SqlParameter("@Completed", DateTime.Now.ToString(txtCompleted.Text)))
            End If

            cmd.Parameters.Add(New SqlParameter("@Method", txtMethod.Text))
            cmd.Parameters.Add(New SqlParameter("@ReportedBy", txtReportedBy.Text))
            cmd.Parameters.Add(New SqlParameter("@Address", txtAddress.Text))
            cmd.Parameters.Add(New SqlParameter("@City", txtCity.Text))
            cmd.Parameters.Add(New SqlParameter("@Phone", txtPhone.Text))
            cmd.Parameters.Add(New SqlParameter("@Status", txtStatus.Text))
            cmd.Parameters.Add(New SqlParameter("@Operator", lblOperator.Text))
            cmd.Parameters.Add(New SqlParameter("@Agency", txtAgency.Text))
            cmd.Parameters.Add(New SqlParameter("@Officer", txtOfficer.Text))
            cmd.Parameters.Add(New SqlParameter("@Shift", Shift))

            If txtstartmiles.Text = "" Then
                cmd.Parameters.Add(New SqlParameter("@StartMiles", DBNull.Value))
                cmd.Parameters.Add(New SqlParameter("@StartMiles", txtstartmiles.Text))
            End If

            If txtendmiles.Text = "" Then
                cmd.Parameters.Add(New SqlParameter("@EndgMiles", DBNull.Value))
                cmd.Parameters.Add(New SqlParameter("@EndMiles", txtendmiles.Text))
            End If

            If txttotalmiles.Text = "" Then
                cmd.Parameters.Add(New SqlParameter("@totalmiles", DBNull.Value))
                cmd.Parameters.Add(New SqlParameter("@totalmiles", txttotalmiles.Text))
            End If

            cmd.Parameters.Add(New SqlParameter("@Remarks", txtRemarks.Text))
            cmd.Parameters.Add(New SqlParameter("@item", txtItem.Text))


Open in new window


Assisted Solution

NARANTHIRAN earned 250 total points
ID: 39982153
Check this query..

Sqlstr = "UPDATE Communications SET Unit=@Unit, location=@Location, [Activity Code]=@ActivityCode, tag=@Tag, state=@State, [case #]=@CaseNumber, Month=@month, Day=@day, Year=@year, Received=@Received, Dispatched=@Dispatched, Arrived=@Arrived, Custody=@Custody, EnrouteHosp=@EnrouteHosp, ArriveHosp=@ArriveHosp, Completed=@Completed, Method=@Method, [Reported By]=@ReportedBy, Address=@Address, City=@City, Phone=@Phone, Status=@Status, Agency=@Agency, [Duty Officers]=@Officer, Shift=@Shift, [Starting Mileage]=@startmiles, [Ending Mileage]=@endmiles, [total daily miles]=@totalmiles, Remarks=@Remarks WHERE Item=@item"

Author Closing Comment

ID: 39982276
I knew it was something simple, I had been coding for hours and I looked and looked before I posted it up.  I knew if another set of eyes would see it, they would see the issue.  Thanks for overlooking it!

Thank you for all your help on this post and the previous one.  I appreciate it.

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
The goal of the tutorial is to teach the user how to use functions in C++. The video will cover how to define functions, how to call functions and how to create functions prototypes. Microsoft Visual C++ 2010 Express will be used as a text editor an…
The goal of the video will be to teach the user the concept of local variables and scope. An example of a locally defined variable will be given as well as an explanation of what scope is in C++. The local variable and concept of scope will be relat…

831 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