adapter update problem Syntax error in INSERT INTO statement.

Discussion in 'ASP .Net' started by compuglobalhypermeganetz0r, Dec 8, 2003.

  1. I am having trouble getting an adapter to update my access database, it
    gives the error Syntax error in INSERT INTO statement. for
    Line 98: Adapter.Update(WineDS, "tblWines")

    Below is my code:

    <%@ Control Explicit="True" Language="VB" Debug="True" %>
    <%@ Import Namespace="System.Data" %>
    <%@ Import Namespace="System.Data.OleDb" %>

    <script runat="server">
    Dim ConnectString, SelectStatement As String
    Dim Connect As OleDbConnection = New OleDbConnection
    Dim Adapter As OleDbDataAdapter = New OleDbDataAdapter
    Dim WineCB As OleDbCommandBuilder
    Dim WineDS As DataSet = New DataSet
    Dim Row As DataRow

    Public IDNum As Integer
    Public Region As String
    Public Vintage As Integer
    Public Type As String
    Public Brand As String
    Public Maker As String
    Public YearToDrink As Integer
    Public BestVintage As Integer
    Public Food as String
    Public Medals as String
    Public Purchase as String
    Public Value as String
    Public Alchohol as Double
    Public Website as String
    Public Notes as String
    Public Quantity as Integer
    Public Bin as Integer

    Public HasErrors As Boolean
    Public RowError As String

    Sub GetWine(IDSent As Integer)

    SelectStatement = "Select * From tblWines Where IDNum=" & IDSent

    ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=c:\aspnet\data\winelist.mdb"

    Connect.ConnectionString = ConnectString

    Adapter.SelectCommand = _
    new OleDbCommand(SelectStatement, Connect)

    Adapter.Fill(WineDS,"tblWines")

    WineCB = New OleDbCommandBuilder(Adapter)

    If WineDS.Tables("tblWines").Rows.Count > 0 Then
    Row = WineDS.Tables("tblWines").Rows(0)

    IDNum = Row.Item("IDNum")
    Region = Row.Item("Region")
    Vintage = Row.Item("Vintage")
    Type = Row.Item("Type")
    Brand = Row.Item("Brand")
    Maker = Row.Item("Maker")
    YearToDrink = Row.Item("YearToDrink")
    BestVintage = Row.Item("BestVintage")
    Food = Row.Item("Food")
    Medals = Row.Item("Medals")
    Purchase = String.Format("{0:c}", Row.Item("Purchase"))
    Value = String.Format("{0:c}", Row.Item("Value"))
    Alchohol = Row.Item("Alchohol")
    Website = Row.Item("Website")
    Notes = Row.Item("Notes")
    Quantity = Row.Item("Quantity")
    Bin = Row.Item("Bin")

    BrandLabel.Text = Brand
    TypeLabel.Text = Type
    VintageLabel.Text = Vintage
    NotesLabel.Text = Notes
    FoodLabel.Text = Food
    AlchoholLabel.Text = Alchohol
    YearToDrinkLabel.Text = YearToDrink
    BestVintageLabel.Text = BestVintage
    RegionLabel.Text = Region
    MakerLabel.Text = Maker
    MedalsLabel.Text = Medals
    PurchaseLabel.Text = Purchase
    ValueLabel.Text = Value
    WebsiteHyperLink.NavigateUrl = Website
    WebsiteHyperLink.Text = Website
    QuantityLabel.Text = Quantity
    BinLabel.Text = Bin
    End If

    PostErrors
    End Sub

    Sub AddWine
    Row = WineDS.Tables("tblWines").NewRow

    CopyToDS
    WineDS.Tables("tblWines").Rows.Add(Row)
    Adapter.Update(WineDS, "tblWines")
    PostErrors
    End Sub

    Sub EditWine
    CopyToDS
    Adapter.Update(WineDS, "tblWines")
    PostErrors
    End Sub

    Sub RemoveWine
    WineDS.Tables("tblWines").Rows(0).Delete
    Adapter.Update(WineDS, "tblWines")
    PostErrors
    End Sub


    ' Copy the values in the properties of this
    ' control in the WineDS
    Private Sub CopyToDS
    Row.Item("IDNum") = CStr(IDNum)
    Row.Item("Region") = CStr(Region)
    Row.Item("Vintage") = CInt(Vintage)
    Row.Item("Type") = CStr(Type)
    Row.Item("Brand") = CStr(Brand)
    Row.Item("Maker") = CStr(Maker)
    Row.Item("YearToDrink") = CInt(YearToDrink)
    Row.Item("BestVintage") = CInt(BestVintage)
    Row.Item("Food") = CStr(Food)
    Row.Item("Medals") = CStr(Medals)
    Row.Item("Purchase") = CSng(Purchase)
    Row.Item("Value") = CSng(Value)
    Row.Item("Alchohol") = CSng(Alchohol)
    Row.Item("Website") = CStr(Website)
    Row.Item("Notes") = CStr(Notes)
    Row.Item("Quantity") = CInt(Quantity)
    Row.Item("Bin") = CInt(Bin)
    End Sub

    Private Sub PostErrors
    HasErrors = WineDS.HasErrors
    If HasErrors Then
    RowError = WineDS.Tables("tblWines").Rows(0).RowError
    End If
    End Sub

    </script>


    <table border=1 width=100% bordercolor=#FFFFFF
    cellspacing=0 cellpadding=2 bgcolor=#990000>
    <tr>
    <td bgcolor=#990000 colspan=2><font color="#FFFF00"><asp:label
    id="BrandLabel" runat="server"
    font-size="12 pt" font-bold="true" /> <asp:label id="TypeLabel"
    runat="server"
    font-size="12 pt" font-bold="true" /> <asp:label id="VintageLabel"
    runat="server"
    font-size="12 pt" font-bold="true" /></font> </td>
    </tr>
    <tr>
    <td width=25% valign=top><font color="#FFFFFF">Notes:</font></td>
    <td> <font color="#FFFFFF">
    <asp:label id="NotesLabel" runat="server"
    font-size="12 pt" /> </font></td>
    </tr>
    <tr>
    <td width=25%><font color="#FFFFFF">Food Suggestion:</font></td>
    <td> <font color="#FFFFFF">
    <asp:label id="FoodLabel" runat="server"
    font-size="12 pt" /> </font></td>
    </tr>
    <tr>
    <td width=25%><font color="#FFFFFF">Alchohol Content:</font></td>
    <td> <font color="#FFFFFF">
    <asp:label id="AlchoholLabel" runat="server"
    font-size="12 pt" /> % </font></td>
    </tr>
    <tr>
    <td width=25%><font color="#FFFFFF">Best Year To Drink:</font></td>
    <td> <font color="#FFFFFF">
    <asp:label id="YearToDrinkLabel" runat="server"
    font-size="12 pt" /> </font></td>
    </tr>
    <tr>
    <td width=25%><font color="#FFFFFF">Best Vintage:</font></td>
    <td> <font color="#FFFFFF">
    <asp:label id="BestVintageLabel" runat="server"
    font-size="12 pt" /> </font></td>
    </tr>
    <tr>
    <td width=25%><font color="#FFFFFF">Region:</font></td>
    <td> <font color="#FFFFFF">
    <asp:label id="RegionLabel" runat="server"
    font-size="12 pt" /> </font>
    <tr>
    <td width=25%><font color="#FFFFFF">Wine Maker:</font></td>
    <td> <font color="#FFFFFF">
    <asp:label id="MakerLabel" runat="server"
    font-size="12 pt" /> </font>
    <tr>
    <td width=25%><font color="#FFFFFF">Medals Won:</font></td>
    <td> <font color="#FFFFFF">
    <asp:label id="MedalsLabel" runat="server"
    font-size="12 pt" /> </font>
    <tr>
    <td width=25%><font color="#FFFFFF">Purchase Price:</font></td>
    <td> <font color="#FFFFFF">
    <asp:label id="PurchaseLabel" runat="server"
    font-size="12 pt" /> </font>
    <tr>
    <td width=25%><font color="#FFFFFF">Value:</font></td>
    <td> <font color="#FFFFFF">
    <asp:label id="ValueLabel" runat="server"
    font-size="12 pt" /> </font>
    <tr>
    <td width=25%><font color="#FFFFFF">Website:</font></td>
    <td> <font color="#FFFFFF"><asp:hyperlink id="WebsiteHyperLink"
    runat="server" target='_blank"
    font-size="12 pt" /> </font>
    <tr>
    <td width=25%><font color="#FFFFFF">Quantity:</font></td>
    <td> <font color="#FFFFFF">
    <asp:label id="QuantityLabel" runat="server"
    font-size="12 pt" /> </font>
    <tr>
    <td width=25%><font color="#FFFFFF">Bin:</font></td>
    <td> <font color="#FFFFFF">
    <asp:label id="BinLabel" runat="server"
    font-size="12 pt" /> </font></td>
    </tr>
    </table>
    compuglobalhypermeganetz0r, Dec 8, 2003
    #1
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Saber
    Replies:
    5
    Views:
    523
    Jo Inferis
    Jul 20, 2004
  2. Neil Zanella
    Replies:
    3
    Views:
    660
    MWells
    Jan 25, 2005
  3. Nathan Sokalski

    SQL syntax error in INSERT INTO statement

    Nathan Sokalski, Jul 1, 2005, in forum: ASP .Net
    Replies:
    5
    Views:
    14,597
    ahmed_samir
    Sep 3, 2010
  4. Gérard Leclercq

    Syntax error in INSERT INTO statement

    Gérard Leclercq, Feb 1, 2005, in forum: ASP General
    Replies:
    6
    Views:
    213
    Bob Barrows [MVP]
    Feb 2, 2005
  5. solomon_13000

    -2147217900:Syntax error in INSERT INTO statement.

    solomon_13000, Oct 2, 2006, in forum: ASP General
    Replies:
    1
    Views:
    267
    Bob Barrows [MVP]
    Oct 2, 2006
Loading...

Share This Page