Empty dataset

[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 doesn't show any records! I have tested the rowcount on the table and it is coming back as zero. The problem is I can't 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

[stustarz]

Could it be that your specifying the value of some of your parameters as "NULL", from my understanding this is actually going to send the String value "NULL" rather than a null value

[Nerseus]

stustarz is right, I would bet. If you want a true NULL passed to a stored proc, you use System.DBNull.Value. For example:

cmd.Parameters("@suburbtown").Value = System.DBNull.Value

 

-ner