UPDATE Statement

shlvy · September 2, 2004

Hi

I use a update sub, the problem is that i got an error, the error is:

Syntax error in UPDATE statement.

I guess the UPDATE statement is:

strUpdate = "Update tblUsers Set UserName=@UserName, Password=@Password, RetypePassword=@RetypePassword, Email=@Email, Comments=@Comments Where UserID=@UserID"

Remark:exactly the same code in SQL DataBase works fine.

Is the problem can be in other place?

Thank you very much for your assistance.

Edited September 2, 2004 by shlvy

kejpa · September 2, 2004

strUpdate = "Update tblUsers Set UserName=@UserName' date=' Password=@Password, RetypePassword=@RetypePassword, Email=@Email, Comments=@Comments Where UserID=@UserID"[/quote']
When sending that string to the database it takes a wild guess and tries to set Username to the field @UserName and so on. It will fail, since there's no such field.
Instead you have to something like this

Update tblUsers Set UserName='" & sUserName & "', Password='" & sPassword & "', RetypePassword='" & sRetypePassword & "', Email='" & sEmail & "', Comments='" & sComments & "' Where UserID=" & iUserID
(My guess is that all fields are strings except UserID which is a long/integer)

The code will work in SQL server since it's using @WhatEver as a variable.

HTH
/Kejpa

shlvy · September 2, 2004

Hi

Thank's but the erroris still shows, here the code of the sub, maybe you will see something wrong:

Sub dlstAuthors_UpdateCommand( s As Object, e As DataListCommandEventArgs )

Dim conPubs As OleDbConnection

Dim strUpdate As String

Dim cmdUpdate As OleDbCommand

Dim strUserID As String

Dim txtUserName As TextBox

Dim txtPassword As TextBox

Dim txtRetypePassword As TextBox

Dim txtEmail As TextBox

Dim txtComments As TextBox

Dim sUserName As string

Dim sPassword As string

Dim sRetypePassword As string

Dim sEmail As string

Dim sComments As string

Dim iUserID As Integer

strUserID = dlstAuthors.DataKeys( e.Item.ItemIndex )

txtUserName = e.Item.FindControl( "txtUserName" )

txtPassword = e.Item.FindControl( "txtPassword" )

txtRetypePassword = e.Item.FindControl( "txtRetypePassword" )

txtEmail = e.Item.FindControl( "txtEmail" )

txtComments = e.Item.FindControl( "txtComments" )

conPubs = New OleDbConnection( "Provider=" & "Microsoft.Jet.OLEDB.4.0;" & _

"Data Source =C:\inetpub\wwwroot\R.mdb" )

strUpdate = "Update tblUsers Set UserName='"& sUserName &"', Password='"& sPassword &"', RetypePassword='"& sRetypePassword &"', Email='"& sEmail &"', Comments='"& sComments &"' Where UserID="& iUserID

cmdUpdate = New OleDbCommand( strUpdate, conPubs )

cmdUpdate.Parameters.Add( "& iUserID ", strUserID )

cmdUpdate.Parameters.Add( "& sUserName ", txtUserName.Text )

cmdUpdate.Parameters.Add( "& sPassword ", txtPassword.Text )

cmdUpdate.Parameters.Add( "& sRetypePassword ", txtRetypePassword.Text )

cmdUpdate.Parameters.Add( "& sEmail ", txtEmail.Text )

cmdUpdate.Parameters.Add( "& sComments ", txtComments.Text )

conPubs.Open()

cmdUpdate.ExecuteNonQuery()

conPubs.Close()

dlstAuthors.EditItemIndex = -1

BindDataList

End Sub

Thank you very much for you assistance.

kejpa · September 2, 2004

Aouch!

;)

iUserID=strUserID 
sUserName=txtUserName.Text
sPassword =txtPassword.Text 
sRetypePassword = txtRetypePassword.Text 
sEmail = txtEmail.Text 
sComments = txtComments.Text 

conPubs = New OleDbConnection( "Provider=" & "Microsoft.Jet.OLEDB.4.0;" & _
"Data Source =C:\inetpub\wwwroot\R.mdb" )
strUpdate = "Update tblUsers Set UserName='"& sUserName &"', Password='"& sPassword &"', RetypePassword='"& sRetypePassword &"', Email='"& sEmail &"', Comments='"& sComments &"' Where UserID="& iUserID 
conPubs.Open()
cmdUpdate.ExecuteNonQuery()
conPubs.Close()

But somehow I think you're making it wonderfully complicated....

Send the data (Username, Password,...) as parameters to the sub, forget the textboxes and stuff.

HTH

/Kejpa

shlvy · September 2, 2004

Hi

I tried what you tell me but it's not work.

