Problem in Vb.net Visual Studio Using Update Command

Question

Hello I am creating a Search function and Update button from a sql database in which I need to be able to search for certain names and such but also need to be able to make changes within the dataset and save them. As of now the search function works as I want it to, however, the update button does not truly save the changes as when I stop and restart the code it reverts back to the default dataset even after I attempted to update it to change certain values. Any Ideas as to what I am doing wrong? Other ways you may suggest to edit insert and delete values in the dataset? Anything helps! Thank You!

Imports System.Data.SqlClient

Imports System.Data.Common

Public Class Form1

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        
        Dim connection As New SqlConnection("Connection String PlaceHolder")
        Dim Table As New DataTable()
        
        Dim Adapter As New SqlDataAdapter("SELECT * FROM TrueTrack1", connection)
        
        Adapter.Fill(Table)
        
        DataGridView1.DataSource = Table
        load_data()



    End Sub



    Private ReadOnly queryTimer As New System.Threading.Timer(AddressOf runQuery, Nothing, -1, -1)
    Private searchName As String
    Private searchType As String
    Private searchIP As String
    Private textChangeQueryDelay As Integer = 1000

    Private Sub TextBoxes_TextChanged(sender As Object, e As EventArgs) Handles TextBox1.TextChanged, TextBox2.TextChanged, TextBox3.TextChanged
        searchName = TextBox1.Text
        searchType = TextBox2.Text
        searchIP = TextBox3.Text
        queryTimer.Change(textChangeQueryDelay, -1)
    End Sub

    Private Sub runQuery(state As Object)
        Dim table = New DataTable()
        Using connection = New SqlConnection("Connection String Placeholder")
            Using command = New SqlCommand()
                command.Connection = connection
                Dim commandText = "SELECT * FROM TrueTrack1 WHERE 1=1 "
                If Not String.IsNullOrEmpty(searchName) Then
                    commandText &= " AND UserName like @name "
                    command.Parameters.Add("@name", SqlDbType.VarChar, 100).Value = "%" & searchName & "%"
                End If
                If Not String.IsNullOrEmpty(searchType) Then
                    commandText &= " AND DeviceType like @type "
                    command.Parameters.Add("@type", SqlDbType.VarChar, 100).Value = "%" & searchType & "%"
                End If
                If Not String.IsNullOrEmpty(searchIP) Then
                    commandText &= " AND IPAddress like @ip "
                    command.Parameters.Add("@ip", SqlDbType.VarChar, 100).Value = "%" & searchIP & "%"
                End If
                command.CommandText = commandText
                Using adapter = New SqlDataAdapter(command)
                    adapter.Fill(table)
                End Using
            End Using
        End Using
        DataGridView1.Invoke(Sub() DataGridView1.DataSource = table)
    End Sub





    Private Sub Button1_Click(sender As Object, e As EventArgs) 

    End Sub

    Dim da As New SqlDataAdapter
    Dim dt As New DataSet

    Private Sub Button1_Click_1(sender As Object, e As EventArgs) Handles Button1.Click
        Dim cmd As New SqlCommandBuilder(da)
        Dim changes As New DataSet
        Dim table As New DataTable()




        changes = dt.GetChanges
        If changes IsNot Nothing Then
            da.Update(changes)
            da.Fill(dt)
            DataGridView1.DataSource = dt.Tables(0)
            load_data()
        End If
    End Sub

    Private Sub load_data()
        Dim conn As New SqlConnection("Connection String Placeholder")
        conn.Open()
        da = New SqlDataAdapter("Select * From TrueTrack", conn)
        dt.Clear()
        da.Fill(dt)
        DataGridView1.DataSource = dt.Tables(0)

        conn.Close()
    End Sub


End Class

Seem to be missing something from your posted code. I can se you using a heap of data adapters to query the database, but where are you implementing the update command(s)? — Hursey
– Hursey, Commented Jan 20, 2022 at 22:15
@Hursey, there's a call to Update in there and a command builder. — user17922293
– user17922293, Commented Jan 21, 2022 at 3:45
When you call Update on your data adapter, what number does it return? If that is not zero then the changes are being saved. That call is not making any changes to your DataSet though, because you're calling GetChanges and saving from that result. If you're going to do that, you should be calling AcceptChanges afterwards, to locally accept the changes you just saved. — user17922293
– user17922293, Commented Jan 21, 2022 at 3:48
@user17922293 yeah true but as the dataadapter is initialised at run time, the will need to be an actual update command if I'm not mistaken. — Hursey
– Hursey, Commented Jan 21, 2022 at 3:50
@Hursey, the command builder is created and associated with the existing data adapter when the changes are saved. The code is messy and confusing but that aspect should work. — user17922293
– user17922293, Commented Jan 21, 2022 at 3:52

Xingyu Zhao · Accepted Answer · 2022-01-21 06:25:16Z

In order to update(including edit, insert and delete) database and DataTable from DataGridView, you can refer to the following code.

Private dt As DataTable = New DataTable
Private da As SqlDataAdapter
Private connection As SqlConnection = New SqlConnection("your connection string")
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    DataGridView1.EndEdit()
    da.Update(dt)
End Sub
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    bind_data()
End Sub
Private Sub Form1_FormClosing(sender As Object, e As FormClosingEventArgs) Handles MyBase.FormClosing
    connection.Close()
End Sub
Private Sub bind_data()
    connection.Open()
    Dim cmdTxt As String = "SELECT * FROM TrueTrack"
    da = New SqlDataAdapter(New SqlCommand(cmdTxt, connection))
    Dim builder As SqlCommandBuilder = New SqlCommandBuilder(da)
    da.Fill(dt)
    Dim source As BindingSource = New BindingSource With {
                                    .DataSource = dt
                                  }
    DataGridView1.DataSource = source
End Sub

Collectives™ on Stack Overflow

Problem in Vb.net Visual Studio Using Update Command

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related