not to sure? updating question!

J

jnash

Well-known member
Joined
Oct 20, 2006
Messages
111
Programming Experience
Beginner
okay so ive got values in textboxes in my form, that i have taken from my database (mySQL)

i know need to update them (not delete, just replace some of the fields) however doing research on the internet has shown me maybe a few ways ado.net, adodb, im now so confused can anyone help me out with code please so i can get a start to this as its been bogging me for a good day now

thanks again

Jon
 
Sort by date Sort by votes
You will need the mySql .Net provider from mySql website and hand code up MySqlCommandBuilder. http://dev.mysql.com/doc/refman/5.0/en/connector-net-examples-mysqlcommandbuilder.html

Check out all the examples with the mysql provider
http://dev.mysql.com/doc/refman/5.0/en/connector-net-examples-mysqlcommand.html


if were using sql server or msaccess, then bindingsource, binding navigator, and a dataset but they only work on sql server and oledb.
MSDN has walkthroughs http://msdn.microsoft.com/vstudio/tour/vs2005_guided_tour/VS2005pro/Smart_Client/BindingSource.htm.

For details see the actual walkthroughs about data handling
http://msdn2.microsoft.com/en-us/library/ms171928(VS.80).aspx

Read about displaying data on a form from a dataset, then updating data on your form back to your dataset, and finally saving data in your dataset to your database.

Look to first create select, insert, update, delete commands with parameters to fill your dataset with data from your database, and then send updates back to the database using a dataadapter or tableadapter.

HTH
 
Upvote 0 Downvote
thanks, however im not allowed to use the wizards, so i cant use the walkthroughs, that was one of the first routes i went through, ive managed to add & retrieve info now i just need to update the values from the textboxes

any other example, i know there are some on the forum but nothing seems perfect!

thank you
 
Upvote 0 Downvote
Hardcoded update

You will have to change the SqlConnection and SqlDataAdapter to be the mySql objects(see post#2 links to the docs)

This will retrieve into dataset and will update insert and delete from dataset. I highly recommend you put a binding source between the dataset and the form controls. Then you can use a binding navigator to move around from row to row.

To save the edits from form to dataset you either need databinding or manually find the row and edit that columns

See this link for databindings http://samples.gotdotnet.com/quickstart/winforms/doc/WinFormsData.aspx

VB.NET: 
textBoxID.DataBindings.Add("Text", THEDATASETVARIABLENAME, "THEFIELDINTHETABLE")

Databinding should be set at Form_Load and will take care of moving data from dataset to form controls.

Here is completely hardcoded example. Notice the use of SOURCEVERSION parameter property

VB.NET: 
Public TheConnection As SqlConnection = New SqlConnection("Data Source=YOURSERVER;User Id=YOURUSERS;Password=YOURPASSWORD;Initial Catalog=northwind")
 
Public Function GetCustomers() As DataSet
   Dim custDa As SqlDataAdapter = New SqlDataAdapter("Select CustomerId, CompanyName From Customers", TheConnection)
   Dim custDs As DataSet = New DataSet()
   custDa.MissingSchemaAction = MissingSchemaAction.AddWithKey
   custDa.Fill(custDs, "Customers")
   GetCustomers = custDs
End Function
 
Public Function UpdateCustomers(ByVal custDs As DataSet) As DataSet
   Dim custDa As SqlDataAdapter = New SqlDataAdapter
   custDa.InsertCommand = New SqlCommand("INSERT INTO CUSTOMERS(CustomerId, CompanyName) Values (@CustomerId, @CompanyName)", TheConnection)
   custDa.InsertCommand.Parameters.Add("@CustomerId", SqlDbType.NChar, 5, "CustomerId")
   custDa.InsertCommand.Parameters.Add("@CompanyName", SqlDbType.NChar, 15, "CompanyName")
   custDa.UpdateCommand = New SqlCommand("UPDATE CUSTOMERS SET CUSTOMERID=@CUSTOMERID, COMPANYNAME=@COMPANYNAME WHERE CUSTOMERID=@OLDCUSTOMERID", TheConnection)
 
   custDa.UpdateCommand.Parameters.Add("@CustomerId", SqlDbType.NChar, 5, "CustomerId")
   custDa.UpdateCommand.Parameters.Add("@CompanyName", SqlDbType.NChar, 15, "CompanyName")
   Dim oPrmUpdate As SqlParameter = custDa.UpdateCommand.Parameters.Add("@OLDCUSTOMERID", SqlDbType.NChar, 15, "CustomerId")
   oPrmUpdate.SourceVersion = DataRowVersion.Original
   custDa.DeleteCommand = New SqlCommand("DELETE FROM CUSTOMERS WHERE CUSTOMERID=@CUSTOMERID", TheConnection)
 
   Dim oPrmDelete As SqlParameter = custDa.DeleteCommand.Parameters.Add("@CustomerId", SqlDbType.NChar, 5, "CustomerId")
   oPrmDelete.SourceVersion = DataRowVersion.Original
   custDa.Update(custDs, "Customers")
 
   UpdateCustomers = custDs
End Function
 
Last edited by a moderator:
Upvote 0 Downvote
im not sure how to call functions so ive droped it into a button even , the only problem is that when i try to press the button it crashed and says have is that there is no data table found

i declared one but no difference it still cant see it !

the structure of the msql db is

db name : supervid
db table : tblmember
db field1 : memberID
db field2 : FirstName

also the textboxes on the form are

txtMemberID & txtFname

also the memberID is a primary key and i dont want to change it EVER this is why ive edited some of your code out
here what i have done!

VB.NET: 
    Private Sub btnUpdateCustomer_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdateCustomer.Click
        Dim TheConnection As New MySqlConnection("server=localhost;" _
     & "user id=root;" _
     & "password=123;" _
     & "database=supervid")


        Dim custDS As New DataSet
        Dim custDa As MySqlDataAdapter = New MySqlDataAdapter
        custDa.InsertCommand = New MySqlCommand("INSERT INTO tblmember(memberID, FirstName) Values (" & txtMemberID.Text & "," & txtFname.Text & ")", TheConnection)
        custDa.InsertCommand.Parameters.Add(txtMemberID.Text, SqlDbType.NChar, 5, "memberID")
        custDa.InsertCommand.Parameters.Add(txtFname.Text, SqlDbType.NChar, 15, "FirstName")
        custDa.UpdateCommand = New MySqlCommand("UPDATE tblmember SET memberID=" & txtMemberID.Text & ", FirstName=" & txtFname.Text & " WHERE memberID=memberID", TheConnection)

        custDa.UpdateCommand.Parameters.Add(txtMemberID.Text, SqlDbType.NChar, 5, "memberID")
        custDa.UpdateCommand.Parameters.Add(txtFname.Text, SqlDbType.NChar, 15, "FirstName")
        Dim oPrmUpdate As MySqlParameter = custDa.UpdateCommand.Parameters.Add("memberID", SqlDbType.NChar, 15, "memberID")
        custDa.Update(custDS, "tblmember")

           End Sub
 
Upvote 0 Downvote
Try some changes to your code

0) You didn't pass the dataset, instead creating one from scratch. The SqlDataAdapter has no idea what data was changed. You will have to pass the dataset from which the data was loaded. Also if you don't name the table then in the update method (the last line of code), remove the second argument.

