Sub SubmitButton_Click(ByVal Sender As Object, ByVal E As EventArgs)
DateSelected.Text = DaintyDate1.Text
If DateSelected.Text = "" Or ItemsName.Text = "" Or ItemsSelect.SelectedValue = "" Then
Alert2.ForeColor = Drawing.Color.Red
Alert2.Text =
"Please Select Date From Calendar And Fill All Fields Before Search Existing Transaction."
Else
returnv.Text = ""
Dim myConnection As SqlConnectionmyConnection = New SqlConnection("Data Source=xx.xx.xx.xx;Initial Catalog=Ds;User ID=sa;Password=XXXX")
myConnection.Open()
Dim CMD As SqlCommand = New SqlCommand("usp_RetrieveData", myConnection)
CMD.CommandType = CommandType.StoredProcedure
Dim param1 As SqlParameter = New SqlParameter()
param1.ParameterName =
"@SearchingCriteria"
param1.SqlDbType = SqlDbType.VarChar
param1.Direction = ParameterDirection.Input
param1.Value = ItemsSelect.Text
Dim param2 As SqlParameter = New SqlParameter()
param2.ParameterName =
"@SearchingColumn"
param2.SqlDbType = SqlDbType.VarChar
param2.Direction = ParameterDirection.Input
param2.Value = ItemsName.Text
Dim param3 As SqlParameter = New SqlParameter()
param3.ParameterName =
"@SearchingDate"
param3.SqlDbType = SqlDbType.VarChar
param3.Direction = ParameterDirection.Input
param3.Value = DateSelected.Text
Dim paramReturnValue As SqlParameter = New SqlParameter()
paramReturnValue.ParameterName =
"@return_value"
paramReturnValue.SqlDbType = SqlDbType.Int
paramReturnValue.Direction = ParameterDirection.ReturnValue
CMD.Parameters.Add(param1)
CMD.Parameters.Add(param2)
CMD.Parameters.Add(param3)
CMD.Parameters.Add(paramReturnValue)
Dim dr As SqlDataReader = CMD.ExecuteReader()
Dim i As Integer = 1While dr.Read()
returnv.Text = dr(2).ToString
End While
dr.Close()
myConnection.Close()
If returnv.Text = "" Then
Alert2.ForeColor = Drawing.Color.Red
Alert2.Text =
"Message: No Existing Result Dated on " + DateSelected.Text + " Found! Searching is Terminated."
Else
Alert2.ForeColor = Drawing.Color.Green
Alert2.Text =
"Search Result Dated on " + DateSelected.Text + ": "
Reset()
End If
End If
DsDataSource.DataBind()
End Sub
and my stored procedure is
ALTER PROCEDURE usp_RetrieveData
@SearchingCriteria VARCHAR(10),
@SearchingColumn
VARCHAR(30),
@SearchingDate
VARCHAR(20)
AS
BEGIN
SET NOCOUNT ON;
IF @SearchingCriteria = 'TransNo'
BEGIN
IF EXISTS(SELECT * FROM Ds WHERE TransNo like @SearchingColumn + '%' and TransDate = @searchingdate)
BEGINSELECT * FROM Ds Where TransNo like @SearchingColumn + '%' and TransDate = @searchingdate
ORDER BY TransNo asc
END
ELSE
BEGIN
RETURN
END
END