locked
update table row with VB.net

    Question

  • I am trying to use VB.net to update a datarow (record) using a tableadapter but it won't update. 

    I created a tableadapter based on the following query which will add a new row, but can't find the way to update the row already in the database.

    SELECT     Player_ID, FirstName, LastName, Gender, Notes
    FROM         tblPlayerInfo
    WHERE     (Player_ID = @Selected_Player_ID)

    My code is

     

    Protected Sub butUpdate_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles butUpdate.Click

     

    Dim uInfota As New dssPlayerInfoTableAdapters.tblPlayerInfoTableAdapter
    Dim uInfodt As dssPlayerInfo.tblPlayerInfoDataTable
    Dim uInfodrow As dssPlayerInfo.tblPlayerInfoRow 

    uInfodt = uInfota.GetDataPlayerInfo(Session(

    "Player_ID"))

    uInfodrow = uInfodt.NewtblPlayerInfoRow

     

    With uInfodrow
    .FirstName = txtFirstName.Text
    .LastName = txtLastName.Text
    .Gender = ddlGender.Text

     

    End With

    uInfodt.AddtblPlayerInfoRow(uInfodrow)
    uInfota.Update(uInfodt)


    End
    Sub

     

     

     

    Tuesday, December 08, 2009 7:06 PM

Answers

  • You can update you table by doing it:

     Using cm As New SqlConnection(your connection string please)

     Dim command As New SqlCommand("UPDATE Table_OBS Set PictureMe=@PictureMe Where IDMe=@IDMe", cm)
     cm.Open()
     command.Parameters.AddWithValue("@IDMe", Me.TextBox5.Text)
     command.Parameters.AddWithValue("@PictureMe", photo)
     Dim ok As Integer = command.ExecuteNonQuery()

     End Using
    Don't judge me, just Upgrade me. Thanks!
    Wednesday, December 09, 2009 12:44 AM
  • Thank you Malange for your friendly help.


    Hi SirAndre,

    Welcome to MSDN forums!

    Additionally, here are four methods to make simple Data Access application(Next, Previous, First, Last, Update, Delete, Insert, Save) for you to check.

    Method 1:  Update (Insert/Update/Delete) data back into MS Access database from DataGridView.
    http://social.msdn.microsoft.com/forums/en-US/Vsexpressvb/thread/5980181e-f666-4f0a-ab50-c4ebecf96f02/

    Imports System.Data.OleDb  

     

    Public Class Form1  

     

        Dim myDA As OleDbDataAdapter  

        Dim myDataSet As DataSet  

     

        Private Sub Form1_Load(ByVal sender As System.ObjectByVal e As System.EventArgs) Handles MyBase.Load  

            Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=|DataDirectory|\myDB.mdb")  

            Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM Table1", con)  

            con.Open()  

            myDA = New OleDbDataAdapter(cmd)  

            'Automatically generates DeleteCommand,UpdateCommand and InsertCommand for DataAdapter object  

            Dim builder As OleDbCommandBuilder = New OleDbCommandBuilder(myDA)  

            myDataSet = New DataSet()  

            myDA.Fill(myDataSet, "MyTable")  

            DataGridView1.DataSource = myDataSet.Tables("MyTable").DefaultView  

            con.Close()  

            con = Nothing 

        End Sub 

          

        ' Save data back into database  

        Private Sub Button1_Click(ByVal sender As System.ObjectByVal e As System.EventArgs) Handles Button1.Click  

            Me.Validate()  

            Me.myDA.Update(Me.myDataSet.Tables("MyTable"))  

            Me.myDataSet.AcceptChanges()  

        End Sub 

     

    End Class 



    Method 2: Execute Select/Insert/Delete/Update T-SQL commands in VB.NET code
       Code sample: How to Select/Insert/Delete/Update records in MS Access database in VB.NET
       http://forums.msdn.microsoft.com/en-US/vbgeneral/thread/87913f28-992d-4705-963b-cb0ffa53d8dd/
       Code sample: How to Select/Insert/Delete/Update records in SQL Server database in VB.NET
       http://forums.msdn.microsoft.com/en-US/vbgeneral/thread/89e1067d-16e7-44e8-b12d-d78845bf255f/


    Method 3. Using Data Wizard with BindingNavigator control.
    Please check the 11th post in this thread for detailed walkthrough:
    http://social.msdn.microsoft.com/forums/en-US/Vsexpressvb/thread/ff3f953b-da66-4f03-b4e4-981bab7d783b/

      

    Method 4. Using DataSet/DataTable/DataAdapter in VB.NET code
    Please check the 12th post and 13th post in this thread for detailed code sample:
    http://social.msdn.microsoft.com/forums/en-US/Vsexpressvb/thread/ff3f953b-da66-4f03-b4e4-981bab7d783b/

      

    Best regards,
    Martin Xie


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Wednesday, December 16, 2009 3:05 AM

