Solved

Syntax error in UPDATE statement Using OleDb and MS Access

Posted on 2008-10-27
6
1,003 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
ID: 22811340
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
ID: 22811422
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
ID: 22811492
I think you need to still add single quotes in the query:

UpdateCommand="UPDATE [tblCustomers] SET [Customer_name] = ' " + <GridViewValue> + " ' "
0
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 
LVL 70

Accepted Solution

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

Expert Comment

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

Author Closing Comment

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

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Runtime Error 2 37
Pagebreak issue while printing the aspx page 3 24
Regex validation 2 24
Question about JQuery and asp.net 3 23
Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
Sometimes in DotNetNuke module development you want to swap controls within the same module definition.  In doing this DNN (somewhat annoyingly) swaps the Skin and Container definitions to the default admin selections.  To get around this you need t…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

816 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

8 Experts available now in Live!

Get 1:1 Help Now