I'm working on a Report form where a user inputs multiple values on textboxes and I take those values and I perform parameterized query search.
My current issue is a report form that has 8 total parameters but has a minimum requirement of three values being: Store_ID, From_date and To_Date. What I want to do is to have a single query that performs a search with the given values. So that I wont have to create cases or if else statement for each possible combination of those 8 parameters.
I would appreciate if someone can provide an example of on how to do that.
For additional context is for a report form Web application build using Visual Studio .NET with Visual basic.
EDIT: I made the modifications to the query as suggested in the comments.
My current query works but only if all the values are provided. But if I don't add one of the optional query parameters I get the following error:
System.FormatException: 'Input string was not in a correct format.'
I suspect this error is because I didn't assign a value to a parameter.
Or that perhaps the AND H.Rgst_ID IS NULL statement might be causing a SQL error and doesn't perform the search.
How can I fix this error so that my query accepts blank/null inputs?
This is my current code:
SQLControl
Imports System.Data.SqlClient
Public Class SQLControl
Private ReadOnly ConStr As String = "String"
Private DBCmd As SqlCommand
'Query Parameters
Public Params As New List(Of SqlParameter)
'This generates a blank sqlclient class with the deafult connection string
Public Sub New()
End Sub
'Allow connection string override
Public Sub New(connectionString As String)
ConStr = connectionString
End Sub
'Execute Query Sub
Public Function ExecQuery(query As String) As DataTable
Dim DBDT = New DataTable
Using DBCon As New SqlConnection(ConStr),
DBCmd As New SqlCommand(query, DBCon)
Params.ForEach(Sub(p) DBCmd.Parameters.Add(p))
Params.Clear()
DBCon.Open()
DBDT.Load(DBCmd.ExecuteReader)
End Using
Return DBDT
End Function
'Add Params
Public Sub AddParam(Name As String, Value As Object)
Dim NewParam As New SqlParameter(Name, Value)
Params.Add(NewParam)
End Sub
End Class
Web Form code:
Protected Sub ExecuteButton_Click(sender As Object, e As EventArgs) Handles ExecuteButton.Click
Dim StoreID As Integer
Dim TransID As Integer
Dim RgstID As Integer
Dim FromDate As DateTime
Dim ToDate As DateTime
Dim DiscCD As Integer 'This is selected from the item list
Dim DiscPercent As Double
Dim DiscAmount As Double
'The minimum required search paraeters are Store_ID, From_Date and To_Date. The rest of the parameters are optional
'StoreID Validation
If Not Integer.TryParse(StoreIDTextbox.Text, StoreID) Then
MsgBox("Invalid input. Please enter Store ID.")
Exit Sub
End If
'FromDateValidation
If Not DateTime.TryParse(FromDateTextbox.Text, FromDate) Then
MsgBox("Invalid input. Please enter from date")
Exit Sub
End If
'ToDateValidation
If Not DateTime.TryParse(ToDateTextbox.Text, ToDate) Then
MsgBox("Invalid input. Please enter to date.")
Exit Sub
End If
Integer.Parse(RegisterIDTextbox.Text, RgstID)
Integer.Parse(TransactionIDTextbox.Text, TransID)
Integer.Parse(ListBox1.SelectedValue, DiscCD)
Double.Parse(DiscountPercentTextbox.Text, DiscPercent)
Double.Parse(DiscountAmtTextbox.Text, DiscAmount)
Dim pct As Double
pct = DiscPercent / 100
Dim amt As Double
amt = DiscAmount * -1
'Adds parameter to the query
SQL.AddParam("@Str_ID", StoreID)
SQL.AddParam("@FromDate", FromDate)
SQL.AddParam("@ToDate", ToDate)
SQL.AddParam("@Rgst_ID", RgstID)
SQL.AddParam("@Trans_ID", TransID)
SQL.AddParam("@DiscType", DiscCD)
SQL.AddParam("@DisPct", pct)
SQL.AddParam("@DisAmt", amt)
Dim dt As DataTable
Try
dt = SQL.ExecQuery("SELECT H.Str_ID, H.Rgst_ID, H.Tran_ID, L.Tran_LN_Num, H.Bus_DT, H.Emp_ID, H.Cust_ID, LD.Auth_Emp_ID, L.Ext_Prc, LD.Disc_Amt, D.Descr, LD.Disc_Pct, LD.DisC_CD
FROM twOLTP.dbo.Transaction_Header H
INNER JOIN twOLTP.dbo.LN_Detail 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 LD ON (L.Str_ID = LD.Str_ID AND L.Rgst_ID = LD.Rgst_ID AND L.Tran_ID = LD.Tran_ID AND L.Tran_Ln_Num = LD.Tran_Ln_Num)
LEFT JOIN twOLTP.dbo.Discount D ON (LD.Disc_CD = D.Disc_CD)
WHERE (H.Str_ID = @Str_ID)
AND (H.Bus_DT >= @FromDate)
AND (H.Bus_DT <= @ToDate)
AND (H.Rgst_ID IS NULL OR H.Rgst_ID = @Rgst_ID)
AND (H.Tran_ID IS NULL OR H.Tran_ID = @Trans_ID)
AND (LD.DisC_CD IS NULL OR LD.DisC_CD = @DiscType)
AND (LD.Disc_Pct IS NULL OR LD.Disc_Pct = @DisPct)
AND (LD.Disc_Amt IS NULL OR LD.Disc_Amt = @DisAmt) ")
Catch ex As Exception
MsgBox(ex.Message)
Exit Sub
End Try
GridView1.DataSource = dt
GridView1.DataBind()
EDIT I was able to solve the problem
For my query to accept blank/null inputs i had t do the following.
WHERE ((H.Rgst_ID = @Rgst_ID) Or (@Rgst_ID Is NULL Or @Rgst_ID = ''))
Thanks for the help
