CODEWITHSUNDEEP
vb.net
user2968155
user2968155

Reputation: 3

Errors Saving in SQL server

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.

Upvotes: 0

Views: 70

Answers (3)

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28413

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

Upvotes: 0

Steve
Steve

Reputation: 216313

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

Upvotes: 0

Amarnath Balasubramanian
Amarnath Balasubramanian

Reputation: 9460

You have two tables:

  1. tblCustomer: ID-Name-Address-Email
  2. 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);"

Upvotes: 1

Related Questions