Help & Advise (Effective way to execute query on selected Radio Button & Checkbox)

[derozza]

Help & Advise (Effective way to execute query on selected Radio Button & Checkbox)

Hi All. I'm New here.

I need assistant from you all. I have situation here. I have 8 Radio Button & 3 Check box. Each radio button and check box has combination of mySQL query.

The question is, how do i make effective way to write code without putting a ton of code into? currently this is my code example:

Dim conString As String = (ConfigurationManager.ConnectionStrings.Item("ItemListing").ToString)
       
 Dim objConnection As New MySqlConnection(conString)
 Dim sSQL As String

If RBFE.Checked And RBA.Checked Then
sSQL = " SELECT i.masterefno,i.fulldescription,i.productcode,i.invoicerefno,a.suppcode, o.cloccode, i.approval_date,i.isapprove" & _
" FROM itemdetail i, account a, itemdetail_outletlist io,outlet o" & _
" WHERE CONCAT(SUBSTR(i.approval_date,7,4),'/',SUBSTR(i.approval_date,4,2),'/',SUBSTR(i.approval_date,1,2)) BETWEEN '" & txtbod.Text & "' AND '" & txtDL.Text & "'" & _
" AND io.pid = i.pid" & _
" AND i.isapprove LIKE '%Approve%'" & _
" AND io.outletcode = o.moutletcode " & _
" AND a.suppcode = i.suppcode " & _
" GROUP BY io.outletcode,i.productcode ASC"

ElseIf RBFE.Checked And RBPA.Checked Then
sSQL = " SELECT i.masterefno,i.fulldescription,i.productcode,i.invoicerefno,a.suppcode, o.cloccode, i.approval_date,i.isapprove" & _
" FROM itemdetail i, account a, itemdetail_outletlist io,outlet o" & _
" WHERE CONCAT(SUBSTR(i.approval_date,7,4),'/',SUBSTR(i.approval_date,4,2),'/',SUBSTR(i.approval_date,1,2)) BETWEEN '" & txtbod.Text & "' AND '" & txtDL.Text & "'" & _
" AND io.pid = i.pid" & _
" AND i.isapprove LIKE '%Pending Approval%'" & _
" AND io.outletcode = o.moutletcode " & _
" AND a.suppcode = i.suppcode " & _
" GROUP BY io.outletcode,i.productcode ASC"
            
 ElseIf RBDay.Checked And RBA.Checked Then
sSQL = " SELECT i.masterefno,i.fulldescription,i.productcode,i.invoicerefno,a.suppcode, o.cloccode, i.approval_date,i.isapprove" & _
" FROM itemdetail i, account a, itemdetail_outletlist io,outlet o" & _
" WHERE i.approval_date = '" & txtDLl.Text & "'" & _
" AND io.pid = i.pid" & _
" AND i.isapprove LIKE '%Approve%'" & _
" AND io.outletcode = o.moutletcode " & _
" AND a.suppcode = i.suppcode " & _
" GROUP BY io.outletcode,i.productcode ASC"
         
ElseIf RBDay.Checked And RBPA.Checked Then
sSQL = " SELECT i.masterefno,i.fulldescription,i.productcode,i.invoicerefno,a.suppcode, o.cloccode, i.approval_date,i.isapprove" & _
" FROM itemdetail i, account a, itemdetail_outletlist io,outlet o" & _
" WHERE i.approval_date = '" & txtDLl.Text & "'" & _
" AND io.pid = i.pid" & _
" AND i.isapprove LIKE '%Pending Approval%'" & _
" AND io.outletcode = o.moutletcode " & _
" AND a.suppcode = i.suppcode " & _
" GROUP BY io.outletcode,i.productcode ASC"

ElseIf RBDes.Checked And RBA.Checked Then
sSQL = " SELECT i.masterefno,i.fulldescription,i.productcode,i.invoicerefno,a.suppcode, o.cloccode, i.approval_date,i.isapprove" & _
" FROM itemdetail i, account a, itemdetail_outletlist io,outlet o" & _
" WHERE i.fullDescription LIKE  '%" & TxtDescription.Text & "%'" & _
" AND io.pid = i.pid" & _
" AND i.isapprove LIKE '%Approve%'" & _
" AND io.outletcode = o.moutletcode " & _
" AND a.suppcode = i.suppcode " & _
" GROUP BY io.outletcode,i.productcode ASC"
          
