Member Avatar for derozza

Dear all. I need help regarding on my error.. this is the sample code:

Private Sub GetDetails(ByVal id As String)
        Dim conString As String = (ConfigurationManager.ConnectionStrings.Item("ItemListing").ToString)
        Dim objConnection As New MySqlConnection(conString)
        objConnection.Open()
        Dim sdr As MySqlDataReader
        Dim sSQL As String = " SELECT i.SuppCode,s.SuppName,s.telno,s.Faxno,s.email,s.Salesman,s.Mobileno,s.add1," & _
                             " i.masterefno, i.invoicerefno,a.remarks, i.approval_date, i.approveby, a.new_listing_fee, a.totalamount, a.collectedby,a.otherinfor" & _
                             " FROM Supplier AS s, itemdetail AS i, account AS a" & _
                             " WHERE i.SuppCode = '" & id & "'" & _
                             " AND s.suppcode=i.suppcode" & _
                             " AND i.masterefno=a.masterefno" & _
                             " AND s.suppcode=a.suppcode" & _
                             " AND i.isApprove LIKE 'Approve%'" & _
                             " GROUP BY s.SuppCode,i.masterefno"
        Dim objCommandGrid As New MySqlCommand(sSQL, objConnection)
        sdr = objCommandGrid.ExecuteReader
        If sdr.HasRows() Then
            sdr.Read()
            txtsuppcode.Text = sdr("SuppCode").ToString()
            txtsuppname.Text = sdr("SuppName").ToString()
            txttelno.Text = sdr("telno").ToString()
            txtfax.Text = sdr("faxno").ToString()
            txtemail.Text = sdr("email").ToString()
            txtsalesman.Text = sdr("Salesman").ToString()
            txtmobile.Text = sdr("mobileno").ToString()
            txtadd.Text = sdr("add1").ToString()
            txtMRef.Text = sdr("masterefno").ToString()
            txtIRef.Text = sdr("Invoicerefno").ToString()
            txtcdate.Text = sdr("Approval_Date").ToString()
            txtcreatedby.Text = sdr("Approveby").ToString()
            txtfee.Text = sdr("new_listing_fee").ToString()
            txtTotal.Text = sdr("totalamount").ToString()
            txtcollected.Text = sdr("collectedby").ToString()
            txtoinfor.Text = sdr("otherinfor").ToString()
        End If
        objConnection.Close()
    End Sub

The error come from this coding below. The problem happen when i choose the "id" to execute and check using local debug. The "id" suddenly changing automatically.
I don't cannot figure out how to solve this problem.

Private Sub GetItemDetails(ByVal id As String)
        Dim conString As String = (ConfigurationManager.ConnectionStrings.Item("ItemListing").ToString)
        Dim objConnection As New MySqlConnection(conString)
        objConnection.Open()
        Dim strsql As String = " SELECT i.fulldescription,i.productcode,i.pack_no,i.group_id," & _
                             " a.unitcost_by_ctn,a.unitcost_by_pcs,a.netcost_by_ctn,a.netcost_by_pcs," & _
                             " a.sp_by_ctn, a.sp_by_pcs, a.remarks" & _
                             " FROM itemdetail AS i, supplier AS s, account AS a" & _
                             " WHERE s.suppcode ='" & id & "'" & _
                             " AND a.suppcode = i.suppcode" & _
                             " AND i.isApprove LIKE 'Approve%'" & _
                             " AND a.productcode = i.productcode" & _
                             " GROUP BY i.suppcode"
        Dim objCommandGrid As New MySqlCommand(strsql, objConnection)
        GridItem.DataSource = objCommandGrid.ExecuteReader
        GridItem.DataBind()
        objConnection.Close()
        objConnection.Dispose()
    End Sub

i hope someone can help me to solve this problem.

  • 3 Contributors
  • 2 Replies
  • 195 Views
  • 14 Hours Discussion Span
  • Latest Post Latest Post by CrappyCoder

Dani AI

Generated

Two things jump out immediately. As pointed out, the second query never joins supplier (s) to itemdetail (i), so the WHERE clause s.suppcode = 'id' can return rows that are not actually linked to the intended item rows. Also, using GROUP BY with many non-aggregated columns lets MySQL return nondeterministic values for those columns — that can make results look like the “id changed” when the engine picks any matching row. Both issues commonly produce surprising data, not a true runtime mutation of the id variable.

Other likely contributors are shadowing and lifetime issues. A parameter or field named id at class scope, or a control with a similar name, can be accidentally overwritten; binding an open DataReader directly to the grid without managing the connection/reader lifetime can hide the real value at debug time; and string-concatenated SQL is fragile (embedded quotes) and insecure.

Recommended checks and fixes (quick list):

  • Rename the method parameter to suppCode (or similar) to avoid shadowing.
  • Add a Trace/Debug.WriteLine at method entry and set a breakpoint to confirm the value on every call.
  • Replace implicit comma joins with explicit INNER JOIN ... ON clauses and remove or fix the GROUP BY (use aggregates or ORDER BY to get deterministic rows).
  • Use parameterized queries and Using-blocks (auto-dispose) or fill a DataTable via MySqlDataAdapter before binding the grid.

Example pattern (safe, deterministic binding):

Using conn As New MySqlConnection(conString)
  conn.Open()
  Dim sql As String = "
    SELECT i.fulldescription, i.productcode, a.unitcost_by_ctn
    FROM itemdetail i
    INNER JOIN account a ON a.productcode = i.productcode
    INNER JOIN supplier s ON s.suppcode = i.suppcode
    WHERE s.suppcode = @suppcode AND i.isApprove LIKE @approve
    ORDER BY i.productcode"
  Using cmd As New MySqlCommand(sql, conn)
    cmd.Parameters.AddWithValue("@suppcode", suppCode)
    cmd.Parameters.AddWithValue("@approve", "Approve%")
    Dim dt As New DataTable()
    Using da As New MySqlDataAdapter(cmd)
      da.Fill(dt)
    End Using
    GridItem.DataSource = dt
    GridItem.DataBind()
  End Using
End Using

Additional context (calling code, event handlers) may be required to track an unexpected overwrite, but fixing the joins, removing unsafe concatenation, and using deterministic result selection will resolve the most common causes. ’s suggestion to post complete code remains relevant if the problem persists.

Recommended Answers

All 2 Replies

Member Avatar for kvprajapati

>The "id" suddenly changing automatically.

Its difficult to imagine what's happening. Please post complete code.

Member Avatar for CrappyCoder

Your s.suppcode does not join with either a.suppcode or i.suppcode as it does in your previous query

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.