PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197
VS 2017 I can populate a DataGridView but I cannot manage to update it-VBForums
Results 1 to 8 of 8

Thread: I can populate a DataGridView but I cannot manage to update it

  1. #1

    Thread Starter
    New Member
    Join Date
    Jan 2019
    Posts
    4

    Cool I can populate a DataGridView but I cannot manage to update it

    Hi all,

    I can populate a DataGridView but I cannot manage to update it. I have tried everything but I just cannot get it working. Is there anyone who can please assist me?

    Here is my code:

    Imports System
    Imports System.Data
    Imports System.Windows.Forms
    Imports System.Data.SqlClient

    Public Class custinfo

    Private Sub custinfo_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    'TODO: This line of code loads data into the 'InsourcenetDataSet.custinfo' table. You can move, or remove it, as needed.
    Me.CustinfoTableAdapter.Fill(Me.InsourcenetDataSet.custinfo)
    Dim sqlcn As New SqlConnection("server=laurence-pc\sqlexpress;database=insourcenet;Integrated Security=True")
    Dim sqladapter As New SqlDataAdapter("select * from custinfo", sqlcn)
    Dim ds As DataSet = New DataSet
    sqladapter.Fill(ds, "custinfo")
    DataGridView1.DataSource = ds.Tables("custinfo")
    End Sub

    Private Sub DataGridView1_MouseUp(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles DataGridView1.MouseUp


    Dim hit As DataGridView.HitTestInfo = Me.DataGridView1.HitTest(e.X, e.Y)

    Me.DataGridView1.ClearSelection()
    Me.DataGridView1.Rows(hit.RowIndex).Selected = True

    If hit.Type = DataGridViewHitTestType.Cell Then
    TextBox1.Text = DataGridView1.SelectedCells(0).Value.ToString
    TextBox2.Text = Me.DataGridView1.SelectedCells(1).Value.ToString
    TextBox3.Text = Me.DataGridView1.SelectedCells(2).Value.ToString
    TextBox4.Text = Me.DataGridView1.SelectedCells(3).Value.ToString
    TextBox5.Text = Me.DataGridView1.SelectedCells(4).Value.ToString
    TextBox6.Text = Me.DataGridView1.SelectedCells(5).Value.ToString
    TextBox7.Text = Me.DataGridView1.SelectedCells(6).Value.ToString
    TextBox8.Text = Me.DataGridView1.SelectedCells(7).Value.ToString
    End If


    End Sub


    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Dim sqlcn As New SqlConnection("server=laurence-pc\sqlexpress;database=insourcenet;Integrated Security=True")
    Dim updatedata As String = String.Format("INSERT INTO custinfo (companyname,tel1,tel2,street,buildingno,buildingname,suburb,acccode), TextBox1.Text, TextBox2.Text, TextBox3, TextBox4.Text, TextBox5.Text, TextBox6.Text, TextBox7.Text, TextBox8.Text")
    Dim sqladapter As New SqlDataAdapter("access", sqlcn)
    Dim ds As DataSet = New DataSet

    'DataGridView1.DataSource = ds.Tables("custinfo")


    DataGridView1.Update(updatedata)
    'DataGridView1.Refresh()
    'Dim tblChanges As String = ds.GetChanges()
    'Try
    'If Not (ds.GetChanges() Is Nothing) Then
    'custinfo.Update(DataGridView1)
    'End If
    ' Catch ex As Exception
    'MsgBox("Something has gone wrong..." & vbNewLine & ex.Message)

    ' End Try

    End Sub
    End Class

  2. #2
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    23,126

    Re: I can populate a DataGridView but I cannot manage to update it

    You need to use a SqlCommandBuilder...

    https://support.microsoft.com/en-gb/...adapter-object

  3. #3
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    104,704

    Re: I can populate a DataGridView but I cannot manage to update it

    A data adapter has four command properties: SelectCommand, InsertCommand, UpdateCommand and DeleteCommand. if you know even a little about data access, you know that they correspond to the basic CRUD operations. When you call Fill on a data adapter, the SelectCommand is executed, which executes the SELECT SQL statement it contains to retrieve data from the database. When you call Update on the data adapter, the InsertCommand, UpdateCommand and DeleteCommand are executed as required, executing the INSERT, UPDATE and DELETE SQL statements they contain to insert rows that you have added, update rows that you have modified and delete rows that you have deleted. If you don't populate those three commands of the data adapter then calling Update can't do anything useful.

    There are two ways to populate those commands. You can use a command builder, as .paul. has suggested, to generate the commands automatically. That's the easiest option and will work in the most common cases. You need to have populated the SelectCommand to be used as a basis, the SQL statement must retrieve from only one table and that table must have a primary key. You can also populate the command properties yourself and you must do so in cases where a command builder cannot be used. If you follow the CodeBank link in my signature below and check out my thread on Retrieving & Saving Data, you'll find examples of both options, as well as other common ADO.NET scenarios.

  4. #4
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    104,704

    Re: I can populate a DataGridView but I cannot manage to update it

    By the way, there's no need for you to manually move back and forth between the gird and the TextBoxes. You can bind your DataTable to all the controls. In that case, selecting a row in the grid will automatically populate the TextBoxes with the data from that row. Any edits you make in the TextBoxes will be automatically pushed back tot he grid too, because they are both bound tot he same DataTable. The binding would look something like this:
    vb.net Code:
    1. 'No point creating a DataSet if you only plan to use one DataTable.
    2. Dim table As New DataTable
    3.  
    4. adapter.Fill(table)
    5.  
    6. 'The BindingSource would be added in the designer.
    7. myBindingSource.DataSource = table
    8. myDataGridView.DataSource = myBindingSource
    9. myTextBox.DataBindings.Add("Text", myBindingSource, "ColumnNameHere")

  5. #5

    Thread Starter
    New Member
    Join Date
    Jan 2019
    Posts
    4

    Re: I can populate a DataGridView but I cannot manage to update it

    Hi .paul.

    thanks for the info, I am having a look.

  6. #6

    Thread Starter
    New Member
    Join Date
    Jan 2019
    Posts
    4

    Re: I can populate a DataGridView but I cannot manage to update it

    Hi .paul.

    I am unable to find your VB examples. could you please give me a link?


    thanks

    bob

  7. #7

    Thread Starter
    New Member
    Join Date
    Jan 2019
    Posts
    4

    Re: I can populate a DataGridView but I cannot manage to update it

    This where I am right now..

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Dim cmdBuilder As SqlCommandBuilder
    Dim sqlcn As New SqlConnection("server=laurence-pc\sqlexpress;database=insourcenet;Integrated Security=True")
    'Dim updatedata As String = ("INSERT INTO custinfo (companyname,tel1,tel2,street,buildingno,buildingname,suburb,acccode), TextBox1.Text, TextBox2.Text, TextBox3, TextBox4.Text, TextBox5.Text, TextBox6.Text, TextBox7.Text, TextBox8.Text")
    Dim updatedata As String = ("INSERT INTO custinfo (companyname), TextBox1.Text")
    'Dim sqladapter As New SqlDataAdapter("access", sqlcn)
    Dim sqladapter As New SqlDataAdapter("select * from custinfo", sqlcn)
    Dim ds As DataSet = New DataSet

    cmdBuilder = New SqlCommandBuilder(sqladapter)
    'sqladapter.Fill(ds, "custinfo")
    'sqladapter.Update(ds, updatedata)
    sqladapter.Update(ds, "custinfo")

    End Sub
    End Class

    Sill no luck. What am I doing wrong?

  8. #8
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    104,704

    Re: I can populate a DataGridView but I cannot manage to update it

    Did you check out my CodeBank thread? What do you not understand about the command builder example it contains?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width