I have a simple question, i took a ready code and just change it to my values.

For example, the delete sub works fine after i changed the values:

Sub dlstAuthors_DeleteCommand( s As Object, e As DataListCommandEventArgs )

Dim conPubs As OleDbConnection

Dim strDelete As String

Dim cmdDelete As OleDbCommand

Dim strUserID As String

strUserID = dlstAuthors.DataKeys( e.Item.ItemIndex )

conPubs = New OleDbConnection( "Provider=" & "Microsoft.Jet.OLEDB.4.0;" & _

"Data Source =C:\inetpub\wwwroot\R.mdb")

strDelete = "Delete from tblUsers Where UserID=@UserID"

cmdDelete = New OleDbCommand( strDelete, conPubs )

cmdDelete.Parameters.Add( "@UserID", strUserID )

conPubs.Open()

cmdDelete.ExecuteNonQuery()

conPubs.Close()

dlstAuthors.EditItemIndex = -1

BindDataList

End Sub

Why is that so complicated with the update sub, i just changed the values and the database(SQL to Access)?

Thank's.

Nerseus · September 2, 2004

I think we have two solutions up for proposal. You can either build a SQL string by hand (kejpa's method) or create "real" parameters (shlvy's method). Either will work, but you can't really mix and match (you can, but shouldn't).

I'd use the parameters as you first tried, something like:

strUpdate = "Update [tblUsers] Set [userName]=@UserName, [Password]=@Password, [RetypePassword]=@RetypePassword, [Email]=@Email, [Comments]=@Comments Where [userID]=@UserID"

mdUpdate.Parameters.Add( "@UserID ", strUserID )
cmdUpdate.Parameters.Add( "@UserName ", txtUserName.Text )
cmdUpdate.Parameters.Add( "@Password ", txtPassword.Text )
cmdUpdate.Parameters.Add( "@RetypePassword ", txtRetypePassword.Text )
cmdUpdate.Parameters.Add( "@Email ", txtEmail.Text )
cmdUpdate.Parameters.Add( "@Comments ", txtComments.Text )

The only thing I really changed above was the variable names and I put brackets around the table and column names in the update string (in case any were reserved words).

If you want to see kejpa's method, try this:

strUpdate = "Update [tblUsers] Set "
strUpdate = strUpdate & "[userName]='" & txtUserName.Text.Replace("'", "''") & "', "
strUpdate = strUpdate & "[Password]='" & txtPassword.Text.Replace("'", "''") & "', "
strUpdate = strUpdate & "[RetypePassword]='" & txtRetypePassword.Text.Replace("'", "''") & "', "
strUpdate = strUpdate & "[Email]='" & txtEmail.Text.Replace("'", "''") & "', "
strUpdate = strUpdate & "[Comments]='" & txtComments.Text.Replace("'", "''") & "' "
strUpdate = strUpdate & "Where [userID]='" & strUserID.Replace("'", "''") & "'"

I used the textbox's to fill in the strings "by hand". This is easier to test since you can look at strUpdate and paste that into a query window to see if it will work. The parameter objects alleviate the need to replace single quotes and do other formatting (for dates, numbers, etc.). Also, the parameters are nicer because the code is a lot easier to read: both the UPDATE statement and the named parameters.

-ner

shlvy · September 2, 2004

Hi

First at all thank you for your answers.

The problem is that i have a new error:

Object reference not set to an instance of an object.

Here what i wrote:

Sub dlstAuthors_UpdateCommand( s As Object, e As DataListCommandEventArgs )

Dim conPubs As OleDbConnection

Dim strUpdate As String

Dim cmdUpdate As OleDbCommand

Dim strUserID As String

Dim txtUserName As TextBox

Dim txtPassword As TextBox

Dim txtRetypePassword As TextBox

Dim txtEmail As TextBox

Dim txtComments As TextBox

strUserID = dlstAuthors.DataKeys( e.Item.ItemIndex )

txtUserName = e.Item.FindControl( "txtUserName" )

txtPassword = e.Item.FindControl( "txtPassword" )

txtRetypePassword = e.Item.FindControl( "txtRetypePassword" )

txtEmail = e.Item.FindControl( "txtEmail" )

txtComments = e.Item.FindControl( "txtComments" )

conPubs = New OleDbConnection( "Provider=" & "Microsoft.Jet.OLEDB.4.0;" & _

"Data Source =C:\inetpub\wwwroot\R.mdb" )

strUpdate = "Update [tblUsers] Set "

strUpdate = strUpdate & "[userName]='" & txtUserName.Text.Replace("'", "''") & "', "

strUpdate = strUpdate & "[Password]='" & txtPassword.Text.Replace("'", "''") & "', "

