Solved

pass gridview's cell value as parameter to query another db table

Posted on 2009-07-09
10
1,593 Views
Last Modified: 2013-11-08
pls i am new to asp.net/vb but have done quite a few work on dat.and i have a problem i need to solve soon.
First, i have a gridview which which displays data from a stored procedure using sqlDatasource.i need to click on a cell and use cell value as parameter to query either a database or another stored procedure as a datatable/dataset and place/bind result to a textbox/dropdownlist/label. I am not geting my query results anytime i click on a cell.below is the code.Please i need ur help. Thanks
VB.NET CODE
Imports System.Data.SqlClient
Imports System.Drawing
Partial Public Class SelectableColumn
    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 GridView1_RowDataBound(ByVal sender As Object, ByVal e As GridViewRowEventArgs)
'formatting the gridview 
        Dim cell As TableCell
        Dim dValue As String
        Dim dTime As String
        If e.Row.RowType = DataControlRowType.DataRow Then
 
            Dim singleClickButton As LinkButton = DirectCast(e.Row.Cells(0).Controls(0), LinkButton)
            Dim javaScriptOnclick As String = ClientScript.GetPostBackClientHyperlink(singleClickButton, "")
            For i As Integer = 0 To e.Row.Cells.Count - 1
                Dim js As String = javaScriptOnclick.Insert(javaScriptOnclick.Length - 2, i.ToString())
                e.Row.Cells(i).Attributes("onclick") = js
                e.Row.Cells(i).Attributes("style") += "cursor:pointer;cursor:hand;"
 
            Next
            cell = e.Row.Controls(2)
            dTime = cell.Text
            dTime = Convert.ToDateTime(dTime).ToString("hh:mmtt")
            cell.Text = dTime
            cell = e.Row.Controls(1)
            dValue = cell.Text
            If Val(dValue) <= 100 And Val(dValue) >= 75 Then
                cell.BackColor = Color.Lime
            ElseIf Val(dValue) < 75 And Val(dValue) >= 60 Then
 
                cell.BackColor = Color.Yellow
            ElseIf Val(dValue) < 60 And Val(dValue) >= 1 Then
                cell.BackColor = Color.Red
            Else
                cell.BackColor = Color.Cyan
            End If
            For i = 3 To e.Row.Controls.Count - 1
 
                cell = e.Row.Controls(i)
                dValue = cell.Text
                If Val(dValue) <= 100 And Val(dValue) >= 75 Then
                    cell.BackColor = Color.Lime
                ElseIf Val(dValue) < 75 And Val(dValue) >= 60 Then
 
                    cell.BackColor = Color.Yellow
                ElseIf Val(dValue) < 60 And Val(dValue) > 0 Then
                    cell.BackColor = Color.Red
                Else
                    cell.BackColor = Color.Cyan
                End If
            Next i
        End If
    End Sub
 
    Protected Overloads Overrides Sub Render(ByVal writer As HtmlTextWriter)
        For Each r As GridViewRow In GridView1.Rows
            If r.RowType = DataControlRowType.DataRow Then
                For columnIndex As Integer = 0 To r.Cells.Count - 1
 
                    Page.ClientScript.RegisterForEventValidation(r.UniqueID & "$ctl00", columnIndex.ToString())
 
                Next
            End If
        Next
        MyBase.Render(writer)
    End Sub
 
    Protected Sub GridView1_RowCommand(ByVal sender As Object, ByVal e As GridViewCommandEventArgs)
        If e.CommandName.ToString() = "ColumnClick" Then
      Dim selectedRowIndex As Integer = Convert.ToInt32(e.CommandArgument.ToString())
 Dim selectedColumnIndex As Integer = Convert.ToInt32(Request.Form("__EVENTARGUMENT").ToString())
            lblSelectedColumnTitle.Text = GridView1.Columns(selectedColumnIndex).HeaderText
                     GetData(lblSelectedColumnTitle.Text)
 
        End If
    End Sub
    Private Sub GetData(ByVal parameter As String)
             Dim strConn As String = "connectionString"
        Dim ds As New DataSet()
          Dim Myconn As New SqlConnection(strConn)
               Myconn.Open()
        Dim sqlstr As String = "select value from table where field='@field'"
        Dim sqlCmd As New SqlCommand(sqlstr, Myconn)
        sqlCmd.Parameters.AddWithValue("@field'", parameter)
        Dim da As New SqlDataAdapter(sqlCmd)
        da.Fill(ds)
    dropdownlist.DataSource = ds
        dropdownlist.DataTextField = "value"
        dropdownlist.DataValueField = "value"
        dropdownlist.DataBind()
       End Sub
