Database inserts don't seem to be working

Question

I'm pretty new to VB programming, and I'm having a hard time getting my database connectivity to work. I've been at this same simple two item insert for two days now, and I can't seem to make it work. This is part of a larger program, but I figure if I can get this small step working, I can use the code in the other parts.

 

Basically, I have two text boxes and a submit button. The user is supposed to enter a name and a number of games, and click the button to enter the data into a database. I've tried a number of different methods, all to no avail. When I get the code to work, I don't get any errors, but I also don't get any data in the database. I've tried using a tableadapter.insert, a tableadapter.update, and an sql executenonquery. My latest attempt is with the executenonquery. Here's a copy of the code, any help would be greatly appreciated. (The messagebox fires at the end, but all I end up with is null values in the db)

 

Imports System.Data

Imports System.Data.SqlClient

Public Class CreateLeague

Dim objConnection As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ybst.mdf;Integrated Security=True;User Instance=True")

 

Private Sub btnSubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSubmit.Click

Dim sqlcom As SqlCommand = New SqlCommand("Insert into league ( [l_name] , [l_games] ) Values (@lname, @lgames)", objConnection)

Dim lname As New SqlClient.SqlParameter("@lname", SqlDbType.VarChar, 25)

Dim lgames As New SqlClient.SqlParameter("@lgames", SqlDbType.Int, 2)

lname.Value = txtLeagueName.Text

lgames.Value = txtNumGames.Text

sqlcom.Parameters.Add(lname)

sqlcom.Parameters.Add(lgames)

sqlcom.Connection.Open()

sqlcom.ExecuteNonQuery()

MsgBox("Successfully Inserted")

sqlcom.Connection.Close()

End Sub

End Class

 

 

Answer 1

I forgot to mention that the text boxes are databound to the database.

Answer 2

the code looks ok it might be something to do with the parameters

 

but looks like your trying to insert your parameters into your sql statement you should identify your columns not the parameters so it should look like this

 

"INSERT INTO league (l_name, l_games) VALUES (@l_name, @l_games)"

 

where l_name and l_games are you column names

 

or you could try adapting the code in my post in this forum

 

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1993012&SiteID=1

 

hope this helps

 

 

Answer 3

Thanks for the link.

I tried your code, and the message box comes back telling me that one record was inserted, but when I stop the application and look at the database, there are only null values in the fields. This is the same issue I have when I try some of the other methods. I know it has to be something simple that I'm missing, but I can't put my finger on it. Any other ideas?

 

Answer 4

in your solution explorer - right click on your database (.mdf) file

select properties

set the copy to ouput directory to - copy if newer

Answer 5

Hi,

I had similar problem of the database not getting updated but was helped by member js06.

 

-In Solution Explorer, Click on your Database to show its properties. If it does not show Right Click on Database and select properties.

- In the property called Copy To Output Directory, change it to Copy If Newer.

 

If the above does not work, try running your program from the YourProjectNameDirectory\bin\Debug\projectname.exe

 

I hope this help.

 

 

Answer 6

Thanks for the input js06 and sahm. I tried what you suggested, but I still get no data. I tried to manually insert the data using the sqlText string, and it works, I see the new row show up. I put a breakpoint at the insert statement, and stepped line by line through the rest of the code. The variables change to the correct values, and I don't get any errors in the try..catch statement. Yet when I stop the program and go check the database, there are no new rows.

 

This is my first time using VB express and SQL express, and also the first time I've packaged the database as part of the application, and not a separate server connection. Could the issue have anything to do with the data not being committed to the database before the application is closed? I'm probably grasping at straws here, but it beats pulling my hair out.

 

Answer 7

are you updating your database?

 

You would use something like this

 

Me.Validate()

Me.Table1BindingSource.EndEdit()

Me.Table1TableAdapter.Update(Me.Database1DataSet.Table1)

 

you need to change the names to the names that you gave for your adapter, table and database

Answer 8

OK, I got it working. I wanted to thank you all for the help again. It turns out that the connection string for the database was incorrect. Even though I copied the string right from the myproject->settings location. I ended up right-clicking on my database, selecting properties, then copying the connection string from there into my program. After I did that, everything worked as it was supposed to work. I don't know why those connection strings would be different when they are part of the same application, but they were. I also have to say that I can't take the credit for finding the issue, I asked a VB developer at work, and he looked at for sometime before he found the problem.

 

Thanks again for the help. This has been a great place to come for answers.

Answer 9

Hi Denied,

 

Visual Studio Express/VB Express actually maintains two copies of the database, one in the Project folder, and the other in the bin folder. Each time when the application is restarted, the former is copied to the latter, so you get a clean copy.

 

Here is detailed explanation from Sarah Parra - MSFT.

FAQ: My database isn't being updated, but no errors occurred in my application

Problem:

You have an ADO.NET application that updates, inserts, or deletes data from your database. No errors occur and the data changes are visible in the application, but do not persist between application runs, or if you view the database file outside of the application.

Solution:

There are two common reasons why this happens...

 

This blog is beneficial to you.

https://blogs.msdn.com/smartclientdata/archive/2005/08/26/456886.aspx

For desktop projects, the database file will be copied to the output folder (bin) each time the project is built.

It is possible that when you hit F5 again after saving the data, the Northwind.MDF file under bin was replaced with the original one and your change was lost. Would you please verify if this is the case and follow the work around in the blog to fix the issue?

1. If you select local database file in the Solution Explorer window, you will see a property called ‘Copy to Output’ in the Properties window. By default it is set to ‘Copy Always’ which means that on each build, the data files in the project folder will be copied to the output folder overwriting the existing data files if any. You can set this property to Copy Never and then manually put a copy of the data file in the output folder. This way, on subsequent builds, the project system will leave the datafile in the output folder and not try to overwrite it with the one from the project. The downside is that you still have two copies so after you modify the database file using the app, if you want to work on those changes in the project, you need to copy it to the project manually and vise-versa.

2. You can leave the data file outside the project and create a connection to it in Database Explorer. When the IDE asks you to bring the file into the project, just say no. This way, both the design-time and the run-time will be using the same data file but the downside is that the path in the connection string will be hard coded and therefore it’ll be harder to share the project and deploy the app. Before deploying the app, just make sure to replace the full path in the settings with a relative path.

 

Regards,

Martin