strUpdate = strUpdate & "[RetypePassword]='" & txtRetypePassword.Text.Replace("'", "''") & "', "

strUpdate = strUpdate & "='" & txtEmail.Text.Replace("'", "''") & "', "

strUpdate = strUpdate & "[Comments]='" & txtComments.Text.Replace("'", "''") & "' "

strUpdate = strUpdate & "Where [userID]='" & strUserID.Replace("'", "''") & "'"

cmdUpdate = New OleDbCommand( strUpdate, conPubs )

conPubs.Open()

cmdUpdate.ExecuteNonQuery()

conPubs.Close()

dlstAuthors.EditItemIndex = -1

BindDataList

End Sub

Thank you very much for your assistance.

Nerseus · September 3, 2004

Which line is giving you the error? Can you step through your ASP.NET code?

Trying outputting the UPDATE string with something like (right after the last set of strUpdate):

Response.Write(strUpdate)
Response.End

-ner

shlvy · September 3, 2004

Hi

I wrote what you told me and it shows the next sentence:

Update [tblUsers] Set [userName]='admin', [Password]='admin', [RetypePassword]='admin', ='admin@admin.com', [Comments]='Hi to ' Where [userID]='515'

Here the rest of the code, the code of the body:

<body>

<asp:DataList

ID="dlstAuthors"

DataKeyField="UserID"

OnEditCommand="dlstAuthors_EditCommand"

OnCancelCommand="dlstAuthors_CancelCommand"

OnDeleteCommand="dlstAuthors_DeleteCommand"

OnUpdateCommand="dlstAuthors_UpdateCommand"

RepeatColumns="4"

GridLines="Both"

CellPadding="10"

EditItemStyle-BackColor="lightgrey"

Runat="Server">

UserName:

<%# Container.DataItem( "UserName" )%>

Password:

<%# Container.DataItem( "Password" )%>

RetypePassword:

<%# Container.DataItem( "RetypePassword" )%>

Email:

<%# Container.DataItem( "Email" )%>

Comments:

<%# Container.DataItem( "Comments" )%>

Text='<%# Container.DataItem( "UserName" )%>'

Text='<%# Container.DataItem( "Password" )%>'

Runat="Server" />

RetypePassword:

<asp:TextBox

ID="txtRetypePassword"

Text='<%# Container.DataItem( "RetypePassword" )%>'

Text='<%# Container.DataItem( "Email" )%>'

Text='<%# Container.DataItem( "Comments" )%>'

PlausiblyDamp · September 3, 2004

As Nerseus asked earlier - which line actually raisese the error?

shlvy · September 3, 2004

Hi

The error line 93:

Line 93: cmdUpdate.ExecuteNonQuery()

The error: Data type mismatch in criteria expression.

Thank's.

PlausiblyDamp · September 3, 2004

what data types are the underlying fields in the table and do they match up with the values you are trying to set them to?

shlvy · September 3, 2004

Hi

All DataTypes are Text except UserID that it's AutoNumber.

Thank's.

PlausiblyDamp · September 3, 2004

try changing the last bit to

[userID]=515

shlvy · September 3, 2004

Hi

I don't understand where excactly to change and what?

In the code or in the table?

Thank's.

PlausiblyDamp · September 3, 2004

Sorry - didn't read far enough back.

If you are using the original method and concatenating strings (not recomended) then you do not need the ' around the user id - these tell SQL to treat the data as a string.

If you are using parameters (prefered way) then you should set the datatype to be an integer.

shlvy · September 3, 2004

Hi

I changed the DataType in the database to integer and nothing change.

I am sorry, if there is no solutions i will give up, it is so exhausted, we changed so much in the origin code that works fine in SQL, all we have to do is to change the Parameters's names.

Thank's.

PlausiblyDamp · September 3, 2004

What was the data type in the database before? The problem is in your SQL query you are treating the userid as a string, it is in the query you need to treat it as a integer - either by removing the ' from around the number or using the correct data type for the parameter.

shlvy · September 3, 2004

Hi

Thank you very very very much, all i have to do is to remove the ' .

Thank you, Nerseus, Kejpa and everyone.

MikeyLDS · September 3, 2004

Hi
Thank you very very very much, all i have to do is to remove the ' .
Thank you, Nerseus, Kejpa and everyone.

"All is well!" :)

Sign In

UPDATE Statement

Recommended Posts

shlvy

kejpa

shlvy

kejpa

shlvy

Nerseus

shlvy

Nerseus

shlvy

PlausiblyDamp

shlvy

PlausiblyDamp

shlvy

PlausiblyDamp

shlvy

PlausiblyDamp

shlvy

PlausiblyDamp

shlvy

MikeyLDS

Join the conversation

Browse

Activity