Update Statement in VB.Net 2008

[smitty68503]

I have been struggling with this for awhile now. lol

I have no code to post because I haven't even started it. I can do the insert statement just fine from vb.net code to a sql database. That's fairly easy. However to do an update statement from a combobox to update a status I haven't been able to.

I have google'd and found some examples that didn't fit my needs. All I am needing is to be able to click on a button and update the table that contains "status" from a combo box. I greatly appreciate any help I can get in this.

[Reverend Jim]

What is it you are having trouble with? Is it the format of the query or is it getting the info from the combobox? A typical update query looks like

UPDATE myTable 
   SET postalCode = 'R2C3N1' 
 WHERE zone = 'East Kildonan'
   AND streetNumber BETWEEN 1600 and 1699

[smitty68503]

The all around statement. The combo box has 6 different choices. I am wanting to update the database based on what the user selects.

Example:

User selects "Awaiting Customer Approval" from the drop down list/combobox and then clicks the update button it updates that in the database.

The biggest problem I am having is the format of the update query. The rest I should be able to figure out. Thanks for your help.

[M.Waqas Aslam]

means there are more then one dataabases you have , ? and you want that your code will update the selected database ? if yes then do something like this

dim con as new sqlconnection("your connection string")
con.ChangeDatabase(comboBox.text) ' you can use combobox.selectedvalue
con.open()
'------------------
here you can use your update command as jim said.
'------------------
con.close()

if this is not your prob then please explain it for me ,

Regards

[Netcode]

declare all necessary variables like connectionstrings and commands
use the update statement below

'------------------------------------------
        UPDATE TableName
        SET columnName = ' + comboBox1.text + '
        WHERE columnName = 'value' AND ID = 'value'
'------------------------------------------

[smitty68503]

 Dim conn As New SqlConnection(My.Settings.Smitty)
        conn.ChangeDatabase(ComboBox1.SelectedValue)
        conn.Open()
        Dim cmd As SqlCommand = New SqlCommand("update Repairs &_ set status = @status WHERE RepairNumber = '" & Label24.Text & " '", conn)

[smitty68503]

I posted the code I have so far. It isn't doing anything. My guess is it needs more code such as the data reader I am sure. It seems like the update statement is much more complex than the insert or delete statements. lol Thanks for all the help.

[Reverend Jim]

Try taking out the "&_" in "update Repairs &_ set status"

[smitty68503]

 Dim conn As New SqlConnection(My.Settings.Smitty)
        conn.Open()
        conn.ChangeDatabase(ComboBox1.SelectedValue) 'Database cannot be null, the empty string, or string of only whitespace is the error I get.
        Dim cmd As SqlCommand = New SqlCommand("update Repairs set status = @status WHERE RepairNumber = '" & Label24.Text & " '", conn)
    End Sub

I have tried different combinations with different commands and I am always getting some sort of error. The label24.text is where it gives the ID of the record to change the status.

[Reverend Jim]

Display the value of the command text from SqlCommand just before you execute it and post the string here so I can have a look at the actual query. It would also help to see the exact error message you are getting.

[smitty68503]

I think I am way off on that code. The part where I have conn.ChangeDatabase(ComboBox1.SelectedValue) isn't what it should be. It is telling me that no database exists with that name. For example if I select "Awaiting approval" it says "No database 'awaiting approval' exists. I appreciate all the help I am getting so far. I like the idea you don't spoon feed on this forum. You actually learn from it.

conn.ChangeDatabase(ComboBox1.SelectedValue)

[smitty68503]

update Repairs set status = @status WHERE RepairNumber = '" & Label24.Text & " '", conn)

I think this is exactly where my problem is. I have update repairs (which is the table) I have set "status" (which is one of the column names) to be the statuscmbx (the control where it gets the status from) where the RepairID = label24.text.

I think where I am having the problem from what I am reading elsewhere is the update statement has these elements:

Update (table)

set (The new value you want to have in the column)

where (The old value)

I'm thinking in this statement I would need to set the value to what the combobox.text is. And the where would be when the RepairID matches IDlbl.text. Hopefully I am on the right track. Just hoping to get this figured out, it will be a huge accomplishment. lol

[M.Waqas Aslam]

smitty , can you please tell me you want to just change the database name or table name , ? i mean

conn.ChangeDatabase(ComboBox1.SelectedValue)

this is used to change the database not table , so when you select table from combo box .it gives error because there is no database of this name . if you want to change the table name just do something like this.

update " & combobox.selectedvalue  & " set field1 = @field1 where userid = @userid

hope this will help you .

Regards

[smitty68503]

I am wanting to keep the table name and the database name the same. Just wanting to change a field. For example:

table name: repairs
Column name: status

I am looking to change the status of a Repair ID I have pulled up on the screen to what the combobox says. So if I have a repair that is Repair ID 100 with a status awaiting diagnostic, I can change it to one of five different statuses from a combo box.

[Reverend Jim]

My access method of choice is ADO. To open a database, update a table, then close it I would do

Imports ADODB  'must add .NET reference to adodb

Public Class Form1

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click

        Dim con As New ADODB.Connection
        con.Open("Driver={SQL Server};Server=.\SQLEXPRESS;Database=mydb;Trusted_Connection=yes;")

        If con.State = ADODB.ObjectStateEnum.adStateOpen Then
            Dim cmd As String = "UPDATE tblvendor " & _
                                "   SET VendorName = 'Hank'" & _
                                " WHERE VendorID = 2"
            con.Execute(cmd)
            con.Close()
        End If

    End Sub

End Class

[smitty68503]

> Dim cmd As String = "UPDATE tblvendor " & _
>                                 "   SET VendorName = 'Hank'" & _
>                                 " WHERE VendorID = 2"

This part is where I have the problem. The set "vendorname =" is a set variable "Hank". This variable isn't always going to be the same as it will be whatever I have my label1 set to. And the vendor ID will be whatever I have another label set to.

I have tried the

This does not give an error but it also doesn't update the database either.

Thanks for all your help.

[Reverend Jim]

There are a couple of ways to patch in a variable. The accepted way is to use parameters like @name and @id then use a method to replace these values with actual values. I don't bother to do that because the only database stuff I do is for myself, but you must use @ parameters if you want to guard against SQL injection attacks. You can find examples of how to do that elsewhere in this forum. The quick (and dirty) way is as follows:

Imports ADODB  'must add .NET reference to adodb

Public Class Form1

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click

        Dim con As New ADODB.Connection
        con.Open("Driver={SQL Server};Server=.\SQLEXPRESS;Database=mydb;Trusted_Connection=yes;")

        If con.State = ADODB.ObjectStateEnum.adStateOpen Then
            Dim cmd As String = "UPDATE tblvendor " & _
                                "   SET VendorName = '" & txtName.Text & "'" & _
                                " WHERE VendorID = " & txtID.text
            con.Execute(cmd)
            con.Close()
        End If

    End Sub

End Class

where txtName and txtID are textbox controls. Note the single quotes around the VendorName value in the cmd string.