Update database adodb.net problem 1

[takwirira]

hie guys, im having trouble with updating an access database. Here is my code below. userAns and QuesNo are fields in the database and userAns and ques are variables that I have declared and will get values from textboxes etc. For some reason the details are not being updated into the database

Dim objConn As New OleDbConnection _
("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"User ID=Admin;" & _
"Data Source=db2.mdb")
objConn.Open()

Dim objAdapter As New OleDbDataAdapter
objAdapter.UpdateCommand = New OleDbCommand( _
"UPDATE Ques_Table SET userAns= '" & userAns & "' Where QuesNo ='" & ques & "'", objConn)

objConn.Close()

if anyone has a template of the SELECT, INSERT and other syntax for access db's please post as well

 

[jebenson]

That's because you are setting up your adapter to do an update, but you never actually tell it to perform the update.

Additionally, the way you are doing this is typically used when there is a table of data to put into/update a database. If you are only inserting one row - as your code indicates - simply use a Command object and call ExecuteNonQuery:

Dim cmd As OleDbCommand

cmd = New OleDbCommand

cmd.Connection = objConn
cmd.CommandType = CommandType.Text
cmd.CommandText = "UPDATE Ques_Table SET userAns= '" & userAns & "' Where QuesNo ='" & ques & "'"

cmd.ExecuteNonQuery()

cmd.dispose()
cmd = Nothing


Also, you should look into using Parameters with your Command object, whether you use it stand-alone or with a DataAdapter. Executing SQL commands with inline variables (e.g., userAns, ques) opens the possibility of SQL insertion attacks, whereby a malicious user could possibly insert SQL code that will erase an entire table in the database, for example.



I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day! Ye has a choice: talk like a pira

 

[takwirira]

Dim objConn As New OleDbConnection _
("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"User ID=Admin;" & _
"Data Source=C:\ePlanet_files\Copy of Radio\db2.mdb")
objConn.Open()

Dim cmd As OleDbCommand

cmd = New OleDbCommand

cmd.Connection = objConn
cmd.CommandType = CommandType.Text
cmd.CommandText = "UPDATE Ques_Table SET userAns= '" & userAns & "' Where QuesNo ='" & ques & "'"

cmd.ExecuteNonQuery()

cmd.Dispose()
cmd = Nothing
after inserting this new code it returns an error saying CommandType is not declared

 

[jebenson]

Try this:

cmd.CommandType = System.Data.CommandType.Text

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day! Ye has a choice: talk like a pira