UPDATE help

[1004]

All I am trying to do is update my access but I got alot of erros. I have Stor_ID in a combobox and it loads the info. Now I am trying to update the information. All I want to do is update city and state.

Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
 
UpdateStoreID()
 
Dim strcmd As String = "UPDATE Stores SET"
strcmd &= "City = @city, "
strcmd &= "State = @state, "
 
Dim cmdUpdate As New OleDbCommand(strcmd, conDB)
With cmdUpdate
cmdUpdate.Parameters.Add("@city", txtCity.Text)
cmdUpdate.Parameters.Add("@state", txtState.Text)
End With
 
Try
conDB.Open()
cmdUpdate.ExecuteNonQuery()
'MessageBox.Show("Update confirmed!")
Catch ex As Exception
MessageBox.Show(ex.ToString)
Finally
conDB.Close()
End Try
End Sub
Private Sub UpdateStoreID()
Dim cmdGetStoreInt As New OleDbCommand("Select Stor_ID from Stores where stor_ID = @ID", conDB)
cmdGetStoreInt.Parameters.Add("@Stor_ID", cboStoreID.SelectedItem)
Try
If conDB.State = ConnectionState.Closed Then
conDB.Open()
End If
intStoreID = cmdGetStoreInt.ExecuteScalar
 
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
conDB.Close()
End Try
End Sub

 

[Anti-Rich]

dude you need to specify the sort of error message you are getting

also, in one of your set statements you have an unnecessary comma, and you havent specified a where clause for your statement, doing your statement how it is (if the dodgy comma were removed) would update all rows in the table to the specified city and state, not just the one you want

 

[vis781]

You are using .net 2003 right. So launch the DataForm Wizard and it will create all the UPDATE, SELECT commands you need. Then you can have a look at the generated code and see how it is done.

 

[1004]

I ended up updating in different method. I think I tried over 8 different ways of coding. Here is what I did:

#Region " Update Data "
Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
' other codes I tried:
' Dim strUpdate As String = "UPDATE SET Stores"
' strUpdate &= "Set stor_id = @id, city = @city, state = @state "
' strUpdate &= "Where stor_id = @id"
Dim strUpdate As String = "UPDATE Stores SET stor_id=@id, city=@city, state=@state WHERE stor_id=@id"
Dim cmdUpdate As New OleDbCommand(strUpdate, conDB)
cmdUpdate.Parameters.Add("@id", cboStoreID.SelectedItem)
cmdUpdate.Parameters.Add("@city", txtCity.Text)
cmdUpdate.Parameters.Add("@state", txtState.Text)
 
Try
conDB.Open()
cmdUpdate.ExecuteNonQuery()
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
conDB.Close()
MessageBox.Show("The Record Have Been Updated")
End Try
 
End Sub
#End Region

I do not know how to do use the Wizard, when I start new project, I do not get the template field to create Data Form Wizard. It does not exist or I am soo blind I can not find it. Any how thank you for reply. please reply if you know how I can open wizard. Thank you.

 

[vis781]

Here's a link to another thread on these forums wher i did a bit of work with another member on MS Access.

http://www.vbdotnetforums.com/showthread.php?t=12869&page=2&highlight=ckeezer

Dim StrUpdate As String = "UPDATE Stores SET stor_id = ? , city = ? , state = ? WHERE stor_id = ?"
Dim cmdUpdate As New OleDbCommand(strUpdate, conDB)
cmdUpdate.Parameters.Add("@id",OleDbType.Integer, 0, "stor_id").Value =  cboStoreID.SelectedItem
cmdUpdate.Parameters.Add("@city", OleDbType.VarWChar,255,"city").Value = txtCity.Text)
cmdUpdate.Parameters.Add("@state",OleDbType.VarWChar,255, "state").Value = TxtState.Text
cmdUpdate.Parameters.Add("@stor_id",OleDbtype.Integer, 0,"stor_id").SourceVersion = DataRowVersion.Original

 

[cjard]

As I understood it (from Microsoft website) the OleDb data provider in ADO.NET doesnt understand named parameters.

Microsoft advocate these for access(ole)/sql server/oracle respectively:

Dim cmd as New OleDbCommand("UPDATE tbl SET col = ? WHERE col2 = ?")
Dim cmd as New SqlCommand("UPDATE tbl SET col = @col WHERE col2 = @col2")
Dim cmd as New OracleCommand("UPDATE tbl SET col = :col WHERE col2 = :col2")

Microsoft access uses OleDB, SQL Server should use SqlClient and Oracle should use OracleClient. Some functionality of SQL Server and Oracle can be achieved through use of the SQL Server OLE driver or OLE for ODBC (being usable for both sql server and oracle) though neither of these routes are recommended in the interests of performance.

It's interesting that you got the @notation to work woith Access (if indeed you work with access)
Both the MSDN website and the SQLCommandBuilder written by MS for generating parameterized SQL Commands automatically, use the ? notation

 

[1004]

it worked for me, it was updating the city and state. That was my first VB with access so if you have alot more of knowledge than I do. vis781's work looks very similar to my work. Maybe his helping somebody I know.