Datagrid fails to fill

[Guest]

I have tried several different methods of getting a datagrid to fill with
information. Below is the code I'm now using. When viewed in the browser and
the text box filled with a parameter value (i.e. CA for Calif), the datagrids
header is displayed when the button is clicked, but no data is displayed.
I used SQL Profiler and can see that the query comes across with the
parameter, and if I take that query string and use it exactly in Query
Analyzer it executes and displays the data I expect. I just can't seem to get
the datagrid to display data.

What am I missing?

Public Class WebForm1
Inherits System.Web.UI.Page

Dim state As String

#Region " Web Form Designer Generated Code "

'This call is required by the Web Form Designer.
<System.Diagnostics.DebuggerStepThrough()> Private Sub
InitializeComponent()
Me.sqlConn = New System.Data.SqlClient.SqlConnection
Me.sqlDA1 = New System.Data.SqlClient.SqlDataAdapter
Me.SqlSelectCommand1 = New System.Data.SqlClient.SqlCommand
Me.Ds11 = New TestPubs.ds1
CType(Me.Ds11, System.ComponentModel.ISupportInitialize).BeginInit()
'
'sqlConn
'
Me.sqlConn.ConnectionString = "workstation id=DOTSW27182;packet
size=4096;user id=xxx;source=dotswapps;pe" & _
"rsist security info=True;initial catalog=pubs;password=xxx"
'
'sqlDA1
'
Me.sqlDA1.SelectCommand = Me.SqlSelectCommand1
Me.sqlDA1.TableMappings.AddRange(New
System.Data.Common.DataTableMapping() {New
System.Data.Common.DataTableMapping("Table", "authors", New
System.Data.Common.DataColumnMapping() {New
System.Data.Common.DataColumnMapping("au_id", "au_id"), New
System.Data.Common.DataColumnMapping("au_lname", "au_lname"), New
System.Data.Common.DataColumnMapping("au_fname", "au_fname"), New
System.Data.Common.DataColumnMapping("city", "city"), New
System.Data.Common.DataColumnMapping("state", "state")})})
'
'SqlSelectCommand1
'
Me.SqlSelectCommand1.CommandText = "SELECT au_id, au_lname,
au_fname, city, state FROM authors WHERE (state = @st)"
Me.SqlSelectCommand1.Connection = Me.sqlConn
Me.SqlSelectCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@st", System.Data.SqlDbType.VarChar, 2))
'
'Ds11
'
Me.Ds11.DataSetName = "ds1"
Me.Ds11.Locale = New System.Globalization.CultureInfo("en-US")
CType(Me.Ds11, System.ComponentModel.ISupportInitialize).EndInit()

End Sub
Protected WithEvents tbState As System.Web.UI.WebControls.TextBox
Protected WithEvents btnSubmit As System.Web.UI.WebControls.Button
Protected WithEvents sqlConn As System.Data.SqlClient.SqlConnection
Protected WithEvents sqlDA1 As System.Data.SqlClient.SqlDataAdapter
Protected WithEvents SqlSelectCommand1 As System.Data.SqlClient.SqlCommand
Protected WithEvents Ds11 As TestPubs.ds1
Protected WithEvents dg1 As System.Web.UI.WebControls.DataGrid
Protected WithEvents RequiredFieldValidator1 As
System.Web.UI.WebControls.RequiredFieldValidator
Protected WithEvents ValidationSummary1 As
System.Web.UI.WebControls.ValidationSummary


'NOTE: The following placeholder declaration is required by the Web Form
Designer.
'Do not delete or move it.
Private designerPlaceholderDeclaration As System.Object


