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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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.
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!


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

Turn Insights into Action

Communication across every corner of your business is essential to increase the velocity of your application delivery and support pipeline. Automate, standardize, and contextualize your communication processes with xMatters.

Question has a verified solution.

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

How to remove superseded packages in windows w60 or w61 installation media (.wim) or online system to prevent unnecessary space. w60 means Windows Vista or Windows Server 2008. w61 means Windows 7 or Windows Server 2008 R2. There are various …
Having just graduated from college and entered the workforce, I don’t find myself always using the tools and programs I grew accustomed to over the past four years. However, there is one program I continually find myself reverting back to…R.   So …
The viewer will learn additional member functions of the vector class. Specifically, the capacity and swap member functions will be introduced.
In a recent question ( here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

717 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