Insert data into SQL Server using VB .NET

Question

i'm trying to insert some sample data into a sql server.

I'm using Visual Basic 2010 Express.

Here's the code:

Public Sub insert()
 Dim myconnect As New SqlClient.SqlConnection
 myconnect.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\DATABASE_NUOVO.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
 Dim mycommand As SqlClient.SqlCommand = New SqlClient.SqlCommand()
 mycommand.Connection = myconnect
 mycommand.CommandText = "INSERT INTO utenti (nome) VALUES ('mario')"
 myconnect.Open()
 Try
 mycommand.ExecuteNonQuery()
 Catch ex As System.Data.SqlClient.SqlException
 MsgBox(ex.Message)
 End Try
 myconnect.Close()
 MsgBox("Success")
End Sub

The code seems to run correctly, but when i look into the database after running the debug i don't see the sample data.

Where is the problem?

Thanks

• RBarryYoung: Don't restrict your Catch to just SqlExceptions. There are other kinds of exceptions that can be thrown here.
• Bellu: How should i change the Catch? @RBarryYoung
• Chait: INSERT INTO utenti (nome) VALUES ('mario') - is that what it is supposed to be, nome? Just a thought.

Answer 1

As I've said before on this site - the whole User Instance and AttachDbFileName= approach is flawed - at best! Visual Studio will be copying around the .mdf file 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

1. install SQL Server Express (and you've already done that anyway)

2. install SQL Server Management Studio Express

3. create your database in SSMS Express, give it a logical name (e.g. Database_Nuovo)

4. connect to it using its logical database name (given when you create it on the server) - and 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=Database_Nuovo;Integrated Security=True
   

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

Also: check what the value of the Copy to Output Directory property is on your DATABASE_NUOVO.mdf file in the App_Data directory (find it inside your Visual Studio Solution Explorer).

What might happen (and does, more often than not):

• when Visual Studio starts your app for debugging, it copies Database_Nuovo.mdf to the output directory where the app is running (your .\debug\bin directory)
• your INSERT then runs against this copy of the .mdf file and works just fine
• you stop debugging and go check the database file again - but this time, you're looking at the Database_Nuovo.mdf in the App_Data directory --> and of course your inserted data isn't there since it was inserted into a different file!

• RBarryYoung: +!: Ah, good one. I missed that the .mdf could be copied rather than moved or renamed (and thus not throw an error).
• Bellu: Thank you to everybody. The 'error' isn't a real error, simply i was searching my data in the wrong database. My data was in \debug\bin directory. Sorry :)

Answer 2

Try changing your Try..Catch code to handle more error types than just SqlExceptions. Like this:

 Try
 mycommand.ExecuteNonQuery()
 Catch ex As System.Data.SqlClient.SqlException
 MsgBox(ex.Message, , "Sql Exception")
 Catch ex As System.Exception
 MsgBox(ex.Message, , "General Exception")
 End Try

SqlExceptions are not the only exceptions that can be thrown here.

• Bellu: I've change the code but i don't get any type of errors... @RBarryYoung
• RBarryYoung: Do you get the "Success" message box?

Answer 3

Try with it..

Dim con As New SqlConnection
Dim cmd As New SqlCommand
Try
con.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\DATABASE_NUOVO.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True" //check your connection string carefully whether it points right directory
con.Open()
cmd.Connection = con
cmd.CommandText = "INSERT INTO table([field1], [field2]) VALUES([Value1], [Value2])" //make sure here your table and column name is exactly like as your database
cmd.ExecuteNonQuery()
Catch ex As Exception
MessageBox.Show("Error while inserting record on table...");
Finally
con.Close()
End Try

• Bellu: I've try your suggestions, but i don't have errors in my sql syntax. @ridoy
• ridoy: Then it would be a problem of your database file,your insert statement don't work for the database what you put in your connection string.Try the procedure of marc_s first and then apply my code.Expect it would be ok..