0
votes

I'm trying to figure how to update a table if a value already exists when I try to insert it.

Ex, I use the following code to check if the name is already registered. But if it does exists on the table, how can I make an update on the already existent ID, to update for the new name? Thanks

sqlConn.ConnectionString = "server =" + server + ";" + "user id=" + username + ";" _
                                             + "password =" + password + ";" + "database=" + database
                                            sqlConn.Open()
                                            sqlCmd.CommandText = "SELECT COUNT(*) FROM teste1    " &
                                                " WHERE ID =     '" & TextBox1.Text & "'"
                                            If sqlCmd.ExecuteScalar <> 0 Then
                                                MessageBox.Show("ID already exists.")
                                                'user is valid
                                            Else

                                                objconn.ConnectionString = "server=localhost;user id=root;password=;database=clientes"
                                                objconn.Open()
                                                 Dim objcmd = New MySql.Data.MySqlClient.MySqlCommand("UPDATE teste1 WHERE (Name,ID)VALUES(@field1,@field2)", objconn)
                                                    With objcmd
                                                    .Parameters.AddWithValue("@field1", Name)
                                                    .Parameters.AddWithValue("@field2", ID)
                                                   
                                                    MsgBox("Done!", MsgBoxStyle.Information, Title:="Great!")
                                                End With
                                                objcmd.ExecuteNonQuery()
                                                objcmd.Dispose()
                                                objconn.Close()
                                                'user is not valid
                                            End If
                                            sqlConn.Close()
                                        Catch ex As Exception
                                            MessageBox.Show("Ops!")
                                        Finally

                                        End Try
I don't think your update syntax is correct. shouldn't it be Update Test set Name = @field1 where ID = @Field2 - Bryan Dellinger
also maybe you are looking for a merge or upsert statement. this will insert if the condition does not exist and update if it does exist. search for merge for your rdbms. - Bryan Dellinger