Ado .net Insert Into [resolved]

Printable View

I have problems with the folowing code, and I don't know what's wrong with it...
VB Code:
Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0;data source=" & Server.MapPath("ProjectDB.mdb") & ";"
Dim cn As OleDbConnection
cn = New OleDbConnection(connString)
cn.Open()
 
Dim sql_ins As String = "INSERT INTO t_user (userid, username, password) VALUES (2, 'test', 'some password');"
Dim cmd_ins As OleDbCommand
cmd_ins = New OleDbCommand(sql_ins, cn)
cmd_ins.ExecuteNonQuery()
If I try to do select statements, it works fine, I can retreive the data, but it does not work for insert...

Mar 18th, 2003, 04:14 PM
Cander

telling us what error or what happens is helpful.
Mar 18th, 2003, 04:16 PM
CVMichael

this is what I get when I do exception.Message

System.Data.OleDb.OleDbException: Syntax error in INSERT INTO statement.
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr)
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
at dforums.NewUser.btnSubmit_Click(Object sender, EventArgs e) in C:\Inetpub\Public\dforums\NewUser.aspx.vb:line 96
Mar 18th, 2003, 04:16 PM
Cander

Try change password to [password] in the statement
Mar 18th, 2003, 04:19 PM
CVMichael

nope, same thing.... I even did that for all of them [userid] and [username], and still same error...
Mar 18th, 2003, 04:20 PM
Cander

well userid an username wouldnt have been an issue, only password since it is some kind of reserved word for access.

Try removing the ; then, maybe it doesnt like that as well as [password]
Mar 18th, 2003, 04:25 PM
CVMichael

I removed the ; and still same error...

This is the t_user table viewed in Access, only the first 3 fields are required, the rest are not required and Allow Zero Length is set to Yes

http://vbforums.com/attachment.php?s=&postid=1388376
Mar 18th, 2003, 04:28 PM
Cander

paste your code exactly as you have it now. something else is happening

I renamed the field password to upassword... (still same error)
ps: this is a web application

VB Code:

