crafuse
asked on
Parse CSV string and perform select & listbox display
Experts,
I have strCardIDs = "1,1,2,2,2,2,3,3,4,1,1,8,8 ,3,3,3,4,4 ,4,4,5,6,7 ,7,7,7,7,7 ,7,8" (this is retrieved from field in db)
These are the IDs of each of the Cards.
I have table Cards that look like this
ID Title
1 CardOne
2 CardTwo
3 CardThree
4 CardFour
5 CardFive
6 CardSix
7 CardSeven
8 CardEight
etc etc
I have a listbox, lstCards
I want to display the following (the card title, and the quantity of each card as a sum of the number of times it's ID appears in the list. Please note, ID of card could appear anywhere in the string. that is, ID 1 could also appear at the end of the string as well as at the beginning, or in the middle):
Title Quantity
CardOne 4
CardTwo 4
CardThree 5
CardFour 5
CardFive 1
CardSix 1
CardSeven 7
CardEight 3
TIA,
crafuse
I have strCardIDs = "1,1,2,2,2,2,3,3,4,1,1,8,8
These are the IDs of each of the Cards.
I have table Cards that look like this
ID Title
1 CardOne
2 CardTwo
3 CardThree
4 CardFour
5 CardFive
6 CardSix
7 CardSeven
8 CardEight
etc etc
I have a listbox, lstCards
I want to display the following (the card title, and the quantity of each card as a sum of the number of times it's ID appears in the list. Please note, ID of card could appear anywhere in the string. that is, ID 1 could also appear at the end of the string as well as at the beginning, or in the middle):
Title Quantity
CardOne 4
CardTwo 4
CardThree 5
CardFour 5
CardFive 1
CardSix 1
CardSeven 7
CardEight 3
TIA,
crafuse
On my code I use specific Hash class for many situations as I use it to much (htCnt, HtSuma, HtAvg).
It is very easy built a class that support Hash if it do´nt need remove method.
on htCnt class, I can use:
MyHtCnt.Add(Value) - I do´nt need worry about value already exists ...
At end, I can access the Data Array (note that it is a public) , Sort It ....
It is very easy built a class that support Hash if it do´nt need remove method.
on htCnt class, I can use:
MyHtCnt.Add(Value) - I do´nt need worry about value already exists ...
At end, I can access the Data Array (note that it is a public) , Sort It ....
Public Class HtCnt(Of T As IComparable(Of T))
Public Structure Node
Implements IComparable(Of Node)
Public Value As T, Cnt As Int32, Nxt As Integer
Public Overrides Function ToString() As String
Return Value.ToString & " : " & Cnt.ToString
End Function
Public Function CompareTo(ByVal other As Node) As Integer Implements System.IComparable(Of Node).CompareTo
Return Value.CompareTo(other.value)
End Function
End Structure
Private ReadOnly Indices() As Integer, IndCount As Integer
Public Data() As Node, Count As Integer
Public Sub New(ByVal size As Integer)
If size < 7 Then size = 7
IndCount = GetPrime(size)
Indices = New Integer(IndCount - 1) {}
Data = New Node(size) {}
End Sub
'0 - en Array Indices es valor inicial, Entrada vacía
Public Sub add(ByVal Value As T)
Dim Index As Int32 = (Value.GetHashCode And &H7FFFFFFF) Mod IndCount
Dim na As Int32 = Indices(Index) - 1, n = na 'n : Bucket / Node.nxt
Do Until n < 0
With Data(n)
If Value.Equals(.Value) Then .Cnt += 1 : Return
n = .Nxt
End With
Loop
If Count >= Data.Length Then Array.Resize(Data, Count << 1)
Data(Count) = New Node() With {.Value = Value, .Nxt = na, .Cnt = 1} : Count += 1
Indices(Index) = Count 'Nueva Entrada - Se Encadena al índice
End Sub
Public Sub addRange(ByVal Values As IEnumerable(Of T))
For Each V In Values : add(V) : Next
End Sub
Public Function Index(ByVal Value As T) As Int32
If Value IsNot Nothing Then
Dim n = Indices((Value.GetHashCode And &H7FFFFFFF) Mod IndCount) - 1
Do Until n < 0
With Data(n) : If Value.Equals(.Value) Then Return n Else n = .Nxt
End With
Loop
End If
Return -1
End Function
Public Sub Clear()
If Count > 1 Then
Array.Clear(Indices, 0, Indices.Length)
Array.Clear(Data, 0, Data.Length) : Count = 0
End If
End Sub
' Public Shared Sub TesthtCnt()
' Dim t = Stopwatch.StartNew
' Dim h As New HtCnt(Of Integer)(100)
' For n = 0 To 1000
' For i = 0 To n
' h.add(i)
' Next
' Next
'Debug.Print(t.Elapsed.ToString)
' For n = 0 To 1000
' If h.Index(n) <> n Then Stop
' If h.Data(n).Cnt <> (1001 - n) Then Stop
' Next
' End Sub
End Class
'This method is comon to all my Hash class.
Private primes() As Integer = {53, 97, 193, 389, 769, 1543, 3079, 6151, 12289, 24593, 49157, 98317, 196613, 393241, 786433, 1572869, 3145739, 6291469, 12582917, 25165843, 50331653, 100663319, 201326611, 402653189, 805306457, 1610612741}
<DebuggerStepThrough()> _
Public Function GetPrime(ByVal size As Integer) As Integer
Dim n As Integer = 0
If size < 0 Then Throw (New Exception("GetPrime - Size < 0"))
size >>= 5 : While (size > 0) : size >>= 1 : n += 1 : End While
Return primes(Math.Min(n, primes.Length - 1))
End Function
From your string, it contains only the value. The text is missing. However you can keep them in a dictionary/List as well.
List<int,string> objList = new List<int,string>();
objList.Add(1,"CardOne");
objList.Add(2,"CardTwo");
and so on...
string[] keys = strCardIDs.split(",".ToCha rArray());
List<int,string> objListRest = new List<int,string>();
int val;
for each (string str in keys )
{
int key = Convert.Toint32(str);
if(objListRest.ContainsKey (key))
{
val = objListRest[key];
val ++;
objListRest[key] = val;
}
else
objListRest[key] = 1;
}
List<int,string> objList = new List<int,string>();
objList.Add(1,"CardOne");
objList.Add(2,"CardTwo");
and so on...
string[] keys = strCardIDs.split(",".ToCha
List<int,string> objListRest = new List<int,string>();
int val;
for each (string str in keys )
{
int key = Convert.Toint32(str);
if(objListRest.ContainsKey
{
val = objListRest[key];
val ++;
objListRest[key] = val;
}
else
objListRest[key] = 1;
}
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Yes. You can form seperate more select queries with ; seperated. This will execute all the statements. NextResultSet() will give successive result set.
Refer http://csharp.net-informations.com/data-providers/csharp-multiple-resultsets.htm
Refer http://csharp.net-informations.com/data-providers/csharp-multiple-resultsets.htm
ASKER
EaswaranP:
thanks for that. could you demonstrate using my code? otherwise, i will look into this tomorrow.
Thanks,
crafuse
thanks for that. could you demonstrate using my code? otherwise, i will look into this tomorrow.
Thanks,
crafuse
Hi!
Please try like this: (see comments also)
Please try like this: (see comments also)
Dim MyArray As New ArrayList()
Dim values() As String = strLibraryList.ToString.Split(",")
Dim int As Integer = 1
Dim total As Integer
MyArray.AddRange(values)
MyArray.Sort()
Dim b As Integer
Dim CardSQL As String
Dim strCardName As String = String.Empty
Dim temp As String = String.Empty 'Declaration ends
For b = 0 To MyArray.Count - 1
If MyArray(b) <> int AndAlso total > 0 Then
temp &= MyArray(b) & "," ' Concatenate IDs in a variable with comma to use in 'IN clause'
total = 1
int = MyArray(b)
Else
total += 1
End If
Next b
temp = temp.TrimEnd(",") ' Remove ',' at the end of the variable.
CardSQL = "select name from merchant where cardno IN (" & temp & ")" ' pass IDs in query ONCE.
Dim conReader1 As New MySqlConnection(strMySQLConnectString)
Dim conString1 As New MySqlCommand(CardSQL, conReader1)
conReader1.Open() ' Open connection once
Dim drReader1 As MySqlDataReader
drReader1 = conString1.ExecuteReader(CommandBehavior.CloseConnection) ' execute command Once
'here datareader has all the name you are willing to popup.
'insert a loop here and read 'drReader1' and display results as you want.
conReader1.Close() ' Close connection once.
ASKER
Shahan_Developer -
I guess this question was not very clear. Your answer neglects a fundamental aspect of the functionality I'm looking for: that each CardName is listed along with that card's quantity, so that the listbox items look like this:
cardone 10
cardforty 3
cardtwentynine 6
cardsix 1
cardonehundred 9
etcetera x
I guess this question was not very clear. Your answer neglects a fundamental aspect of the functionality I'm looking for: that each CardName is listed along with that card's quantity, so that the listbox items look like this:
cardone 10
cardforty 3
cardtwentynine 6
cardsix 1
cardonehundred 9
etcetera x
ASKER
will open a new question to help with passing through my db.
Did you get the logic what I tried to express ?
I don't have further information so i can't test. That was the only reason to add "comments" in my code.
I don't have further information so i can't test. That was the only reason to add "comments" in my code.
ASKER
i suppose i got your logic, yes. what is important, however, is displaying also a count of each cards quantity. populating the listbox with card names derived from a dynamically concatenated string of values is no problem for me, but it's not what i need in this case.
but thank you for your help.
;-)
crafuse
but thank you for your help.
;-)
crafuse
dim sep As Char() = New Char() {","c}
dim cnt as integer
for each v as string in strCardIDs.split(sep,Strin
if myDict.TryGetValue(v,cnt) then
cnt += 1
myDict(v) = cnt
else
MyDict.Add(v,1)
end if
next
Next enumerate the dictionary (Values, Count) to list values.