Results 1 to 3 of 3

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

  1. Aug 30th, 2005, 01:06 AM #1
    rankun
    rankun is offline

    Thread Starter
    New Member
    Join Date
    Aug 2005
    Posts
    2

    Resolved [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">
    <ASPataGrid 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>
    Reply With Quote Reply With Quote
  2. Aug 30th, 2005, 01:26 AM #2
    Deepak Sakpal
    Deepak Sakpal is offline
    PowerPoster Deepak Sakpal's Avatar
    Join Date
    Mar 2002
    Location
    Mumbai, India
    Posts
    2,424

    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.
    Last edited by Deepak Sakpal; Aug 30th, 2005 at 01:29 AM.
    Reply With Quote Reply With Quote
  3. Aug 30th, 2005, 02:02 AM #3
    rankun
    rankun is offline

    Thread Starter
    New Member
    Join Date
    Aug 2005
    Posts
    2

    Red face Re: Can see data but not write to Access Database

    Now I feel silly Thanks Deepak!!!
    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

Forum Rules


Click Here to Expand Forum to Full Width