Errors Saving in SQL server

Question

I am trying to save my textboxes in SQL server.

But i'm getting some errors.

Here is my code:

Private Sub cmdSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSave.Click
    Dim strconnection As String = "Data Source=EASMAR-PC;Initial Catalog=DatabaseConnection;Integrated Security=True;"
    Dim _cn As SqlConnection = New SqlConnection(strconnection)
    Dim cmd As New SqlCommand
    _cn.Open()
    cmd.CommandText = "INSERT INTO tblCustomer([ID],[Name], [Email], [Address], [DeptID]) VALUES (@ID, @Name, @Email, @Address, @DeptID);"
    cmd.Connection = _cn
    cmd.Parameters.AddWithValue("@ID", Me.txtIDD.Text)
    cmd.Parameters.AddWithValue("@Name", Me.txtName.Text)
    cmd.Parameters.AddWithValue("@Email", Me.txtEmail.Text)
    cmd.Parameters.AddWithValue("@Address", Me.txtAddress.Text)
    cmd.Parameters.AddWithValue("@DeptID", Me.txtIDD.Text)

    cmd.ExecuteNonQuery()
    Me._DataAdapter.Fill(Me._DataSet)
End Sub

The error is:

invalid column DeptID.

And if i took out this line of code

 (cmd.Parameters.AddWithValue("@DeptID", Me.txtIDD.Text))

another error comes up:

pimary key violation./cannot insert duplicate...

i have 2 tables:

tblCustomer: ID-Name-Address-Email

tblDept: DeptID-Namee

And if i did this:

     Private Sub cmdSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSave.Click
            Dim strconnection As String = "Data Source=EASMAR-PC;Initial Catalog=DatabaseConnection;Integrated
 Security=True;"
             Dim _cn As SqlConnection = New SqlConnection(strconnection)
             Dim cmd As New SqlCommand
             _cn.Open()
             cmd.CommandText = "INSERT INTO tblCustomer([ID],[Name], [Email], [Address],[DeptID]) VALUES (@ID, @Name, @Email,
 @Address,@DeptID);"
             cmd.Connection = _cn
             cmd.Parameters.AddWithValue("@ID", Me.tt.Text)
             cmd.Parameters.AddWithValue("@Name", Me.txtName.Text)
             cmd.Parameters.AddWithValue("@Email", Me.txtEmail.Text)
             cmd.Parameters.AddWithValue("@Address", Me.txtAddress.Text)
             cmd.Parameters.AddWithValue("@DeptID", Me.txtIDD.Text)

             cmd.ExecuteNonQuery()
             Me._DataAdapter.Fill(Me._DataSet)
         End Sub

I get this error:

invalid column name ID.

Are the data type of the columns match with the passing parameters? i.e. Is ID column is varchar? — huMpty duMpty
– huMpty duMpty, Commented Dec 16, 2013 at 10:08
The first error is because tblCustomer does not have a column DeptId that is in tblDept. From what I can see there is not key between the two either. The second error is probably because you have a unique key on ID in the tblCustomer and are trying to insert an ID that already exists. — Fred
– Fred, Commented Dec 16, 2013 at 10:11

Amarnath Balasubramanian · Accepted Answer · 2013-12-16 10:13:57Z

1

You have two tables:

tblCustomer: ID-Name-Address-Email

tblDept: DeptID-Namee

How can you insert DeptID to tblCustomer if thers is no such column in the tblCustomer

Correct Code for inserting into tblCustomer

cmd.CommandText = "INSERT INTO tblCustomer([ID],[Name], [Email], [Address]) VALUES (@ID, @Name, @Email, @Address);"

Correct Code for inserting into tblDept

 cmd.CommandText = "INSERT INTO tblCustomer([DeptID],[Namee]) VALUES (@DeptID, @Namee);"

answered Dec 16, 2013 at 10:13

Amarnath Balasubramanian

9,5028 gold badges38 silver badges62 bronze badges

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

1 Comment

user2968155 Over a year ago

Invalid column name ID :/

Steve · Accepted Answer · 2013-12-16 10:15:37Z

According to your description, the DeptID column is in another table. You cannot insert that column in the tblCustomer where it doesn't exists. You need two separate statements to insert. One for the tblCustomer without the DeptID column and one for the table tblDept

A tentative solution could be

Dim cmd As New SqlCommand
_cn.Open()
cmd.Connection = _cn
cmd.CommandText = "INSERT INTO tblCustomer([ID],[Name], [Email], [Address]) " + 
                  "VALUES (@ID, @Name, @Email, @Address)"
.... add parameters without DeptID
cmd.ExecuteNonQuery()

cmd = new SqlCommand()
cmd.Connection = _cn
cmd.CommantText = "INSERT INTO tblDept VALUES(@DeptID, @deptName)"

... add parameters for the second insert....

cmd.ExecuteNonQuery()

Also keep in mind that when you need to insert two records in the same batch of statements you should ask yourself if you want to be sure that both the insert succeds or neither. In the second case you need to encapsulate both query using a transaction block. See here an introductory article on transactions

Vignesh Kumar A · Accepted Answer · 2014-02-21 17:00:38Z

0

I think the error in

cmd.CommandText = "INSERT INTO tblCustomer([ID],[Name], [Email], [Address], **[DeptID]**) VALUES (@ID, @Name, @Email, @Address, @DeptID);"

Please make Sure Correct Column Name in Your Insert query

edited Feb 21, 2014 at 17:00

answered Dec 16, 2013 at 10:12

Vignesh Kumar A

28.6k14 gold badges67 silver badges119 bronze badges

Collectives™ on Stack Overflow

Errors Saving in SQL server

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related