[RESOLVED] Can see data but not write to Access Database

Yes I'm a newb, flame if you must. Help if you can. All suggestions and criticism welcome. Thanks in advance, Rankun

Using .Net 2002 trying to connect and edit an Access 2003 database (eventually from a website)

I can display the data, but when I try to add a record it errors out.

Error: Index #0 Message: Syntax error in INSERT INTO statement. Native: -529665454 Source: Microsoft JET Database Engine SQL: 3000

My test database is a simple one table with three fields: autonumber, lastname and first name.

What I am trying to do is create a webpage that I can update an access database with (small personal calorie counting program when finished).

Here is my test code:

<%@ Page Language="vb" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.OleDb" %>
<HTML>
<script runat="server">
Dim MyConnection As OleDbConnection
Dim MyCommand As OleDbCommand

Sub Page_Load(Sender As Object, E As EventArgs)

MyConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:/Inetpub/wwwroot/AccessTest/DB/test.mdb")

If Not (IsPostBack)
BindGrid()
End If
End Sub

Sub AddRow_Click(Sender As Object, E As EventArgs)

Dim InsertCmd As String = "INSERT INTO table1 ([last],[first]) values (@last, @first"

MyCommand = New OleDbCommand(InsertCmd, MyConnection)

MyCommand.Parameters.Add(New OleDbParameter("@last", OleDbType.VarChar, 50))
MyCommand.Parameters("@last").Value = Server.HtmlEncode(last.Value)

MyCommand.Parameters.Add(New OleDbParameter("@first", OleDbType.VarChar, 50))
MyCommand.Parameters("@first").Value = Server.HtmlEncode(first.Value)

MyCommand.Connection.Open()

Try
MyCommand.ExecuteNonQuery()
Message.InnerHtml = "<b>Record Added</b><br>" & InsertCmd.ToString()

Catch myException As OleDbException

Dim i As Integer
For i = 0 To myException.Errors.Count - 1
Message.InnerHtml = ("Index #" + i.ToString() + ControlChars.Cr _
+ "Message: " + myException.Errors(i).Message + ControlChars.Cr _
+ "Native: " + myException.Errors(i).NativeError.ToString() + ControlChars.Cr _
+ "Source: " + myException.Errors(i).Source + ControlChars.Cr _
+ "SQL: " + myException.Errors(i).SQLState + ControlChars.Cr)
Next i

Message.Style("color") = "red"

End Try

MyCommand.Connection.Close()

BindGrid()
End Sub

Sub BindGrid()

Dim MyAdapter As OleDbDataAdapter = new OleDbDataAdapter("select * from table1", MyConnection)
Dim DS As DataSet = new DataSet()

MyAdapter.Fill(DS, "table1")

MyDataGrid.DataSource=DS.Tables("table1").DefaultView
MyDataGrid.DataBind()
End Sub

</script>
<body>
<form runat="server" ID="Form1">
<h3><font face="Verdana">Inserting a new row into the Table.</font></h3>
<table width="95%">
<tr>
<td valign="top">
<ASP:DataGrid id="MyDataGrid" runat="server" Width="700" BackColor="#ccccff" BorderColor="black" ShowFooter="false" CellPadding="3" CellSpacing="0" Font-Name="Verdana" Font-Size="8pt" HeaderStyle-BackColor="#aaaadd" EnableViewState="false" />
</td>
<td valign="top">
<table>
<tr>
<td colspan="2" bgcolor="#aaaadd">Add a New Record:</td>
<tr>
<td nowrap>Last:
</td>
<td><input type="text" id="last" runat="server" NAME="last"></td>
</tr>
<tr>
<td>First:
</td>
<td><input type="text" id="first" runat="server" NAME="first"></td>
</tr>
<tr>
<td></td>
<td>
<INPUT id="Submit" type="submit" value="Add the row" name="Submit" runat="server" OnServerClick="AddRow_Click">
</td>
</tr>
<tr>
<td colspan="2" align="middle">
<span id="Message" EnableViewState="false" runat="server"></span>
</td>
</tr>
</table>
</td>
</tr>
</table>
</form>
</body>
</HTML>

Re: Can see data but not write to Access Database

Dim InsertCmd As String = "INSERT INTO table1 ([last],[first]) values (@last, @first)"

I added ) to the end of the query.

Re: Can see data but not write to Access Database

Now I feel silly :blush: Thanks Deepak!!!