asp.netsql-servervb.netstored-procedures

Fetch data between two dates in VB.NET from SQL Server by passing multiple parameter like @customerNo, @registraionNo


Here is my VB.NET code:

Public Shared Function SearchRoboCallByDate(datefrom As String, dateto As String, customerNo As String, regNo As String, terminalNo As String) As DataSet
        Dim conn As New SqlConnection
        Dim cmd As New SqlCommand
        Dim adapter As New SqlDataAdapter
        Dim ds As New Data.DataSet

        conn = New SqlConnection(ConfigurationManager.ConnectionStrings("SQLConnString").ConnectionString)


        'conn.ConnectionString = ConfigurationManager.ConnectionStrings("SQLConnString").ConnectionString

        adapter.SelectCommand = cmd
        ds.DataSetName = "DataSet"
        cmd.CommandText = "sp_getRoboAlarmDetailByDate"
        cmd.CommandTimeout = 600
        cmd.Connection = conn
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.AddWithValue("@datefrom", datefrom)
        cmd.Parameters.AddWithValue("@dateto", dateto)
        cmd.Parameters.AddWithValue("@customerNo", customerNo)
        cmd.Parameters.AddWithValue("@regNo", regNo)
        cmd.Parameters.AddWithValue("@TerminalNo", terminalNo)
        Try
            adapter.Fill(ds)
        Catch ex As Exception
            ds = Nothing
        End Try

        Return ds
    End Function

This is my stored procedure:

ALTER PROCEDURE sp_getRoboAlarmDetailByDate
    @datefrom datetime = NULL,
    @dateto datetime = NULL,
    @customerNo as nvarchar(60) = NULL,
    @regNo as nvarchar(60) = NULL,
    @TerminalNo as nvarchar(60) = NULL
AS
BEGIN
    IF (@regNo = '0')
    BEGIN 
        SET @regNo = NULL
    END 

    IF (@customerNo = '0')
    BEGIN
        SET @customerNo = NULL
    END

    IF (@TerminalNo = '0')
    BEGIN
        SET @TerminalNo = NULL
    END

    SELECT
        ID = 0,  
        rda.RCLogID, 
        rda.VehicleID,
        cv.RegistrationNo, 
        rda.TerminalNo, 
        rda.PollID,
        rda.PacketNo, 
        rda.TableNo,
        rda.ServerID,
        rda.ServerName,
        rda.isCallSuccessful,
        rda.Remarks,
        rda.CreatedOn, 
        c.CustomerID, 
        c.CustomerNo
    FROM
        RoboCalls_DefuseAlarms_Log rda
    LEFT JOIN
        CustomersVehicles cv ON cv.VehicleId = rda.VehicleID 
    LEFT JOIN
        Customers c ON c.CustomerID = cv.CustomerId
    WHERE
        rda.CreatedOn BETWEEN @datefrom AND @dateto 
        AND c.CustomerNo = ISNULL(@customerNo, c.CustomerNo)  
        AND RegistrationNo = ISNULL(@regNo, cv.RegistrationNo) 
        AND rda.TerminalNo = ISNULL(@TerminalNo, rda.TerminalNo)
END

When I select from and to date the data shows in a grid view but when I select another parameter like @customerNo, or, @regNo the data grid shows an error:

Data Has Not Been Found

and in VB Code no exception thrown.

By passing static value to the stored procedure, the data shown in SQL Server, but passing in a parameter value from VB.NET code, the data disappears from the grid.

Can anyone tell me what I am doing wrong here?

Thanks


Solution

  • I Just tried the following Code

     cmd.CommandText = "Exec sp_GetRoboCallAlarmNotChecked '" & dtStart & "' ,'" & dtEnd & "' , '" & customerNo & "' , '" & regNo & "' ,'" & terminalNo & "', '" & notSuccessCheck & "'"
    

    and my work done THANKS