Solved

Update a record and insert NULL in db

Posted on 2014-04-03
6
336 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
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
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.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Visual Studio 2013 Shortcut (VB) 4 34
DataGridView Events ? 3 37
vb.net - How to check if current user is an administrator? 6 34
ADO.NET ENTITY DATA MODEL 3 30
There is an easy way, in .NET, to centralize the treatment of all unexpected errors. First of all, instead of launching the application directly in a Form, you need first to write a Sub called Main, in a module. Then, set the Startup Object to th…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
The viewer will learn how to implement Singleton Design Pattern in Java.
The viewer will learn additional member functions of the vector class. Specifically, the capacity and swap member functions will be introduced.

914 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

20 Experts available now in Live!

Get 1:1 Help Now