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

Solution 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!

Solution 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.