Have something to say? Contribute New Article Reply to this Article
Selecting a record from a datagrid and passing it as a variable
I am creating a page to search for a user in a database and display the results in a gata grid (Which i have done) Then i would like to select one of the results and use this record by holding it as a variable and pass it back into the database (which i cant do!) Help!
this is the aspx.VB code
Imports System.Data.OleDb
Imports System.Data
Partial Class Search3
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
End Sub
Protected Sub Button2_OnClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click
Dim objCommand As OleDbCommand
Dim objAdapter As OleDbDataAdapter
Dim objDataSet As DataSet
Dim strSearch As String
Dim objConnection As String = ("Provider=Microsoft.ACE.OLEDB.12.0;" _
& "Data Source=" & Server.MapPath("DB\ScrumManagementSystem.accdb") & ";")
Dim cn As New OleDbConnection(objConnection)
Dim strSQLQuery As String
strSearch = TextBox1.Text
If Len(Trim(strSearch)) > 0 Then
strSQLQuery = "SELECT FName & SName & ProductOwnerRole FROM tblUser WHERE ProductOwnerRole <> 'false' AND FName LIKE '%" & Replace(strSearch, "'", "''") & "%' ORDER BY FName;"
cn.Open()
Dim cmd As New OleDbCommand(strSQLQuery, cn)
objCommand = New OleDbCommand(strSQLQuery, cn)
objAdapter = New OleDbDataAdapter(objCommand)
objDataSet = New DataSet()
objAdapter.Fill(objDataSet)
dgPaging1.DataSource = objDataSet
dgPaging1.DataBind()
cn.Close()
Else
TextBox1.Text = "Enter search criteria!"
End If
End Sub
Protected Sub Button3_OnClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button3.Click
Dim objCommand As OleDbCommand
Dim objAdapter As OleDbDataAdapter
Dim objDataSet As DataSet
Dim strSearch As String
Dim objConnection As String = ("Provider=Microsoft.ACE.OLEDB.12.0;" _
& "Data Source=" & Server.MapPath("DB\ScrumManagementSystem.accdb") & ";")
Dim cn As New OleDbConnection(objConnection)
Dim strSQLQuery As String
strSearch = TextBox2.Text
If Len(Trim(strSearch)) > 0 Then
strSQLQuery = "SELECT ProjectName & Description FROM tblProject WHERE ProjectName LIKE '%" & Replace(strSearch, "'", "''") & "%' ORDER BY ProjectName;"
cn.Open()
Dim cmd As New OleDbCommand(strSQLQuery, cn)
objCommand = New OleDbCommand(strSQLQuery, cn)
objAdapter = New OleDbDataAdapter(objCommand)
objDataSet = New DataSet()
objAdapter.Fill(objDataSet)
dgPaging2.DataSource = objDataSet
dgPaging2.DataBind()
cn.Close()
Else
TextBox2.Text = "Enter search criteria!"
End If
End Sub
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
'Declaration of variables
Dim myConnection2 As OleDbConnection
Dim myCommand2 As OleDbCommand
Dim sql As String
Dim dlstDataListItem As DataListItem
Dim intItemIndex As Int32
'SQL query to access the table tblProject
sql = "insert into tblProject "
sql = sql & "(ProjectName)"
sql = sql & "values('" "')"
'Creates a connection to the database
myConnection2 = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" _
& "Data Source=" & Server.MapPath("DB\ScrumManagementSystem.accdb") & ";")
'Opens a connection
myConnection2.Open()
myCommand2 = New OleDbCommand(sql, myConnection2)
myCommand2.ExecuteNonQuery()
'Closes the connection
myConnection2.Close()
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Declaration of variables
Dim myConnection As OleDbConnection
Dim myCommand As OleDbCommand
Dim sql1 As String
'SQL query to access the table tblProject
sql1 = "insert into tblProject "
sql1 = sql1 & "(FName)"
sql1 = sql1 & "values(LinkButton2)"
'Creates a connection to the database
myConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" _
& "Data Source=" & Server.MapPath("DB\ScrumManagementSystem.accdb") & ";")
'Opens a connection
myConnection.Open()
myCommand = New OleDbCommand(sql1, myConnection)
myCommand.ExecuteNonQuery()
'Closes the connection
myConnection.Close()
Response.Redirect("ProductBacklog.aspx")
End Sub
End Class
this is the aspx code
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Search3.aspx.vb" Inherits="Search3" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<link rel="stylesheet" type="text/css" href="style.css" />
<title>Untitled Page</title>
<style type="text/css">
#Select1
{
height: 54px;
width: 138px;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<div id="site">
<div id="header">
<img src = "images/header.gif" alt=".NETMARE LOGO" /> <div id="menu"><!--#include file="menutest.html"--></div>
<h1>Adding product owners to projects</h1>
</div>
<div class="box-content">
<asp:Label ID="Label1" runat="server" Text="Enter a registered product owner"></asp:Label>
<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
<asp:Button ID="Button2" runat="server" Height="22px"
style="font-weight: 700" Text="Search" Width="57px" />
<asp:Label ID="Label3" runat="server" Text="Results:"></asp:Label>
<asp:GridView ID="dgPaging1" runat="server">
<Columns>
<asp:CommandField ShowSelectButton="True" />
<asp:TemplateField ShowHeader="false">
<ItemTemplate>
<asp:LinkButton ID="LinkButton1" runat="server" CommandName="Select" Text="Select B">
</asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
<asp:Label ID="Label2" runat="server" Text="Enter a project to add them to:"></asp:Label>
<asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
<asp:Button ID="Button3" runat="server" Height="20px"
style="font-weight: 700" Text="Search" Width="62px" />
<asp:Label ID="Label4" runat="server"
Text="Results:"></asp:Label>
<asp:GridView ID="dgPaging2" runat="server">
<Columns>
<asp:TemplateField ShowHeader="false">
<ItemTemplate>
<asp:LinkButton ID="LinkButton2" runat="server" CommandName="Select" Text="Select">
</asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
<asp:Button ID="Button1" runat="server" style="font-weight: 700"
Text="Update" />
<div id="footer"><!--#include file="footer.html"--></div>
</div>
</div>
</form>
</body>
</html>1. After the selection made in the Datagrid, u convert the gridvalues in Xml and then pass in to one Stored procedure
Ex :
=================
string strXml = "<ROWS>";
foreach (GridViewRow gvRC in GridView1.Rows)
{
strXml += "<ROW><CheckBoxVal>" + gvRC.Cells["CheckBoxVal"].Text +"</CheckBoxVal>";
strXml += "<InsertValue>" + gvRC.Cells["InsertValue"].Text; +"</InsertValue></ROW>";
}
strXml = "</ROWS>";
PasstoDB(strXml)
In Store Proc :
===========
Create Procedure sp_InsertUser
(
@p_XML varchar(max)
)
as
Begin
/*Exec sp_InsertUser '
<ROWS>
<ROW>
<CheckBoxVal>0</CheckBoxVal>
<InsertValue>Sathish</InsertValue>
</ROW>
<ROW>
<CheckBoxVal>1</CheckBoxVal>
<InsertValue>Kumar</InsertValue>
</ROW>
</NewDataSet>';
*/
Begin Try
Set Nocount On
Declare @m_HdrXMLDoc Int;
EXEC sp_xml_preparedocument @m_HdrXMLDoc OUTPUT, @p_XML
Select CheckBoxVal,InsertValue into #tmp
From OPENXML (@m_HdrXMLDoc, '/ROWS/ROW',2)
WITH (CheckBoxVal bit,InsertValue varchar(50))
EXEC sp_xml_removedocument @m_HdrXMLDoc
Begin Tran
Insert into UrTable
select InsertValue from #tmp where CheckBoxVal =1
Commit tran
drop table #tmp
Set Nocount Off
End Try
Begin Catch
declare @error_line int
set @ErrMsg=rtrim(error_message())
if (XACT_STATE()) <> 0
Begin
Rollback Transaction
End
Select error_message() as ErrMsg
End Catch
Endsomething is like this :
Sub ActionSave() Dim D As GridViewRow Dim SQL As String = "" Dim TrxID As String = "" For Each D In GV.Rows TrxID = D.Cells(0).Text If ChkSelect.Checked = True Then SQL = "INSERT INTO Trx VALUES('" & TrxID & "')" End If Next End Sub
Having a small problem with the line
"If ChkSelect.Checked = True Then"
Where is ChkSelect.Checked coming from? my gridview is called dgPaging1 and the check box ID is called:
CheckBox ID="CheckBoxPurchase"
What am i doing wrong?
Try with the following way:
If ((CheckBox)D.FindControl("CheckBoxPurchase")).Checked Then
SQL = "INSERT INTO Trx VALUES('" & TrxID & "')"
End If mail2saion
Posting Whiz in Training
247 posts since Apr 2009
Reputation Points: 26
Solved Threads: 44
I am creating a page to search for a user in a database and display the results in a gata grid (Which i have done) Then i would like to select one of the results and use this record by holding it as a variable and pass it back into the database (which i cant do!) Help!
this is the aspx.VB code
Imports System.Data.OleDb Imports System.Data Partial Class Search3 Inherits System.Web.UI.Page Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load End Sub Protected Sub Button2_OnClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click Dim objCommand As OleDbCommand Dim objAdapter As OleDbDataAdapter Dim objDataSet As DataSet Dim strSearch As String Dim objConnection As String = ("Provider=Microsoft.ACE.OLEDB.12.0;" _ & "Data Source=" & Server.MapPath("DB\ScrumManagementSystem.accdb") & ";") Dim cn As New OleDbConnection(objConnection) Dim strSQLQuery As String strSearch = TextBox1.Text If Len(Trim(strSearch)) > 0 Then strSQLQuery = "SELECT FName & SName & ProductOwnerRole FROM tblUser WHERE ProductOwnerRole <> 'false' AND FName LIKE '%" & Replace(strSearch, "'", "''") & "%' ORDER BY FName;" cn.Open() Dim cmd As New OleDbCommand(strSQLQuery, cn) objCommand = New OleDbCommand(strSQLQuery, cn) objAdapter = New OleDbDataAdapter(objCommand) objDataSet = New DataSet() objAdapter.Fill(objDataSet) dgPaging1.DataSource = objDataSet dgPaging1.DataBind() cn.Close() Else TextBox1.Text = "Enter search criteria!" End If End Sub Protected Sub Button3_OnClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button3.Click Dim objCommand As OleDbCommand Dim objAdapter As OleDbDataAdapter Dim objDataSet As DataSet Dim strSearch As String Dim objConnection As String = ("Provider=Microsoft.ACE.OLEDB.12.0;" _ & "Data Source=" & Server.MapPath("DB\ScrumManagementSystem.accdb") & ";") Dim cn As New OleDbConnection(objConnection) Dim strSQLQuery As String strSearch = TextBox2.Text If Len(Trim(strSearch)) > 0 Then strSQLQuery = "SELECT ProjectName & Description FROM tblProject WHERE ProjectName LIKE '%" & Replace(strSearch, "'", "''") & "%' ORDER BY ProjectName;" cn.Open() Dim cmd As New OleDbCommand(strSQLQuery, cn) objCommand = New OleDbCommand(strSQLQuery, cn) objAdapter = New OleDbDataAdapter(objCommand) objDataSet = New DataSet() objAdapter.Fill(objDataSet) dgPaging2.DataSource = objDataSet dgPaging2.DataBind() cn.Close() Else TextBox2.Text = "Enter search criteria!" End If End Sub Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click 'Declaration of variables Dim myConnection2 As OleDbConnection Dim myCommand2 As OleDbCommand Dim sql As String Dim dlstDataListItem As DataListItem Dim intItemIndex As Int32 'SQL query to access the table tblProject sql = "insert into tblProject " sql = sql & "(ProjectName)" sql = sql & "values('" "')" 'Creates a connection to the database myConnection2 = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" _ & "Data Source=" & Server.MapPath("DB\ScrumManagementSystem.accdb") & ";") 'Opens a connection myConnection2.Open() myCommand2 = New OleDbCommand(sql, myConnection2) myCommand2.ExecuteNonQuery() 'Closes the connection myConnection2.Close() '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' 'Declaration of variables Dim myConnection As OleDbConnection Dim myCommand As OleDbCommand Dim sql1 As String 'SQL query to access the table tblProject sql1 = "insert into tblProject " sql1 = sql1 & "(FName)" sql1 = sql1 & "values(LinkButton2)" 'Creates a connection to the database myConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" _ & "Data Source=" & Server.MapPath("DB\ScrumManagementSystem.accdb") & ";") 'Opens a connection myConnection.Open() myCommand = New OleDbCommand(sql1, myConnection) myCommand.ExecuteNonQuery() 'Closes the connection myConnection.Close() Response.Redirect("ProductBacklog.aspx") End Sub End Classthis is the aspx code
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Search3.aspx.vb" Inherits="Search3" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <link rel="stylesheet" type="text/css" href="style.css" /> <title>Untitled Page</title> <style type="text/css"> #Select1 { height: 54px; width: 138px; } </style> </head> <body> <form id="form1" runat="server"> <div id="site"> <div id="header"> <img src = "images/header.gif" alt=".NETMARE LOGO" /> <div id="menu"><!--#include file="menutest.html"--></div> <h1>Adding product owners to projects</h1> </div> <div class="box-content"> <asp:Label ID="Label1" runat="server" Text="Enter a registered product owner"></asp:Label> <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox> <asp:Button ID="Button2" runat="server" Height="22px" style="font-weight: 700" Text="Search" Width="57px" /> <asp:Label ID="Label3" runat="server" Text="Results:"></asp:Label> <asp:GridView ID="dgPaging1" runat="server"> <Columns> <asp:CommandField ShowSelectButton="True" /> <asp:TemplateField ShowHeader="false"> <ItemTemplate> <asp:LinkButton ID="LinkButton1" runat="server" CommandName="Select" Text="Select B"> </asp:LinkButton> </ItemTemplate> </asp:TemplateField> </Columns> </asp:GridView> <asp:Label ID="Label2" runat="server" Text="Enter a project to add them to:"></asp:Label> <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox> <asp:Button ID="Button3" runat="server" Height="20px" style="font-weight: 700" Text="Search" Width="62px" /> <asp:Label ID="Label4" runat="server" Text="Results:"></asp:Label> <asp:GridView ID="dgPaging2" runat="server"> <Columns> <asp:TemplateField ShowHeader="false"> <ItemTemplate> <asp:LinkButton ID="LinkButton2" runat="server" CommandName="Select" Text="Select"> </asp:LinkButton> </ItemTemplate> </asp:TemplateField> </Columns> </asp:GridView> <asp:Button ID="Button1" runat="server" style="font-weight: 700" Text="Update" /> <div id="footer"><!--#include file="footer.html"--></div> </div> </div> </form> </body> </html>
Hey friend just set auto generated select button as true
and use the event selected index changing of the gridview which will solve your problem
Try with the following way:
If ((CheckBox)D.FindControl("CheckBoxPurchase")).Checked Then SQL = "INSERT INTO Trx VALUES('" & TrxID & "')" End If
Sorry for bothering you again!
Keep getting an error on this line
If ((CheckBox)D.FindControl("CheckBoxPurchase")).Checked Then
It says CheckBox is a type and cannot be used in an expression??
Help please!
This question has already been solved
You
© 2012 DaniWeb® LLC
