Trouble with Sample Code

Can anyone please help me out ??

I am totally new to ASP.NET.

I learnt the basics of asp (enough to create my own interactive web site) by using the asp for dummies book. In that book is some sample code called classy classifieds. This is a sample classified ads site where you can post ads and search the access database provided. By chopping this code up and customising it I learnt asp.

I am now attempting to do the same thing with the new classy classifieds data provided in ASP.NET for dummies. I am having trouble right at the start with the code provided. When I try to place a new add I get the following error message.

If I edit an existing ad, it updates to the database OK. The editad.aspx file is used for processing new ad as well as any updates to existing ones.

Could anyone please help me with this error.

PS If you need more info than the error message, please let me know and I could email the sample code files.

Many Thanks in advance !!!

Server Error in '/' Application.
------------------------

Syntax error in INSERT INTO statement.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.OleDb.OleDbException: Syntax error in INSERT INTO statement.

Source Error:

Line 74: Row.Item("Posted") = CDate(Today)
Line 75: ClassyDS.Tables("Ads").Rows.Add(Row)
Line 76: Adapter.Update(ClassyDS, "Ads")
Line 77: PostErrors
Line 78: End Sub

Source File: C:\Inetpub\wwwroot\classy\classyad.ascx Line: 76

Stack Trace:

[OleDbException (0x80040e14): Syntax error in INSERT INTO statement.]
System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping) +1534
System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable) +152
ASP.ClassyAd_ascx.PlaceAd() in C:\Inetpub\wwwroot\classy\classyad.ascx:76
ASP.EditAd_aspx.Submit_Click(Object Sender, EventArgs E) in C:\Inetpub\wwwroot\classy\editad.aspx:80
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +83
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +57
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain() +1263

------------------------
Version Information: Microsoft .NET Framework Version:1.0.3705.0; ASP.NET Version:1.0.3705.0The problem is with the SQL statement that does the actual insert. Please post the actual SQL that the method is using. Thanks.
Thanks Nabil for helping with this !!

This is the code from my editad.aspx file, with me being new to ASP.NET I am not sure how it is trying to insert the data into the access tables. As I mentioned before, with asp I recognised the sql select and insert statements.

If you need any more info please let me know.

PS The editad.aspx file is used for entering new and also editing existing entries. It is when I try to enter a completely new record in the table that I got the original error, if you edit an existing entry, it enters the new data fine !!, thats why I thought any sql statements must be OK, anyway, Im only a beginner !!

Thanks again for your time on this.

EditAd.aspx

<%@. Page Explicit="True" Language="VB" Debug="True" %>
<%@. Register TagPrefix="ASPFD" TagName="Header" src="http://pics.10026.com/?src=header.ascx" %>
<%@. Register TagPrefix="ASPFD" TagName="ClassyAd" src="http://pics.10026.com/?src=classyad.ascx" %>
<%@. import Namespace="System.Data" %>
<%@. import Namespace="System.Data.OleDb" %>
<script runat="server"
 Dim AdNumSent As Integer
 Dim Operation As String
 Dim CatListIndex As Integer
 Sub Page_Load(Sender As Object, E As EventArgs)
 If Not IsPostBack Then
 If Trim(Request.QueryString("AdNum")) = "" Then
 Operation = "PLACE"
 Header.AddPageName("Place Ad")
 Submit.Text = "Place Ad"
 AdNumSent = 0
 Else
 Operation = "EDIT"
 Header.AddPageName("Edit Ad")
 Submit.Text = "Make Changes"
 ' If you make a control invisible
 ' you MUST also make the validation
 ' control(s) that refer to it
 ' invisible, too (not just disabled)
 PasswordRequired.Visible = False
 PasswordText.Visible = False
 PasswordLabel.Visible = False
 AdNumSent = Request.QueryString("AdNum")
 ClassyAd.GetAd(AdNumSent)
 TitleText.Text = ClassyAd.Title
 DescriptionText.Text = ClassyAd.Description
 For CatListIndex=0 To CategoryDropDown.Items.Count -1
 If CategoryDropDown.Items(CatListIndex).Value = ClassyAd.Category Then
 CategoryDropDown.Items(CatListIndex).Selected = True
 End If
 Next
 PriceText.Text = ClassyAd.Price
 PhoneText.Text = ClassyAd.Phone
 EmailText.Text = ClassyAd.Email
 StateText.Text = ClassyAd.State
 End If
 ViewState("Operation")=Operation
 ViewState("AdNumSent")=AdNumSent
 End If
 End Sub
 Sub Submit_Click(Sender As Object, E As EventArgs)
 Operation=ViewState("Operation")
 AdNumSent = ViewState("AdNumSent")
 ClassyAd.GetAd(AdNumSent)
 ClassyAd.Title = TitleText.Text
 ClassyAd.Description = DescriptionText.Text
 ClassyAd.Category = CategoryDropDown.SelectedItem.Value
 ClassyAd.Price = PriceText.Text
 ClassyAd.Phone = PhoneText.Text
 ClassyAd.Email = EmailText.Text
 ClassyAd.State = StateText.Text
 If Operation="PLACE" Then
 ClassyAd.Password = PasswordText.Text
 ClassyAd.Posted = Today
 ClassyAd.PlaceAd
 Else
 ClassyAd.EditAd
 End If
 If ClassyAd.HasErrors Then
 Message.Text = "There was a database error: " & _
 ClassyAd.RowError
 Else
 If Operation="PLACE"
 Message.Text = "Your classified ad has been placed"
 Else
 Message.Text = "Your classified ad changes have been made"
 End If
 TitleText.Enabled=False
 DescriptionText.Enabled=False
 CategoryDropDown.Enabled=False
 PriceText.Enabled=False
 PhoneText.Enabled=False
 EmailText.Enabled=False
 StateText.Enabled=False
 PasswordText.Enabled=False
 Submit.Enabled=False
 End If
 End Sub
