Solved

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

Posted on 2009-07-09
10
1,587 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

User art_snob (http://www.experts-exchange.com/M_6114203.html) encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
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 this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

706 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now