[RESOLVED] [2005] Table not being updated

[dminder]

I have played with this and been all over the place over the past 3 days. I cannot get the record to insert into the Access table. Please help me to understand what i am doing incorrectly.

Dim cn As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.StartupPath & "\EQ2.mdb")
        Try
            cn.Open()
        Catch ex As Exception
            MsgBox("Failed to open DB")
        End Try
 
        Dim cmd As OleDbCommand
        Dim sStr As String
        Dim icount As Integer
 
        Try
            sStr = "insert into armor(ItemName, Slot)"
            sStr += "Values('" & name & "', '" & slot & "')"
            cmd = New OleDbCommand(sStr, cn)
            icount = cmd.ExecuteNonQuery
            MsgBox(icount) 'I get a 1 here
 
        Catch ex As Exception
            MsgBox(Err.Number & vbCrLf & Err.Description)
        End Try
        cn.Close()
        cn.Dispose()
        da.Dispose()

Or if there is a better way to insert records into an access database I am all ears.

Thank you in advance,

dminder

[warrenayen]

That looks perfectly fine to me. Should be working without a problem. Try changing "icount = cmd.ExecuteNonQuery" to just "cmd.ExecuteNonQuery"

[warrenayen]

Also, I'm betting you're using the default Workspace for the DB. Try adding "username=Admin"

[circuits2]

That looks perfectly fine to me. Should be working without a problem. Try changing "icount = cmd.ExecuteNonQuery" to just "cmd.ExecuteNonQuery"

You need to leave this as it is because setting icount allows you to see how many rows in the database were affected.


How many records are you trying to update? You're not using a loop or anything so where you say you get a value of 1 for icount, that means the record that you were trying to update was actually updated. However, you are not resetting the value of icount so it could be from the last transaction. Hang on and let me suggest some changes to your code.

[dminder]

Records will only be posted one at a time. Values are assigned from a form (textboxes and comboboxes). Yeah it is weird that i am getting a count of 1 but the data is not showing up in the access table.

[circuits2]

Dim cn As OleDbConnection = New OleDbConnection _ 
     ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.StartupPath & "\EQ2.mdb")
 
Try
     cn.Open()
Catch ex As Exception
     MessageBox.Show("Failed to open DB")
End Try
 
Dim cmd As OleDbCommand
Dim icount As Integer
 
Try
     cmd = New OleDbCommand("INSERT INTO armor (ItemName, Slot) Values (@name, @slot)", cn)
     cmd.Parameters.AddWithValue("@name", name)
     cmd.Parameters.AddWithValue("@slot", slot)
     icount = cmd.ExecuteNonQuery
      MessageBox.Show(icount)
 
Catch ex As Exception
      MessageBox.Show(ex.message)
 
Finally
      cn.Close()
      cn.Dispose()
      da.Dispose()
End Try

[dminder]

I get an error message saying "Syntax error in INTO statement"

[warrenayen]

Yeah that's not a valid Insert. He's doing an update by accident. Try:

INSERT INTO armor (ItemName, Slot) Values (@name, @slot)

[circuits2]

Yeah that's not a valid Insert. He's doing an update by accident. Try:

INSERT INTO armor (ItemName, Slot) Values (@name, @slot)

Actually, the only accident was that I typed half of an INSERT and half of an UPDATE statement. Sometimes happens when bouncing back and forth between 4 monitors. I agree with what you typed for the INSERT as it is what I meant to type (i.e. Parameterized).

[dminder]

Thank you all for the help. The insert statement wasn't the problem. Somehow the program created a copy of my database in a debug folder and it was writing to that db instead of the one that the data connection is using. Thank you all again for your help!! I though application.startuppath would take me where the rest of the forms and stuff are stored. I cannot get any data from it using the immediate window so I hardcoded the path.

dminder

[jmcilhinney]

Woah, woah, woah! The program didn't SOMEHOW create a copy. That's EXACTLY what's supposed to happen. You don't want to make changes to your original database while you're debugging. You want that original to stay pristine so that you can distribute a nice clean database with your app when it comes time to deploy. The idea is that you have a nice clean original, then when you debug you can butcher the copy as much as you like without hurting the original. Whenever you want to reset your copy you can and you'll start with a fresh database again.

Your issue is the same as plenty of other people who've posted before you. The default action is to copy the clean database to the Debug folder every time you compile, which means that every time you run your project in the debugger the database gets overwritten. What you need to do is change the properties of the database so that it is only copied when the original is newer, i.e. only when you've changed the schema. That way you can maintain the changes in your copy as long as you like. When you decide that you need a fresh database you simply change the properties again and the copy will be overwritten next time you run your project.

In short, select your database in the Solution Explorer, open the Properties window and change the Copy To Output Folder property from Copy Always to Copy If Newer.

Also, you shouldn't be using Application.StartupPath with databases. Your connection string should contain the |DataDirectory| place-holder. That place holder is replaced at run time by the current application's data directory path. This will be the same as the path returned by Application.StartupPath by default but you can change it if you want. That way you never have to change your connection string and you definitely don't hard-code a path.

[circuits2]

Also, you shouldn't be using Application.StartupPath with databases. Your connection string should contain the |DataDirectory| place-holder. That place holder is replaced at run time by the current application's data directory path. This will be the same as the path returned by Application.StartupPath by default but you can change it if you want. That way you never have to change your connection string and you definitely don't hard-code a path.

I use Application.StartupPath for debugging purposes when the production database will not end up residing on the same PC as the APP (I create a debugging copy of the db for my own disposal). I agree with you completely though for instances where this is not the case.

[jmcilhinney]

I use Application.StartupPath for debugging purposes when the production database will not end up residing on the same PC as the APP (I create a debugging copy of the db for my own disposal). I agree with you completely though for instances where this is not the case.

That's exactly why you should use |DataDirectory|. You simply put |DataDirectory| in your connection string and leave it. That gets replaced with whatever the current data directory is. By default it will be your app's program folder, which you can accept while debugging. In a Release build you change the value of the data directory and the connection string will pick it up automatically without any intervention from you. How you set the data directory is up to you and depends on the situation. It may be supplied by the user, come from the registry, come from a config file or whatever.

To set the data directory you use:

AppDomain.CurrentDomain.SetData("DataDirectory", "folder path here")

Whatever you pass as the second parameter will henceforth be substituted for |DataDirectory| in your connection strings.

[jmcilhinney]

Having said that, that assumes that you are using a file in production. If you're using a file while debugging and an existing catalog in production then you will have to change your connection string. If you're using a file for both, like an MDB of MDF, then you can leave the connection string the same for both. Also, using Application.StartupPath requires you to concatenate strings in code, while |DataDirectory| gets hard-coded and replaced by the system at the appropriate moment.

The only reason to use Application.StartupPath in VB 2005 is if you have your data directory set to some other path for one or more databases but you want to use the program folder for another. If you only have one data source then you shouldn't use it.

[dminder]

Thank you all again for the input and guidance. This has been a combination of learning experience and fun program for an MMORPG I play (helps to work with something you know alot about!!). I did not know that it created a copy - I was running the code then checking the data in the datasource to see if it updated and make sure everything made it in ok. I had put a check into the program originally to test for the record(s) after insert and it kept failing. Knowing all this now it makes sense.

Happy Coding!!

dminder