Dataset not populating

[darenkov]

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

 

[Robby]

Sorry for the late reply;
Since the stored proc is defaulting the varchars to null values you dont need to pass any null value to it from your code. As a matter of fact you should not pass something like "NULL" on quotes because it is seen as a litteral string value of NULL and not actually Null or Nothing.