Private Sub btnSubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
        If txtPassword.Text <> "d41d8cd98f00b204e9800998ecf8427e" AndAlso txtUserName.Text.Length > 0 Then
            Dim cn As OleDbConnection = Session("Connection")
            Dim cmd_chk As OleDbCommand
            Dim dr_chk As OleDbDataReader
 
            Try
                If cn.State <> ConnectionState.Open Then cn.Open()
 
                cmd_chk = New OleDbCommand("SELECT username FROM t_user where username = '" & txtUserName.Text & "'", cn)
                dr_chk = cmd_chk.ExecuteReader()
 
                If dr_chk.Read() Then
                    Response.Write("<http><body>" & vbNewLine)
                    Response.Write("Username already taken.<br>" & vbNewLine)
                    Response.Write("<a href=""NewUser.aspx"">Click here to go back and try again</a>" & vbNewLine)
                    Response.Write("</body></html>")
                    Response.End()
                Else
                    If Not dr_chk Is Nothing Then dr_chk.Close()
                    If Not cmd_chk Is Nothing Then cmd_chk.Dispose()
 
                    'Dim sql_ins As String = "INSERT INTO t_user (userid, username, password, email, " & _
                    '    "signature, webpage, occupation, interests, location, biography, date_registered) " & _
                    '    "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);"
                    Dim sql_ins As String = "INSERT INTO t_user (userid, username, upassword) " & _
                        "VALUES (2, 'test', 'some password')"
 
                    Dim cmd_ins As OleDbCommand
                    'Dim par As OleDbParameter
 
                    Try
                        'Dim trn As OleDbTransaction = cn.BeginTransaction()
                        cmd_ins = New OleDbCommand(sql_ins, cn)
                        'trn.Begin()
 
                        'With cmd_ins
                            '.Parameters.Clear()
                            '.Parameters.Add(New OleDb.OleDbParameter("@userid", OleDbType.Numeric))
                            '.Parameters.Add(New OleDb.OleDbParameter("@username", OleDbType.VarChar, 15))
                            '.Parameters.Add(New OleDb.OleDbParameter("@password", OleDbType.VarChar, 32))
 
                            '.Parameters.Add(New OleDb.OleDbParameter("@userid", 2))
                            '.Parameters.Add(New OleDb.OleDbParameter("@username", txtUserName.Text))
                            '.Parameters.Add(New OleDb.OleDbParameter("@password", txtPassword.Text))
                            '.Parameters.Add(New OleDb.OleDbParameter("@email", OleDbType.VarChar, 128))
                            '.Parameters.Add(New OleDb.OleDbParameter("@signature", OleDbType.VarChar, 255))
 
                            '.Parameters.Add(New OleDb.OleDbParameter("@webpage", OleDbType.VarChar, 255))
                            '.Parameters.Add(New OleDb.OleDbParameter("@occupation", OleDbType.VarChar, 100))
                            '.Parameters.Add(New OleDb.OleDbParameter("@interests", OleDbType.VarChar, 255))
                            '.Parameters.Add(New OleDb.OleDbParameter("@location", OleDbType.VarChar, 100))
                            '.Parameters.Add(New OleDb.OleDbParameter("@biography", OleDbType.VarChar, 255))
                            '.Parameters.Add(New OleDb.OleDbParameter("@date_registered", OleDbType.Date))
 
                            '.Parameters("@userid").Value = 2
                            '.Parameters("@username").Value = txtUserName.Text
                            '.Parameters("@password").Value = txtPassword.Text
                            '.Parameters("@date_registered").Value = Now
                            'End With
                        cmd_ins.ExecuteNonQuery()
                        'trn.Commit()
 
 
                        Response.Redirect("LogIn.aspx")
                    Catch ex As OleDbException
                        Response.Write("<http><body>" & vbNewLine)
                        Response.Write("Error inserting: " & Now & "<br>" & vbNewLine)
                        Response.Write(ex.Message.Replace(vbNewLine, "<br>") & "<br>" & vbNewLine)
                        Response.Write(ex.ToString.Replace(vbNewLine, "<br>") & "<br>" & vbNewLine)
                        Response.Write("<a href=""NewUser.aspx"">Click here to go back and try again</a>" & vbNewLine)
                        Response.Write("</body></html>")
                        Response.End()
                    Finally
                        If Not cmd_ins Is Nothing Then cmd_ins.Dispose()
                    End Try
                End If
            Catch ex As OleDbException
                Response.Write("<http><body>" & vbNewLine)
                Response.Write("Error:<br>" & vbNewLine)
                Response.Write(ex.Message.Replace(vbNewLine, "<br>") & "<br>" & vbNewLine)
                Response.Write("<a href=""NewUser.aspx"">Click here to go back and try again</a>" & vbNewLine)
                Response.Write("</body></html>")
                Response.End()
            Finally
                If Not dr_chk Is Nothing Then dr_chk.Close()
                If Not cmd_chk Is Nothing Then cmd_chk.Dispose()
            End Try
        Else
            Response.Write("<http><body>" & vbNewLine)
            Response.Write("You MUST type a User Name and Password<br>" & vbNewLine)
            Response.Write("<a href=""NewUser.aspx"">Click here to go back and try again</a>" & vbNewLine)
            Response.Write("</body></html>")
            Response.End()
        End If
    End Sub

Mar 18th, 2003, 04:33 PM
Cander

the only other thing to check is your database fields, you might have one of them set to required and dont realize it. That is the only othe thing that may cause the error you reported, unless you are now getting a different error.
Mar 18th, 2003, 04:37 PM
CVMichael

ex.Message returns:
Operation must use an updateable query.

ex.ToString returns:
System.Data.OleDb.OleDbException: Operation must use an updateable query.
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr)
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
at dforums.NewUser.btnSubmit_Click(Object sender, EventArgs e) in C:\Inetpub\Public\dforums\NewUser.aspx.vb:line 97

what's "Operation must use an updateable query." ?
Mar 18th, 2003, 04:41 PM
Cander

http://www.dotnet247.com/247referenc...30/152303.aspx
Mar 18th, 2003, 04:44 PM
CVMichael

"You need to set the folder and the database permissions
so that ASPNET user has read, write."

How do I do that ?
Mar 18th, 2003, 04:48 PM
CVMichael

nevermind, I got it working.... the database was set to hidden attribute (i did that because I don't want anyone to download the database) but i guess I'll move it in a directory that is not shared on the web...

Thanx for everything...
Mar 18th, 2003, 04:49 PM
Cander

Right click the mdb. Select Properties. goto the Security tab
Mar 18th, 2003, 04:52 PM
CVMichael

I never thought that would be a problem, because i could add/modify data in the database from Access, so i thought it should work in my app also...

I moved the DB to a directory where permissions are set to eveyone, and there is no need to make it hidden....

Thanks again
Mar 18th, 2003, 04:55 PM
Cander

"because i could add/modify data in the database from Access,"

yeah but that would happen only under YOUR account, not the ASPNET account. But Everyone include ASP_NET, so that will be fine. But I would strongly suggest for security reasons, you lock it down to only allow you and ASPNET to access it.