darenkov
Member
Hi I have been trying to populate a dataset (that binds to a datalist) via a stored procedure. The stored procedure works well on its own, but when I run my .net code to display the records the page comes up empty. It loads but doesnt show any records! I have tested the rowcount on the table and it is coming back as zero. The problem is I cant see anything in my code which looks out of place. I need a fresh pair of eyes to look over it. Anyone got any ideas? I have pasted the logic for the Sproc and the ASP.NET below:
ASP.NET:
Dim MyConnection As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Dim cmd As New SqlCommand("uspSearch", MyConnection)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@suburbtown", SqlDbType.Varchar, 30)
cmd.Parameters("@suburbtown").Direction = ParameterDirection.Input
cmd.Parameters("@suburbtown").Value = "NULL"
cmd.Parameters.Add("@accountant_type", SqlDbType.Int)
cmd.Parameters("@accountant_type").Direction = ParameterDirection.Input
cmd.Parameters("@accountant_type").Value = 1
cmd.Parameters.Add("@keyword", SqlDbType.Varchar, 25)
cmd.Parameters("@keyword").Direction = ParameterDirection.Input
cmd.Parameters("@keyword").Value = "NULL"
cmd.Parameters.Add("@state", SqlDbType.Varchar, 3)
cmd.Parameters("@state").Direction = ParameterDirection.Input
cmd.Parameters("@state").Value = "NULL"
cmd.Parameters.Add("@PageSize", SqlDbType.Int)
cmd.Parameters("@PageSize").Direction = ParameterDirection.Input
cmd.Parameters("@PageSize").Value = 10
cmd.Parameters.Add("@CurrentPage", SqlDbType.Int)
cmd.Parameters("@CurrentPage").Direction = ParameterDirection.Input
cmd.Parameters("@CurrentPage").Value = 1
cmd.Parameters.Add("@FullCount", SqlDbType.Int)
cmd.Parameters("@FullCount").Direction = ParameterDirection.Output
Dim adapter As New SqlDataAdapter(cmd)
Dim ds As New DataSet()
adapter.Fill(ds)
ListResults.DataSource = ds.Tables(0).DefaultView
ListResults.DataBind()
---------------------------
SPROC (Works fine)
----------------------------
CREATE PROCEDURE uspSearch(
@suburbtown varchar(30) = NULL,
@accountant_type int = NULL,
@keyword varchar(25) = NULL,
@state varchar(3) = NULL,
@PageSize INT,
@CurrentPage INT,
@FullCount int OUTPUT
)
AS
BEGIN
select identity(int, 1, 1) as ID, *
INTO #Temp
from
(
SELECT DISTINCT B.business_id, B.rank, B.business_name, B.suburb_town,
B.postcode, B.phone, B.fax, B.status, BF.description
FROM BUSINESS B
LEFT JOIN BUSINESSFULL BF ON B.business_id = BF.business_id
LEFT JOIN SERVICESOFFERED SO on B.business_id = SO.business_id
LEFT JOIN SERVICES S on S.service_id = SO.service_id
WHERE @accountant_type in(15,s.service_id)
AND (BF.description like @keyword + % OR @keyword is NULL)
AND (B.suburb_town = @suburbtown OR B.postcode = @suburbtown OR @suburbtown is NULL)
AND (B.state = @state OR @state is NULL)
AND (B.status >=3 )
) t1
ORDER BY t1.RANK ASC
SELECT @FullCount = count(*)
FROM #Temp
SELECT @FullCount = case when @FullCount % @PageSize = 0 then @FullCount/@PageSize
ELSE @FullCount/@PageSize + 1
END
SET rowcount @PageSize
SELECT *
FROM #temp
WHERE ID>@PageSize*(@CurrentPage-1)
SET rowcount 0
END
GO
ASP.NET:
Dim MyConnection As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Dim cmd As New SqlCommand("uspSearch", MyConnection)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@suburbtown", SqlDbType.Varchar, 30)
cmd.Parameters("@suburbtown").Direction = ParameterDirection.Input
cmd.Parameters("@suburbtown").Value = "NULL"
cmd.Parameters.Add("@accountant_type", SqlDbType.Int)
cmd.Parameters("@accountant_type").Direction = ParameterDirection.Input
cmd.Parameters("@accountant_type").Value = 1
cmd.Parameters.Add("@keyword", SqlDbType.Varchar, 25)
cmd.Parameters("@keyword").Direction = ParameterDirection.Input
cmd.Parameters("@keyword").Value = "NULL"
cmd.Parameters.Add("@state", SqlDbType.Varchar, 3)
cmd.Parameters("@state").Direction = ParameterDirection.Input
cmd.Parameters("@state").Value = "NULL"
cmd.Parameters.Add("@PageSize", SqlDbType.Int)
cmd.Parameters("@PageSize").Direction = ParameterDirection.Input
cmd.Parameters("@PageSize").Value = 10
cmd.Parameters.Add("@CurrentPage", SqlDbType.Int)
cmd.Parameters("@CurrentPage").Direction = ParameterDirection.Input
cmd.Parameters("@CurrentPage").Value = 1
cmd.Parameters.Add("@FullCount", SqlDbType.Int)
cmd.Parameters("@FullCount").Direction = ParameterDirection.Output
Dim adapter As New SqlDataAdapter(cmd)
Dim ds As New DataSet()
adapter.Fill(ds)
ListResults.DataSource = ds.Tables(0).DefaultView
ListResults.DataBind()
---------------------------
SPROC (Works fine)
----------------------------
CREATE PROCEDURE uspSearch(
@suburbtown varchar(30) = NULL,
@accountant_type int = NULL,
@keyword varchar(25) = NULL,
@state varchar(3) = NULL,
@PageSize INT,
@CurrentPage INT,
@FullCount int OUTPUT
)
AS
BEGIN
select identity(int, 1, 1) as ID, *
INTO #Temp
from
(
SELECT DISTINCT B.business_id, B.rank, B.business_name, B.suburb_town,
B.postcode, B.phone, B.fax, B.status, BF.description
FROM BUSINESS B
LEFT JOIN BUSINESSFULL BF ON B.business_id = BF.business_id
LEFT JOIN SERVICESOFFERED SO on B.business_id = SO.business_id
LEFT JOIN SERVICES S on S.service_id = SO.service_id
WHERE @accountant_type in(15,s.service_id)
AND (BF.description like @keyword + % OR @keyword is NULL)
AND (B.suburb_town = @suburbtown OR B.postcode = @suburbtown OR @suburbtown is NULL)
AND (B.state = @state OR @state is NULL)
AND (B.status >=3 )
) t1
ORDER BY t1.RANK ASC
SELECT @FullCount = count(*)
FROM #Temp
SELECT @FullCount = case when @FullCount % @PageSize = 0 then @FullCount/@PageSize
ELSE @FullCount/@PageSize + 1
END
SET rowcount @PageSize
SELECT *
FROM #temp
WHERE ID>@PageSize*(@CurrentPage-1)
SET rowcount 0
END
GO
