Hi, ive recently started to play around with asp.net and have hit a 
problem when writing to a database. Im using a access database and the 
following code to write to it,

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As 
System.EventArgs) Handles MyBase.Load
        Dim objConnection As System.Data.OleDb.OleDbConnection
        Dim objCmd As OleDb.OleDbCommand
        Dim strConnection As String
        Dim strSQL As String
 
        If Page.IsPostBack Then
        
        strConnection = Session("strconnectstring")
        objConnection = New OleDb.OleDbConnection(strConnection)
        objConnection.Open()
        
        strSQL = "INSERT INTO auth(user,pass) VALUES ('" & TextBox1.Text 
& "','" & TextBox2.Text & "');"

        objCmd = New OleDb.OleDbCommand(strSQL, objConnection)

        objCmd.ExecuteNonQuery()

        Label1.Text = objCmd.CommandText
        'objBuilder = New System.Data.OleDb.OleDbCommandBuilder
(objAdapter)

        End if
    End Sub


And the error message i get when i post back some info from the page is

Syntax error in INSERT INTO statement.

I've probably been looking at it too long to see the obvious starting me 
in the face. Any help would be appreciated.

Cheers
Ben

have youy tried putting brackets around  user and pass? 

e.g.

        strSQL = "INSERT INTO auth([user],[pass]) VALUES ('" & TextBox1.Text

& "','" & TextBox2.Text & "');"


-----Original Message-----
From: Ben Lee [mailto:ben@a...]
Sent: Wednesday, December 04, 2002 12:05 PM
To: aspx_beginners
Subject: [aspx_beginners] write to database problem


Hi, ive recently started to play around with asp.net and have hit a 
problem when writing to a database. Im using a access database and the 
following code to write to it,

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As 
System.EventArgs) Handles MyBase.Load
        Dim objConnection As System.Data.OleDb.OleDbConnection
        Dim objCmd As OleDb.OleDbCommand
        Dim strConnection As String
        Dim strSQL As String
 
        If Page.IsPostBack Then
        
        strConnection = Session("strconnectstring")
        objConnection = New OleDb.OleDbConnection(strConnection)
        objConnection.Open()
        
        strSQL = "INSERT INTO auth(user,pass) VALUES ('" & TextBox1.Text 
& "','" & TextBox2.Text & "');"

        objCmd = New OleDb.OleDbCommand(strSQL, objConnection)

        objCmd.ExecuteNonQuery()

        Label1.Text = objCmd.CommandText
        'objBuilder = New System.Data.OleDb.OleDbCommandBuilder
(objAdapter)

        End if
    End Sub


And the error message i get when i post back some info from the page is

Syntax error in INSERT INTO statement.

I've probably been looking at it too long to see the obvious starting me 
in the face. Any help would be appreciated.

Cheers
Ben

If you're writing your aspx pages yourself (no vs.net) you may use this
approach:

<%@ page language="vb" debug="true"%>
<%@ import namespace="system.data" %>
<%@ import namespace="system.Data.Oledb" %>

<script runat="server">
Sub Page_Load(sender as object, e as eventargs)


Dim objConnection as new
oleDbConnection("provider=microsoft.jet.oledb.4.0;" & "data source=" &
server.mappath("DatabaseName.mdb"))
Dim oCmd as OleDbCommand
Dim sSQL as string

objConnection.Open()

sSql="............."
oCmd = New OleDbCommand(sSQL, objConnection)
oCmd.ExecuteNonQuery()

objConnection.Close() 

End Sub
</script>


Add your session var and your sql-string to fit. Your sql-string seem ok
to me.

When you have a problem with your sql, your first thought should always be
"what does the sql string actually contain?".  To find out, response.write
strSQL instead of executing it.

-Kim

-----Original Message-----
From: Ben Lee [mailto:ben@a...]
Sent: 4. december 2002 20:05
To: aspx_beginners
Subject: [aspx_beginners] write to database problem


Hi, ive recently started to play around with asp.net and have hit a
problem when writing to a database. Im using a access database and the
following code to write to it,

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
        Dim objConnection As System.Data.OleDb.OleDbConnection
        Dim objCmd As OleDb.OleDbCommand
        Dim strConnection As String
        Dim strSQL As String

        If Page.IsPostBack Then

        strConnection = Session("strconnectstring")
        objConnection = New OleDb.OleDbConnection(strConnection)
        objConnection.Open()

        strSQL = "INSERT INTO auth(user,pass) VALUES ('" & TextBox1.Text
& "','" & TextBox2.Text & "');"

        objCmd = New OleDb.OleDbCommand(strSQL, objConnection)

        objCmd.ExecuteNonQuery()

        Label1.Text = objCmd.CommandText
        'objBuilder = New System.Data.OleDb.OleDbCommandBuilder
(objAdapter)

        End if
    End Sub


And the error message i get when i post back some info from the page is

Syntax error in INSERT INTO statement.

I've probably been looking at it too long to see the obvious starting me
in the face. Any help would be appreciated.

Cheers
Ben

Have tried the brackets and i get a different error message now, 

Operation must use an updateable query. 

im starting to think it might be something to do with my system now



> have youy tried putting brackets around  user and pass? 

e.g.

        strSQL = "INSERT INTO auth([user],[pass]) VALUES ('" & 
TextBox1.Text

& "','" & TextBox2.Text & "');"

Tried that code, still get the same error messages, either

Operation must use an updateable query. 

when i use the square brackets and the orignal one when i dont.

> If you're writing your aspx pages yourself (no vs.net) you may use this
approach:

<%@ page language="vb" debug="true"%>
<%@ import namespace="system.data" %>
<%@ import namespace="system.Data.Oledb" %>

<script runat="server">
Sub Page_Load(sender as object, e as eventargs)


Dim objConnection as new
oleDbConnection("provider=microsoft.jet.oledb.4.0;" & "data source=" &
server.mappath("DatabaseName.mdb"))
Dim oCmd as OleDbCommand
Dim sSQL as string

objConnection.Open()

sSql="............."
oCmd = New OleDbCommand(sSQL, objConnection)
oCmd.ExecuteNonQuery()

objConnection.Close() 

End Sub
</script>


Add your session var and your sql-string to fit. Your sql-string seem ok
to me.

Thanks Kim, have outputted the SQL to the screen instead of executing it 
and it prints

INSERT INTO auth([user],[pass]) VALUES ('test','test'); 

which seems valid to me, as I said I think it might be my machine, gonna 
send it to a mate to try on his to see if I get the same errors.

Ben

> When you have a problem with your sql, your first thought should always 
be
"what does the sql string actually contain?".  To find out, response.write
strSQL instead of executing it.

-Kim

All is fixed, thankyou all for your help, seems it was a security 
setting, id not given write access to the web account on my computer, 
doh!!!

Ben