Datalist not displaying records from stored procedure

[Daren Kovacs via DotNetMonster.com]

Hi,

I have created and tested a stored procedure with some input values and one
output value. When I run it the page seems to come up but no data exists,
and no errors. This is odd because there if definitely info ont he
table/datbase. My code is below if anyone can see what I might be doing
wrong.

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 = ""

cmd.Parameters.Add("@accountant_type", SqlDbType.Int)
cmd.Parameters("@accountant_type").Direction =
ParameterDirection.Input
cmd.Parameters("@accountant_type").Value = 15

cmd.Parameters.Add("@keyword", SqlDbType.Varchar, 25)
cmd.Parameters("@keyword").Direction = ParameterDirection.Input
cmd.Parameters("@keyword").Value = ""

cmd.Parameters.Add("@state", SqlDbType.Varchar, 3)
cmd.Parameters("@state").Direction = ParameterDirection.Input
cmd.Parameters("@state").Value = "TAS"

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
ListResults.DataBind()

 

[Guest]

I'm assuming that you have formatted at the very least the row template for
the DataList control on your design page. If not, then it will always be
blank since there is no default implementation to simply display results from
a data table as there is in the datagrid and other non-template data
controls. (A little trick I use here to take advantage of the wizard
designers is to temporarly add a table to my dataset with the exact same
schema as the result set that will be returned by a stored procedure. You
can then set the controls datasource property to this table while you are
designing the layout of the control and then simply remove the table from the
dataset and set the datasource in code during runtime)

If you have done this, the next step will be to open and step through the
stored procedure in the VS.Net IDE and supply it with the exact same
parameters that you are using in the code (simply go to the stored procedure
on the server explorer page and double click it to open in editor) to insure
that it is indeed returning what you expect. Lastly, I would suggest a
couple of things. First, supply a table name as the second parameter to the
fill command. It can be any string, but by default, since a stored procedure
has no associated table name, the system will add it to your DataSet as
'TableX' with X depending on how many other default named tables have already
been added to the dataset, you are always better off explictedly naming your
objects rather then relying on the defaults (makes maintaining code so much
easier down the road). Second, supply the DataMember property of the
DataList with the table name to ensure that it is binding with the correct
table in the dataset.

 

[William \(Bill\) Vaughn]

Bind to the DataTable instead of the DataSet...
ListResults.DataSource=ds.Tables(0)


--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________