Solved

Syntax error in UPDATE statement Using OleDb and MS Access

Posted on 2008-10-27
6
1,002 Views
Last Modified: 2010-04-21
Hi,

I have used VS 2005 to create an update statement to an Access table but it is throwing the following error:

System.Data.OleDb.OleDbException: Syntax error in UPDATE statement.

The AccessDataSource control's SQL Statement and parameters look like so...

UpdateCommand="UPDATE [tblCustomers] SET [Customer_name] = ?, [Customer_Username] = ?, [Customer_Password] = ?, [Customer_Live] = ?, WHERE [CustomerID] = ?"

Parameters -

<UpdateParameters>
            <asp:Parameter Name="Customer_name" Type="String" />
            <asp:Parameter Name="Customer_Username" Type="String" />
            <asp:Parameter Name="Customer_Password" Type="String" />
            <asp:Parameter Name="Customer_Live" Type="Boolean" />
            <asp:Parameter Name="CustomerID" Type="Int32" />
        </UpdateParameters>

Is anyone able to see any issues?

Many thanks,

Rit
0
Comment
Question by:rito1
  • 3
  • 2
6 Comments
 
LVL 8

Assisted Solution

by:rpkhare
rpkhare earned 150 total points
Comment Utility
Are you supplying string values as:

'Customer Name'

One other possibility I guess is the Boolean DataType. In Access it is Yes/No. What you are supplying?
0
 
LVL 1

Author Comment

by:rito1
Comment Utility
Hi rpkhare

The string values are coming straigh from the Gridview as I am using the Update command button.

The data type Customer_Live was Yes/No but have just changed it to True/False but still getting this error.

My gridview looks like this...



<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="CustomerID" DataSourceID="AccessDataSource1" Width="100%" CellPadding="4" AlternatingRowStyle-BackColor="#eeeeee" BorderWidth="1px" BorderColor="#EEEEEE">

        <Columns>

            <asp:BoundField DataField="Customer_name" HeaderText="Customer Name" SortExpression="Customer_name" />

            <asp:BoundField DataField="Customer_Username" HeaderText="Username" SortExpression="Customer_Username" />

            <asp:BoundField DataField="Customer_Password" HeaderText="Password" SortExpression="Customer_Password" />

            <asp:CheckBoxField DataField="Customer_Live" HeaderText="Live?" SortExpression="Customer_Live" />

            <asp:CommandField ShowEditButton="True" />

            <asp:TemplateField ShowHeader="False">

                <ItemTemplate>

                    <asp:LinkButton ID="LinkButton1" runat="server" CausesValidation="False" CommandName="Delete" Text=" X " OnClientClick="return confirm('Are you sure you want to delete this record?');" ForeColor="red"></asp:LinkButton>

                </ItemTemplate>

            </asp:TemplateField>

        </Columns>

    </asp:GridView>

Open in new window

0
 
LVL 8

Expert Comment

by:rpkhare
Comment Utility
I think you need to still add single quotes in the query:

UpdateCommand="UPDATE [tblCustomers] SET [Customer_name] = ' " + <GridViewValue> + " ' "
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 69

Accepted Solution

by:
Éric Moreau earned 350 total points
Comment Utility
remove the comma just before the WHERE
0
 
LVL 8

Expert Comment

by:rpkhare
Comment Utility
emoreau, Yes you got it. I think that is the problem.
0
 
LVL 1

Author Closing Comment

by:rito1
Comment Utility
Thanks emoreau, I would have been there for ages! Thanks rpkhare for your support too.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video discusses moving either the default database or any database to a new volume.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now