</script>
<html>
<head>
</head>
<body vlink="red">
 <form runat="server">
 <aspfd:header id="Header" runat="server"></aspfd:header>
 <aspfd:classyad id="ClassyAd" runat="server" visible="false"></aspfd:classyad>
 <% If Operation="PLACE" Then %>
 <p>
 Please fill inall of these textboxes below. Be careful when entering a Password
 and be sure to remember what you type. You may be required to enter the password later
 to identify yourself if you need to edit or delete this ad.
 </p>
 <p>
 When you are finished, click the Place Ad button.
 </p>
 <% Else %>
 <p>
 Edit any of the information you like. Make sure each textbox has a valid value before
 you click the Make Changes button.
 </p>
 <% End If %>
 <table>
 <tbody>
 <tr>
 <td>
 Title:</td>
 <td>
 <asp:requiredfieldvalidator id="TitleRequired" runat="server" errormessage="*" controltovalidate="TitleText"></asp:requiredfieldvalidator>
 <asp:textbox id="TitleText" runat="server" columns="50"></asp:textbox>
 </td>
 </tr>
 <tr>
 <td valign="top">
 Description:</td>
 <td>
 <asp:requiredfieldvalidator id="DescriptionRequired" runat="server" errormessage="*" controltovalidate="DescriptionText"></asp:requiredfieldvalidator>
 <asp:textbox id="DescriptionText" runat="server" columns="40" rows="3" textmode="multiline"></asp:textbox>
 </td>
 </tr>
 <tr>
 <td>
 Category:</td>
 <td>
 <asp:requiredfieldvalidator id="CategoryRequired" runat="server" errormessage="*" controltovalidate="CategoryDropDown" initialvalue="* Pick a Category *"></asp:requiredfieldvalidator>
 <asp:dropdownlist id="CategoryDropDown" runat="server">
 <asp:listitem >* Pick a Category *</asp:listitem>
 <asp:listitem value="VEHICLES">Vehicles</asp:listitem>
 <asp:listitem value="COMPUTERS">Computers</asp:listitem>
 <asp:listitem value="REALESTATE">Real Estate</asp:listitem>
 <asp:listitem value="COLLECTIBLES">Collectibles</asp:listitem>
 <asp:listitem value="GENERAL">General Merchandise</asp:listitem>
 </asp:dropdownlist>
 </td>
 </tr>
 <tr>
 <td>
 Price:</td>
 <td>
 <asp:requiredfieldvalidator id="PriceRequired" runat="server" errormessage="*" controltovalidate="PriceText"></asp:requiredfieldvalidator>
 $
 <asp:textbox id="PriceText" runat="server" columns="10"></asp:textbox>
 </td>
 </tr>
 <tr>
 <td>
 Phone</td>
 <td>
 <asp:requiredfieldvalidator id="PhoneRequired" runat="server" errormessage="*" controltovalidate="PhoneText"></asp:requiredfieldvalidator>
 <asp:textbox id="PhoneText" runat="server" columns="15"></asp:textbox>
 </td>
 </tr>
 <tr>
 <td>
 Email:</td>
 <td>
 <asp:requiredfieldvalidator id="EmailRequired" runat="server" errormessage="*" controltovalidate="EmailText"></asp:requiredfieldvalidator>
 <asp:textbox id="EmailText" runat="server" columns="50"></asp:textbox>
 </td>
 </tr>
 <tr>
 <td>
 State:</td>
 <td>
 <asp:requiredfieldvalidator id="StateRequired" runat="server" errormessage="*" controltovalidate="StateText"></asp:requiredfieldvalidator>
 <asp:textbox id="StateText" runat="server" columns="2"></asp:textbox>
 </td>
 </tr>
 <tr>
 <td>
 <asp:Label id="PasswordLabel" runat="server" text="Password:"></asp:Label></td>
 <td>
 <asp:requiredfieldvalidator id="PasswordRequired" runat="server" errormessage="*" controltovalidate="PasswordText"></asp:requiredfieldvalidator>
 <asp:textbox id="PasswordText" runat="server" columns="15" textmode="password"></asp:textbox>
 </td>
 </tr>
 <tr>
 <td>
 <asp:button id="Submit" onclick="Submit_Click" runat="server"></asp:button>
 </td>
 <td align="middle">
 <asp:Label id="Message" runat="server" forecolor="red" backcolor="yellow" font-size="16 pt" font-italic="true" font-bold="true"></asp:Label></td>
 </tr>
 </tbody>
 </table>
 <br />
 <center>
 <asp:hyperlink id="HomeLink" runat="server" font-size="12 pt" font-bold="true" font-name="Arial" navigateurl="default.aspx">
