Update SQL Database in code not working.

[Shift838]

can anyone tell me what I have going wrong here on this command button?

Private Sub cmdUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdUpdate.Click

        Dim myCmd As SqlCommand

        Dim myConn = New SqlConnection("Initial Catalog=NG_DB_Acct_Info;" & _
                "Data Source=netgildb.chevron.com;Persist Security Info=True; Integrated Security=SSPI;")

        'myCmd = myConn.CreateCommand
        'myCmd.CommandText = "SELECT distinct OSversion, Site, DBHost, DBName, DBport, " & _
        myCmd.CommandText = "Update NG_DB_Data SET OSVersion = '" & txtOSVersion.Text & "' and Site = '" & txtSite.Text & _
        "' and DBHost = '" & txtDBHost.Text & "' and DBAcctID = '" & txtDBAcct.Text & "' and DBAcctPswd '" & txtdbPswd.Text & _
        "' and FarmDNS = '" & txtDSNFile.Text & "' WHERE FarmDesc = '" & cmbFarmDesc.SelectedItem & "'"
        'SELECT * from NG_DB_Data WHERE (FarmDesc = '" & cmbFarmDesc.SelectedItem & "')"

        'Open the connection.
        If myConn.state = ConnectionState.Closed Then myConn.open(myCmd.CommandText)
        myCmd.ExecuteNonQuery()
        MsgBox("Data updated.")
    End Sub

 

[Andrzejek]

That could be one of your problems:
This is NOT a valid UPDATE statement:

 myCmd.CommandText = "Update NG_DB_Data SET OSVersion = '" & txtOSVersion.Text & "' and Site = '" & txtSite.Text & _        "' and DBHost = '" & txtDBHost.Text & "' and DBAcctID = '" & txtDBAcct.Text & "' and DBAcctPswd '" & txtdbPswd.Text & _        "' and FarmDNS = '" & txtDSNFile.Text & "' WHERE FarmDesc = '" & cmbFarmDesc.SelectedItem & "'"

Try something like:[tt]

UPDATE NG_DB_Data SET OSVersion = 'abc', Site = 'XYZ', DBHost = 'ooops' WHERE ...[/tt]

Have fun.

---- Andy

 

[Shift838]

Andrzejek,

I tried that but it did not work. still got an error. I ended up doing the below and it worked. I'm also looking to be able to port my database into a form with fields and as soon as I make a change it asked me if I want to do that and if yes it executes. maybe even have the "<" and ">" like MS access does to cycle through records. Does anyone have any code like that?

If MsgBox("Is password encrypted in password field", vbYesNo) = vbYes Then

            Try


                Dim myConnection As SqlConnection
                Dim myCommand As SqlCommand
                Dim ra As Integer
                myConnection = New SqlConnection("Initial Catalog=NG_DB_Acct_Info;" & _
                        "Data Source=netgildb.chevron.com;Persist Security Info=True; Integrated Security=SSPI;")
                'you need to provide password for sql server
                myConnection.Open()
                myCommand = New SqlCommand("Update NG_DB_Data SET OSVersion = '" & txtOSVersion.Text & "', Site = '" & txtSite.Text & _
                    "', DBHost = '" & txtDBHost.Text & "', DBAcctID = '" & txtDBAcct.Text & "', DBAcctPswd = '" & txtdbPswd.Text & _
                    "', FarmDSN = '" & txtDSNFile.Text & "' WHERE FarmDesc = '" & cmbFarmDesc.SelectedItem & "'", myConnection)
                'Update NG_DB_Data Set DBAcctID='" & txtDBAcct.Text & "' Where FarmDesc = '" & cmbFarmDesc.SelectedItem & "'", myConnection)
                ra = myCommand.ExecuteNonQuery()
                MessageBox.Show("Records updated: " & ra)
                myConnection.Close()
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
        Else
            MsgBox("Please encrypt the password using the encryption tab and copy and paste the encrypted result before updating table.")
        End If