Datagrid, SQL Adapter question

[Goober at christianDOTnet]

A friend of mine is doing some datagrids on a web page, and was using the
SQL data adapter on the design view of the page.

He is sorting the datagrid on each of the columns as a user clicks on the
column header. He showed me how he was creating the page, and we had a
question about the wizard that sets up the connection.

In it, he had to specify the sql server, and it gave him an option to use an
existing stored procedure. He picked a stored procedure out of the list he
was provided.

The stored procedure was a select query, and returned 3 fields. The
question though - is there a way to pass a parameter - like a userid - into
the stored procedure in setting up that wizard. Or, is there a way to call
it with a parameter and override what the code in the #region section of the
codebehind has in it?

The code follows....

Thanks,
SC

#Region " Web Form Designer Generated Code "

'This call is required by the Web Form Designer.
<System.Diagnostics.DebuggerStepThrough()> Private Sub
InitializeComponent()
Me.SqlDataAdapter1 = New System.Data.SqlClient.SqlDataAdapter
Me.SqlSelectCommand1 = New System.Data.SqlClient.SqlCommand
Me.SqlConnection1 = New System.Data.SqlClient.SqlConnection
Me.SqlSelectCommand2 = New System.Data.SqlClient.SqlCommand
Me.SqlInsertCommand1 = New System.Data.SqlClient.SqlCommand
Me.SqlUpdateCommand1 = New System.Data.SqlClient.SqlCommand
Me.SqlDeleteCommand1 = New System.Data.SqlClient.SqlCommand

'
'SqlDataAdapter1
'
Me.SqlDataAdapter1.SelectCommand = Me.SqlSelectCommand1
Me.SqlDataAdapter1.TableMappings.AddRange(New
System.Data.Common.DataTableMapping() {New
System.Data.Common.DataTableMapping("Table", "sp_WIP_Centerfire", New
System.Data.Common.DataColumnMapping() {New
System.Data.Common.DataColumnMapping("model", "model"), New
System.Data.Common.DataColumnMapping("ramac", "ramac"), New
System.Data.Common.DataColumnMapping("cal", "cal"), New
System.Data.Common.DataColumnMapping("serial", "serial"), New
System.Data.Common.DataColumnMapping("condition", "condition"), New
System.Data.Common.DataColumnMapping("price", "price"), New
System.Data.Common.DataColumnMapping("id", "id")})})
'
'SqlSelectCommand1
'
Me.SqlSelectCommand1.CommandText = "[sp_WIP_Centerfire]"
Me.SqlSelectCommand1.CommandType =
System.Data.CommandType.StoredProcedure
Me.SqlSelectCommand1.Connection = Me.SqlConnection1
Me.SqlSelectCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@RETURN_VALUE" ,
System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue,
False, CType(0, Byte), CType(0, Byte), "",
System.Data.DataRowVersion.Current, Nothing))
'
'SqlConnection1
'
Me.SqlConnection1.ConnectionString = "data source=mysqlserver;
database=WIP; User ID=login-pwd; Password=login-pwd; Persist" & _
" Security Info=True;packet size=4096"
'
End Sub


Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
Dim ds As New DataSet
SqlDataAdapter1.Fill(ds, "tblWIP")
DataGrid1.DataSource = ds
DataGrid1.DataMember = "tblWIP"
DataGrid1.DataBind()
Session("ds") = ds

End Sub

Private Sub DataGrid1_SortCommand(ByVal source As Object, ByVal e As
System.Web.UI.WebControls.DataGridSortCommandEvent Args) Handles
DataGrid1.SortCommand
Dim ds As DataSet = CType(Session("ds"), DataSet)
Dim dv As New DataView(ds.Tables("tblWIP"))

'Only have to specify Desc b/c default is to ASC
'ViewState is Case Sensitive
Dim desc As Boolean
If viewstate("desc") Is Nothing Then
desc = False
Else
desc = CBool(viewstate("desc"))
End If

dv.Sort = e.SortExpression

If desc = True Then
dv.Sort &= " DESC"
End If

viewState("desc") = Not desc

DataGrid1.DataSource = dv
DataGrid1.DataBind()
End Sub

[Eliyahu Goldin]

Add a parameter to SqlSelectCommand1 in Page_Load. The automatically
generated code already includes a statement for adding a parameter. Use it
as an example.

