Insert and Editing records through SQL Dataadapter

[GrimmReaper]

Hello, I'm using this:

Private dacustomers As New SqlClient.SqlDataAdapter
    Private selectcom As New SqlClient.SqlCommand("SELECT * FROM Customers")
    Private ds As New DataSet("Northwind")
    Private SQLCount As String = "SELECT Count(CustomerID) FROM Customers"
    Private RecordCount As Integer
    Private bmCustomers As BindingManagerBase
    Private InsBuilder As New StringBuilder
    Private EDBuilder As New StringBuilder
 
   Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
 
        If btnAdd.Text = "ADD" Then
            btnAdd.Text = "Finish"
            TextBox1.Text = ""
            TextBox2.Text = ""
            TextBox3.Text = ""
        Else
            btnAdd.Text = "ADD"
            'SqlConnection1.Open()
 
            InsBuilder.Append("INSERT INTO Customers (CompanyName,CustomerID,ContactName) ")
            InsBuilder.Append("VALUES (")
            InsBuilder.Append(TextBox1.Text)
            InsBuilder.Append(",")
            InsBuilder.Append(TextBox2.Text)
            InsBuilder.Append(",")
            InsBuilder.Append(TextBox3.Text)
            InsBuilder.Append(")")
            Dim InsComm As New SqlClient.SqlCommand("InsBuilder")
            dacustomers.InsertCommand = InsComm
            dacustomers.InsertCommand.ExecuteNonQuery()
        End If
    End Sub
 
    Private Sub btnEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEdit.Click
        If btnEdit.Text = "EDIT" Then
            btnAdd.Text = "Update"
            TextBox1.Text = ""
            TextBox2.Text = ""
            TextBox3.Text = ""
        Else
            btnAdd.Text = "EDIT"
            EDBuilder.Append("UPDATE Customers SET ")
            EDBuilder.Append("CompanyName = ")
            EDBuilder.Append(TextBox1.Text)
            EDBuilder.Append(",")
            EDBuilder.Append("CustomerID = ")
            EDBuilder.Append(TextBox2.Text)
            EDBuilder.Append(",")
            EDBuilder.Append("ContactName = ")
            EDBuilder.Append(TextBox3.Text)
 
            dacustomers.SelectCommand.ExecuteNonQuery()
        End If
    End Sub

to try add and edit records, but The compiler keeps throwing me out, complaining about a table not specified

How can I fix this, to add and edit records at run time?

[Asgorath]

You are updating the entire table Customers - is that what you want , no where clause. Modified it slighly...

Private Sub btnEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEdit.Click
        If btnEdit.Text = "EDIT" Then
            btnAdd.Text = "Update"
            TextBox1.Text = ""
            TextBox2.Text = ""
            TextBox3.Text = ""
        Else
            btnAdd.Text = "EDIT"
            EDBuilder.Append("UPDATE Customers SET ")
            EDBuilder.Append("CompanyName =' ")
            EDBuilder.Append(TextBox1.Text)
            EDBuilder.Append("',")
            EDBuilder.Append("CustomerID = ")
            EDBuilder.Append(TextBox2.Text)
            EDBuilder.Append(",")
            EDBuilder.Append("ContactName =' ")
            EDBuilder.Append(TextBox3.Text)
            EBBuilder.Append("'")
 
            dacustomers.SelectCommand.ExecuteNonQuery()
        End If
    End Sub

Regards
Jorge

[GrimmReaper]

Ah... the single quotes

Thanx buddy!
Can you perhaps see what's wrong with my Add sub (it's alos probably something silly, but I can't see what I'm doing wrong

[Asgorath]

Same problem when your dealing with chars in sql you need to separate them the quotes.

Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
 
        If btnAdd.Text = "ADD" Then
            btnAdd.Text = "Finish"
            TextBox1.Text = ""
            TextBox2.Text = ""
            TextBox3.Text = ""
        Else
            btnAdd.Text = "ADD"
            'SqlConnection1.Open()
 
            InsBuilder.Append("INSERT INTO Customers (CompanyName,CustomerID,ContactName) ")
            InsBuilder.Append("VALUES ('")
            InsBuilder.Append(TextBox1.Text)
            InsBuilder.Append("',")
            InsBuilder.Append(TextBox2.Text)
            InsBuilder.Append(",'")
            InsBuilder.Append(TextBox3.Text)
            InsBuilder.Append("')")
            Dim InsComm As New SqlClient.SqlCommand("InsBuilder")
            dacustomers.InsertCommand = InsComm
            dacustomers.InsertCommand.ExecuteNonQuery()
        End If
    End Sub

Regards
Jorge

[GrimmReaper]

Got it Thank you!

BTW. I've modified my Edit code

Dim sqlCommand As System.Data.SqlClient.SqlCommand
        Dim CompanyNameStr As String
        Dim NewCustName As String
 
        Try
            If btnEdit.Text = "EDIT" Then
                CompanyNameStr = InputBox("Enter Company Name To Change")
                NewCustName = InputBox("Enter New Name")
                btnEdit.Text = "Update"
            Else
                btnEdit.Text = "EDIT"
 
                EDBuilder.Append("UPDATE Customers SET ")
                EDBuilder.Append("CompanyName = '")
                EDBuilder.Append(NewCustName)
                EDBuilder.Append("',")
                EDBuilder.Append("CustomerID = '")
                EDBuilder.Append(TextBox2.Text)
                EDBuilder.Append("',")
                EDBuilder.Append("ContactName = '")
                EDBuilder.Append(TextBox3.Text)
                EDBuilder.Append("'")
                EDBuilder.Append(" WHERE CompanyName = '")
                EDBuilder.Append(CompanyNameStr)
                EDBuilder.Append("'")
 
                sqlCommand = New System.Data.SqlClient.SqlCommand(EDBuilder.ToString(), SqlConnection1)
                SqlConnection1.Open()
                sqlCommand.ExecuteNonQuery()
            End If
        Catch ex As Exception
            MsgBox(ex.Message.ToString())
        Finally
            SqlConnection1.Close()
 
 
        End Try

The program runs, doesn't throw errors, BUT, the info doesn't get updated when I click "Update" - Is there something else I should include, like refreshing the Dataset, what should I do ?

[Asgorath]

If you are displaying the information in a datagrid just clear the dataset, fill it again and bound the datagrid with the updated datable.

Regards
Jorge