Insert and Editing records through SQL Dataadapter
Hello, I'm using this:
VB Code:
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 :confused:
How can I fix this, to add and edit records at run time?
Re: Insert and Editing records through SQL Dataadapter
You are updating the entire table Customers - is that what you want , no where clause. Modified it slighly...
VB Code:
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
Re: Insert and Editing records through SQL Dataadapter
Ah... the single quotes :blush:
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 :cry:
Re: Insert and Editing records through SQL Dataadapter
Same problem when your dealing with chars in sql you need to separate them the quotes.
VB Code:
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
Re: Insert and Editing records through SQL Dataadapter
Got it Thank you! :thumb: :afrog:
BTW. I've modified my Edit code
VB 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 ?
Re: Insert and Editing records through SQL Dataadapter
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