G
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.DataGridSortCommandEventArgs) 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
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.DataGridSortCommandEventArgs) 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