How do i output a parameterized query on a GridView on a Visual Studio VB .NET Web Application?

Question

I'm trying to create a report form where it can perform a query search with user input parameters.

For the query i have 3 values in total two are mandatory and one is an optional. The mandatory values are H.Str_ID and H.Tran_ID. The optional value is H.Rgst_ID. All three values are taken from textboxes.

How do i declare those values to perform a parameterized query search on the GridView.

GridView Columns

<Columns>
<asp:BoundField DataField="Emp_ID" HeaderText="Emp_ID" SortExpression="Emp_ID" />
<asp:BoundField DataField="Cust_ID" HeaderText="Cust_ID" SortExpression="Cust_ID" />
<asp:BoundField DataField="Rgst_ID" HeaderText="Rgst_ID" SortExpression="Rgst_ID" />
<asp:BoundField DataField="TRAN_LN_NUM" HeaderText="TRAN_LN_NUM" SortExpression="TRAN_LN_NUM" />
<asp:BoundField DataField="DISC_CD" HeaderText="DISC_CD" SortExpression="DISC_CD" />
<asp:BoundField DataField="AUTH_EMP_ID" HeaderText="AUTH_EMP_ID" SortExpression="AUTH_EMP_ID" />
<asp:BoundField DataField="ORIG_PRC" HeaderText="ORIG_PRC" SortExpression="ORIG_PRC" />
<asp:BoundField DataField="DISC_AMT" HeaderText="DISC_AMT" SortExpression="DISC_AMT" />
<asp:BoundField DataField="DISC_PCT" HeaderText="DISC_PCT" SortExpression="DISC_PCT" />
<asp:BoundField DataField="GL_ACCT_ID" HeaderText="GL_ACCT_ID" SortExpression="GL_ACCT_ID" />
</Columns>

SQLDataSource Select Command

SelectCommand="SELECT H.Emp_ID, H.Cust_ID, H.Rgst_ID, D.TRAN_LN_NUM, D.DISC_CD, D.AUTH_EMP_ID, D.ORIG_PRC, D.DISC_AMT, D.DISC_PCT, D.GL_ACCT_ID 
                        FROM twOLTP.dbo.Transaction_Header AS 
                        H INNER JOIN twOLTP.dbo.LN_Detail AS L ON H.Str_ID = L.Str_ID AND H.Rgst_ID = L.Rgst_ID AND H.Tran_ID = L.Tran_ID 
                        INNER JOIN twOLTP.dbo.LN_Discount AS D ON L.Str_ID = D.Str_ID AND L.Rgst_ID = D.Rgst_ID AND L.Tran_ID = D.Tran_ID AND L.Tran_LN_Num = D.Tran_LN_Num
                        WHERE(H.Str_ID = @Str_ID)
                        And (H.Tran_ID = @Tran_ID)
                        And ((H.Rgst_ID = @Rgst_ID) Or (@Rgst_ID Is NULL Or @Rgst_ID = ''))">

Does this answer your question? [How to pass a variable to the SelectCommand of a SqlDataSource?](https://stackoverflow.com/questions/485821/how-to-pass-a-variable-to-the-selectcommand-of-a-sqldatasource) — Caius Jard, Sep 02 '20 at 19:28

Albert D. Kallal · Answer 1 · 2020-09-02T21:28:00.257

You can do this several ways. One is to build up the sql string on the fly for each text box, but THEN you have also code in a set of if/then for the parameters.

So, to tame this type of beast and problem? Write the sql query to test against BOTH the parameter and the actual condition you want.

So, write the query like this:

the 'bla bla first part, and then:

strSQL = += "WHERE " & 
   "( (@str_ID = '-1') or (H.Str_ID = @Str_ID) )" & 
   "AND ( (@tRAN_ID = '-1') OR  (H.Tran_ID = @Tran_ID) )" &
   "AND ( (@rGST_id = '-1') OR (H.Rgst_ID = @Rgst_ID) )"

So note how we check against the @parm value and NOT a table column.

so, lets assume the first 2 are optional for this example.

You thus now have:

Dim rstData As New DataTable
Dim strSQL As String = our above sql string
Dim cmdSQL As New SqlCommand(strSQL, New SqlConnection(My.Settings.Test3))
With cmdSQL.Parameters
  .Add("@Str_ID", SqlDbType.NVarChar).Value = IIf(txtStrbox.Text = "", "-1", txtStrbox.Text)
  .Add("@Tran_ID", SqlDbType.NVarChar).Value = IIf(txtTransID.Text = "", "-1", txtTransID.Text)
  .Add("@Hrgst_ID", SqlDbType.NVarChar).Value = IIf(txtgST.Text = "", "-1", txtGst.Text)

End With

Dim da As New SqlDataAdapter(cmdSQL)
da.Fill(rstData)
Me.GridView1.DataSource = rstData
Me.GridView1.DataBind()

Of course, if the data type is integer etc., then change the above, and use -1 as opposed to "-1".

The above thus saves a LOT of if then, and gets you off the hook having to build the sql string up.

The only down side is that you could in some cases have data that matches your "catch" all when in fact you don't want a match. But, I can't say this has been a problem for my use cases.

In your case then you don't need the first two iif() as per above. So, less code then even the above.

score 0 · Answer 2 · answered Sep 02 '20 at 22:28

Since the fist 2 variables are required then the first 2 in where clause is fine:

  WHERE(H.Str_ID = @Str_ID)
  And (H.Tran_ID = @Tran_ID)

however 3rd is optional so maybe you can check its length then build a sql string specific for that parameter. See below but excuse my sntax as I am freely writing this without syntax checker

    //string to store sql for parm
   string sqlRgst = "";
   
 //since there is a parm value construct sql string for parm
 if(@Hrgst_ID.Trim().Length > 0)
 {
    sqlRgst = " And ((H.Rgst_ID = " + @Rgst_ID + ") Or ( " + @Rgst_ID + " Is NULL Or " + @Rgst_ID + " = '') "
 }
 //now set your sql and append your sql parm variable you constructed to it 
 SelectCommand="SELECT H.Emp_ID, H.Cust_ID, H.Rgst_ID, D.TRAN_LN_NUM, D.DISC_CD, D.AUTH_EMP_ID, D.ORIG_PRC, D.DISC_AMT, D.DISC_PCT, D.GL_ACCT_ID 
                    FROM twOLTP.dbo.Transaction_Header AS 
                    H INNER JOIN twOLTP.dbo.LN_Detail AS L ON H.Str_ID = L.Str_ID AND H.Rgst_ID = L.Rgst_ID AND H.Tran_ID = L.Tran_ID 
                    INNER JOIN twOLTP.dbo.LN_Discount AS D ON L.Str_ID = D.Str_ID AND L.Rgst_ID = D.Rgst_ID AND L.Tran_ID = D.Tran_ID AND L.Tran_LN_Num = D.Tran_LN_Num
                    WHERE(H.Str_ID = @Str_ID)
                    And (H.Tran_ID = @Tran_ID) " + sqlRgst

How do i output a parameterized query on a GridView on a Visual Studio VB .NET Web Application?

2 Answers2