locked
Need Help one to many relationship between columns RRS feed

  • Question

  • User1523385712 posted

    Hi,

    I have a table Structure

    filename AKA
    12-3-09.pdf 443567
    12-3-09.pdf 345678
    12-3-09.pdf 456789
    3-3-10.pdf 78901
    filename AKA
    I want to output as 12-3-09.pdf 443567,345678,456789
    3-3-10.pdf 78901.

    Any Help would be appreciated

    Thanks!

    Wednesday, March 31, 2010 10:57 AM

Answers

  • User-1199946673 posted

     You're mixing up Classic ASP code with ASP.NET! Do not use recordset in ASP.NET, use OleDbDataReader instead.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 2, 2010 5:24 PM
  • User1867929564 posted

     hello,
    you are mixing asp and asp.net code.
    guess you are using asp.right ?
    i told you to apply the logic not the code.
    And no problem,if you are using asp.net.Even gridview control is ok.
    you can format same in gridview too.
    just lemme know the latest developement.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, April 3, 2010 2:01 AM

All replies

  • User1867929564 posted

    see,if this was sql server,then  it was easy.
    Since it is msaccess.Then i dunno.
    Don't know how to declare variable in msaccess,query window and use it.

    Any way you can also this in front end.
    Proceed then ask.

    Thursday, April 1, 2010 3:02 AM
  • Thursday, April 1, 2010 3:48 AM
  • User1523385712 posted

    Hi Harsh,


    Function concatproducts(image As String) As String
    Dim mystring As String
    Dim rs As Recordset
    Set rs = CurrentDb.OpenRecordset("Primary", dbOpenDynaset)
    With rs
       .MoveFirst
          Do Until .EOF
             If ![image] = image Then
                mystring = mystring & ![AKA/HS1] & ", "
             End If
                   .MoveNext
          Loop
    End With
    mystring = Left(mystring, (Len(mystring) - 2))
    concatproducts = mystring
    End Function

    Function concat(image As String) As String

    Dim mystring As String

    Dim rs As Recordset

    Set rs = CurrentDb.OpenRecordset("Primary", dbOpenDynaset)

    With rs

       .MoveFirst

          Do Until .EOF

             If ![image] = image Then

                mystring = mystring & ![AKA/HS1] & ", "

             End If

                   .MoveNext

        Loop

    End With


    mystring = Left(mystring, (Len(mystring) - 2))


    concat = mystring


    End Function



    I implemented this function in Access DB.When i run this query with this function in access.It works perfectly fine.But when i get the access database n use the particular access DB in my webform.It is not recognizing the function.
    I can do this easily with MSSQl server but my requirement is i need to use access database itself.I m newbie to access.

    Can you please guide me how to implement this particular function in frontend.I cant work on that access database anymore.Can you please guide me how can i do this in FrontEnd.

    Thanks 

    Thursday, April 1, 2010 12:42 PM
  • User1867929564 posted

     i) did you refer to the link i gave above ?Any problem ask.
    in msaccess,where you pasting this function,Module ?Is it public ?


    ii) in front end, iam giving you idea.
    Sorry you understand the logic only and not the language.
    in your table1 you hv 2 column filename and AKA,both text,right ?

    first you make qry in one recordset rs,
    string strfilename
    string strAKA
    sql="Select distinct filename from table1"
    while not rs.eof
    straka=""
    sql1=Select aka from table1 where filename=strfilename
    while not rs1.eof
    strAKA=strAKA+rs1["aka"]

    <tr><td>filename</td><td>AKA</td></tr>
    end while
    end while

    write it correctly.

    Friday, April 2, 2010 12:33 AM
  • User1523385712 posted


    Friday, April 2, 2010 9:43 AM
  • User1523385712 posted


    Friday, April 2, 2010 9:47 AM
  • User1523385712 posted

    Sub btnSearch_OnClick(ByVal sender As Object, ByVal e As EventArgs)

            

            Dim objCommand As OleDbCommand

            Dim objAdapter As OleDbDataAdapter

            Dim objDataSet As DataTable

            

            Dim rs As New ADODB.Recordset

            Dim rs1 As New ADODB.Recordset

           

            Dim strHSA As String

            Dim objConnection As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\nimmy.k\Desktop\achievemath_1\WebSite1\App_Data\project2.mdb"

            Dim cn As New OleDbConnection(objConnection)

            Dim strSQLQuery As String

            Dim strSQLQuery2 As String

           

            cn.Open()

            rs.Open("Select * From Primary", cn)

            rs1.Open("Select * From Primary", cn)

           

                

            strSQLQuery = "select distinct [image] from [Primary]"

            While Not rs.EOF

                strHSA = ""

                strSQLQuery2 = "Select [AKA/HS1] from [Primary] where [image]=strimage"

                While Not rs1.EOF

                    strHSA = strHSA + Convert.ToString(rs1("[AKA/HS1]"))

                    

                    Dim cmd As New OleDbCommand(strSQLQuery, cn)

                    cmd = New OleDbCommand(strSQLQuery2, cn)

                    objCommand = New OleDbCommand(strSQLQuery, cn)

                    objCommand = New OleDbCommand(strSQLQuery2, cn)

                    objAdapter = New OleDbDataAdapter(objCommand)

                    objDataSet = New DataTable()

                    objAdapter.Fill(objDataSet)

                

                    dgPaging.DataSource = objDataSet

                    dgPaging.DataBind()


                        

                End While

            End While

                

            cn.Close()

                 

                

            

        End Sub


    </script>

    <html>

    <head>

      <title>Search</title>

        </head>

    <body>


    <form id="Form1" runat="server">


    <asp:Button id="btnSearch" runat="server"

    Text    ="Search"

    OnClick ="btnSearch_OnClick"

    />


    <asp:GridView ID="dgPaging" runat="server" AutoGenerateColumns="false"  HorizontalAlign="Justify" ShowHeader="False">

        <EditRowStyle ForeColor="White" />

        <AlternatingRowStyle ForeColor="White" HorizontalAlign="Justify"></AlternatingRowStyle>

        <Columns>

        <asp:TemplateField HeaderText="HS1">

    <ItemTemplate>

    <img src="pdf\<%#Eval("image")%>" />HS1-<%#Eval("AKA/HS1")%> 

    </ItemTemplate>

    </asp:TemplateField>

        </Columns>

    </asp:GridView>



    </form>


    </body>

    </html>

    I am getting a runtime exception : 

    xception Details: System.Runtime.InteropServices.COMException: Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

    Source Error: 

    Line 63:        
    Line 64:         cn.Open()
    Line 65:         rs.Open("Select * From Primary", cn)
    Line 66:         rs1.Open("Select * From Primary", cn)
    Line 67:      

    Can you please suggest me

    Thanks,

    Nimmy

    Sub btnSearch_OnClick(ByVal sender As Object, ByVal e As EventArgs)
            
            Dim objCommand As OleDbCommand
            Dim objAdapter As OleDbDataAdapter
            Dim objDataSet As DataTable
            Dim strSearch As String
            Dim strSearch2 As String
            Dim strSearch3 As String
            Dim strSearch4 As String
            Dim strSearch5 As String
            Dim strSearch6 As String
            Dim rs As New ADODB.Recordset
            Dim rs1 As New ADODB.Recordset
            Dim strimage As String
            Dim strHSA As String
            Dim objConnection As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\nimmy.k\Desktop\achievemath_1\WebSite1\App_Data\project2.mdb"
            Dim cn As New OleDbConnection(objConnection)
            Dim strSQLQuery As String
            Dim strSQLQuery2 As String
            strSearch = txtSearch.Text
            strSearch2 = txtSearch2.Text
            strSearch3 = txtSearch3.Text
            strSearch4 = txtSearch4.Text
            strSearch5 = txtSearch5.Text
            strSearch6 = txtSearch6.Text
            If (Len(Trim(strSearch)) & Len(Trim(strSearch2)) & Len(Trim(strSearch3)) & Len(Trim(strSearch4)) & Len(Trim(strSearch5)) & Len(Trim(strSearch6))) > 0 Then
                
                rs.Open("Primary", cn)
                rs1.Open("Primary", cn)
                cn.Open()
                
                strSQLQuery = "select distinct [image] from [Primary]"
                While Not rs.EOF
                    strHSA = ""
                    strSQLQuery2 = "Select [AKA/HS1] from [Primary] where [image]=strimage"
                    While Not rs1.EOF
                        strHSA = strHSA + rs1[[AKA/HS1]]
                        
                        
                    End While
                End While
                
                
                 
                Dim cmd As New OleDbCommand(strSQLQuery, cn)
                cmd = New OleDbCommand(strSQLQuery2, cn)
                objCommand = New OleDbCommand(strSQLQuery, cn)
                objCommand = New OleDbCommand(strSQLQuery2, cn)
                objAdapter = New OleDbDataAdapter(objCommand)
                objDataSet = New DataTable()
                objAdapter.Fill(objDataSet)
                
                dgPaging.DataSource = objDataSet
                dgPaging.DataBind()
                cn.Close()
            Else
                txtSearch.Text = "Add the text here!!"
            End If
        End Su
    Friday, April 2, 2010 10:44 AM
  • User-1199946673 posted

     You're mixing up Classic ASP code with ASP.NET! Do not use recordset in ASP.NET, use OleDbDataReader instead.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 2, 2010 5:24 PM
  • User1867929564 posted

     hello,
    you are mixing asp and asp.net code.
    guess you are using asp.right ?
    i told you to apply the logic not the code.
    And no problem,if you are using asp.net.Even gridview control is ok.
    you can format same in gridview too.
    just lemme know the latest developement.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, April 3, 2010 2:01 AM
  • User1523385712 posted

    I have a table Structure

    filename AKA
    12-3-09.pdf 443567
    12-3-09.pdf 345678
    12-3-09.pdf 456789
    3-3-10.pdf 78901
    filename AKA
    I want to output as 12-3-09.pdf 443567,345678,456789
    3-3-10.pdf 78901.


    Now i m using SQLExpress now i used concat(AKA) so i get the output but now i have a problem in display them.


    I need to display user with ahref tag ultimately i.e.

     <a href='pdf/<%# Eval("filename") %>#nameddest=HS1-<%# Eval("AKA/HS1")%>  ' target="_blank">HS1-<%#Eval("AKA/HS1")%> 
                                                        </a>

     <a href='pdf/<%# Eval("filename") %>#nameddest=HS1-<%# Eval("AKA/HS1")%>  ' target="_blank">HS1-<%#Eval("AKA/HS1")%>  </a>


    AKA/HS1 is the result that i get from concat(AKA) i.e . for for one filename i need to display multiple HS1's.

    for 12-3-09.pdf i need to give 3links of HS1's 443567,345678,456789

    but i m not getting it as a 3 links i m getting the result as one single link 443567,345678,456789.

    Is their any way to display them as three different links???

    Any Help would b appreciated

    Wednesday, April 21, 2010 10:48 PM
  • User1867929564 posted

    So finally you are getting the output the way you want.
    No confusion ?

    Yes can show them as three link in grid in column.Just you hv to code it.
    Have you use any function that get call when grid get bind ?
    If you hv use then you get the idea,how to code it.
    in grid create a itemtemplatecolumn,
    <asp:TemplateColumn HeaderText="Filename" >
                            <ItemTemplate>
                                <%# CreateLink(Eval("filename", "{0}"), Eval("AKA/HS1", "{0}"))%>
                            </ItemTemplate>
                          
                        </asp:TemplateColumn>

    where CreateLink is function in cs file that you will write with 2 parameter,filename and AKA/HS1.

    First make it simple then you can further modify it.
    Note :AKA/HS1(change this to AKA_HS1)

    Or you can show your desired output in image then I can help you out.


    Thursday, April 22, 2010 12:36 AM
  • User1523385712 posted

    Hi Harsh ,

    This is my ASP.NET Page :

    <%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default"  Debug="true"%>


    <%@ Import Namespace="System.Data" %>


    <%@ Import Namespace="System.Data.Odbc" %>





    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">


    <script language="VB" runat="server">

        Public Property CurrentPage() As Integer

            Get

                ' look for current page in ViewState

                Dim o As Object = Me.ViewState("_CurrentPage")

                If o Is Nothing Then

                    Return 0

                Else

                    ' default to showing the first page

                    Return CInt(o)

                End If

            End Get

       

            Set(ByVal value As Integer)

                Me.ViewState("_CurrentPage") = value

            End Set

        End Property

        Private Sub cmdPrev_Click(ByVal sender As Object, ByVal e As EventArgs)

            ' Set viewstate variable to the previous page

            CurrentPage -= 1

       

            ' Reload control

            btnSearch_OnClick(sender, e)

            

            

        End Sub

        Private Sub cmdNext_Click(ByVal sender As Object, ByVal e As EventArgs)

            ' Set viewstate variable to the next page

            CurrentPage += 1

       

            ' Reload control

            btnSearch_OnClick(sender, e)

            

        End Sub




    Sub btnSearch_OnClick(ByVal sender As Object, ByVal e As EventArgs)

            Dim objCommand As OdbcCommand

            

            

            Dim objAdapter As OdbcDataAdapter

            Dim objDataSet As DataSet

            Dim strSearch6 As String

            Dim objConnection As String = "Driver={MySQL ODBC 5.1 Driver};" + "Server=localhost;Database=project;uid=root;"


            Dim cn As New OdbcConnection(objConnection)

            Dim strSQLQuery As String


            

            strSearch6 = txtSearch6.Text

            If (Len(Trim(strSearch6))) > 0 Then


                strSQLQuery = "SELECT filename,GROUP_CONCAT(DISTINCT HS1 ORDER BY HS1 DESC SEPARATOR ' ') AS HS1 FROM nimmy WHERE filename LIKE '%" & Replace(strSearch6, "'", "''") & "%' GROUP BY filename;"


                

                cn.Open()

                 

                Dim cmd As New OdbcCommand(strSQLQuery, cn)


                objCommand = New OdbcCommand(strSQLQuery, cn)


                objAdapter = New OdbcDataAdapter(objCommand)

                objDataSet = New DataSet()

                objAdapter.Fill(objDataSet)

               

                

                Dim objPds As New PagedDataSource()

                objPds.DataSource = objDataSet.Tables(0).DefaultView

                objPds.AllowPaging = True

                objPds.PageSize = 88

                objPds.CurrentPageIndex = CurrentPage

                cmdPrev.Enabled = Not objPds.IsFirstPage

                cmdNext.Enabled = Not objPds.IsLastPage

                DataList1.DataSource = objPds

                DataList1.DataBind()

                DataList1.RepeatDirection = RepeatDirection.Vertical

                DataList1.RepeatLayout = RepeatLayout.Table

                DataList1.RepeatColumns = 8

                DataList1.BorderWidth = Unit.Pixel(1)

                DataList1.GridLines = GridLines.Both

                holder.Visible = True


                cn.Close()

            Else

                txtSearch6.Text = "Add the text here!!"

            End If

        End Sub 


    </script>


    <html xmlns="http://www.w3.org/1999/xhtml">

    <head runat="server">

        <title></title>

    </head>

    <body>

        <form id="form1" runat="server">

        <div>

        <table>

    <tr>

    <td >

    <asp:Label ID="Label6" runat="server" Text="Keyword:"></asp:Label>

    </td>

    <td >

    <asp:TextBox ID="txtSearch6" runat="server" />

    </td>

    </tr>


    <tr>

    <td align="center" class="style4">

    <div id="center">

    <asp:Button id="btnSearch" runat="server"

    Text    ="Search"

    OnClick ="btnSearch_OnClick"

    /></div>

    </td>

    </tr>


    </table>



    <asp:DataList id="DataList1" runat="server"

                BorderColor="Black"

                CellPadding="3"

                Font-Names="Verdana"

                Font-Size="10pt"

                HeaderStyle-BackColor="#ffffff" BackColor="#ffffff" GridLines="Both"

                

                >

                  <HeaderTemplate>

                    HS1

                  </HeaderTemplate>


    <HeaderStyle BackColor="#000000"></HeaderStyle>

                  <ItemTemplate>

                    Filename-<%#Eval("filename")%> 

                    <a href='pdf/<%# Eval("filename","{0}") %>#nameddest=HS1-<%# Eval("HS1","{0}")%>  ' target="_blank">HS1-<%#Eval("HS1")%> 

                                                        </a>

                  </ItemTemplate>

            </asp:DataList>

            

            

    <table>

            

          <tr>

             <td>  <asp:PlaceHolder ID="holder" runat="server" Visible="false">

             <asp:button id="cmdPrev" runat="server" text=" << " onclick="cmdPrev_Click"></asp:button>

                 <asp:button id="cmdNext" runat="server" text=" >> " onclick="cmdNext_Click"></asp:button></asp:PlaceHolder></td>

          </tr>

          </table>  


        </div>

        </form>

    </body>

    </html>


    The output i m getting is :

    HS1:

    <input id="txtSearch6" value="12-03-09.pdf" type="text" name="txtSearch6">
    <input id="btnSearch" value="Search" type="submit" name="btnSearch">
    HS1
    Filename-12-03-09.pdf HS1-5 4 3 2 1
    <input id="cmdPrev" disabled="" value=" << " type="submit" name="cmdPrev"><input id="cmdNext" disabled="" value=" >> " type="submit" name="cmdNext">


    But i want the output as :

    filename - 12-03-09.pdf   HS1 1

                                            HS1 2

                                            HS1 3

                                            HS1 4

                                            HS1 5

    Can you guide me how to achieve this??

    Thanks!!!




    <%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default"  Debug="true"%>
    <%@ Import Namespace="System.Data" %>
    <%@ Import Namespace="System.Data.Odbc" %>
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <script language="VB" runat="server">
        Public Property CurrentPage() As Integer
            Get
                ' look for current page in ViewState
                Dim o As Object = Me.ViewState("_CurrentPage")
                If o Is Nothing Then
                    Return 0
                Else
                    ' default to showing the first page
                    Return CInt(o)
                End If
            End Get
       
            Set(ByVal value As Integer)
                Me.ViewState("_CurrentPage") = value
            End Set
        End Property
        Private Sub cmdPrev_Click(ByVal sender As Object, ByVal e As EventArgs)
            ' Set viewstate variable to the previous page
            CurrentPage -= 1
       
            ' Reload control
            btnSearch_OnClick(sender, e)
            
            
        End Sub
        Private Sub cmdNext_Click(ByVal sender As Object, ByVal e As EventArgs)
            ' Set viewstate variable to the next page
            CurrentPage += 1
       
            ' Reload control
            btnSearch_OnClick(sender, e)
            
        End Sub
    Sub btnSearch_OnClick(ByVal sender As Object, ByVal e As EventArgs)
            Dim objCommand As OdbcCommand
            
            
            Dim objAdapter As OdbcDataAdapter
            Dim objDataSet As DataSet
            Dim strSearch6 As String
            Dim objConnection As String = "Driver={MySQL ODBC 5.1 Driver};" + "Server=localhost;Database=project;uid=root;"
            Dim cn As New OdbcConnection(objConnection)
            Dim strSQLQuery As String
            
            strSearch6 = txtSearch6.Text
            If (Len(Trim(strSearch6))) > 0 Then
                strSQLQuery = "SELECT filename,GROUP_CONCAT(DISTINCT HS1 ORDER BY HS1 DESC SEPARATOR ' ') AS HS1 FROM nimmy WHERE filename LIKE '%" & Replace(strSearch6, "'", "''") & "%' GROUP BY filename;"
                
                cn.Open()
                 
                Dim cmd As New OdbcCommand(strSQLQuery, cn)
                objCommand = New OdbcCommand(strSQLQuery, cn)
                objAdapter = New OdbcDataAdapter(objCommand)
                objDataSet = New DataSet()
                objAdapter.Fill(objDataSet)
               
                
                Dim objPds As New PagedDataSource()
                objPds.DataSource = objDataSet.Tables(0).DefaultView
                objPds.AllowPaging = True
                objPds.PageSize = 88
                objPds.CurrentPageIndex = CurrentPage
                cmdPrev.Enabled = Not objPds.IsFirstPage
                cmdNext.Enabled = Not objPds.IsLastPage
                DataList1.DataSource = objPds
                DataList1.DataBind()
                DataList1.RepeatDirection = RepeatDirection.Vertical
                DataList1.RepeatLayout = RepeatLayout.Table
                DataList1.RepeatColumns = 8
                DataList1.BorderWidth = Unit.Pixel(1)
                DataList1.GridLines = GridLines.Both
                holder.Visible = True
                cn.Close()
            Else
                txtSearch6.Text = "Add the text here!!"
            End If
        End Sub 
    </script>
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title></title>
    </head>
    <body>
        <form id="form1" runat="server">
        <div>
        <table>
    <tr>
    <td >
    <asp:Label ID="Label6" runat="server" Text="Keyword:"></asp:Label>
    </td>
    <td >
    <asp:TextBox ID="txtSearch6" runat="server" />
    </td>
    </tr>
    <tr>
    <td align="center">
    <div id="center">
    <asp:Button id="btnSearch" runat="server"
    Text    ="Search"
    OnClick ="btnSearch_OnClick"
    /></div>
    </td>
    </tr>
    </table>
    <asp:DataList id="DataList1" runat="server"
                BorderColor="Black"
                CellPadding="3"
                Font-Names="Verdana"
                Font-Size="10pt"
                HeaderStyle-BackColor="#ffffff" BackColor="#ffffff" GridLines="Both"
                
                >
                  <HeaderTemplate>
                    HS1
                  </HeaderTemplate>
    <HeaderStyle BackColor="#000000"></HeaderStyle>
                  <ItemTemplate>
                    Filename-<%#Eval("filename")%> 
                    <a href='pdf/<%# Eval("filename","{0}") %>#nameddest=HS1-<%# Eval("HS1","{0}")%>  ' target="_blank">HS1-<%#Eval("HS1")%> 
                                                        </a>
                  </ItemTemplate>
            </asp:DataList>
            
            
    <table>
            
          <tr>
             <td>  <asp:PlaceHolder ID="holder" runat="server" Visible="false">
             <asp:button id="cmdPrev" runat="server" text=" << " onclick="cmdPrev_Click"></asp:button>
                 <asp:button id="cmdNext" runat="server" text=" >> " onclick="cmdNext_Click"></asp:button></asp:PlaceHolder></td>
          </tr>
          </table>  
        </div>
        </form>
    </body>
    </html>


    Monday, April 26, 2010 1:17 PM
  • User1867929564 posted

    you are very near to your desired output.
    first let me know,thru query do you get the desired output like
    filename    ||     AKA
    12-03-09.pdf    HS1 1
    12-03-09.pdf    HS1  2
    12-03-09.pdf    HS1   3
    12-03-09.pdf    HS1   4

    so on.
    If yes,then its more easy.
    Even if no,then no problem you can manipulate this in front end.
    And did you tried to understand my last post.
    in place of,

    <ItemTemplate>            Filename-<%#Eval("filename")%>     <a href='pdf/<%# Eval("filename","{0}") %>#nameddest=HS1-<%# Eval("HS1","{0}")%>  ' target="_blank">HS1-<%#Eval("HS1")%    </a>  </ItemTemplate>


    write,
    <asp:TemplateColumn HeaderText="Filename" >
                            <ItemTemplate>
                                <%# CreateLink(Eval("filename", "{0}"), Eval("AKA/HS1", "{0}"))%>
                            </ItemTemplate>
                          
                        </asp:TemplateColumn>
    where in cs file,there is function called(this very rough),
    protected string CreateLink(string filename, string AKA_HS1)
            {
                string StrReturn = "";
     //string manipulation if required,say for one filename there can 4,5 or more AKA_HS1(count in,then while loop)
     //also if link requreied the  
                try
                {
     StrReturn = "<table><tr><td>" + filename + "</td><td><a href='#' class='classname'>" +AKA_HS1 + "</a></td></tr>";
     while(condition)
    {
                    StrReturn = "<table><tr><td></td><td><a href='#' class='classname'>" +AKA_HS1 + "</a></td></tr>";
    }
     StrReturn=StrReturn+"</table>";
                    return StrReturn;
                }
                catch (Exception ex)
                {
                    Response.Write(ex.Message);
                    return StrReturn;
                }
            }

    you are done.Before this take one simple example and try to understand i want to say.
    for this i am giving you  link .If you fully understand to use the function then you can solve your problem,

    http://msdn.microsoft.com/en-us/library/aa479353.aspx

    http://aspalliance.com/782_CodeSnip_How_to_Display_Sum_Total_in_the_Footer_of_the_GridView_Control

    any problem ask.

    Tuesday, April 27, 2010 12:28 AM
  • User826222609 posted

    DECLARE @Table TABLE (FileName VARCHAR(20), AKA INT)

    INSERT INTO @Table VALUES ('12-3-09.pdf', 443567)
    INSERT INTO @Table VALUES ('12-3-09.pdf', 345678)
    INSERT INTO @Table VALUES ('12-3-09.pdf', 456789)
    INSERT INTO @Table VALUES ('3-3-10.pdf', 78901)

    SELECT
        DISTINCT FileName
        , (
            STUFF((
                    SELECT ',' + CAST( B.AKA AS VARCHAR )
                    FROM @Table B
                    WHERE B.FileName = A.FileName
                    FOR XML PATH('')
                ),1,1,'')
          ) AS AKA
    FROM @Table AS A

    Tuesday, April 27, 2010 12:45 AM
  • User1523385712 posted

    Hi Harsh,


    I m not getting the desired output from sql query.

    I m getting it as 

    filename    ||     AKA
    12-03-09.pdf    HS1 1 HS1 2 HS1 3 HS1 4 HS1 5

    If i could get in the query itself then they wouldn't have been any prob in solving it.

    And the function that u have given me gives me the same output as the previous output i m not getting the desired output.


    And more over the i wanted ahref tag to be the combination of my filename n HS1.

    something like this : <ahref=pdf/'filename'#nameddest=HS1-'AKA_HS1'></a>

    filename and AKA_HS1 are the output result of the query that should be passed through to the a href tag.

    And i couldn't understand the logic also.

    my desired output is :

    one filename containing my HS1's

    filename    ||     AKA
    12-03-09.pdf    HS1 1

                             HS1 2

                             HS1 3

                              HS1 4

                              HS1 5

    The HS1's link's are combination of filename and HS1.


    Thanks!

    Tuesday, April 27, 2010 10:20 AM
  • User1867929564 posted

    All these time i was thinking that query problem is solved.
    Anyway kindly once again post table structure with few diffrent data samples.
    Like your first post data do not match with your present post data.
    do post few diffrent data sample.
    Like,

    filename    ||     AKA
    12-03-09.pdf    HS1 1 HS1 2 HS1 3 HS1 4 HS1 5
    13-03-09.pdf     *
    13-04-10.pdf     *

    *=wht kind of data there ?first lets

    with your existing data it appear that you can still manipulate the string,but since i hv'nt seen other data sample like then I am not sure.

    Try to seperate this with comma( in qry itself),like HS1 1,HS1 2,HS1 3,HS1 4,HS1 5.

    suppose you get this then,
    string buffer="HS1 1 ,HS1 2, HS1 3, HS1 4, HS1 5";
     string [] bufferlist;
      bufferlist = buffer.Split(new char[] { ', ' });
    debug and check bufferlist

    Finally,just post table structure and few distinct data samples(at least 5).
    Then only we can think which approach to apply.

    Wednesday, April 28, 2010 12:29 AM
  • User1523385712 posted

    Hi Harsh,


    The Table Structure is as Follows:


    Create Table nimmy (  filename varchar(13),

                                          HS1      varchar(10)

                                       );

                                        


    Filename         HS1

    12-03-09.pdf   12345

    12-03-09.pdf   456789

    12-04-09.pdf  67890

    12-04-09.pdf  23456

    12-05-09.pdf  40987


    The Output Result :

    filename               HS1

    12-03-09.pdf        12345

                                 456789

     12-04-09.pdf        23456

                                  67890

    12-05-09.pdf         40987

    By using group_contact function in MYSQl i have got the output as follows


    filename          HS1

    12-03-09.pdf  1234,456789

    12-04-09.pdf  23456,67890

    12-05-09.pdf  40987

    Right now i wanted to mingled HS1's to seperated.I wanted each one of it as a seperate string.Not as a combined  strings.

    That's wat i Need right now.

    Thanks!!




    Wednesday, April 28, 2010 1:33 PM
  • User1867929564 posted

     First,did you followed the link i gave you,where you able to implement any example ?
    Second with your existing result also you can achieve your output. 

    filename          HS1

    12-03-09.pdf  1234,456789

    12-04-09.pdf  23456,67890

    12-05-09.pdf  40987

    ,
    i gave you example of array and spliting it with coma.i.e.
    bufferlist = buffer.Split(new char[] { ', ' });

    Now it all about using proper loop inside createlink function.

    Thirdly,one of my query

    create table #temptbl
    (
    filename varchar (50),
    HS1 varchar (50)
    )
    insert into #temptbl values('12-03-09.pdf','12345')
    insert into #temptbl values('12-03-09.pdf','456789')
    insert into #temptbl values('12-04-09.pdf','67890')
    insert into #temptbl values('12-04-09.pdf','23456')
    insert into #temptbl values('12-05-09.pdf','40987')

    Select distinct a.filename, a.HS1 from #temptbl a ,#temptbl b
    where a.filename =b.filename

    Don't compromise with your output.Do tell me which way you did it .

     

    Friday, April 30, 2010 12:36 AM
  • User1523385712 posted






    Friday, April 30, 2010 4:34 PM
  • User1523385712 posted

    Hi Harsh,

    I used the second function i.e spilt the string.

    Protected Function CreateLink(ByVal filename As String, ByVal AKA_HS1 As String) As String
            Dim StrReturn As String = ""
            Dim str2 As String = ""
            Dim bufferlist As String()
            
            bufferlist = AKA_HS1.Split(New Char(), ",")
            Dim buffer As String
            
            'string manipulation if required,say for one filename there can 4,5 or more AKA_HS1(count in,then while loop)
            'also if link requreied the
            Try
                For Each buffer In bufferlist
                    StrReturn = ("<table><tr><td>" & filename & "</td><td><a href='#' class='classname'>") + buffer & "</a></td></tr>"
                    
                     
                    'str2 = ("<table><tr><td><a href='pdf/'filename'#nammeddest=HS1- 'buffer' ' target='_blank'>") + buffer & "</a></td></tr>"
                    
                    StrReturn = StrReturn & "</table>"
                    'str2 = str2 & "</table>"
                    
                Next
                Return StrReturn
                
               
            Catch ex As Exception
                Response.Write(ex.Message)
                Return StrReturn
            End Try
        End Function

    Protected Function CreateLink(ByVal filename As String, ByVal AKA_HS1 As String) As String

            Dim StrReturn As String = ""

            Dim str2 As String = ""

            Dim bufferlist As String()

            

            bufferlist = AKA_HS1.Split(New Char(), ",")

            Dim buffer As String

            

            'string manipulation if required,say for one filename there can 4,5 or more AKA_HS1(count in,then while loop)

            'also if link requreied the

            Try

                For Each buffer In bufferlist

                    StrReturn = ("<table><tr><td>" & filename & "</td><td><a href='#' class='classname'>") + buffer & "</a></td></tr>"

                    

                     

                    'str2 = ("<table><tr><td><a href='pdf/'filename'#nammeddest=HS1- 'buffer' ' target='_blank'>") + buffer & "</a></td></tr>"

                    

                    StrReturn = StrReturn & "</table>"

                    'str2 = str2 & "</table>"

                    

                Next

                Return StrReturn

                

               

            Catch ex As Exception

                Response.Write(ex.Message)

                Return StrReturn

            End Try

        End Function

    Output i get :

    Keyword: <input id="txtSearch6" value="12-03-09.pdf" type="text" name="txtSearch6">
    <input id="btnSearch" value="Search" type="submit" name="btnSearch">
    HS1
    12-03-09.pdf 1

    <input id="cmdPrev" disabled="" value=" << " type="submit" name="cmdPrev"><input id="cmdNext" disabled="" value=" >> " type="submit" name="cmdNext">

    I dont why but it is iterating to the loop only for one time and exiting.

    and their is another problem too :

    i need to create a ahref as a combination of filename n HS1.something like this 

    <ahref=pdf/'filename'#nammeddest='HS1'></a>.

    How can i make ahref in a function????

    Friday, April 30, 2010 4:59 PM