Answered by:
Need Help one to many relationship between columns

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 -
User1867929564 posted
refer this,
http://www.access-programmers.co.uk/forums/showthread.php?t=64611Thursday, April 1, 2010 3:48 AM -
User1523385712 posted
Hi Harsh,
Function concatproducts(image As String) As StringDim mystring As StringDim rs As RecordsetSet rs = CurrentDb.OpenRecordset("Primary", dbOpenDynaset)With rs.MoveFirstDo Until .EOFIf ![image] = image Thenmystring = mystring & ![AKA/HS1] & ", "End If.MoveNextLoopEnd Withmystring = Left(mystring, (Len(mystring) - 2))concatproducts = mystringEnd FunctionFunction 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.
ThanksThursday, 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 whilewrite 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 OleDbCommandDim objAdapter As OleDbDataAdapterDim objDataSet As DataTableDim strSearch As StringDim strSearch2 As StringDim strSearch3 As StringDim strSearch4 As StringDim strSearch5 As StringDim strSearch6 As StringDim rs As New ADODB.RecordsetDim rs1 As New ADODB.RecordsetDim strimage As StringDim strHSA As StringDim 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 StringDim strSQLQuery2 As StringstrSearch = txtSearch.TextstrSearch2 = txtSearch2.TextstrSearch3 = txtSearch3.TextstrSearch4 = txtSearch4.TextstrSearch5 = txtSearch5.TextstrSearch6 = txtSearch6.TextIf (Len(Trim(strSearch)) & Len(Trim(strSearch2)) & Len(Trim(strSearch3)) & Len(Trim(strSearch4)) & Len(Trim(strSearch5)) & Len(Trim(strSearch6))) > 0 Thenrs.Open("Primary", cn)rs1.Open("Primary", cn)cn.Open()strSQLQuery = "select distinct [image] from [Primary]"While Not rs.EOFstrHSA = ""strSQLQuery2 = "Select [AKA/HS1] from [Primary] where [image]=strimage"While Not rs1.EOFstrHSA = strHSA + rs1[[AKA/HS1]]End WhileEnd WhileDim 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 = objDataSetdgPaging.DataBind()cn.Close()ElsetxtSearch.Text = "Add the text here!!"End IfEnd SuFriday, 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 IntegerGet' look for current page in ViewStateDim o As Object = Me.ViewState("_CurrentPage")If o Is Nothing ThenReturn 0Else' default to showing the first pageReturn CInt(o)End IfEnd GetSet(ByVal value As Integer)Me.ViewState("_CurrentPage") = valueEnd SetEnd PropertyPrivate Sub cmdPrev_Click(ByVal sender As Object, ByVal e As EventArgs)' Set viewstate variable to the previous pageCurrentPage -= 1' Reload controlbtnSearch_OnClick(sender, e)End SubPrivate Sub cmdNext_Click(ByVal sender As Object, ByVal e As EventArgs)' Set viewstate variable to the next pageCurrentPage += 1' Reload controlbtnSearch_OnClick(sender, e)End SubSub btnSearch_OnClick(ByVal sender As Object, ByVal e As EventArgs)Dim objCommand As OdbcCommandDim objAdapter As OdbcDataAdapterDim objDataSet As DataSetDim strSearch6 As StringDim objConnection As String = "Driver={MySQL ODBC 5.1 Driver};" + "Server=localhost;Database=project;uid=root;"Dim cn As New OdbcConnection(objConnection)Dim strSQLQuery As StringstrSearch6 = txtSearch6.TextIf (Len(Trim(strSearch6))) > 0 ThenstrSQLQuery = "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).DefaultViewobjPds.AllowPaging = TrueobjPds.PageSize = 88objPds.CurrentPageIndex = CurrentPagecmdPrev.Enabled = Not objPds.IsFirstPagecmdNext.Enabled = Not objPds.IsLastPageDataList1.DataSource = objPdsDataList1.DataBind()DataList1.RepeatDirection = RepeatDirection.VerticalDataList1.RepeatLayout = RepeatLayout.TableDataList1.RepeatColumns = 8DataList1.BorderWidth = Unit.Pixel(1)DataList1.GridLines = GridLines.Bothholder.Visible = Truecn.Close()ElsetxtSearch6.Text = "Add the text here!!"End IfEnd 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 ATuesday, 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 5If 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 1HS1 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 bufferlistFinally,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 querycreate 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 StringDim 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 theTryFor Each buffer In bufferlistStrReturn = ("<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>"NextReturn StrReturnCatch ex As ExceptionResponse.Write(ex.Message)Return StrReturnEnd TryEnd FunctionProtected 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