End Class
 
 
 
 
 
ASPCODE
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
            OnRowDataBound="GridView1_RowDataBound" OnRowCommand="GridView1_RowCommand" 
            DataSourceID="SqlDataSource1">
             <Columns>
             <asp:ButtonField CommandName="ColumnClick" Visible="false" />
             
             <asp:BoundField />
             <asp:BoundField />
             </Columns>
           <%-- <PagerStyle BackColor="White" ForeColor="#000066" HorizontalAlign="Left" />--%>
                            <SelectedRowStyle BackColor="Red" Font-Bold="True" ForeColor="White" Font-Size="14" HorizontalAlign="Center" />
                            <HeaderStyle Font-Bold="True"  Font-Size="14" ForeColor="#000099" Font-Names="Arial" />
            
        </asp:GridView>
                    <strong> Selected Column Title:</strong>
                                    <asp:Label ID="lblSelectedColumnTitle" runat="server" ForeColor="Red"></asp:Label>
                                  
     <asp:DropDownList ID="dropdownlist" runat="server">
        </asp:DropDownList>
        <br />
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
            ConnectionString="<%$ ConnectionStrings:ConnectionStringName %>" 
            SelectCommand="StoredProcedureName" SelectCommandType="StoredProcedure">
            </asp:SqlDataSource>

Open in new window

0
Comment
Question by:gimbus
  • 6
  • 4
10 Comments
 
LVL 41

Accepted Solution

by:
guru_sami earned 500 total points
ID: 24825146
Considering you have boundFields here is sample code:
Compare it with yours and try to make appropriate adjustments:

Protected Sub GridView1_RowDataBound(ByVal sender As Object, ByVal e As GridViewRowEventArgs)
    Dim csm As ClientScriptManager = Me.Page.ClientScript
    Dim grdview As GridView = DirectCast(sender, GridView)
    If e.Row.RowType = DataControlRowType.DataRow Then
        For i As Integer = 0 To e.Row.Cells.Count - 1
            e.Row.Cells(i).Attributes("onclick") = csm.GetPostBackClientHyperlink(grdview, ("Test$" & e.Row.RowIndex & "|") + i.ToString())
        Next
    End If
End Sub
Protected Sub GridView1_RowCommand(ByVal sender As Object, ByVal e As GridViewCommandEventArgs)
    If e.CommandName = "Test" Then
        Dim rowcell As String() = e.CommandArgument.ToString().Split("|"c)
        Dim rowindex As Integer = Convert.ToInt32(rowcell(0))
        Dim cell As Integer = Convert.ToInt32(rowcell(1))
        Dim s As String = GridView1.Rows(rowindex).Cells(cell).Text
        Response.Write(s)
    End If
End Sub

Note:
1: If you have first cell /column as ButtonField then in the for loop start with i=1.
2: If you decide to use TemplateField for a column the code might change.
0
 

Author Comment

by:gimbus
ID: 24826729
THank you very much guru_sami for your help.but the main problem is connecting the gridview to another table in the database via clicking on the cells to get a filtered data on a textbox or label because it seems that all i have been able to do is just get the text on each cell.I v tried using the Getdata() method in my code but i dont know if it is the right way to go.Counting on u guyz.thanks
0
 

Author Comment

by:gimbus
ID: 24826737
I forgot to mention that my gridview displays a stored procedure for a pivoted table.thanks
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 41

Expert Comment

by:guru_sami
ID: 24827562
You gridView Columns in the code above looks like below:
 <Columns>
             <asp:ButtonField CommandName="ColumnClick" Visible="false" />
              <asp:BoundField />
             <asp:BoundField />
             </Columns>