Example:
Inside the Form class ==> add a public variable of type DataSet and call is oDataset
Form_Load ===> run the SELECT statement in the SqlDataAdapter to FILL oDataset and add code for the databindings

Button Click ==> don't new up another dataset, instead use oDataset

1) Comment out the PARAMETERS.ADD because you are hard coding the SQL
by adding a single quote to the beginning of the line
'custDa.InsertCommand.Parameters.Add
'custDa.UpdateCommand.Parameters.Add
'Dim oPrmUpdate As MySqlParameter


2) Add single quotes on the inside of your paranthesis to quote identify the text in the sql statement
CHANGE Values (" & txtMemberID.Text & "," & txtFname.Text & ")",
TO..... Values ('" & txtMemberID.Text & "','" & txtFname.Text & "')",

3) Change the update statement to not SET the MEMBERID and use the MEMBERID in the where clause
CHANGE ("UPDATE tblmember SET memberID=" & txtMemberID.Text & ", FirstName=" & txtFname.Text & " WHERE memberID=memberID", TheConnection)
TO.... ("UPDATE tblmember SET FirstName=" & txtFname.Text & " WHERE memberID='" & txtMemberID.Text & "'", TheConnection)
 
Last edited:
Upvote 0 Downvote
i still get the same error > Update unable to find TableMapping['tblmember'] or DataTable 'tblmember'


and it points to

custDa.Update(custDS, "tblmember")
 
Upvote 0 Downvote
not sure about these parts???

Form_Load ===> run the SELECT statement in the SqlDataAdapter to FILL oDataset and add code for the databindings

&

Button Click ==> don't new up another dataset, instead use oDataset

is this right

