Need help to fix a database entry Bug in Access from VB.net Form

Dunno if this is your problem (doesn't seem likely...) but OleDb stuff doesn't use named parameters, it instead uses the '?' placeholder.

You can leave this part:
               .Parameters.Add(New OleDbParameter("@FirstName", OleDbType.Char, 30))
            .Parameters.Add(New OleDbParameter("@MiddleName", OleDbType.Char, 30))
because it allows you to access the params by name for purposes of lookup


but change this:
            .CommandText = "Insert INTO [PrintTable](FirstName,MiddleName)" & _
                            " Values (@FirstName,@MiddleName) "

to this:
            .CommandText = "Insert INTO [PrintTable](FirstName,MiddleName)" & _
                            " Values (?, ?) "

since OleDb works by replacing each ? with the parameters specified in the order you added them to the command object.

Also to keep things updated I have enter break points in
   
 .Parameters.Add(New OleDbParameter("@FirstName", OleDbType.Char, 30))
 .Parameters.Add(New OleDbParameter("@MiddleName", OleDbType.Char, 30))
 AND        
.Parameters("@FirstName").Value = FirstName
.Parameters("@MiddleName").Value = MiddleName

At this point I see Values within (FirstName) and (MiddleName)

But Still nothing happens in access
...Unless if I erase it and do it over then things get inserted again :(
           

So far I've used this technique to enter data in many occasion, but what I'm wondering is why is that everytime I decide to enter a new row of data my data insertion gets crazy and doesnt insert into access.
But if I decide to go back to access and delete the row and then try again it works lovely.

The point is that a new row is not created at all.

I also tried the technique you just posted ibost
  .CommandText = "Insert INTO [PrintTable](FirstName,MiddleName)" & _
                            " Values (?, ?) "
and I still get the same results..although the data does get inserted, but I still face the same problem.
It only works if I delete the row off from access and then reload my program to enter the data again :(

Update**

Well I still face the same problem I even went back to the access table to see perhaps if there was a major restriction with the primary key..so for testing purpose I took out the Primary key from this particular table to see if the table was acting crazy because it would find duplicate entries.

Long and behold I tried again the data didnt save...I go back again to access erase the data , reload my program and it inserts the data and then it saves.

Now im not to sure if the problem is in Access or my code.

I know the code works because I have been using for sometime now.
and as I posted before I have inserted break points to see if there is actual data within.

Please guys don't be shy and post up your opinions :) I appreciate it .

Are your catch blocks writing any errors the second time?

When you empty the Access table then reload it from your program, do you always use the same values?  Try some completely different values, and when you go back to Access check whether what it is showing you is the old values that you deleted last time or the new values you have just put in.  You do not say whether you are using VB.NET 2005 or earlier, but there are some settings in 2005 which can cause a database to be overwritten with a previous version every time an application reloads.  This should test if that is happening with your scenario.

The other thing you ought to do is get rid of the last of these three lines

        If myConnection.State <> ConnectionState.Closed Then myConnection.Close()

        myConnection.Dispose()
        myConnection.Close()

If you look at them you will see that by the time the last of them is reached myConnection has already been (a) closed and (b)  disposed of.

Roger

do you mean the second time I reload my program?...

Go to your Access table.  Make a note of the values in the single record.  Delete that record.  Run your program.  Use it to put a single record in the Access table with different values from those you have noted.  Close your program.  Go to your Access table.  Are the values those that you first noted or the different ones that you have just put into it with your program?  That's what I mean.

Roger

Update No.2**

ok this is what I did so far..im playing with only 1 value to keep things even more simple.
I decide to just use first name only and took out middlename for now.

as for this part of the code
If myConnection.State <> ConnectionState.Closed Then myConnection.Close()

        myConnection.Dispose()
        myConnection.Close()

I changed it to this

////
 myConnection.Dispose()
        myConnection = Nothing
////

And I notice the speed pick up a bit more which is good...but I still get the same trouble of saving data to my access table :(

///////
Now for what you said before sancler about the access database I did go back and erase the whole row to start clean
I loaded my program and I put in a different name.So everything saved, but when I went to put in another name , the program slow down again and then the messagebox said I save..(but I know it really didnt , because its lot like the symptoms that i have been seeing since the beginning and the program slow down a bit as well)...went back to access and I still see only 1 row inserted....so it still has the same problem.

/////

Also to note I delete the row again in access to start clean...BUT this time I decide to put in my own data so I put in the Field name of (FirstName) (Dan)  and saved it in access
Went back to my program loaded it again and then when i decide to save another name it slow down again , the save message came up again, I checked back in access and nothing happen..no new row, no new data.

Here is what I have so far in my noobish code lol
////

        Dim FirstName = LabelFirstName.Text
       


        Dim connString As String = "Provider =Microsoft.JET.OLEDB.4.0; Data Source =X:\memberform.mdb;"
        Dim myConnection As OleDbConnection = New OleDbConnection
        Dim InsertCMD As OleDbCommand = New OleDbCommand

        myConnection.ConnectionString = connString

        myConnection.Open()

        With InsertCMD

            .Connection = myConnection
            .CommandText = "Insert INTO [PrintForm] (FirstName)" & _
                            " VALUES (@FirstName) "

            .CommandType = CommandType.Text

           .Parameters.Add(New OleDbParameter("@FirstName", OleDbType.Char, 30))
         
             .Parameters("@FirstName").Value = FirstName


        End With


        Try

            InsertCMD.ExecuteNonQuery()
        Catch OleDbExceptionErr As OleDbException
            Debug.WriteLine(OleDbExceptionErr.Message)
        Catch InvalidOperationExceptionErr As InvalidOperationException
            Debug.WriteLine(InvalidOperationExceptionErr.Message)


        End Try
     
         myConnection.Dispose()
        myConnection = Nothing
        '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        MessageBox.Show("Form is now Saved!!.)
//////////////////

So far I notice the program is very Speedy when there is no Data in access.
As soon as 1 row is inserted and I need to insert a new row of data
Things go awfully wrong.....to the point that you can see my program slows down a bit, gives me false info telling me it saves, and then nothing actually saves in the access table.

So far my assumption could be that either I'm closing  to early the program...or still something is wrong with my access.

Incidentally the message box will always say it saved - even when it didn't.  If you want it to only show when it saves, put it in the TRY block.

Very true.
I put the messagebox in the try catch block and now well it doesnt appear.
Which is good because at least i don't get false information

So that means you must be throwing an exception.  What is the exception message?

First, the change you've made to the last three lines of your original code is not what I suggested.  It should read

        If myConnection.State <> ConnectionState.Closed Then myConnection.Close()

        myConnection.Dispose()

not

         myConnection.Dispose()
        myConnection = Nothing

Secondly

>>
Now for what you said before sancler about the access database I did go back and erase the whole row to start clean
I loaded my program and I put in a different name.So everything saved, but when I went to put in another name ,
<<

No, that's not quite what I meant.  Just go this far

>>
Now for what you said before sancler about the access database I did go back and erase the whole row to start clean
I loaded my program and I put in a different name.
<<

Don't try in the same session to put in another name.  Close the program as soon as one name has been put in.  Then go and check what is in Access.  From what you've said so far the program should work OK with that scenario: just putting one name in an empty Access table.  What I'm asking is, is the one name that is now shown in the Access table the "different name" your program has just put in, or the one that was there before you "erase the whole row to start clean"?

Thirdly, can you please say which version of VB.NET you are working with?  2002. 2003 or 2005?

Roger

And, I have just noticed, your new code does not change

            .CommandText = "Insert INTO [PrintForm] (FirstName)" & _
                            " VALUES (@FirstName) "

to

            .CommandText = "Insert INTO [PrintForm] (FirstName)" & _
                            " VALUES (?) "

as ibost suggested right at the start.  I agree with that suggestion

Roger

Ok the first exception comes out to this.
"The changes you requested to the table were not successful because they would create duplicate values in the index,primarykey, or relationship. Change teh data in the field or fields that contain duplicate data, remove the index, or redefined the index to permit duplicate entries and try again


now the question is ...why does this come out when I have in my access table a primary keys with an autonumber which access itself provides?...i know that im not duplicating Keys in this table field..especially the field name (firstname) which could have many duplicate names within that field.

do you have any relationships or indexes defined?

Also after I saw this message I decide to just take out the primary key and stick only with firstname field within access.
I still get the same error message...even though in access I just set it up to be a text field and no primary key.

Shouldnt the new data still continue to make new rows with new names regardless if they are duplicated or not?

no relationships at all.
Hell I even wanted to go to the next step to see if I had and i decided to make a new access file..if you notice it here this is a bit different

       Dim connString As String = "Provider =Microsoft.JET.OLEDB.4.0; Data Source =X:\memberform.mdb;"
        Dim myConnection As OleDbConnection = New OleDbConnection
        Dim InsertCMD As OleDbCommand = New OleDbCommand


before it was X:\members.mdb
now it is X:\memberform.mdb

so I made a whole new access file to see if the problem persist which eventually it still does
believe I didnt export the table either..i just copy and pasted into a new access DB file and continue to work on it there
to see again if the problem persist

Don't copy and paste.  Open the database you are referencing - whether it is members or memberform.  Create a new table in design view.  Add a field ID, type autonumber, and set it as the Primary Key.  Add a second field, text.  Save the table as "Test".  Come out of Access.  Change the tablename in your program from [PrintForm] to [Test].  Run.  See if that's OK.

Roger

To clarify

>>
Add a field ID, type autonumber, and set it as the Primary Key.  Add a second field, text.
<<

should read

>>
Add a field called ID, type autonumber, and set it as the Primary Key.  Add a second field called FirstName, type text.
<<

Roger

ok just did what you said sancler and it works
...the question is why?...

I did what you just said...which i made a new table called [Test] (firstname) and things work fine.
I even delete all the rows..decide to put extra field names and things work like a charm

again the question is why? does it work now..(believe me im not complaining it works i just like to learn from my mistakes)

I know the code is not super sharp , but I know it works well.
I knew it had something to do with my database but again why does it work when i now manually inserted the field name instead of copying and pasting it from members.mdb to memberform.mdb

Could it be along the way of pasting some corruption occured...or perhaps it copy a relationship trying to link to members.mdb?!?!