none
Microsoft SQL Server 2005, using sqlConnection, won't insert any data and query has no errors RRS feed

  • Question

  • Need help with SQL Server 2005 with Visual Basic .net 2005.
    Basically i can read data from DB without any problems.
    When i write to DB using Insert query, i get no errors, and i get the correct insert id as well but data doesn't go in to the database.

    The following is the code:

    Try
                Dim sqlConn2 As New SqlClient.SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\mydb.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True")
               
                Dim sql As String
                sqlConn2.Open()

                sql = "INSERT INTO project (name, description, project_type_id) VALUES ('Project Name','Project Description',1);SELECT id FROM project WHERE id=@@IDENTITY"

                Dim sqlComm2 As New SqlClient.SqlCommand(sql, sqlConn2)
                Dim return_id As Integer = CInt(sqlComm2.ExecuteScalar())
                MessageBox.Show(return_id)


                sqlConn2.Close()
    Catch ex As Exception
                MessageBox.Show(ex.Message)
    End Try

    Please suggest what am i doing wrong?

    I am using Visual Basic 2008 Express Edition with Microsoft SQL Server 2005
    Tuesday, January 29, 2008 9:54 PM

Answers

  • Hi Manish

     

    One possibility is that every time you build and execute the project from Visual Studio, the mdf file is being overwritten thus causing you to lose the inserts. Try the following steps and see if it fixes the issue

     

    1. Copy the mdf file to lets say c:\MyData\Mydb.mdf.

    2. Change the connection string to "Data Source=.\SQLEXPRESS;AttachDbFilename=c:\MyData\Mydb.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"

    3. Now run your code.

    Wednesday, January 30, 2008 3:10 AM

All replies

  • Hi Manish

     

    One possibility is that every time you build and execute the project from Visual Studio, the mdf file is being overwritten thus causing you to lose the inserts. Try the following steps and see if it fixes the issue

     

    1. Copy the mdf file to lets say c:\MyData\Mydb.mdf.

    2. Change the connection string to "Data Source=.\SQLEXPRESS;AttachDbFilename=c:\MyData\Mydb.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"

    3. Now run your code.

    Wednesday, January 30, 2008 3:10 AM
  • Thanks Himanshu,

    that exactly was the problem. Its all working fine now.

     

    Regards,

    Manish

    Wednesday, January 30, 2008 9:50 AM