Solved

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

Posted on 2006-07-19
22
310 Views
Last Modified: 2010-04-23
Hey whats up guys how is everyone doing on this HOT HOT HOT Summer day lol.

Anyhow I'm facing a bug with my VB form inserting data into Access.

Ok here is the scoop.

First and foremost so far I'm trying to enter data only using 2  label values (FirstName, MiddleName) into my Access database.
Now here is the tricky part
when I enter data for the first time Everything is cool.
Now the 2nd time I decide to enter data the new data is not inserted.
BUT if I decide to wipe out the first data I inserted (goind into access and clicking on the row and deleting the data ) and then try again the new data gets inserted with no problem.

So far I can say is this
I have a primary key in the table within access that uses an AUTO number by access to give me an ID.
so in that case I don't need my user to put in an ID key , Access takes care of it for me :)

Everytime I decide to put in extra data (in other words new names etc) the program runs a bit slow and then it gives me a save messagebox,...but it really doesnt save at all.

But again if I go back to access and delete the whole row and try it again..it saves perfectly.

What could be the problem guys!?!?

Here is my little rookie code...tell me what you guys think?


////////////////////////

      Dim FirstName = LabelFirstName.Text
        Dim MiddleName = LabelMiddleName.Text




        Dim connString As String = " Provider =Microsoft.JET.OLEDB.4.0; Data Source =X:\members.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 [PrintTable](FirstName,MiddleName)" & _
                            " Values (@FirstName,@MiddleName) "

            .CommandType = CommandType.Text

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




             .Parameters("@FirstName").Value = FirstName
            .Parameters("@MiddleName").Value = MiddleName
           



        End With


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



        End Try



     

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

        myConnection.Dispose()
        myConnection.Close()



        '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        MessageBox.Show("Form is now Saved!!)
0
Comment
Question by:masterat03
  • 10
  • 7
  • 5
22 Comments
 
LVL 10

Expert Comment

by:ibost
ID: 17139376
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.

0
 

Author Comment

by:masterat03
ID: 17139396
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 :(
           
0
 

Author Comment

by:masterat03
ID: 17139504
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 :(




0
 

Author Comment

by:masterat03
ID: 17139733
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 .



0
 
LVL 10

Expert Comment

by:ibost
ID: 17139938
Are your catch blocks writing any errors the second time?
0
 
LVL 34

Expert Comment

by:Sancler
ID: 17139986
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
0
 

Author Comment

by:masterat03
ID: 17139988
do you mean the second time I reload my program?...
0
 
LVL 34

Expert Comment

by:Sancler
ID: 17140068
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
0
 

Author Comment

by:masterat03
ID: 17140229
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.

0
 
LVL 10

Expert Comment

by:ibost
ID: 17140269
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.
0
 

Author Comment

by:masterat03
ID: 17140297
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

0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 10

Expert Comment

by:ibost
ID: 17140620
So that means you must be throwing an exception.  What is the exception message?
0
 
LVL 34

Expert Comment

by:Sancler
ID: 17140635
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
0
 
LVL 34

Expert Comment

by:Sancler
ID: 17140672
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
0
 

Author Comment

by:masterat03
ID: 17140686

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.





0
 
LVL 10

Expert Comment

by:ibost
ID: 17140713
do you have any relationships or indexes defined?
0
 

Author Comment

by:masterat03
ID: 17140745
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?
0
 

Author Comment

by:masterat03
ID: 17140762
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


0
 
LVL 34

Expert Comment

by:Sancler
ID: 17140886
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
0
 
LVL 34

Expert Comment

by:Sancler
ID: 17140894
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
0
 

Author Comment

by:masterat03
ID: 17140978
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?!?!


0
 
LVL 34

Accepted Solution

by:
Sancler earned 500 total points
ID: 17141040
If that's OK, it confirms that there is something about the structure of your PrintForm table in Access that is offended by just adding a partial record to it.  My guess (despite the terms of the error message) is that it may be that - rather than being a Primary Key or relationship constraint being broken - one or more fields may be set not to allow null values.  If, by inspecting the filed list, you can identify any such cases then you can alter tham.  If not, the best bet may be to create a new table from scratch with the same field names but making sure that you allow nulls in all fields except the Primary Key field.  The problem with just copying the existing table over - even just its structure - is that all field settings, constraints and relationships will also be copied.

Roger
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now