1. Home
  2. Forums Index
  3. Server Side / Microsoft IIS Web Server and ASP.NET 3:51 pm Apr 28, 2026

Forum Moderators: open

Message Too Old, No Replies

Problem Adding A New Row To Access Database using ADO.NET

         

prodigy27

3:34 pm on May 28, 2010 (gmt 0)

10+ Year Member



Hi. I am trying to add a new row to access database but i am getting an error. Could someone help me please? Thanks.

Here is the ASP Codes:
<snip> See correct code in the next post

Here is the error message i am getting:

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 29: ClassyDS.Tables("Ads").Rows.Add(Row)
Line 30:
Line 31: Adapter.Update(ClassyDS, "Ads")
Line 32:
Line 33: If ClassyDS.HasErrors Then


Source File: C:\MemberSites\MemberSites_AspSPider_Net\aant2010\webroot\placead.aspx Line: 31

Stack Trace:

[OleDbException (0x80040e14): Syntax error in INSERT INTO statement.]
System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount) +1437055
System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount) +45
System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping) +2001
System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping) +41
System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable) +176
ASP.placead_aspx.Submit_Click(Object Sender, EventArgs E) in C:\MemberSites\MemberSites_AspSPider_Net\aant2010\webroot\placead.aspx:31
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +118
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +112
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +36
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5563

[edited by: marcel at 4:56 pm (utc) on May 28, 2010]

prodigy27

3:45 pm on May 28, 2010 (gmt 0)

10+ Year Member



oops. sorry. here is the correct codes.

<%@ Page Explicit="True" Language="VB" Debug="True" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<html>
<script runat="server">
Dim ConnectString, SelectStatement As String
Dim Connect As OleDbConnection = New OleDbConnection
Dim Adapter As OleDbDataAdapter = New OleDbDataAdapter
Dim ClassyCB As OleDbCommandBuilder
Dim ClassyDS As DataSet = New DataSet
Dim Row As DataRow
Dim AdNumSent As Integer
Dim CatListIndex As Integer

Sub Submit_Click(Sender As Object, E As EventArgs)
AdNumSent = Request.QueryString("AdNum")
GetAd(0)
Row = ClassyDS.Tables("Ads").NewRow
Row.Item("Title") = TitleText.Text
Row.Item("Description") = DescriptionText.Text
Row.Item("Category") = CategoryDropDown.SelectedItem.Value
Row.Item("Price") = PriceText.Text
Row.Item("Phone") = PhoneText.Text
Row.Item("Email") = EmailText.Text
Row.Item("State") = StateText.Text
Row.Item("Password") = PasswordText.Text
Row.Item("Posted") = Today

ClassyDS.Tables("Ads").Rows.Add(Row)

Adapter.Update(ClassyDS, "Ads")

If ClassyDS.HasErrors Then
Message.Text = "There was an error placing your ad. " & _
ClassyDS.Tables("Ads").Rows(0).RowError
Else
Message.Text = "Your classified ad has been placed"
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

Sub GetAd(AdNumSent As Integer)
SelectStatement = "Select * From Ads Where AdNum=" & AdNumSent
ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\inetpub\wwwroot\classy\classydb.mdb"
Connect.ConnectionString = ConnectString
Adapter.SelectCommand = _
new OleDbCommand(SelectStatement, Connect)
ClassyCB = New OleDbCommandBuilder(Adapter)
Adapter.Fill(ClassyDS,"Ads")
End Sub
</script>
<body vlink=red>
<form runat="server">
<asp:label id="Header" runat="server"
text="Place an Ad" font-name="arial"
font-size="22" font-bold="true"/>
<p>Please fill in <i>all</i> 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>

<table>
<tr><td>Title:</td><td>
<asp:textbox id="TitleText" runat="server" columns="50"/>
</td></tr>

</td></tr><td valign=top>Description:</td><td>
<asp:textbox id="DescriptionText" runat="server"
textmode="multiline" rows="3" columns="40" />

</td></tr><tr><td>Category:</td><td>
<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:textbox id="PriceText" runat="server" columns="10" />
</td></tr><tr><td>Phone</td><td>
<asp:textbox id="PhoneText" runat="server" columns="15" />
</td></tr><tr><td>Email:</td><td>
<asp:textbox id="EmailText" runat="server" columns="50" />
</td></tr><tr><td>State:</td><td>
<asp:textbox id="StateText" runat="server"
columns="2"/>

</td></tr><tr><td>Password:</td><td>
<asp:textbox id="PasswordText" runat="server"
columns="15" textmode="password"/>

</td></tr><tr><td>
<asp:button id="Submit" runat="server"
text="Place Ad" onclick="Submit_Click"/>

</td><td align="center">
<asp:label id="Message" runat="server"
font-italic="true" font-size="16 pt" />
</td></tr>
</table>

<br><center>
<asp:hyperlink id="HomeLink" runat="server"
navigateurl="default.aspx" font-bold="true"
font-name="Arial" font-size="12 pt">
[ Home ]</asp:hyperlink>
</center>
</form>
</body>
</html>

Ocean10000

2:50 am on May 29, 2010 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member Top Contributors Of The Month



The following Code should work, but I did not try to compile it. What I did was fully define both Select statement and Insert statements including using parameters to avoid sql injection style attacks.

I also removed OleDbCommandBuilder since we won't need it. This also save a bit of resources by doing this.

Sub GetAd(ByVal AdNumSent As Integer)

Dim SelectStatement As String = "Select AdNum, Title, Description, Category, Price, Phone, Email, State, Password, Posted From Ads Where AdNum=@AdNumSent"

Dim InsertStatement As String = "Insert into Ads (Title, Description, Category, Price, Phone, Email, State, Password, Posted) values (@Title, @Description, @Category, @Price, @Phone, @Email, @State, @Password, @Posted);
Select Select AdNum,Title,Description,Category,Price,Phone,Email,State,Password,Posted From Ads Where Title=@Title, Description=@Description, Category=@Category, Price=@Price, Phone=@Phone, Email=@Email, State=@State, Password=@Password, Posted=@Posted"

ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\inetpub\wwwroot\classy\classydb.mdb"
Connect.ConnectionString = ConnectString
Adapter.SelectCommand = New OleDbCommand(SelectStatement, Connect)
Adapter.SelectCommand.Parameters.Add(New SqlParameter("@AdNumSent", AdNumSent))
Adapter.InsertCommand = New OleDbCommand(InsertStatement, Connect)
Adapter.InsertCommand.Parameters.Add(New SqlParameter("@Title", String.Empty()))
Adapter.InsertCommand.Parameters.Add(New SqlParameter("@Description", String.Empty()))
Adapter.InsertCommand.Parameters.Add(New SqlParameter("@Category", String.Empty()))
Adapter.InsertCommand.Parameters.Add(New SqlParameter("@Category", String.Empty()))
Adapter.InsertCommand.Parameters.Add(New SqlParameter("@Price", String.Empty()))
Adapter.InsertCommand.Parameters.Add(New SqlParameter("@Phone", String.Empty()))
Adapter.InsertCommand.Parameters.Add(New SqlParameter("@Email", String.Empty()))
Adapter.InsertCommand.Parameters.Add(New SqlParameter("@State", String.Empty()))
Adapter.InsertCommand.Parameters.Add(New SqlParameter("@Password", String.Empty()))
Adapter.InsertCommand.Parameters.Add(New SqlParameter("@Posted", String.Empty()))
Adapter.Fill(ClassyDS, "Ads")
End Sub
 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

  1. Home
  2. Forums Index
  3. Server Side / Microsoft IIS Web Server and ASP.NET 3:51 pm Apr 28, 2026