Web Development

General discussion

Gravatar
0 Votes
Locked

Using ASP.NET to update Access Database

By davebyrne ·
Hi all,

I'm new to ASP.NET and I am trying to get it to update an access database. The problem I am having is that whenever I try to pass the following SQL I get the error "Syntax error in UPDATE statement". Can anyone see what would cause this?

code:
Sub submit(sender As Object, e As System.EventArgs)

Dim MyConnection As OleDbConnection
MyConnection = New OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" & server.mappath("internal.mdb") & ";")
Dim MyCommand As OleDbCommand
Dim UpdateCmd As String = "UPDATE login SET password = 'Hello'"
MyCommand = New OleDbCommand(UpdateCmd, MyConnection)
MyCommand.Connection.Open()
MyCommand.ExecuteNonQuery()
MyCommand.Connection.Close()

End Sub

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

gravatar
Collapse -

by techrep In reply to Using ASP.NET to update A ...

Login is a table in your database. Right? I don't see anything wrong, but I'm wondering if the word "login" is a reserved word in ASP.NET. Try putting brackets around it like this: UPDATE [login] SET password, etc.

Just a thought. Good luck.

gravatar
Collapse -

by davebyrne In reply to Using ASP.NET to update A ...

Have changed the name of the table to login_details and still get the same problem.

gravatar
Collapse -

by Towquest In reply to Using ASP.NET to update A ...

Try adding a "WHERE" clause.

exmaple:
Dim UpdateCmd As String = "UPDATE login SET password = 'Hello' WHERE username='Fred'"

gravatar
Collapse -

by davebyrne In reply to Using ASP.NET to update A ...

Sorted this problem myself. Changed sql to:

Dim UpdateCmd As String = "UPDATE [login] SET [password] = 'Hello'"

The square brackets fixed the problem. Thanks for the suggestions.

gravatar
Collapse -

by LukCAD In reply to Using ASP.NET to update A ...

Explore it fragment of code, note that fmail- is not repeated field for key when you are going update your database (and it name for example):

Dim MyConnection As OleDBConnection
MyConnection = New OleDBConnection("Provider=Microsoft.JET.OLEDB.4.0;Data Source=" & Server.MapPath("internal.mdb"))
Dim MyCommand As OleDBCommand

Dim UpdateCmd As String = "update login set password=@password where fmail=@fmail"

MyCommand = New OleDBCommand(UpdateCmd, MyConnection)

MyCommand.Parameters.Add(New OleDBParameter("@fid", OleDbType.VarChar, 50))
MyCommand.Parameters("@password").Value = "Hello"


MyCommand.Parameters.Add(New OleDBParameter("@fmail", OleDbType.VarChar, 200))
MyCommand.Parameters("@fmail").Value = "msn@msn.com" 'for example

MyCommand.Connection.Open()
Try
MyCommand.ExecuteNonQuery()
Catch Exp As OleDBException
If Exp.ErrorCode = 2627
Else
End If
End Try
MyCommand.Connection.Close()

And you can send me letter - i will give to you ready module - and you can study more things. Sincerely, LukCAD

gravatar
Collapse -

by LukCAD In reply to

Sorry, I have found one error. It is more right:
Explore it fragment of code, note that fmail- is not repeated field for key when you are going update your database (and it name for example):

Dim MyConnection As OleDBConnection
MyConnection = New OleDBConnection("Provider=Microsoft.JET.OLEDB.4.0;Data Source=" & Server.MapPath("internal.mdb"))
Dim MyCommand As OleDBCommand

Dim UpdateCmd As String = "update login set password=@password where fmail=@fmail"

MyCommand = New OleDBCommand(UpdateCmd, MyConnection)

MyCommand.Parameters.Add(New OleDBParameter("@password", OleDbType.VarChar, 50))
MyCommand.Parameters("@password").Value = "Hello"


MyCommand.Parameters.Add(New OleDBParameter("@fmail", OleDbType.VarChar, 200))
MyCommand.Parameters("@fmail").Value = "msn@msn.com" 'for example

MyCommand.Connection.Open()
Try
MyCommand.ExecuteNonQuery()
Catch Exp As OleDBException
If Exp.ErrorCode = 2627
Else
End If
End Try
MyCommand.Connection.Close()

And you can send me letter - i will give to you ready module - and you can study more things. Sincerely, LukCAD

Back to Web Development Forum

Start or search

Related Discussions

Related Forums