Error while Upating Record

[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.ExecuteCommandTextE rrorHandling(Int32
hr)
at
System.Data.OleDb.OleDbCommand.ExecuteCommandTextF orSingleResult(tagDBPARAMS
dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText( Object&
executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(Comm andBehavior
behavior, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderIntern al(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\Inventor yDatabase.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

[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

"Stephen" <st*********@hotmail.com> wrote in message
news:uC**************@TK2MSFTNGP11.phx.gbl...

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.ExecuteCommandTextE rrorHandling(Int32
hr)
at
System.Data.OleDb.OleDbCommand.ExecuteCommandTextF orSingleResult(tagDBPARAMS dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText( Object&
executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(Comm andBehavior
behavior, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderIntern al(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\Inventor yDatabase.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

[Bill Borg]

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.ExecuteCommandTextE rrorHandling(Int32
hr)
at
System.Data.OleDb.OleDbCommand.ExecuteCommandTextF orSingleResult(tagDBPARAMS
dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText( Object&
executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(Comm andBehavior
behavior, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderIntern al(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\Inventor yDatabase.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

[bruce barker]

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

-- bruce (sqlwork.com)
"Bill Borg" <Bi******@discussions.microsoft.com> wrote in message
news:02**********************************@microsof t.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.ExecuteCommandTextE rrorHandling(Int32
| > hr)
| > at
| >
System.Data.OleDb.OleDbCommand.ExecuteCommandTextF orSingleResult(tagDBPARAMS
| > dbParams, Object& executeResult)
| > at System.Data.OleDb.OleDbCommand.ExecuteCommandText( Object&
| > executeResult)
| > at System.Data.OleDb.OleDbCommand.ExecuteCommand(Comm andBehavior
| > behavior, Object& executeResult)
| > at
System.Data.OleDb.OleDbCommand.ExecuteReaderIntern al(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\Inventor yDatabase.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
| >
| >
| >