1

I have SQL SERVER database file in Project folder. The same file copy in Debug folder. I attach these two files in SQL Server. File in project folder table contains Null value in all field. But there is data in the file attached from the Debug folder. I created the connection string with the file in Project folder. Actually which database file is the correct file? Try to solve this problem.

The Connection String is

Public Conn As SqlConnection

Public Function getConnect() As SqlConnection

    Conn = New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\EMP_DB.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True")

    Return Conn

End Function

And this is my code..

 Try
            getConnect()
            Dim query As SqlCommand
            Dim strSQL As String
            strSQL = "INSERT INTO EMPLOYEE (EMP_ID,EMP_NAME,EMP_FNAME,EMP_GENDER,EMP_DOB,EMP_CAST,EMP_DEPART,EMP_DESIG,EMP_DOJ,EMP_SALARY,EMP_PF_ESI,EMP_BRANCH,EMP_CONTACT,EMP_ADDRESS)VALUES(@EMP_ID,@EMP_NAME,@EMP_FNAME,@EMP_GENDER,@EMP_DOB,@EMP_CAST,@EMP_DEPART,@EMP_DESIG,@EMP_DOJ,@EMP_SALARY,@EMP_PF_ESI,@EMP_BRANCH,@EMP_CONTACT,@EMP_ADDRESS)"
            query = New SqlCommand(strSQL, Conn)
            query.Parameters.Add(New SqlParameter("@EMP_ID", TXTEMPID.Text))
            query.Parameters.Add(New SqlParameter("@EMP_NAME", TXTNAME.Text))
            query.Parameters.Add(New SqlParameter("@EMP_FNAME", TXTFNAME.Text))
            query.Parameters.Add(New SqlParameter("@EMP_GENDER", gend))
            query.Parameters.Add(New SqlParameter("@EMP_DOB", DTPEMPDOB.Value.Date))
            query.Parameters.Add(New SqlParameter("@EMP_CAST", TXTCASTE.Text))
            query.Parameters.Add(New SqlParameter("@EMP_DEPART", CMBDEPT.Text))
            query.Parameters.Add(New SqlParameter("@EMP_DESIG", CMBDESIG.Text))
            query.Parameters.Add(New SqlParameter("@EMP_DOJ", DTPEMPDOJ.Value.Date))
            query.Parameters.Add(New SqlParameter("@EMP_SALARY", MTXTSAL.Text))
            query.Parameters.Add(New SqlParameter("@EMP_PF_ESI", MTXTPFESI.Text))
            query.Parameters.Add(New SqlParameter("@EMP_BRANCH", TXTBRANCH.Text))
            query.Parameters.Add(New SqlParameter("@EMP_CONTACT", MTXTCONTACT.Text))
            query.Parameters.Add(New SqlParameter("@EMP_ADDRESS", RTXTADDRESS.Text))
            Conn.Open()
            Dim numAffected = query.ExecuteNonQuery()
            'MessageBox.Show(numAffected)
            Conn.Close()
            If numAffected > 0 Then
                Call getConnect()
                MessageBox.Show("Successfully Added", "Add", MessageBoxButtons.OK, MessageBoxIcon.Information)
                BTNCLEAR.PerformClick()
            Else
                MsgBox("No record was inserted")
            End If
        Catch ex As Exception
            MsgBox("ERROR: " + ex.Message, MsgBoxStyle.Information, "Add")
        End Try
    End If

I change my connection string like this...

Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\EMP_DB.mdf;Initial Catalog=EMP_DB;Integrated Security=True;Connect Timeout=30;User Instance=False
10
  • which database file is the correct file - how are we meant to know? What are you trying to do? Commented Jan 15, 2013 at 8:42
  • @Bridge -- I try to insert data into database from VB.NET form. When i press the submit button, It shows the message "Successfully Added". But i check the database table. All fields have Null values. After i attach database file from the Debug directory. Its table have data. So which database file is the main database? In project directory or in Debug directory? Commented Jan 15, 2013 at 8:46
  • 2
    If you're trying to attach to an SQL/Server database file directly you're almost certainly doing things the wrong way. Commented Jan 15, 2013 at 8:46
  • 1
    You should be trying to attach to an SQL/Server instance by server / database name. You really need to put more detail in the question to get a sensible answer including your connection string and what you're trying to achieve. Technically you can attach a database to a file, but the situations you'd really want to do that are pretty rare. Commented Jan 15, 2013 at 8:55
  • 1
    @Thanzeem Please read this answer. Commented Jan 15, 2013 at 9:42

1 Answer 1

0

User instances are depreciated, and probably what is causing this confusion.

To quote SQL Server MVP Aaron Bertrand:

Using User Instance means that SQL Server is creating a special copy of that database file for use by your program. If you have two different programs using that same connection string, they get two entirely different copies of the database. This leads to a lot of confusion, as people will test updating data with their program, then connect to a different copy of their database in Management Studio, and complain that their update isn't working. This sends them through a flawed series of wild goose chase steps trying to troubleshoot the wrong problem.

[Source]

He also goes on to list some alternatives in the same post:

  1. Create or attach your database to a real instance of SQL Server. Your connection string will then just need to specify the instance name, the database name, and credentials. There will be no mixup as Management Studio, Visual Studio and your program(s) will all be connecting to a single copy of the database.

  2. If you're using SQL Server 2012, use SqlLocalDb for local development. See: "Getting Started with SQL Server 2012 Express LocalDB."

  3. Use SQL Server Compact. I like this option the least because the functionality and syntax is not the same - so it's not necessarily going to provide you with all the functionality you're ultimately going to want to deploy.

Sign up to request clarification or add additional context in comments.

2 Comments

i change my connection string Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\EMP_DB.mdf;Initial Catalog=EMP_DB;Integrated Security=True;Connect Timeout=30;User Instance=False. Now its working fine.... now my confusions cleared.
You shouldn't need the AttachDbFilename part - your database is attached once to the server, you don't need to attach it again! Also, the default for User Instance is false, so no need to include it. Try this: Data Source=.\SQLEXPRESS;Initial Catalog=EMP_DB;Integrated Security=True;Connect Timeout=30;

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.