2

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

Improve this question
3
  • 1
    Don't restrict your Catch to just SqlExceptions. There are other kinds of exceptions that can be thrown here. Commented Jan 2, 2013 at 19:19
  • How should i change the Catch? @RBarryYoung Commented Jan 2, 2013 at 19:32
  • 1
    INSERT INTO utenti (nome) VALUES ('mario') - is that what it is supposed to be, nome? Just a thought. Commented Jan 2, 2013 at 21:33

3 Answers 3

Reset to default
6

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!
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

+!: Ah, good one. I missed that the .mdf could be copied rather than moved or renamed (and thus not throw an error).
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 :)
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.

Improve this answer

2 Comments

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

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
Improve this answer

2 Comments

I've try your suggestions, but i don't have errors in my sql syntax. @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..

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.