[ Home ]</asp:hyperlink>
 </center>
 </form>
</body>
</html>

the error message stated:
"Syntax error in INSERT INTO statement. "

Nabil asked you to post the insert sql statement - - in ALL that code, I can't find an Insert Statement
David, thanks for your help...
I have to reiterate, im a total beginner here !!

This is where Im confused, there isnt any sql insert statements.
This is sample code, that wont run...
To add a row into the dataset the method I think is being used is as follows:-

1) Create a new row object
Row=ClassyDS.Tables("Ads").NewRow

2) fill that object with data for each column
Row.Item("Title") = TitleText.Text
Row.Item("Description") = DescriptionText.Text
Row.Item("Category") = CategoryText.Text
Row.Item("Price") = PriceText.Text
etc,etc,etc

3) Add the row object into the dataset as a new row for the given table
ClassyDS.Tables("Ads").Rows.Add(Row)

This line does, in fact, add the row to the dataset table. But that only happens after the row is created and filled in. This line does not add the row to the database.
To finally do that apparently you use

Adapter.Update(ClassyDS, "Ads")

The update method causes the dataadapter to look through the dataset and find out what it needs to do. In this case, it finds one newly added row, which causes the dataadapter to look for its InsertCommand (filled in by the command builder) and execute it with the newly entered information. This causes the new row to be added to the table in the database.

The error message I get points to this line Adapter.Update(ClassyDS, "Ads")

The code in the previous not is both for enetering new info (which isnt working) and also editing existing data (which works fine)

Hope you understand all this

Thanks Again
To follow on from my previous note...

This is a link to a website where I have added all the sample code from ASP.NET for dummies in a zip file.
I have not modified this code at all, my problem is before I start working on it to learn ASP.NET, the place a new ad option is not working.

Hope someone can help here...

regards Steve

http://www28.brinkster.com/jaxxgolf/

Download the classy.zip file !!
I have put the code in a zip file on this site..

If anybody can get this sample code from ASP.NET for dummies to work when trying to Place a NEw Ad, could they please let me know what the problem is.

Thanks

http://www28.brinkster.com/jaxxgolf/
Ok ... I tried the sample and managed to reproduce the error.

Here's what's happening:

The code uses an OleDbCommandBuilder to automatically generate the SQL statement for you.

If you look at the ACCESS database you will find that one of the columns in the "Ads" table is called "Password". Of course, "Password" is a reserved word in most databases, including ACCESS. So the Insert statement that gets created ends up failing because it is misinterpreting the word "Password". Thankfully Microsoft has already thought of this problem and added two properties to the OleDbCommandBuilder object called "QuotePrefix" and "QuoteSuffix" that can employ special characters to signal to the database that you are not reffering to a reserved word. In the case of ACCESS the characters are "[" and "]". So, if you add the following two lines to ClassyAd.ascx on line 42, you will no longer get the same error.

 ClassyCB.QuotePrefix = "["
 ClassyCB.QuoteSuffix = "]"

Now you may get aNEW error that states:
"Operation must use an updateable query."

My research indicates that this may either be a permissions problem or a conversion problem. You have to make sure that the ASPNET account has permission to access the directory that contains the file "classydb.mdb". Also, if you converted the ".mdb" file from an earlier version of ACCESS you may need to create a new DB in your version of ACCESS and import the data from the original file. If none of that works go to "http://www.google.com" and search on "Operation must use an updateable query" .

Hope that helps.
Hi Nabil,
That worked a treat !!

Many thanks for your help on this !! Hopefully one day I can return the favour !!

Best Regards
Steve