By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,559 Members | 1,180 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,559 IT Pros & Developers. It's quick & easy.

adapter update problem Syntax error in INSERT INTO statement.

P: n/a
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)


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

End Sub

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

Adapter.Update(WineDS, "tblWines")
End Sub

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

Sub RemoveWine
Adapter.Update(WineDS, "tblWines")
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

<table border=1 width=100% bordercolor=#FFFFFF
cellspacing=0 cellpadding=2 bgcolor=#990000>
<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"
font-size="12 pt" font-bold="true" /> <asp:label id="VintageLabel"
font-size="12 pt" font-bold="true" /></font> </td>
<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>
<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>
<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>
<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>
<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>
<td width=25%><font color="#FFFFFF">Region:</font></td>
<td> <font color="#FFFFFF">
<asp:label id="RegionLabel" runat="server"
font-size="12 pt" /> </font>
<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>
<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>
<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>
<td width=25%><font color="#FFFFFF">Value:</font></td>
<td> <font color="#FFFFFF">
<asp:label id="ValueLabel" runat="server"
font-size="12 pt" /> </font>
<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>
<td width=25%><font color="#FFFFFF">Quantity:</font></td>
<td> <font color="#FFFFFF">
<asp:label id="QuantityLabel" runat="server"
font-size="12 pt" /> </font>
<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>
Nov 18 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.