Solved

Update a record and insert NULL in db

Posted on 2014-04-03
6
332 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
  • 3
  • 2
6 Comments
 
LVL 2

Accepted Solution

by:
GowthamNatarajan earned 250 total points
Comment Utility
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
Comment Utility
Set Allow Null Values for the both the fields in the table.
For both the fields..
0
 
LVL 2

Author Comment

by:russell12
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 2

Author Comment

by:russell12
Comment Utility
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
Comment Utility
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
Comment Utility
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
The goal of this video is to provide viewers with basic examples to understand and use switch statements in the C programming language.
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.

762 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now