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
I Just tried the following Code
cmd.CommandText = "Exec sp_GetRoboCallAlarmNotChecked '" & dtStart & "' ,'" & dtEnd & "' , '" & customerNo & "' , '" & regNo & "' ,'" & terminalNo & "', '" & notSuccessCheck & "'"
and my work done THANKS