Fetch Data Between Two Dates in VB.NET from SQL Server by giving multiple parameter like @customerNo-CodePudding

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

and below 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. and by giving static value to store procedure the data shown in sql server but giving parameter from VB.NET code with parameters data disappear from Grid

Can anyone tell me what I am doing wrong here?

Thanks

CodePudding user response：

I Just tried the following Code

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

and my work done THANKS