Solved

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

Posted on 2009-07-09
10
1,598 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
looking for an ASP.net report designer 4 43
Upgrade code from VS 2010 to VS 2015 7 34
How to force output to ascii 2 41
VB.net Filesystem watcher not working 5 38
A quick way to get a menu to work on our website, is using the Menu control and assign it to a web.sitemap using SiteMapDataSource. Example of web.sitemap file: (CODE) Sample code to add to the page menu: (CODE) Running the application, we wi…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

730 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