Can you share the original code....that you are saying you have label and textboxes...
0
 

Author Comment

by:gimbus
ID: 24853597
Thank you for your response.Actually i shortened my gridview code coz i have many BoundFields. and the label/texboxboxes are ouside the grid.i was able to get the solution by  making this correction in the query string i.e by replacing
  Dim sqlstr As String = "select value from table where field='@field'"
with
Dim sqlstr As String = "select value from table where field=@field"
i.e by removing the quote in the parameter @field with a new getDate() method below

 Private Sub GetData(ByVal parameter As String)
 Dim textVal as String
             Dim strConn As String = "connectionString"
          Dim Myconn As New SqlConnection(strConn)
                     Dim sqlstr As String = "select value from table where field=@field"
        Dim sqlCmd As New SqlCommand(sqlstr, Myconn)
        sqlCmd.Parameters.AddWithValue("@field'", parameter)
          Myconn.Open()
Dim dr as SqlDataReader=sqlCmd.Cmd.ExecuteReader()
If dr.HasRows() Then
            dr.Read()
             textVal = dr("TableField")
Else
 textVal =" "
End if
Label1.Text= textVal
 Myconn.Close()
       End Sub

Which worked fine. I have another issue though which i need help with. I have sucessfully populated some labels with the queried data of the value from a particular row and column on page load by calling the GetData Method
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
             Try
                        lblValue.Text = GridView1.Rows(0).Cells(3).Text
           GetData(lblValue.Text)
              Catch ex As Exception
             lblValue.Text = ""
                       Exit Sub
        End Try
    End Sub

My Question is
1. How do i do a page load at timed intervals and loop through the cells of  gridview every pageload at those timed intervals so that e.g the value of the label changes say every 2 mins  with data gotten by perfoming the select query using different values of cells as parameters at the diiferent timed intervals

Pls i need help on this.Thanks in advance
0
 
LVL 41

Expert Comment

by:guru_sami
ID: 24860082
0
 

Author Comment

by:gimbus
ID: 24863268
Thank you coz u have been very helpful. i am yet to find a headway coz am not actually performing an auto refresh but auto reload of data from a particular row in a gridview with timed interval(20secs) increments of the columns.
Problem:My code below loops through the whole columns in the gridview and loads the last column when the GetData() method is called after the time interval of 20 secs instead of loading the next i th column inthe loop. Thanks in advance

below is my timer_ tick event and my method
 Protected Sub Timer1_Tick(ByVal sender As Object, ByVal e As EventArgs) Handles Timer1.Tick
        Timer1.Enabled = False
        LoadData()
        Timer1.Enabled = True
    End Sub

 Private Sub LoadData()
    Dim dTime As String
           For Each r As GridViewRow In GridView1.Rows
            If r.RowType = DataControlRowType.DataRow Then
              For i As Integer = 3 To r.Cells.Count - 1
                    If Timer1.Interval < 20000 Then
                       lblvalue.Text = GridView1.Columns(i).HeaderText
                                        End If
                               GetData(lblvalue.Text )
                    Next i
                   End If
        Next
    End Sub
0
 
LVL 41

Expert Comment

by:guru_sami
ID: 24863663
Problem:My code below loops through the whole columns in the gridview and loads the last column when the GetData() method is called after the time interval of 20 secs instead of loading the next i th column inthe loop.

I am sure I did not understand the problem correctly...but from from I understood...you need to keep track of "i" i.e. either have a static variable or store it in Session...Then inside the LoadData do your operation for particular column i only...getting this i from session.

Sorry if I am misinterpreting your problem.
0
 

Author Comment

by:gimbus
ID: 24873398
no u are not.you are right it is sort of like a slide show loop of loading i.thus on page load, i need to load all i's with an interval of say 20 secs of which if i click on a cell,it continues from that cell or start loading from the first i. Thanks
0
 

Author Comment

by:gimbus
ID: 24899387
got it using ViewState().thanks for your help
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
In C# Using WebClient method how to post certificate from file with password 3 31
asp day pilot 3 18
Delete HTML table rows 12 28
Visual Studio 2013 and Eclipse Neon 6 35
Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question