VB.NET: 
Public Class frmEditCustomer
    Dim oDataset As DataSet


    Private Sub frmEditCustomer_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Dim adapter As MySqlDataAdapter = New MySqlDataAdapter("select * from tblmember", "server=localhost;database=supervid;uid=root;pwd=password")
        Dim ds As DataSet = New DataSet
        adapter.Fill(ds)
        cboCustomerID.DataSource = ds
        cboCustomerID.DataSource = ds.Tables(0)
        cboCustomerID.DisplayMember = "PhoneNo"
        cboCustomerID.ValueMember = "memberID"
    End Sub






    Private Sub btnUpdateCustomer_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdateCustomer.Click
        Dim TheConnection As New MySqlConnection("server=localhost;" _
        & "user id=root;" _
        & "password=password;" _
        & "database=supervid")


        Dim custDa As MySqlDataAdapter = New MySqlDataAdapter("Select memberId, FirstName From tblMembers", TheConnection)
        custDa.InsertCommand = New MySqlCommand("INSERT INTO tblmember(memberID, FirstName) Values ('" & txtMemberID.Text & "','" & txtFname.Text & "')", TheConnection)
        custDa.UpdateCommand = New MySqlCommand("UPDATE tblmember SET FirstName=" & txtFname.Text & " WHERE memberID='" & txtMemberID.Text & "'", TheConnection)
        custDa.Update(oDataset, "tblmember")
    End Sub


 Private Sub cboCustomerID_SelectionChangeCommitted(ByVal sender As Object, ByVal e As System.EventArgs) Handles cboCustomerID.SelectionChangeCommitted
        Dim temp As String
        temp = cboCustomerID.SelectedValue

        Dim adapter As MySqlDataAdapter = New MySqlDataAdapter("select * from tblmember where memberID =" & temp & "", "server=localhost;database=supervid;uid=root;pwd=password")
        Dim myDataTable As New DataTable

        adapter.Fill(myDataTable)
        ' this is where the textboxes recieve data!
        Me.txtMemberID.DataBindings.Add("Text", myDataTable, "memberID")
        Me.txtFname.DataBindings.Add("Text", myDataTable, "FirstName")
        Me.txtSname.DataBindings.Add("Text", myDataTable, "SurName")
        Me.txtAddress1.DataBindings.Add("Text", myDataTable, "Address1")
        Me.picCustomer.DataBindings.Add("ImageLocation", myDataTable, "CustomerPic")
        Me.txtFname.DataBindings.Clear() 'later on turn the clearing of the bindings into a module and just call it as a function'
        Me.txtSname.DataBindings.Clear()
        Me.txtMemberID.DataBindings.Clear()
        Me.picCustomer.DataBindings.Clear()
        Me.txtAddress1.DataBindings.Clear()
        Me.Text = picCustomer.ImageLocation
    End Sub
why do i need to fill, the information is already there from a previous retrieve however i want to change them then update!
 
Last edited:
Upvote 0 Downvote
Complete working example to try out

Ok here is a complete working example. It uses MsAccess database. Try it out and let us know what you think. It is all hardcoded using no wizards.

It uses a binding source and shows the data in both text boxes and a grid. The textbox MemberId is readonly and you can do the same for the datagrid column.

I tested and it does inserts, updates, and deletes.

As you make edits in either the textbox or the grid the changes are saved in the dataset and you need to click the save button to send the changes to the database.

Put the mdb file on your C drive or change the path in the connection string.
 

Attachments

  • MSACCESS_EDITABLE_GRID.zip
    64.1 KB · Views: 25
Last edited:
Upvote 0 Downvote
not sure about these parts???

Form_Load ===> run the SELECT statement in the SqlDataAdapter to FILL oDataset and add code for the databindings

&

Button Click ==> don't new up another dataset, instead use oDataset

is this right

VB.NET: 
Public Class frmEditCustomer
    Dim oDataset As DataSet


    Private Sub frmEditCustomer_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Dim adapter As MySqlDataAdapter = New MySqlDataAdapter("select * from tblmember", "server=localhost;database=supervid;uid=root;pwd=password")
        Dim ds As DataSet = New DataSet
        adapter.Fill(ds)
        cboCustomerID.DataSource = ds
        cboCustomerID.DataSource = ds.Tables(0)
        cboCustomerID.DisplayMember = "PhoneNo"
        cboCustomerID.ValueMember = "memberID"
    End Sub






    Private Sub btnUpdateCustomer_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdateCustomer.Click
        Dim TheConnection As New MySqlConnection("server=localhost;" _
        & "user id=root;" _
        & "password=password;" _
        & "database=supervid")


        Dim custDa As MySqlDataAdapter = New MySqlDataAdapter("Select memberId, FirstName From tblMembers", TheConnection)
        custDa.InsertCommand = New MySqlCommand("INSERT INTO tblmember(memberID, FirstName) Values ('" & txtMemberID.Text & "','" & txtFname.Text & "')", TheConnection)
        custDa.UpdateCommand = New MySqlCommand("UPDATE tblmember SET FirstName=" & txtFname.Text & " WHERE memberID='" & txtMemberID.Text & "'", TheConnection)
        custDa.Update(oDataset, "tblmember")
    End Sub


 Private Sub cboCustomerID_SelectionChangeCommitted(ByVal sender As Object, ByVal e As System.EventArgs) Handles cboCustomerID.SelectionChangeCommitted
        Dim temp As String
        temp = cboCustomerID.SelectedValue

        Dim adapter As MySqlDataAdapter = New MySqlDataAdapter("select * from tblmember where memberID =" & temp & "", "server=localhost;database=supervid;uid=root;pwd=password")
        Dim myDataTable As New DataTable

        adapter.Fill(myDataTable)
        ' this is where the textboxes recieve data!
        Me.txtMemberID.DataBindings.Add("Text", myDataTable, "memberID")
        Me.txtFname.DataBindings.Add("Text", myDataTable, "FirstName")
        Me.txtSname.DataBindings.Add("Text", myDataTable, "SurName")
        Me.txtAddress1.DataBindings.Add("Text", myDataTable, "Address1")
        Me.picCustomer.DataBindings.Add("ImageLocation", myDataTable, "CustomerPic")
        Me.txtFname.DataBindings.Clear() 'later on turn the clearing of the bindings into a module and just call it as a function'
        Me.txtSname.DataBindings.Clear()
        Me.txtMemberID.DataBindings.Clear()
        Me.picCustomer.DataBindings.Clear()
        Me.txtAddress1.DataBindings.Clear()
        Me.Text = picCustomer.ImageLocation
    End Sub
