1

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" />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<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>
&nbsp;<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
         &nbsp;&nbsp;<asp:Button ID="Button2" runat="server" Height="22px" 
             style="font-weight: 700" Text="Search" Width="57px" />
         &nbsp;&nbsp;&nbsp;<br />
         <br />
         <asp:Label ID="Label3" runat="server" Text="Results:"></asp:Label>
&nbsp;
         <br />
         <br />
         <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>
         <br />
         <br />
&nbsp;<asp:Label ID="Label2" runat="server" Text="Enter a project to add them to:"></asp:Label>
         <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
&nbsp;&nbsp;&nbsp;&nbsp;<asp:Button ID="Button3" runat="server" Height="20px" 
             style="font-weight: 700" Text="Search" Width="62px" />
         &nbsp;&nbsp;&nbsp;&nbsp;<br />
         <br />
         <asp:Label ID="Label4" runat="server" 
             Text="Results:"></asp:Label>
         <br />
         <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>
         &nbsp;<br />
     <br>
      <br>
         <asp:Button ID="Button1" runat="server" style="font-weight: 700" 
             Text="Update" />
       <br>
        <br>
         <br>
         
      <div id="footer"><!--#include file="footer.html"--></div>
    </div>
    </div>
    </form>
</body>
</html>
5
Contributors
6
Replies
7
Views
7 Years
Discussion Span
Last Post by 15389049
0

something 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

Edited by Kusno: ..

0

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          
End

Edited by __avd: Added [code] tags. Encase your code in: [code] and [/code] tags.

0

something 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?

0

Try with the following way:

If ((CheckBox)D.FindControl("CheckBoxPurchase")).Checked Then
                SQL = "INSERT INTO Trx VALUES('" & TrxID & "')"
            End If
0

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" />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<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>
&nbsp;<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
         &nbsp;&nbsp;<asp:Button ID="Button2" runat="server" Height="22px" 
             style="font-weight: 700" Text="Search" Width="57px" />
         &nbsp;&nbsp;&nbsp;<br />
         <br />
         <asp:Label ID="Label3" runat="server" Text="Results:"></asp:Label>
&nbsp;
         <br />
         <br />
         <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>
         <br />
         <br />
&nbsp;<asp:Label ID="Label2" runat="server" Text="Enter a project to add them to:"></asp:Label>
         <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
&nbsp;&nbsp;&nbsp;&nbsp;<asp:Button ID="Button3" runat="server" Height="20px" 
             style="font-weight: 700" Text="Search" Width="62px" />
         &nbsp;&nbsp;&nbsp;&nbsp;<br />
         <br />
         <asp:Label ID="Label4" runat="server" 
             Text="Results:"></asp:Label>
         <br />
         <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>
         &nbsp;<br />
     <br>
      <br>
         <asp:Button ID="Button1" runat="server" style="font-weight: 700" 
             Text="Update" />
       <br>
        <br>
         <br>
         
      <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

0

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 answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.