Eliyahu

"Goober at christianDOTnet" <[e-mail]> wrote in message
news:e1**************@TK2MSFTNGP09.phx.gbl...

A friend of mine is doing some datagrids on a web page, and was using the
SQL data adapter on the design view of the page.

He is sorting the datagrid on each of the columns as a user clicks on the
column header. He showed me how he was creating the page, and we had a
question about the wizard that sets up the connection.

In it, he had to specify the sql server, and it gave him an option to use an existing stored procedure. He picked a stored procedure out of the list he was provided.

The stored procedure was a select query, and returned 3 fields. The
question though - is there a way to pass a parameter - like a userid - into the stored procedure in setting up that wizard. Or, is there a way to call it with a parameter and override what the code in the #region section of the codebehind has in it?

The code follows....

Thanks,
SC

#Region " Web Form Designer Generated Code "

'This call is required by the Web Form Designer.
<System.Diagnostics.DebuggerStepThrough()> Private Sub
InitializeComponent()
Me.SqlDataAdapter1 = New System.Data.SqlClient.SqlDataAdapter
Me.SqlSelectCommand1 = New System.Data.SqlClient.SqlCommand
Me.SqlConnection1 = New System.Data.SqlClient.SqlConnection
Me.SqlSelectCommand2 = New System.Data.SqlClient.SqlCommand
Me.SqlInsertCommand1 = New System.Data.SqlClient.SqlCommand
Me.SqlUpdateCommand1 = New System.Data.SqlClient.SqlCommand
Me.SqlDeleteCommand1 = New System.Data.SqlClient.SqlCommand

'
'SqlDataAdapter1
'
Me.SqlDataAdapter1.SelectCommand = Me.SqlSelectCommand1
Me.SqlDataAdapter1.TableMappings.AddRange(New
System.Data.Common.DataTableMapping() {New
System.Data.Common.DataTableMapping("Table", "sp_WIP_Centerfire", New
System.Data.Common.DataColumnMapping() {New
System.Data.Common.DataColumnMapping("model", "model"), New
System.Data.Common.DataColumnMapping("ramac", "ramac"), New
System.Data.Common.DataColumnMapping("cal", "cal"), New
System.Data.Common.DataColumnMapping("serial", "serial"), New
System.Data.Common.DataColumnMapping("condition", "condition"), New
System.Data.Common.DataColumnMapping("price", "price"), New
System.Data.Common.DataColumnMapping("id", "id")})})
'
'SqlSelectCommand1
'
Me.SqlSelectCommand1.CommandText = "[sp_WIP_Centerfire]"
Me.SqlSelectCommand1.CommandType =
System.Data.CommandType.StoredProcedure
Me.SqlSelectCommand1.Connection = Me.SqlConnection1
Me.SqlSelectCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@RETURN_VALUE" ,
System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue,
False, CType(0, Byte), CType(0, Byte), "",
System.Data.DataRowVersion.Current, Nothing))
'
'SqlConnection1
'
Me.SqlConnection1.ConnectionString = "data source=mysqlserver;
database=WIP; User ID=login-pwd; Password=login-pwd; Persist" & _
" Security Info=True;packet size=4096"
'
End Sub


Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
Dim ds As New DataSet
SqlDataAdapter1.Fill(ds, "tblWIP")
DataGrid1.DataSource = ds
DataGrid1.DataMember = "tblWIP"
DataGrid1.DataBind()
Session("ds") = ds

End Sub

Private Sub DataGrid1_SortCommand(ByVal source As Object, ByVal e As
System.Web.UI.WebControls.DataGridSortCommandEvent Args) Handles
DataGrid1.SortCommand
Dim ds As DataSet = CType(Session("ds"), DataSet)
Dim dv As New DataView(ds.Tables("tblWIP"))

'Only have to specify Desc b/c default is to ASC
'ViewState is Case Sensitive
Dim desc As Boolean
If viewstate("desc") Is Nothing Then
desc = False
Else
desc = CBool(viewstate("desc"))
End If

dv.Sort = e.SortExpression

If desc = True Then
dv.Sort &= " DESC"
End If

viewState("desc") = Not desc

DataGrid1.DataSource = dv
DataGrid1.DataBind()
End Sub