Solved

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

Posted on 2009-07-09
10
1,590 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
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: 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Class object 2 26
Error in JQuery 5 38
Automated testing suggestions? 2 26
I need help converting a bitmap to an image in VB.Net 1 11
This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
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…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

910 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

22 Experts available now in Live!

Get 1:1 Help Now