Insert data into SQL database in VB.NET

Question

I am trying to insert Supplier data into SQL database which I built inside VB.NET.

Here is the code that I am using:

Dim myconnect As New SqlClient.SqlConnection
        myconnect.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\DB\InvDB.mdf;Integrated Security=True;User Instance=True"


        Dim mycommand As SqlClient.SqlCommand = New SqlClient.SqlCommand()
        mycommand.Connection = myconnect
        mycommand.CommandText = "INSERT INTO Supplier (SupplierID, Name, Email,Phone,Address,City) VALUES (@SID, @Name, @Email, @Phone, @Address, @City)"
        myconnect.Open()

        Try
            mycommand.Parameters.Add("@SID", SqlDbType.Int).Value = SIDTextBox.Text
            mycommand.Parameters.Add("@Name", SqlDbType.NVarChar).Value = NameTextBox.Text
            mycommand.Parameters.Add("@Email", SqlDbType.VarChar).Value = EmailTextBox.Text
            mycommand.Parameters.Add("@Phone", SqlDbType.VarChar).Value = PhoneTextBox.Text
            mycommand.Parameters.Add("@Address", SqlDbType.NVarChar).Value = AddressTextBox.Text
            mycommand.Parameters.Add("@City", SqlDbType.NVarChar).Value = CityTextBox.Text
            mycommand.ExecuteNonQuery()
            MsgBox("Success")
        Catch ex As System.Data.SqlClient.SqlException
            MsgBox(ex.Message)
        End Try
        myconnect.Close()

The problem when I enter the data, I got the Success message but when I check the database, I can't find the data !!

I checked the mdf file and I found it correct (The same one that I am using)

Any help please.

What's the table definition for Supplier?

DeanOC
– DeanOC

2013-06-17 07:42:31 +00:00
Commented Jun 17, 2013 at 7:42 — DeanOC
– DeanOC, Commented Jun 17, 2013 at 7:42

Nasser · Accepted Answer · 2013-06-17 08:14:47Z

2

I got it

The problem was with this line:

myconnect.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\DB\InvDB.mdf;Integrated Security=True;User Instance=True"

I replaced |DataDirectory| with the path of the InvDB.mdf and it worked

answered Jun 17, 2013 at 8:14

Nasser

2,1486 gold badges35 silver badges58 bronze badges

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

Comments

sentil kumar · Accepted Answer · 2013-06-17 08:17:49Z

Dim query as String = String.Empty
query = "INSERT INTOSupplier (SupplierID, Name, Email,Phone,Address,City) VALUES (@SID, @Name, @Email, @Phone, @Address, @City) "   
Using conn as New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\DB\InvDB.mdf;Integrated Security=True;User Instance=True")
    Using comm As New SqlCommand()
        With comm
            .Connection = conn
            .CommandType = CommandType.Text
            .CommandText = query
            mycommand.Parameters.Add("@SID", SqlDbType.Int).Value = SIDTextBox.Text
        .Parameters.Add("@Name", SqlDbType.NVarChar).Value = NameTextBox.Text
        .Parameters.Add("@Email", SqlDbType.VarChar).Value = EmailTextBox.Text
        .Parameters.Add("@Phone", SqlDbType.VarChar).Value = PhoneTextBox.Text
        .Parameters.Add("@Address", SqlDbType.NVarChar).Value = AddressTextBox.Text
        .Parameters.Add("@City", SqlDbType.NVarChar).Value = CityTextBox.Text            
        End With
        Try
            conn.open()
            comm.ExecuteNonQuery()
        Catch(ex as SqlException)
            MessageBox.Show(ex.Message.ToString(), "Error Message")
        End Try
    End Using
End USing

Try this out.

Adam Lear · Accepted Answer · 2013-10-16 18:40:21Z

-2

I had a similar problem and in my case the application didn't have write access to the folder where the database file was.

edited Oct 16, 2013 at 18:40

Adam Lear♦

38.9k13 gold badges83 silver badges105 bronze badges

answered Oct 13, 2013 at 17:39

Shadow

11 bronze badge

Collectives™ on Stack Overflow

Insert data into SQL database in VB.NET

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related