Cannot insert into Access db - Please Help!

Hello: I'm really stumped on this one. I've reviewed my code and don't see what is wrong, and I get no errors, and absolutely nothing happens when the submit button is clicked:

I'm posing my code below, but some background....

On another page, I used a datareader to display the information, so I have to assume that my connections are fine. Any help is appreciated, as I just don't know what else to try.

Sub Submit1_Click (s as Object, e as EventArgs)

Dim dbconn as oledbConnection
Dim dbCMD as oleDbCommand
Dim dtr as oledbdatareader
Dim cmdInsert as oleDbCommand
dim strInsert as String

dbConn = New oledbConnection ("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA Source=" & server.MapPath("_database/LS_Contacts.mdb"))
dbConn.open()

strInsert = "Insert Into LS_Contacts " _
& " (FirstName, LastName) " _
& " Values ('Test50','Test 60')"


cmdInsert = New OleDbCommand( strInsert, dbConn)

dbConn.open()
cmdInsert.ExecuteNonQuery


dbConn.close()

End Sub

 

If nothing happens when the submit button is clicked, then I suspect Submit1_Click is never called. You'll have to search around your code to find out why this isn't being called.


I recommend putting in a 'Throw New Exception("...")' line so you can easily identify when that Sub _is_ being called.


Joel Thoms

DiscountASP.NET
http://www.DiscountASP.NET

 

It may be that you are trying to open the same connection twice and the app is bailing before it ever calls the Execute. Remove one of the dbConn.open() lines. Doesn't matter which you remove, but I would remove the first one. If anything else fails below the first one, you will leave an open connection.

-Jeff

 

I have been having the same problem inserting records into msaccess database.

Hereis my code:




Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click


Dim conn As OleDbConnection


Dim strSelect As String


Dim dadInput As OleDbDataAdapter


Dim insertCmd As OleDbCommand


Me.TextBox1.Text = "work0"


Try


Me.TextBox1.Text = "work1"


conn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath(".") & "\_database\fieldInput.mdb;User Id=admin;Password=;")


Dim planStart As String = CStr(Me.planStart1.Text) & ":" & CStr(Me.planStart1.Text)


strSelect = "INSERT INTO Raw_Crew_Data ( Date_1, Subdivision_Number_2, Crew_Number_3 ) values (2, 1, 1)"


Me.TextBox1.Text = "work2"


insertCmd = New OleDbCommand(strSelect, conn)


Me.TextBox1.Text = "work3"


insertCmd.Connection.Open()


Me.TextBox1.Text = "work4"


insertCmd.ExecuteNonQuery()


Me.TextBox1.Text = "work5"


Me.TextBox2.Text = CStr(Me.planStart1.Text) & ":" & CStr(Me.planStart2.Text)


Catch


'MsgBox("errors")


Finally


insertCmd.Connection.Close()


End Try


End Sub


I am changing the textBox1.text to see where the code stops. I get to 'work4' in the textbox so the code is not running inserCmd.executeNonQuery. I am thinking that the problem is a Frontpage problem and am thinking that I will go buy visual studio 2005 tomorrow





I think that one of your problems is that you never really close the connection because the error at inserCmd.executeNonQuery stops the code and never continues to the close command. At least that is what I kept running into.


Dan

 

I have the same problem too. I am sure that my subroutine was called because I have the output from the exception handling (see codes at the bottom of message):


Syntax error in INSERT INTO statement.


INSERT INTO LIMS_OPERATOR (name, password, full_name) VALUES ('hello', 'xs34234','hello there')


I opened the access database andcopied and pasted thecommand textto the access query. It inserts a record in access without any problem. Can any one help me figuring out what is the problem?


Thanks


Quincy




Private Sub AddUser()


Dim sCmd As String


Dim conn As Data.OleDb.OleDbConnection


Dim sConn As String


Dim cmd As Data.OleDb.OleDbCommand


Dim i As Integer


sConn = "Provider=Microsoft.Jet.OLEDB.4.0;"


sConn += "Data Source=" & Server.MapPath("\_database\LIMSDB.mdb")


sCmd = "INSERT INTO LIMS_OPERATOR (name, password, full_name) VALUES ("


scmd = scmd & "'" & txtUserName.Text & "', '" & txtPassword.Text & "',"


sCmd = sCmd & "'" & txtFullName.Text & "')"


conn = New Data.OleDb.OleDbConnection(sConn)


Try


conn.Open()


cmd = New Data.OleDb.OleDbCommand(sCmd, conn)


Label1.Text = cmd.CommandText


i = cmd.ExecuteNonQuery()


Catch ex As Exception


Label1.Text = ex.Message & vbCrLf & sCmd


Finally


conn.Close()


End Try


End Sub

 

I figured out what's wrong. The password is reserved key work for Jet 4.0. I rename that field, everything works fine.

http://support.microsoft.com/default.aspx?scid=kb;EN-US;321266

Quincy