Updating table from vb to Access using ConnectionString
Private Sub btnUpdate_Click(sender As Object, e As EventArgs) Handles btnUpdate.Click
Try
Dim con As New SqlConnection
Dim cmd As New SqlCommand
con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\Comp-296\Project1\Project1\Game_time.mdb"
con.Open()
cmd.Connection = con
cmd.Connection = con
cmd.CommandText = ("UPDATE User_Name SET User_Name = @User_Name, Game_Name = @Game_Name, Score = @Score, Time/Date = @Time/Date")
cmd.Parameters.Add("@User_Name", SqlDbType.VarChar).Value = txtUser.Text
cmd.Parameters.Add("@Game_Name", SqlDbType.VarChar).Value = txtGame.Text
cmd.Parameters.Add("@Score", SqlDbType.VarChar).Value = txtScore.Text
cmd.Parameters.Add("@Time/Date", SqlDbType.DateTime).Value = txtDate.Text
cmd.ExecuteNonQuery()
MessageBox.Show("Data Update successfully")
con.Close()
Catch ex As System.Exception
MessageBox.Show("Data Update has failed")
End Try
End Sub
The code providing the exception is an ArgumentException and also: Keyword not supported: "provider".
source to share
You are using Access. This database cannot be opened using the classes in System.Data.SqlClient. These classes are used when you want to connect to Sql Server, Sql Server Express, or LocalDB.
If you want to get to MSAccess database you need classes in System.Data.OleDb and those classes are OleDbConnection, OleDbCommand etc.
Said that please note that your Date / Time field will give you headaches. Change this name or put square brackets around it, because / will be interpreted as the division operator
So your code could be:
Using con = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\Comp-296\Project1\Project1\Game_time.mdb")
Using cmd = new OleDbCommand("UPDATE User_Name
SET User_Name = @User_Name,
Game_Name = @Game_Name,
Score = @Score, [Time/Date] = @dt", con)
con.Open()
cmd.Parameters.Add("@User_Name", OleDbType.VarWChar).Value = txtUser.Text
cmd.Parameters.Add("@Game_Name", OleDbType.VarWChar).Value = txtGame.Text
cmd.Parameters.Add("@Score", OleDbType.VarWChar).Value = txtScore.Text
cmd.Parameters.Add("@dt", OleDbType.Date).Value = Convert.ToDateTime(txtDate.Text)
cmd.ExecuteNonQuery()
MessageBox.Show("Data Update successfully")
End Using
End Using
Other notes: Disposable objects such as connection and command should be enclosed within a Use Operation, which should be removed and closed as soon as possible.
The time field requires a DateTime value, not a string. If you pass in a string, you will run into the automatic conversion done by the engine, and sometime the engine will fail to output a valid date from your input string. This will throw another exception (DataType mismatch). Better to check and convert the value before passing it.
Also, the type of parameters must be from the OleDbType enumeration.
source to share