[RESOLVED] Updating Mysql database with vb datagridview

[vinitagrawal]

Hi!

I am creating a windows form with vb to edit a mysql database. I have a datagridview where i can view the database with custom queries. But, I want to save the edited cells back to the source database in their respective places. I dont know how to do that. Any help is appreciated. I am attaching my code:

Imports MySql.Data.MySqlClient

Private Sub showdata_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles showdata.Click

    Dim uspatserver As String = "server = localhost; user id = ****; password = ****; database = us_pat"
    Dim uspatconnection As MySqlConnection = New MySqlConnection

        datagrid.Columns.Clear()

       
        Dim cmd As MySqlCommand = New MySqlCommand
        Dim stm As String

        If uspatconnection.State = ConnectionState.Closed Then
            uspatconnection.Open()
        End If

        stm = "SELECT * FROM clinicalinfo"
               
        cmd.CommandType = CommandType.Text
        cmd.CommandText = stm
        cmd.Connection = uspatconnection

        Dim dt As New DataTable
        dt.Load(cmd.ExecuteReader)

        With datagrid
            .AutoGenerateColumns = True
            .DataSource = dt
        End With

        
        cmd.Dispose()
        cmd = Nothing
        uspatconnection.Close()
        uspatconnection.Dispose()

    End Sub

Thanks!

[Siddharth Rout]

Welcome to the vbforums vinitagrawal

I have moved your question from Visual Basic 6 and Earlier to VB.net.

[.paul.]

try this:

Imports MySql.Data.MySqlClient

Public Class form1

    Dim dt As DataTable
    Dim da As MySqlDataAdapter

    Private Sub showdata_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles showdata.Click

        Dim uspatserver As String = "server = localhost; user id = ****; password = ****; database = us_pat"
        Dim uspatconnection As MySqlConnection = New MySqlConnection(uspatserver)

        DataGrid.Columns.Clear()


        Dim cmd As MySqlCommand = New MySqlCommand
        Dim stm As String

        If uspatconnection.State = ConnectionState.Closed Then
            uspatconnection.Open()
        End If

        stm = "SELECT * FROM clinicalinfo"

        cmd.CommandType = CommandType.Text
        cmd.CommandText = stm
        cmd.Connection = uspatconnection

        da = New MySqlDataAdapter(cmd)

        dt = New DataTable
        da.Fill(dt)

        Dim cb As New MySqlCommandBuilder(da)

        With DataGrid
            .AutoGenerateColumns = True
            .DataSource = dt
        End With

        cmd.Dispose()
        cmd = Nothing
        uspatconnection.Close()
        uspatconnection.Dispose()

    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        'save changes
        DataGrid.EndEdit()
        da.Update(dt)
    End Sub

End Class

[vinitagrawal]

try this:

Imports MySql.Data.MySqlClient

Public Class form1

    Dim dt As DataTable
    Dim da As MySqlDataAdapter

    Private Sub showdata_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles showdata.Click

        Dim uspatserver As String = "server = localhost; user id = ****; password = ****; database = us_pat"
        Dim uspatconnection As MySqlConnection = New MySqlConnection(uspatserver)

        DataGrid.Columns.Clear()


        Dim cmd As MySqlCommand = New MySqlCommand
        Dim stm As String

        If uspatconnection.State = ConnectionState.Closed Then
            uspatconnection.Open()
        End If

        stm = "SELECT * FROM clinicalinfo"

        cmd.CommandType = CommandType.Text
        cmd.CommandText = stm
        cmd.Connection = uspatconnection

        da = New MySqlDataAdapter(cmd)

        dt = New DataTable
        da.Fill(dt)

        Dim cb As New MySqlCommandBuilder(da)

        With DataGrid
            .AutoGenerateColumns = True
            .DataSource = dt
        End With

        cmd.Dispose()
        cmd = Nothing
        uspatconnection.Close()
        uspatconnection.Dispose()

    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        'save changes
        DataGrid.EndEdit()
        da.Update(dt)
    End Sub

End Class

Thanks for the help! you made my day. How can i put datatype checking, so that errors from user can save them the pain of reediting?

[.paul.]

your db table should have a primary key field + it should work

[vinitagrawal]

your db table should have a primary key field + it should work

yes, it did work. that was my mistake. i was using an alias in select command.

anyways, it works fine now.

I was wondering if there is anyway to check for data types for incorrect entries by user, in case it happens.

[.paul.]

I was wondering if there is anyway to check for data types for incorrect entries by user, in case it happens.

not sure about that. start a new thread for that question

[vinitagrawal]

Thank you, very much. I will.