none
Data Load on Datagrid Columns(1) RRS feed

  • Question

  • If DataGridView Columns(4) = "batch_no" then Data Load on DatagridView Columns(1). I write some code it's not function. I've already data load from another table on DataGridView. How to write this code :

     Public Sub Batch_Load()
            For i = 0 To DGV.Rows.Count-1
                Cons()
                Str = "Select batch_no from tbl_mstock where batch_no= '" & DGV.Item(4, i).Value & "'"
                Cmd = New MySqlCommand(Str, Con)
                Dr = Cmd.ExecuteReader
                While Dr.Read = True
                    Batchs = Dr(0)
                End While
                connection_close()
            Next
            DGV.Item(1, i).Value = Batchs
        End Sub
    
    Private Sub DGV_CellEndEdit(sender As Object, e As DataGridViewCellEventArgs) Handles DGV.CellEndEdit
            If e.ColumnIndex = 7 Then
                Batch_Load()
            End If
        End Sub

    Please help my problem........



    dopenoinam


    Friday, June 9, 2017 12:27 PM

All replies

  • Please be more specific to what is not working.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Friday, June 9, 2017 12:37 PM
    Moderator
  • Cannot data load on dgv.items(1,i).value

    dopenoinam

    Friday, June 9, 2017 12:42 PM
  • Use Visual Studio's debugger, place a break-point on

    Str = "Select batch_no from tbl_mstock where batch_no= '" & DGV.Item(4, i).Value & "'"
    When hit, examine values, if that is not enough step through the rest of the code looking for an issue.

    How to debug

    https://msdn.microsoft.com/en-us/library/y740d9d3.aspx


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Friday, June 9, 2017 12:48 PM
    Moderator
  • Error Image
    Public Sub Bill_Load()
            Bill = cboYear.Text & "/" & cboMonth.Text & "-" & txtBill.Text
            If Len(txtBill.Text) = 0 Then
                MsgBox("Enter Bill No.")
            Else
                Cons()
                Str = "Select S_ID,Bill_Date,cus_name,cus_address,g_total,discount,VAT,net_amt,paid_amt from sales where bill_no= '" & Bill & "'"
                Cmd = New MySqlCommand(Str, Con)
                Dr = Cmd.ExecuteReader
                If Dr.Read = True Then
                    lblDate.Text = Dr(1)
                    lblName.Text = Dr(2)
                    lblAddress.Text = Dr(3)
                    lblTotal.Text = Dr(4)
                    lblDiscount.Text = Dr(5)
                    lblVAT.Text = Dr(6)
                    lblGT.Text = Dr(7)
                    lblPaid.Text = Dr(8)
                End If
                connection_close()
            End If
        End Sub
    
        Public Sub Bill_Body()
            Bill = cboYear.Text & "/" & cboMonth.Text & "-" & txtBill.Text
            If Len(txtBill.Text) = 0 Then
                MsgBox("Enter Bill No.")
            Else
                DGV.Rows.Clear()
                i = 0
                Cons()
                Str = "Select m_name,m_cat,batch_no,mfg_dt,exp_dt,qty,qty1,rate,mrp,vat,vat1,amt from sales1 where bill_no= '" & Bill & "'"
                Cmd = New MySqlCommand(Str, Con)
                Dr = Cmd.ExecuteReader
                While Dr.Read = True
                    DGV.Rows.Add()
                    DGV.Item(2, i).Value = Dr(0)    'Description
                    DGV.Item(3, i).Value = Dr(1)    'Pack
                    DGV.Item(4, i).Value = Dr(2)    'Batch No.
                    DGV.Item(5, i).Value = Dr(3)    'Mfg
                    DGV.Item(6, i).Value = Dr(4)    'Exp
                    DGV.Item(7, i).Value = Dr(5)    'Qty
                    DGV.Item(8, i).Value = Dr(6)    '+Qty
                    DGV.Item(9, i).Value = Dr(7)    'Rate
                    DGV.Item(10, i).Value = Dr(8)    'MRP
                    DGV.Item(11, i).Value = Dr(9)    'VAT
                    DGV.Item(12, i).Value = Dr(10)    'VAT1
                    DGV.Item(13, i).Value = Dr(11)    'AMT
                    i = i + 1
                End While
                connection_close()
            End If
        End Sub
    
    Private Sub txtBill_KeyPress(sender As Object, e As KeyPressEventArgs) Handles txtBill.KeyPress
            If e.KeyChar = Convert.ToChar(Keys.Enter) Or e.KeyChar = Convert.ToChar(Keys.Tab) Then
                Cons()
                Str = "Select Max(S_ID) from Sales"
                Cmd = New MySqlCommand(Str, Con)
                Dr = Cmd.ExecuteReader
                If Dr.Read = True Then
                    If Val(txtBill.Text) >= Dr(0) Then
                        MsgBox("Bill No. Not found....., Try Another Bill No.")
                    Else
                        Bill_Load()
                        Bill_Body()
                    End If
                    connection_close()
                End If
                DGV.Select()
                DGV.CurrentCell = DGV(6, DGV.NewRowIndex - 1)
               
            End If
        End Sub
    
    
     Public Sub Batch_Load()
            For i = 0 To DGV.Rows.Count
                Cons()
                Str = Str = "Select batch_no from tbl_mstock where batch_no= '" & DGV.Item(4, i).Value & "'"
                Cmd = New MySqlCommand(Str, Con)
                Dr = Cmd.ExecuteReader
                While Dr.Read = True
    
                    DGV.Item(1, i).Value = Dr(0)
    
                End While
                connection_close()
            Next
    
        End Sub
    
    
    Private Sub DGV_CellBeginEdit(sender As Object, e As DataGridViewCellCancelEventArgs) Handles DGV.CellBeginEdit
            If e.ColumnIndex = 7 Then
                Batch_Load()
            End If
        End Sub

    I cannot run above code. Please help my problem.

    dopenoinam

    Friday, June 9, 2017 2:22 PM
  • In regards to your last post with screenshot and code, what I saw was how you setup the operation overall was not proper and secondly the error as per the screenshot error message points to an issue with your SQL statement which could be 

    • How you constructed the SQL
    • Using variables declared at form level rather than procedure level which is best.
    • The WHERE clause assumes a string, there are many issues that can cause issues here and why you should be using parameters.

    With the above in mind I would follow the pattern shown below, you will need to change the connection, command and reader objects from SqlConnection, SqlCommand and SqlDataReader to the equivalent for MySql and you should be fine syntax-wise.

    Focusing in the parameter, I set it up as a string, if not a string but perhaps a number then change .DbType in the construction of the parameter to the appropriate type e.g. integer, double etc. 

    Lastly, note for you I wrapped possible failing code in a try-catch.

    Public Class Form1 Public Sub Batch_Load() Dim errorList As New List(Of String) Using cn As New SqlConnection With {.ConnectionString = "TODO"} Using cmd As New SqlCommand With {.Connection = cn} cmd.CommandText = "SELECT batch_no FROM tbl_mstock WHERE batch_no = @batch_no" cmd.Parameters.Add(New SqlParameter With { .ParameterName = "@batch_no", .DbType = DbType.String })

    cn.Open Try For i As Integer = 0 To DGV.Rows.Count -1 If Not DGV.Rows(i).IsNewRow Then

    cmd.Parameters("@batch_no").Value = DGV(4, i).Value Dim reader As SqlDataReader = cmd.ExecuteReader While reader.Read DGV.Item(1, i).Value = reader(0) End While reader.Close() End If Next Catch ex As Exception errorList.Add(ex.Message) End Try End Using End Using If errorList.Count > 0 Then MessageBox.Show("See Visual Studio's Output window for errors!!!") For Each msg As String In errorList Console.WriteLine(msg) Next Else MessageBox.Show("Finished") End If End Sub End Class

    The next version handles things a little differently. We stop on the first exception, show the exception message along with the value for the WHERE condition.

    Public Class Form1 Public Sub Batch_Load() Dim currentWhereValue As Object = Nothing Using cn As New SqlConnection With {.ConnectionString = "TODO"} Using cmd As New SqlCommand With {.Connection = cn} cmd.CommandText = "SELECT batch_no FROM tbl_mstock WHERE batch_no = @batch_no" cmd.Parameters.Add(New SqlParameter With { .ParameterName = "@batch_no", .DbType = DbType.String })

    cn.Open

    Try For i As Integer = 0 To DGV.Rows.Count -1 If Not DGV.Rows(i).IsNewRow Then currentWhereValue = DGV(4, i).Value cmd.Parameters("@batch_no").Value = currentWhereValue Dim reader As SqlDataReader = cmd.ExecuteReader While reader.Read DGV.Item(1, i).Value = reader(0) End While reader.Close() End If Next Catch ex As Exception MessageBox.Show("See Visual Studio's Output window for errors!!!") Console.WriteLine($"Exception: {ex.Message}") Console.WriteLine($"@batch_no = {currentWhereValue}") Exit Try End Try End Using End Using End Sub End Class

    That is my take on how to figure out the issue plus working with the debugger.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites




    Friday, June 9, 2017 5:16 PM
    Moderator