Solved

Update a record and insert NULL in db

Posted on 2014-04-03
6
343 Views
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++
0
Comment
Question by:russell12
[X]
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
6 Comments
 
LVL 2

Accepted Solution

by:
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))
Else
cmd.Parameters.Add(New SqlParameter("@text", DbNull.Value))
End If
cmd.Parameters.Add(New SqlParameter("@item", txtitem.text))

con.Open()
cmd.ExecuteNonQuery()
con.Close()
0
 
LVL 5

Expert Comment

by:NARANTHIRAN
ID: 39977269
Set Allow Null Values for the both the fields in the table.
For both the fields..
0
 
LVL 2

Author Comment

by:russell12
ID: 39977298
GowthamNatarajan:
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.

NARANTHIRAN:
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.
0
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 
LVL 2

Author Comment

by:russell12
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))
            Else
                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))
            Else
                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))
            Else
                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))
            Else
                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))
            Else
                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))
            Else
                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))
            Else
                cmd.Parameters.Add(New SqlParameter("@StartMiles", txtstartmiles.Text))
            End If

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

            If txttotalmiles.Text = "" Then
                cmd.Parameters.Add(New SqlParameter("@totalmiles", DBNull.Value))
            Else
                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))

            con.Open()
            cmd.ExecuteNonQuery()
            con.Close()

Open in new window

0
 
LVL 5

Assisted Solution

by:NARANTHIRAN
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"
0
 
LVL 2

Author Closing Comment

by:russell12
ID: 39982276
NARANTHIRAN:
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!

GowthamNatarajan:
Thank you for all your help on this post and the previous one.  I appreciate it.
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

Suggested Solutions

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…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
The viewer will learn how to implement Singleton Design Pattern in Java.
The viewer will learn how to clear a vector as well as how to detect empty vectors in C++.

733 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