Error while Upating Record

S

Stephen

Hi,

I am trying to update a record in the DB and i am using OLEDB command and
connection.
I get this error while updating.

Error inserting data: System.Data.OleDb.OleDbException: Syntax error in
UPDATE statement.
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32
hr)
at
System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS
dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object&
executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior
behavior, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior
behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
at Customer.InventoryDatabase.UpdateLogin(String uNamee, String pwde,
String accesse) in
C:\Inetpub\wwwroot\HP\Customer\Management\InventoryDatabase.vb:line 51\r\n

Can anyone please suggest what this means,
this is my function that I an using to update the database (MS access)
Public Function Update(ByVal uName As String, ByVal pwd As String, ByVal
access As String) As Boolean
Dim CmdText As String = "UPDATE Login SET Password=@Password,
Access=@Access WHERE UName=@UName"
Dim cmd As New OleDbCommand(CmdText, conn)
Try
cmd.CommandType = CommandType.Text
cmd.CommandType = CommandType.Text

Dim pms As OleDbParameterCollection = cmd.Parameters
pms.Add("@UName", OleDbType.VarChar, 15)
pms.Add("@Password", OleDbType.VarChar, 15)
pms.Add("@Access", OleDbType.VarChar, 5)

pms("@UName").Value = uName
pms("@Password").Value = pwd
pms("@Access").Value = access

conn.Open()
cmd.ExecuteNonQuery()
conn.Close()
Return True

Catch ex As Exception
logFile.LogOutput("Error inserting data: " + ex.ToString())
Return False

End Try
End Function

Thanks,

Stephen
 
J

Jeff Dillon

What working existing UPDATE code are you trying to modify? I assume you
have test code working?

I've never seen parameters set like that that for a direct SQL statement
personally. Stored procedures, yes. I thought that parameter replacements
within inline SQL required question marks?

Jeff
 
G

Guest

I never use Access, but thought I read somewhere it doesn't take named
parameters, and that you need to use ?'s.

Bill
 
B

bruce barker

you are correct. the oledb driver (even if accessing sqlserver) only
supports "?" parameters.

-- bruce (sqlwork.com)


| I never use Access, but thought I read somewhere it doesn't take named
| parameters, and that you need to use ?'s.
|
| Bill
|
| "Stephen" wrote:
|
| > Hi,
| >
| > I am trying to update a record in the DB and i am using OLEDB command
and
| > connection.
| > I get this error while updating.
| >
| > Error inserting data: System.Data.OleDb.OleDbException: Syntax error in
| > UPDATE statement.
| > at
System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32
| > hr)
| > at
| >
System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS
| > dbParams, Object& executeResult)
| > at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object&
| > executeResult)
| > at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior
| > behavior, Object& executeResult)
| > at
System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior
| > behavior, String method)
| > at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
| > at Customer.InventoryDatabase.UpdateLogin(String uNamee, String pwde,
| > String accesse) in
| > C:\Inetpub\wwwroot\HP\Customer\Management\InventoryDatabase.vb:line
51\r\n
| >
| > Can anyone please suggest what this means,
| > this is my function that I an using to update the database (MS access)
| > Public Function Update(ByVal uName As String, ByVal pwd As String, ByVal
| > access As String) As Boolean
| > Dim CmdText As String = "UPDATE Login SET Password=@Password,
| > Access=@Access WHERE UName=@UName"
| > Dim cmd As New OleDbCommand(CmdText, conn)
| > Try
| > cmd.CommandType = CommandType.Text
| > cmd.CommandType = CommandType.Text
| >
| > Dim pms As OleDbParameterCollection = cmd.Parameters
| > pms.Add("@UName", OleDbType.VarChar, 15)
| > pms.Add("@Password", OleDbType.VarChar, 15)
| > pms.Add("@Access", OleDbType.VarChar, 5)
| >
| > pms("@UName").Value = uName
| > pms("@Password").Value = pwd
| > pms("@Access").Value = access
| >
| > conn.Open()
| > cmd.ExecuteNonQuery()
| > conn.Close()
| > Return True
| >
| > Catch ex As Exception
| > logFile.LogOutput("Error inserting data: " + ex.ToString())
| > Return False
| >
| > End Try
| > End Function
| >
| > Thanks,
| >
| > Stephen
| >
| >
| >
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top