Database connection and data saving in SQL Server

Question

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

`which database file is the correct file` - how are we meant to know? What are you trying to do? — Bridge, Jan 15 '13 at 08: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? — Thanzeem, Jan 15 '13 at 08:46
If you're trying to attach to an SQL/Server database file directly you're almost certainly doing things the wrong way. — PeterJ, Jan 15 '13 at 08:46
@Thanzeem It sounds like you're either connected to the wrong database, or your VB.NET form doesn't work. I'd suggest posting your code if you want people to help you debug it! — Bridge, Jan 15 '13 at 08:48
@PeterJ -- How to attach database file with right way? Give me a solution my friend. — Thanzeem, Jan 15 '13 at 08:49
@Bridge-- My form is working properly and there is no error returns. My problem is When i press the submit button, shows message data inserted successfully. But i check the table in sql server, all the fields have Null. I attach the database file in the bin/Debug Directory. the table contains data i submitted from the form. — Thanzeem, Jan 15 '13 at 08:54
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. — PeterJ, Jan 15 '13 at 08:55
@PeterJ -- I will add the connection string in My Question... — Thanzeem, Jan 15 '13 at 09:27
@Thanzeem Please read [this answer](http://stackoverflow.com/a/11178889/1220971). — Bridge, Jan 15 '13 at 09:42
@Thanzeem No problem, glad I could help. I'll write it up into an answer for you. — Bridge, Jan 15 '13 at 10:37

score 1 · Accepted Answer · edited May 23 '17 at 11:43

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:

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.
If you're using SQL Server 2012, use SqlLocalDb for local development. See: "Getting Started with SQL Server 2012 Express LocalDB."
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.

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. — Thanzeem, Jan 15 '13 at 13:43
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;` — Bridge, Jan 15 '13 at 14:06

Database connection and data saving in SQL Server

1 Answers1