All replies

  • You can update you table by doing it:

     Using cm As New SqlConnection(your connection string please)

     Dim command As New SqlCommand("UPDATE Table_OBS Set PictureMe=@PictureMe Where IDMe=@IDMe", cm)
     cm.Open()
     command.Parameters.AddWithValue("@IDMe", Me.TextBox5.Text)
     command.Parameters.AddWithValue("@PictureMe", photo)
     Dim ok As Integer = command.ExecuteNonQuery()

     End Using
    Don't judge me, just Upgrade me. Thanks!
    Wednesday, December 09, 2009 12:44 AM
  • Thank you Malange for your friendly help.


    Hi SirAndre,

    Welcome to MSDN forums!

    Additionally, here are four methods to make simple Data Access application(Next, Previous, First, Last, Update, Delete, Insert, Save) for you to check.

    Method 1:  Update (Insert/Update/Delete) data back into MS Access database from DataGridView.
    http://social.msdn.microsoft.com/forums/en-US/Vsexpressvb/thread/5980181e-f666-4f0a-ab50-c4ebecf96f02/

    Imports System.Data.OleDb  

     

    Public Class Form1  

     

        Dim myDA As OleDbDataAdapter  

        Dim myDataSet As DataSet  

     

        Private Sub Form1_Load(ByVal sender As System.ObjectByVal e As System.EventArgs) Handles MyBase.Load  

            Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=|DataDirectory|\myDB.mdb")  

            Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM Table1", con)  

            con.Open()  

            myDA = New OleDbDataAdapter(cmd)  

            'Automatically generates DeleteCommand,UpdateCommand and InsertCommand for DataAdapter object  

            Dim builder As OleDbCommandBuilder = New OleDbCommandBuilder(myDA)  

            myDataSet = New DataSet()  

            myDA.Fill(myDataSet, "MyTable")  

            DataGridView1.DataSource = myDataSet.Tables("MyTable").DefaultView  

            con.Close()  

            con = Nothing 

        End Sub 

          

        ' Save data back into database  

        Private Sub Button1_Click(ByVal sender As System.ObjectByVal e As System.EventArgs) Handles Button1.Click  

            Me.Validate()  

            Me.myDA.Update(Me.myDataSet.Tables("MyTable"))  

            Me.myDataSet.AcceptChanges()  

        End Sub 

     

    End Class 



    Method 2: Execute Select/Insert/Delete/Update T-SQL commands in VB.NET code
       Code sample: How to Select/Insert/Delete/Update records in MS Access database in VB.NET
       http://forums.msdn.microsoft.com/en-US/vbgeneral/thread/87913f28-992d-4705-963b-cb0ffa53d8dd/
       Code sample: How to Select/Insert/Delete/Update records in SQL Server database in VB.NET
       http://forums.msdn.microsoft.com/en-US/vbgeneral/thread/89e1067d-16e7-44e8-b12d-d78845bf255f/


    Method 3. Using Data Wizard with BindingNavigator control.
    Please check the 11th post in this thread for detailed walkthrough:
    http://social.msdn.microsoft.com/forums/en-US/Vsexpressvb/thread/ff3f953b-da66-4f03-b4e4-981bab7d783b/

      

    Method 4. Using DataSet/DataTable/DataAdapter in VB.NET code
    Please check the 12th post and 13th post in this thread for detailed code sample:
    http://social.msdn.microsoft.com/forums/en-US/Vsexpressvb/thread/ff3f953b-da66-4f03-b4e4-981bab7d783b/

      

    Best regards,
    Martin Xie


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Wednesday, December 16, 2009 3:05 AM