Datagrid, SQL Adapter question

  • Thread starter Goober at christianDOTnet
  • Start date
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
 
E

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 said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,744
Messages
2,569,482
Members
44,901
Latest member
Noble71S45

Latest Threads

Top