why do i need to fill, the information is already there from a previous retrieve however i want to change them then update!
 
Upvote 0 Downvote
thank you for that code, ive put this in there however im getting the error that the table doesnt exist!

VB.NET: 
Public Function UpdateCustomers(ByVal ds As DataSet) As DataSet

        Dim custDa As MySqlDataAdapter = New MySqlDataAdapter
        custDa.InsertCommand = New MySqlCommand("INSERT INTO tblMember(MemberId, FirstName) Values (@MemberId, @FirstName)", TheConnection)
        custDa.InsertCommand.Parameters.Add("@MemberId", SqlDbType.VarChar, 100, "MemberId")
        custDa.InsertCommand.Parameters.Add("@FirstName", SqlDbType.VarChar, 100, "FirstName")

        custDa.UpdateCommand = New MySqlCommand("UPDATE tblMember SET FirstName=@FirstName WHERE MemberId=@MemberId", TheConnection)
        custDa.UpdateCommand.Parameters.Add("@MemberId", SqlDbType.VarChar, 100, "MemberId")
        custDa.UpdateCommand.Parameters.Add("@FirstName", SqlDbType.VarChar, 100, "FirstName")

        custDa.DeleteCommand = New MySqlCommand("DELETE * FROM tblMember WHERE MemberId=@MemberId", TheConnection)
        Dim oPrmDelete As MySqlParameter = custDa.DeleteCommand.Parameters.Add("@MemberId", SqlDbType.VarChar, 100, "MemberId")
        oPrmDelete.SourceVersion = DataRowVersion.Original

        custDa.Update(ds, "tblmember")

        UpdateCustomers = ds
    End Function
 
Upvote 0 Downvote
Add the second arg to call table by name

Try changing the fill method from

adapter.Fill(myDataTable)

to

adapter.Fill(myDataTable, "tblMember")

you have to explicitly name the table in the dataset. If you don't it will be called Table1.
 
Upvote 0 Downvote
whilst looking at your code, ilooked at my code thats working and modified the add method i have done would this work
the error im getting at the moment is that my SQL is incorrect

VB.NET: 
    Private Sub btnUpdateCustomer_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdateCustomer.Click

        Dim cmd As New MySqlCommand
        cmd.CommandType = System.Data.CommandType.Text

        cmd.CommandText = "UPDATE tblMember SET Firstname =" & txtFname.Text & "WHERE memberID =" & txtMemberID.Text & ")"
        cmd.Connection = TheConnection

        TheConnection.Open()
        cmd.ExecuteNonQuery()
        TheConnection.Close()

        MessageBox.Show("Updated")
        Me.Controls.Clear()

    End Sub
End Class
 
Upvote 0 Downvote

Similar threads

D
  • Question
Create a table in a class that inherits from another class?
Replies
1
Views
1K
jmcilhinney
jmcilhinney
ALX
  • Question
Resolved Closing a Web Browser Control with KeyDown event
Replies
8
Views
3K
ALX
ALX
V
  • Question
Vscript to Vb.net
Replies
0
Views
2K
vvic
V
S
  • Question
Question New way to interact with cmd though windows forms
Replies
6
Views
6K
cjard
cjard
DarkGamerNZ
  • Question
Question Saving xml changes
Replies
1
Views
3K
DarkGamerNZ
DarkGamerNZ

Share this page

Latest posts

Back
Top