CODEWITHSUNDEEP
sqlsql-serverdatabasevb.net
marcimist
marcimist

Reputation: 11

Can't save data to SQL Server database

I'm quite confused why the data that I added was not saved to the database.

While my program is running there are no problems in updating the data that is shown in DataGridView but when I close the program, the added data disappears.

I tried to show table data but there were no new data added. Can you tell me what is the problem?

This is my code:

Dim con As New SqlClient.SqlConnection
Dim cmd As New SqlClient.SqlCommand
Dim adaptor As New SqlClient.SqlDataAdapter
Dim dataset As New DataSet

con.ConnectionString = ("Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True")

con.Open()

cmd.CommandText = "INSERT INTO [Table](FirstName,LastName,MI,Address,Email) VALUES(@FN,@LN,@MI,@AD,@EM)"
cmd.Connection = con

cmd.Parameters.Add("@FN", SqlDbType.VarChar).Value = TextBox1.Text
cmd.Parameters.Add("@LN", SqlDbType.VarChar).Value = TextBox2.Text
cmd.Parameters.Add("@MI", SqlDbType.VarChar).Value = TextBox3.Text
cmd.Parameters.Add("@AD", SqlDbType.VarChar).Value = TextBox4.Text
cmd.Parameters.Add("@EM", SqlDbType.VarChar).Value = TextBox5.Text

cmd.ExecuteNonQuery()

MsgBox("Added!")
con.Close()

Me.TableTableAdapter.Fill(Me.Database1DataSet.Table)

Upvotes: 1

Views: 1528

Answers (1)

marc_s
marc_s

Reputation: 754963

The whole AttachDbFileName= approach is flawed - at best! When running your app in Visual Studio, it will be copying around the .mdf file (from your App_Data directory to the output directory - typically .\bin\debug - where you app runs) and most likely, your INSERT works just fine - but you're just looking at the wrong .mdf file in the end!

If you want to stick with this approach, then try putting a breakpoint on the myConnection.Close() call - and then inspect the .mdf file with SQL Server Mgmt Studio Express - I'm almost certain your data is there.

The real solution in my opinion would be to create your database on the server using a management tool (like SSMS Express), give it a logical name (e.g. MyDatabase), and then connect to it using its logical database name (given when you create it on the server). Don't mess around with physical database files and user instances. In that case, your connection string would be something like:

Data Source=.\\SQLEXPRESS;Database=MyDatabase;Integrated Security=True

and everything else is exactly the same as before...

Also see Aaron Betrand's blog post Bad Habits to Kick - using AttachDbFileName for more background info

Upvotes: 1

Related Questions