INSERT INTO Access Database shows no changes in table 1

[drchuck59]

I am unable to INSERT INTO an Access database.

The following two example BOTH result in a "1" rows updated,
but when I look into the database using Access, the data
does not appear to have been added.

Here are the two code snippets. All data comes from a form
with no databindings. All the tables are in PeerReview_Dataset. The target table is "Results". This is a survey program with some identifying parameters, 20 questions, 20 answers, and 21 comments.

Sample 1 - using an INSERT adapter called "InsertInto". Oh - all target columns are Long, Int, or String (one date).
(In the real program these are converted tags or text of the controls - using CStr(), CLng(), etc.). That one also executes correctly, but no data in the table.

Dim ta As New PeerReview_DataSetTableAdapters.ResultsTableAdapter
ta.InsertInto(1, 2, 3, 4, #1/1/1980#, "Test", "Test", 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, _
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, "Test", "Test", "Test", "Test", "Test", "Test", "Test", _
"Test", "Test", "Test", "Test", "Test", "Test", "Test""Test""Test", "Test", "Test", "Test", "Test", "Test", "Test", "Test")
ta.Dispose()

This one executes without errors and returns a value of 1, but no data is in the form.

Sample 2 - using an SQLcmd and OleDB connection. This one shows the actual elements.

        ' Create the SQL Statement - First declare the Column elements
        Dim strSQL As String = "INSERT INTO ResultsTEST ([Assignment_FK], [Reviewer_FK], [Reviewee_FK], [Section_FK], [Event_Date], " & _
        "[Event_Qualifier1], [Event_Qualifier2]"

        For iQue = 1 To 20
            strSQL = strSQL & ", [Q" & iQue & "]"
            strSQL = strSQL & ", [A" & iQue & "]"
            strSQL = strSQL & ", [C" & iQue & "]"
        Next

        ' Add the values to the SQL statement
        strSQL = strSQL & ") VALUES ("
        strSQL = strSQL & "'" & gCurAssignment.ID & "', '" & gCurUser.ID & "', '" & gCurAssignment.RevieweeID & "', '" & _
        gCurAssignment.Section_FK & "', #" & Me.TextBoxExamDate.Text & "#, '" & Me.Qualifier1.Text & "', '" & Me.Qualifier2.Text & "'"

        For iQue = 1 To 20
            strSQL = strSQL & ", " & Me.Controls("Label" & iQue).Tag                    ' Question ID
            strSQL = strSQL & ", " & Me.Controls("GroupBox" & iQue).Tag                 ' Answer ID
            strSQL = strSQL & ", """ & Me.Controls("TextBox" & iQue).Text & """"        ' Comment
        Next
        strSQL = strSQL & ")"
       
        Dim cnOleDB As OleDbConnection = New OleDbConnection(_strConnPeerReview)
        Dim cmdUpdate As OleDbCommand = New OleDbCommand(strSQL, cnOleDB)
        cnOleDB.Open()
        cmdUpdate.ExecuteNonQuery()
        cmdUpdate.Dispose()
        cnOleDB.Close()
        cnOleDB.Dispose()

This one also shows a return of '1' if I ask. (Amazingly, the SQL command was balanced.)

Yet, when I open the table in ACCESS, neither adds a record to the table. Yes, there is an auto-number index in the table (removing it does nothing), and yes, I am making sure that I am opening the correct database.

Am I missing something obvious?


Chuck Kowalewski
"Forget world peace. Visualize using your turn signal.

 

[lotharious]

well, im having the same problem.
I have not been able to figure out why it wont write to it.

http://www.tek-tips.com/viewthread.cfm?qid=1425345&page=1

 

[lotharious]

Ok, in my program it was writing to the db file it made in the debug directory. So check to see if your is doing the same thing.

 

[drchuck59]

lotarious, THANK YOU - it was, as I had hoped, something obvious! The database was updating, but the results were in the debug-folder's database. Enjoy your star!

Chuck Kowalewski
"Forget world peace. Visualize using your turn signal.