Private Sub Page_Init(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
InitializeComponent()
End Sub

#End Region

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here


End Sub


Private Sub btnSubmit_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnSubmit.Click
Try
Me.sqlConn.Open()
sqlDA1.SelectCommand.Parameters("@st").Value = tbState.Text
DataBind()
dg1.DataBind()
sqlDA1.Fill(Ds11, "Customers")
Me.sqlConn.Close()
' End If
' )
Catch
'ex As Exception
'Console.WriteLine = ex.Message.ToString

End Try
'Me.SqlSelectCommand1.Parameters("@St").Value = 'CA'
'Me.SqlSelectCommand1.ExecuteReader()
'Me.sqlDA1.Fill(Ds11)
'Me.sqlConn.Close()

End Sub
End Class

 

[Guest]

where is the DataSource set??
try this
dg1.DataSource = Ds11

 

[Elton Wang]

To fill datagrid, there are following steps:

1. Create a new DataAdapter:
Although there are several different ways, simplest
way is:
Dim dap As New SqlDataAdapter(QueryString,
ConnectionString)

2. Fill data to a DataSet or DataTable:
Dim ds As New DataSet();
dap.Fill(ds)

3. Fill Datagrid with data
dg1.DataSource = ds
dg1.DataBind()

So check your steps to see if you missed any step.

Elton Wang

-----Original Message-----
I have tried several different methods of getting a datagrid to fill with
information. Below is the code I'm now using. When viewed in the browser and
the text box filled with a parameter value (i.e. CA for Calif), the datagrids
header is displayed when the button is clicked, but no data is displayed.
I used SQL Profiler and can see that the query comes across with the
parameter, and if I take that query string and use it exactly in Query
Analyzer it executes and displays the data I expect. I just can't seem to get
the datagrid to display data.

What am I missing?

Public Class WebForm1
Inherits System.Web.UI.Page

Dim state As String

#Region " Web Form Designer Generated Code "

'This call is required by the Web Form Designer.
<System.Diagnostics.DebuggerStepThrough()> Private Sub
InitializeComponent()
Me.sqlConn = New System.Data.SqlClient.SqlConnection
Me.sqlDA1 = New System.Data.SqlClient.SqlDataAdapter
Me.SqlSelectCommand1 = New System.Data.SqlClient.SqlCommand
Me.Ds11 = New TestPubs.ds1
CType(Me.Ds11, System.ComponentModel.ISupportInitialize).BeginInit()
'
'sqlConn
'
Me.sqlConn.ConnectionString = "workstation id=DOTSW27182;packet
size=4096;user id=xxx;source=dotswapps;pe" & _
"rsist security info=True;initial catalog=pubs;password=xxx"
'
'sqlDA1
'
Me.sqlDA1.SelectCommand = Me.SqlSelectCommand1
Me.sqlDA1.TableMappings.AddRange(New
System.Data.Common.DataTableMapping() {New
System.Data.Common.DataTableMapping("Table", "authors", New
System.Data.Common.DataColumnMapping() {New
System.Data.Common.DataColumnMapping("au_id", "au_id"), New
System.Data.Common.DataColumnMapping ("au_lname", "au_lname"), New
System.Data.Common.DataColumnMapping ("au_fname", "au_fname"), New
System.Data.Common.DataColumnMapping("city", "city"), New
System.Data.Common.DataColumnMapping("state", "state")})})
'
'SqlSelectCommand1
'
Me.SqlSelectCommand1.CommandText = "SELECT au_id, au_lname,
au_fname, city, state FROM authors WHERE (state = @st)"
Me.SqlSelectCommand1.Connection = Me.sqlConn
Me.SqlSelectCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@st",

System.Data.SqlDbType.VarChar, 2))

 

[Guest]

Vinay:

I tried both your suggestion and "dg1.DataSource = Ds11.Tables("authors")

and still get the same thing. The datagrid headers and no data.

Jeff

 

[Guest]

Vinay:

Thanks, got it now.

where is the DataSource set??
try this
dg1.DataSource = Ds11

 

[Guest]

Vinay:

I now see why I didn't add the line you have listed below. This information
is entered into the properties of the datagrid on the WebForm1.aspx page.
There is a place for DataSource, DataMember, and DataKeyField. I had this
same information there. Why wasn't it being used.

I went back and tested, and took these parameters out of the properties and
my datagrid in the IDE goes back to a default datagrid with column names 0,
1, and 2, but when I view the page in the browser, it works and displays
properly.

Do you know why, or can some tell me why?

Thanks again.

 

[Guest]

Pls check the order


sqlDA1.Fill(Ds11, "Customers")
dg1.DataSource = Ds11
dg1.DataBind()