提问:
怎么获取数据表格行中的 ID号?
For Each row As GridViewRow In gvManagerList.Rows
Dim cb As CheckBox = row.FindControl( " cbSelect " )
If cb.Checked = True Then
' HELP! Anyone here know the command here to get the IDs from datatable ?
' GetID = how to GET the IDs column value of the database table
Label.Text = Label.Text + getID + " , "
End If
Dim cb As CheckBox = row.FindControl( " cbSelect " )
If cb.Checked = True Then
' HELP! Anyone here know the command here to get the IDs from datatable ?
' GetID = how to GET the IDs column value of the database table
Label.Text = Label.Text + getID + " , "
End If
解决方案:
使用分隔功能
IF
EXISTS
(
SELECT
*
FROM
INFORMATION_SCHEMA.ROUTINES
WHERE
SPECIFIC_NAME
=
N
'
Split
'
)
DROP FUNCTION Split
GO
CREATE FUNCTION dbo.Split ( @ItemList NVARCHAR ( 4000 ), @delimiter CHAR ( 1 ) )
RETURNS @IDTable TABLE (Item VARCHAR ( 50 ))
AS
BEGIN
DECLARE @tempItemList NVARCHAR ( 4000 ) SET @tempItemList = @ItemList
DECLARE @i INT DECLARE @Item NVARCHAR ( 4000 )
SET @tempItemList = REPLACE ( @tempItemList , ' ' , '' )
SET @i = CHARINDEX ( @delimiter , @tempItemList )
DROP FUNCTION Split
GO
CREATE FUNCTION dbo.Split ( @ItemList NVARCHAR ( 4000 ), @delimiter CHAR ( 1 ) )
RETURNS @IDTable TABLE (Item VARCHAR ( 50 ))
AS
BEGIN
DECLARE @tempItemList NVARCHAR ( 4000 ) SET @tempItemList = @ItemList
DECLARE @i INT DECLARE @Item NVARCHAR ( 4000 )
SET @tempItemList = REPLACE ( @tempItemList , ' ' , '' )
SET @i = CHARINDEX ( @delimiter , @tempItemList )
WHILE
(
LEN
(
@tempItemList
)
>
0
)
BEGIN
IF @i = 0 SET @Item = @tempItemList
ELSE SET @Item = LEFT ( @tempItemList , @i - 1 )
INSERT INTO @IDTable (Item) VALUES ( @Item )
IF @i = 0 SET @tempItemList = ''
ELSE SET @tempItemList = RIGHT ( @tempItemList , LEN ( @tempItemList ) - @i )
SET @i = CHARINDEX ( @delimiter , @tempItemList )
END RETURN
END GO
BEGIN
IF @i = 0 SET @Item = @tempItemList
ELSE SET @Item = LEFT ( @tempItemList , @i - 1 )
INSERT INTO @IDTable (Item) VALUES ( @Item )
IF @i = 0 SET @tempItemList = ''
ELSE SET @tempItemList = RIGHT ( @tempItemList , LEN ( @tempItemList ) - @i )
SET @i = CHARINDEX ( @delimiter , @tempItemList )
END RETURN
END GO
我建立了以下的存储过程来调用分隔功能.
CREATE
PROCEDURE
USP_RetrieveInformationForSelectedEmployees
@p_selectedEmployees
NVARCHAR
(
50
)
AS
BEGIN
SELECT
*
FROM
Employees
WHERE
EmployeeID
in
(
SELECT
Item
FROM
split(
@p_selectedEmployees
,
'
,
'
))
END
下面是ASP.NET应用程序:
1
:
<%
@ Page Language
=
"
C#
"
%>
2 : <% @ Import Namespace = " System.Data.SqlClient " %>
3 : <% @ Import Namespace = " System.Web.Configuration " %>
4 : <% @ Import Namespace = " System.Data " %>
5 :
6 : <! DOCTYPE html PUBLIC " -//W3C//DTD XHTML 1.0 Transitional//EN "
7 : " http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd " >
8 :
9 : < script runat = " server " >
10 :
11 : protected void Button1_Click( object sender, EventArgs e)
12 : ... {
13: RetrieveInformationForSelectedEmployees();
14: }
15 :
16 : private void RetrieveInformationForSelectedEmployees()
17 : ... {
18: StringBuilder sb = new StringBuilder();
19:
20: // First loop through the GridView and see which
21: // employees were selected. I use the StringBuilder
22: // since the list could be a very long list.
23: foreach (GridViewRow row in GridView1.Rows)
24: ...{
25: if (((CheckBox)row.FindControl("CheckBox1")).Checked)
26: ...{
27: sb.Append(GridView1.DataKeys[row.RowIndex].Value.ToString() + ',');
28: }
29: }
30:
31: using (SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings
32: ["NorthwindConnectionString"].ConnectionString))
33: ...{
34: using (SqlCommand cmd = new SqlCommand("USP_RetrieveInformationForSelectedEmployees", conn))
35: ...{
36: cmd.CommandType = CommandType.StoredProcedure;
37: cmd.Parameters.AddWithValue("@p_selectedEmployees", sb.ToString());
38:
39: DataSet ds = new DataSet();
40: SqlDataAdapter da = new SqlDataAdapter(cmd);
41: da.Fill(ds);
42:
43: GridViewResult.DataSource = ds;
44: GridViewResult.DataBind();
45: }
46: }
47: }
48 :
49 : </ script >
50 :
51 : < html xmlns = " http://www.w3.org/1999/xhtml " >
52 : < head runat = " server " >
53 : < title > Untitled Page </ title >
54 : </ head >
55 : < body >
56 : < form id = " form1 " runat = " server " >
57 : < div >
58 : < asp:GridView ID = " GridView1 " runat = " server " AutoGenerateColumns = " False "
59 : DataKeyNames = " EmployeeID " DataSourceID = " SqlDataSource1 " >
60 : < Columns >
61 : < asp:TemplateField >
62 : < ItemTemplate >
63 : < asp:CheckBox ID = " CheckBox1 " runat = " server " />
64 : </ ItemTemplate >
65 : </ asp:TemplateField >
66 : < asp:BoundField DataField = " LastName " HeaderText = " LastName "
67 : SortExpression = " LastName " />
68 : < asp:BoundField DataField = " FirstName " HeaderText = " FirstName "
69 : SortExpression = " FirstName " />
70 : < asp:BoundField DataField = " Title " HeaderText = " Title "
71 : SortExpression = " Title " />
72 : </ Columns >
73 : </ asp:GridView >
74 : < asp:SqlDataSource ID = " SqlDataSource1 " runat = " server "
75 : ConnectionString = " <%$ ConnectionStrings:NorthwindConnectionString %> "
76 : SelectCommand = " SELECT [EmployeeID], [LastName], [FirstName], [Title]
77 : FROM [Employees] ORDER BY [LastName], [FirstName] " >
78 : </ asp:SqlDataSource >
79 :
80 : < asp:Button ID = " Button1 " runat = " server " Text = " Retrieve data " OnClick = " Button1_Click " />
81 : < p >
82 : < asp:GridView runat = " server " ID = " GridViewResult " />
83 : </ p >
84 : </ div >
85 : </ form >
86 : </ body >
87 : </ html >
2 : <% @ Import Namespace = " System.Data.SqlClient " %>
3 : <% @ Import Namespace = " System.Web.Configuration " %>
4 : <% @ Import Namespace = " System.Data " %>
5 :
6 : <! DOCTYPE html PUBLIC " -//W3C//DTD XHTML 1.0 Transitional//EN "
7 : " http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd " >
8 :
9 : < script runat = " server " >
10 :
11 : protected void Button1_Click( object sender, EventArgs e)
12 : ... {
13: RetrieveInformationForSelectedEmployees();
14: }
15 :
16 : private void RetrieveInformationForSelectedEmployees()
17 : ... {
18: StringBuilder sb = new StringBuilder();
19:
20: // First loop through the GridView and see which
21: // employees were selected. I use the StringBuilder
22: // since the list could be a very long list.
23: foreach (GridViewRow row in GridView1.Rows)
24: ...{
25: if (((CheckBox)row.FindControl("CheckBox1")).Checked)
26: ...{
27: sb.Append(GridView1.DataKeys[row.RowIndex].Value.ToString() + ',');
28: }
29: }
30:
31: using (SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings
32: ["NorthwindConnectionString"].ConnectionString))
33: ...{
34: using (SqlCommand cmd = new SqlCommand("USP_RetrieveInformationForSelectedEmployees", conn))
35: ...{
36: cmd.CommandType = CommandType.StoredProcedure;
37: cmd.Parameters.AddWithValue("@p_selectedEmployees", sb.ToString());
38:
39: DataSet ds = new DataSet();
40: SqlDataAdapter da = new SqlDataAdapter(cmd);
41: da.Fill(ds);
42:
43: GridViewResult.DataSource = ds;
44: GridViewResult.DataBind();
45: }
46: }
47: }
48 :
49 : </ script >
50 :
51 : < html xmlns = " http://www.w3.org/1999/xhtml " >
52 : < head runat = " server " >
53 : < title > Untitled Page </ title >
54 : </ head >
55 : < body >
56 : < form id = " form1 " runat = " server " >
57 : < div >
58 : < asp:GridView ID = " GridView1 " runat = " server " AutoGenerateColumns = " False "
59 : DataKeyNames = " EmployeeID " DataSourceID = " SqlDataSource1 " >
60 : < Columns >
61 : < asp:TemplateField >
62 : < ItemTemplate >
63 : < asp:CheckBox ID = " CheckBox1 " runat = " server " />
64 : </ ItemTemplate >
65 : </ asp:TemplateField >
66 : < asp:BoundField DataField = " LastName " HeaderText = " LastName "
67 : SortExpression = " LastName " />
68 : < asp:BoundField DataField = " FirstName " HeaderText = " FirstName "
69 : SortExpression = " FirstName " />
70 : < asp:BoundField DataField = " Title " HeaderText = " Title "
71 : SortExpression = " Title " />
72 : </ Columns >
73 : </ asp:GridView >
74 : < asp:SqlDataSource ID = " SqlDataSource1 " runat = " server "
75 : ConnectionString = " <%$ ConnectionStrings:NorthwindConnectionString %> "
76 : SelectCommand = " SELECT [EmployeeID], [LastName], [FirstName], [Title]
77 : FROM [Employees] ORDER BY [LastName], [FirstName] " >
78 : </ asp:SqlDataSource >
79 :
80 : < asp:Button ID = " Button1 " runat = " server " Text = " Retrieve data " OnClick = " Button1_Click " />
81 : < p >
82 : < asp:GridView runat = " server " ID = " GridViewResult " />
83 : </ p >
84 : </ div >
85 : </ form >
86 : </ body >
87 : </ html >