ElseIf RBDes.Checked And RBPA.Checked Then
sSQL = " SELECT i.masterefno,i.fulldescription,i.productcode,i.invoicerefno,a.suppcode, o.cloccode, i.approval_date,i.isapprove" & _
" FROM itemdetail i, account a, itemdetail_outletlist io,outlet o" & _
" WHERE i.fullDescription LIKE  '%" & TxtDescription.Text & "%'" & _
" AND io.pid = i.pid" & _
" AND i.isapprove LIKE '%Pending Approval%'" & _
" AND io.outletcode = o.moutletcode " & _
" AND a.suppcode = i.suppcode " & _
" GROUP BY io.outletcode,i.productcode ASC"
            
ElseIf RBMas.Checked And RBA.Checked Then
sSQL = " SELECT i.masterefno,i.fulldescription,i.productcode,i.invoicerefno,a.suppcode, o.cloccode, i.approval_date,i.isapprove" & _
" FROM itemdetail i, account a, itemdetail_outletlist io,outlet o" & _
" WHERE i.masterefno = '" & txtMRef.Text & "'" & _
" AND io.pid = i.pid" & _
" AND i.isapprove LIKE '%Approve%'" & _
" AND io.outletcode = o.moutletcode " & _
" AND a.suppcode = i.suppcode " & _
 " GROUP BY io.outletcode,i.productcode ASC"
            
ElseIf RBMas.Checked And RBPA.Checked Then
sSQL = " SELECT i.masterefno,i.fulldescription,i.productcode,i.invoicerefno,a.suppcode, o.cloccode, i.approval_date,i.isapprove" & _
" FROM itemdetail i, account a, itemdetail_outletlist io,outlet o" & _
" WHERE i.fullDescription = '" & txtMRef.Text & "'" & _
" AND io.pid = i.pid" & _
" AND i.isapprove LIKE '%Pending Approval%'" & _
" AND io.outletcode = o.moutletcode " & _
" AND a.suppcode = i.suppcode " & _
" GROUP BY io.outletcode,i.productcode ASC"

ElseIf RBMonth.Checked And RBA.Checked Then
sSQL = " SELECT i.masterefno,i.fulldescription,i.productcode,i.invoicerefno,a.suppcode, o.cloccode, i.approval_date,i.isapprove" & _
" FROM itemdetail i, account a, itemdetail_outletlist io,outlet o" & _
" WHERE i.approval_date LIKE  '%" & txtDDL.Text & "%'" & _
" AND io.pid = i.pid" & _
" AND i.isapprove LIKE '%Approve%'" & _
" AND io.outletcode = o.moutletcode " & _
" AND a.suppcode = i.suppcode " & _
" GROUP BY io.outletcode,i.productcode ASC"
           
ElseIf RBMonth.Checked And RBPA.Checked Then
sSQL = " SELECT i.masterefno,i.fulldescription,i.productcode,i.invoicerefno,a.suppcode, o.cloccode, i.approval_date,i.isapprove" & _
" FROM itemdetail i, account a, itemdetail_outletlist io,outlet o" & _
" WHERE i.Approval_date LIKE  '%" & txtDDL.Text & "%'" & _
" AND io.pid = i.pid" & _
" AND i.isapprove LIKE '%Pending Approval%'" & _
" AND io.outletcode = o.moutletcode " & _
" AND a.suppcode = i.suppcode " & _
" GROUP BY io.outletcode,i.productcode ASC"
 End If
        objConnection.Open()
        Dim objCommandGrid As New MySqlCommand(sSQL, objConnection)
        Dim da As New MySqlDataAdapter(sSQL, objConnection)
        Dim dt As New DataSet
        da.Fill(dt, "data")
        Dim dv As New DataView
        dv = dt.Tables("data").DefaultView
        GridItem.DataSource = dv
        GridItem.DataBind()
        objConnection.Close()

this is my code & just 20% from actual code. Could anyone advise me how to make the code more effective and shorter then this...

Thanks
P/S: Urgent

Regards

 

[jmcilhinney]

You write one query with parameters. You can set the parameters directly using the CheckBoxes and use a bit of conditional code for the RadioButtons, e.g.

myCommand.CommandText = "SELECT * FROM MyTable WHERE Column1 = @Column1 AND Column2 = @Column2"

Dim column1 As String

If RadioButton1.Checked Then
    column1 = "Value1"
ElseIf RadioButton2.Checked Then
    column1 = "Value2"
End If

With myCommand.Parameters
    .AddWithValue("@Column1", column1)
    .AddWithValue("@Column2", CheckBox1.Checked)
End With

 

[derozza]

Thank u for your advise. The code will referring on 1 table. How about we the data come from 4 different table. i got no idea how to write it...

 

[jmcilhinney]

It's exactly the same, except you incorporate joins into your query, e.g.

SELECT Table1.Column1, Table2.Column2
FROM Table1 INNER JOIN Table2
ON Table1.PrimaryKeyColumn = Table2.ForeignKeyColumn

You can add whatever WHERE clause you like to that.