|
Hi,
I have a question regarding a datagridview and an access database.
With my current application I can select a database, make a connection all the tables are shown into a combobox. With this combobox I can select a table and the columns of this table are listed in a listbox1. From listbox1 I can select 1 or more columns and put them in listbox2.
The columns listed in listbox2 i want to add to the datagridview.
The reason for this, is that i want to make a selection which columns I want to use.
The database could be any database, so the whole thing is rather dynamic, therefor I cannot not use fixed values.
If somebody can help out on this one I would be very thankfull.
kind regards.
Heng
|
|
|
|
|
Member 12822573 wrote: If somebody can help out on this one I would be very thankfull. Help in what way? You have explained what you want to do, but not what your problem is.
|
|
|
|
|
Hi,
Well the problem is that with the code i used, I see the records, but the data is not in line.
For example:
I have 2 columns ID and Test. Both columns have e.g. 2 records (or rows)
Normal you would expect to see two columns and two rows.
I my case i see 2 columns and 4 rows, the first two rows of columns 2 are empty.
I know this has something to with the way i have program it, but I don't know the correct way.
Herewith my code:
Private Sub btnShowData_Click(sender As Object, e As EventArgs) Handles btnShowData.Click
Dim selectFile As String = txtSelectFile.Text
Dim selectTable As String = cmbSelectTable.Text
con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & selectFile
Try
con.Open()
Dim ds As New DataSet
Dim dt As New DataTable
Dim da As New OleDbDataAdapter
For Each Item As Object In lbxSelectItems.Items
da = New OleDbDataAdapter("Select " & Item & " from " & selectTable, con)
da.Fill(dt)
Next
DGVselectedTable.DataSource = dt.DefaultView
con.Close()
Catch ex As Exception
MsgBox("Error: " & ex.Message)
End Try
End Sub
I hope it is clear, basically I was hoping for a good example or code.
thanks
kind regards,
heng
|
|
|
|
|
Because you are selecting items from each column one at a time instead of all together. You should first build your SELECT statement with the column names from all the selected items in the listbox. You can then make one database call which will read all the items from the selected records. Something like:
Dim cols as StringBuilder
For Each Item As Object In lbxSelectItems.Items
cols.Append(Item.ToString + ", ")
Next
Dim rep = cols.Length
cols.Remove(rep-2, 2)
da = New OleDbDataAdapter("Select " & cols.ToString & " from " & selectTable, con)
da.Fill(dt)
I apologise for any syntax errors in the above; I am not a VB expert.
|
|
|
|
|
Thanks!
As far as i have tested it now it works.
I have used the same code, no errors.
You mentioned that your not an expert, but much better than i am.
thanks again
rgds
heng
|
|
|
|
|
|
I'm trying to work with 3 progress bars where one fill another. Is associated to treenodes in treeview. When research precess is completed with each nodes at certain level, need be filling the progressbar associated to parent node. But I cant make to work correctly. I need help to understand the logic.
Private Sub AtualizaSubcategorias(ByVal execucao As BackgroundWorker)
Dim pbar_step_n2 As Double = 0, pbar_total_n2 As Double = 0
Dim pbar_step_n3 As Double = 0, pbar_total_n3 As Double = 0
Dim pbar_step_n4 As Double = 0, pbar_total_n4 As Double = 0
Dim pbar_alvo As String = "", menos3 As String
Dim tree_temp As New TreeView
Dim n As New TreeNode
n = tree_menu.Nodes(0).Clone
tree_temp.Nodes.Add(n)
pbar_alvo = "n2"
menos3 = pbar_alvo & "|@|" & "100"
execucao.ReportProgress(-3, menos3)
pbar_total_n2 = 0
pbar_step_n2 = (pbar_n2.Maximum / categoriasPARAatualizar.Count)
For Each n2 As TreeNode In tree_temp.Nodes(0).Nodes
If (execucao.CancellationPending = True) Then Exit For
If categoriasPARAatualizar.Contains(n2.Tag.ToString) Then
Dim catTitulo = Split(n2.Text.ToString, "{")
execucao.ReportProgress(-1, catTitulo(0).ToUpper)
Capturador(n2.Tag.ToString, 2, n2)
If pbar_total_n2 >= pbar_n2.Maximum Then
execucao.ReportProgress(pbar_n2.Maximum, pbar_alvo)
Else
End If
pbar_alvo = "n3"
If n2.Nodes.Count = 0 Then
menos3 = pbar_alvo & "|@|" & categoriasPARAatualizar.Count
execucao.ReportProgress(-3, menos3)
execucao.ReportProgress(pbar_n3.Maximum, pbar_alvo)
pbar_total_n2 = pbar_total_n2 + pbar_step_n2
Else
menos3 = pbar_alvo & "|@|" & n2.Nodes.Count
execucao.ReportProgress(-3, menos3)
pbar_total_n3 = 0
pbar_step_n3 = (pbar_step_n2 / n2.Nodes.Count)
For Each n3 As TreeNode In n2.Nodes
If (execucao.CancellationPending = True) Then Exit For
Capturador(n3.Tag.ToString, 2, n3)
If pbar_total_n3 >= pbar_n3.Maximum Then
execucao.ReportProgress(pbar_n3.Maximum, pbar_alvo)
pbar_total_n2 = pbar_total_n2 + pbar_step_n2
Else
End If
pbar_alvo = "n4"
If n3.Nodes.Count = 0 Then
menos3 = pbar_alvo & "|@|" & "100"
execucao.ReportProgress(-3, menos3)
execucao.ReportProgress(pbar_n4.Maximum, pbar_alvo)
pbar_total_n3 = pbar_total_n3 + pbar_step_n3
Else
menos3 = pbar_alvo & "|@|" & n3.Nodes.Count
execucao.ReportProgress(-3, menos3)
pbar_total_n4 = 0
pbar_step_n4 = (pbar_step_n3 / n3.Nodes.Count)
For Each n4 As TreeNode In n3.Nodes
If (execucao.CancellationPending = True) Then Exit For
Capturador(n4.Tag.ToString, 2, n4)
pbar_total_n4 = pbar_total_n4 + pbar_step_n4
If pbar_total_n4 >= pbar_n4.Maximum Then
execucao.ReportProgress(pbar_n4.Maximum, pbar_alvo)
pbar_total_n3 = pbar_total_n3 + pbar_step_n3
Else
execucao.ReportProgress(pbar_total_n4, pbar_alvo)
End If
Next
End If
Next
End If
End If
Next
execucao.ReportProgress(-2, tree_temp)
End Sub
Private Sub subcategorias_BckGrndWorker_ProgressChanged(sender As Object, e As ProgressChangedEventArgs) Handles subcategorias_BckGrndWorker.ProgressChanged
If e.ProgressPercentage < 0 Then
Select Case e.ProgressPercentage
Case -1
lbl_categoria.Text = e.UserState
Case -2
tree_menu.Nodes.Clear()
tree_menu = e.UserState
Panel1.Controls.Add(tree_menu)
tree_menu.Dock = DockStyle.Fill
tree_menu.ExpandAll()
Case -3
Dim splt = Split(e.UserState, "|@|")
If UCase(splt(0)) = "N2" Then
pbar_n2.Maximum = Int(splt(1))
ElseIf UCase(splt(0)) = "N3" Then
pbar_n3.Maximum = Int(splt(1))
ElseIf UCase(splt(0)) = "N4" Then
pbar_n4.Maximum = Int(splt(1))
End If
End Select
End If
If e.ProgressPercentage > 0 Then
Select Case UCase(e.UserState)
Case "N2"
pbar_n2.Value = e.ProgressPercentage
If pbar_n2.Value = pbar_n2.Maximum Then pbar_n2.Value = 0
Case "N3"
pbar_n3.Value = e.ProgressPercentage
If pbar_n3.Value = pbar_n3.Maximum Then pbar_n3.Value = 0
Case "N4"
pbar_n4.Value = e.ProgressPercentage
If pbar_n4.Value = pbar_n4.Maximum Then pbar_n4.Value = 0
End Select
End If
End Sub
|
|
|
|
|
Instead of saying "I can't make to work correctly" can you be more precise with what is happening?
|
|
|
|
|
I have a menu with categories and subcategories structured as follows:
N1
|___ N2
|___ N3
|___ N4
The routine walks each item in N1 and checks for subitems(N2). If there is, go through each item N2 and check for subitems(N3). If there is, go through each item N3 and check for sub items(N4).
I am trying to implement an function like when unpacking a file, where there is a progressbar that shows the total progress (main) and another progressbar that shows the progress of each file being extracted. When each file is extracted and your progressbar reaches 100%, fills X% of the main progressbar.
N4 fills the N3 PBar -> N3 fills the N2 Pbar and N2 fills the N1(main) Pbar.
For Each N2 As TreeNode In N1.Nodes
For Each N3 As TreeNode In N2.Nodes
For Each N4 As TreeNode In N3.Nodes
Picture example
I am having difficulty defining the logic with which these Pbar work together.
Was I clear?
Thankyou for your reply.
|
|
|
|
|
I'm trying to change listview image at runtime, with this code:
Sub update_available_room()
Try
lvroom.BeginUpdate()
For Each L As ListViewItem In lvroom.Items
Dim mycommand As MySqlCommand = conn.open.CreateCommand
Dim query1 = "SELECT room.*,roomtype.* FROM room, roomtype WHERE room.RoomType=roomtype.RoomType AND RoomNo='" & L.ImageKey & "'"
mycommand.CommandText = query1
Dim baca1 As MySqlDataReader = mycommand.ExecuteReader
If baca1.Read() Then
Dim NoKamar As String = baca1("RoomNo")
Dim str As String
If Len(NoKamar) = 1 Then
str = "00" & NoKamar
ElseIf Len(NoKamar) = 2 Then
str = "0" & NoKamar
Else
str = NoKamar
End If
If baca1("Available") = True And baca1("Status") = False Then
L.ImageList.Images.RemoveByKey(L.ImageKey)
Dim newbitmap As Bitmap = My.Resources.bulat_hijau
Dim g As Graphics
g = Graphics.FromImage(newbitmap)
g.DrawString(str, New Font("Arial", 65, FontStyle.Bold), New SolidBrush(Color.Yellow), New Point(85, 50))
g.DrawString("______________________", New Font("Arial", 65, FontStyle.Bold), New SolidBrush(Color.White), New Point(0, 80))
g.DrawString(baca1("Singkatan"), New Font("Arial", 65, FontStyle.Bold), New SolidBrush(Color.White), New Point(82, 170))
L.Text = "EMPTY"
L.Font = New Font("Arial", 12, FontStyle.Bold)
L.ImageList.Images.Add(NoKamar, newbitmap)
ElseIf baca1("Available") = False And baca1("Status") = False Then
L.ImageList.Images.RemoveByKey(L.ImageKey)
Dim newbitmap As Bitmap = My.Resources.bulat_purple
Dim g As Graphics
g = Graphics.FromImage(newbitmap)
g.DrawString(str, New Font("Arial", 65, FontStyle.Bold), New SolidBrush(Color.Yellow), New Point(85, 50))
g.DrawString("______________________", New Font("Arial", 65, FontStyle.Bold), New SolidBrush(Color.White), New Point(0, 80))
g.DrawString(baca1("Singkatan"), New Font("Arial", 65, FontStyle.Bold), New SolidBrush(Color.White), New Point(82, 170))
L.Text = "N/A"
L.Font = New Font("Arial", 12, FontStyle.Bold)
L.ImageList.Images.Add(NoKamar, newbitmap)
End If
End If
baca1.Close()
lvroom.Refresh()
Next
conn.close()
lvroom.EndUpdate()
Catch ex As Exception
MsgBox(ex.Message)
Finally
conn.close()
End Try
End Sub
but I am getting err:
Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index
|
|
|
|
|
Where do you get the error?
|
|
|
|
|
You already posted it here[^].
We can't tell you why this error came up because we don't have the data that this code is working against. I've told you how to debug this yourself because you're the only one who's going to be able to do it.
And I've already told you about the pile of resource leaks you have in this code.
|
|
|
|
|
|
I'm having a small issue wrapping my head around why this isn't working.
I have a file in my project called CollectionClasses.vb - within the Solution Explorer it's located in ProjectBase\Utilities\CollectionClasses.vb. Within that file, I've got classes that do not have Namespaces attached to them , they look like this.
Public Class LinerTypesCollection
Inherits System.Collections.ObjectModel.ObservableCollection(Of String)
Public Sub New(ByVal IsMetric As Boolean)
End Sub
End Class
Now, I've got a file within my project called StartPanel.vb, it is located ProjectBase\UI\Fitting Panels\Start\StartPanel.vb that has a Partial Class that uses the class defined above
Namespace Base.Controls.Panels
Partial Public Class StartPanel
Public Sub New (ByVal bool as Boolean, Optional ByVal bool2 as Boolean = false)
cmbBox.ItemsSource = New LinerTypesCollection(False)
End Sub
End Class
End Namespace
I am also trying to utilize this LinerTypesCollection class in another part of my software within a Public Class, like so, but unfortunately, I'm stuck in a rut, possibly over something simple, as to trying to figure out why. While trying to use LinerTypesCollection within my second class, it just won't show up. This class is located in the following location ProjectBase\UI\Primatives\ConnectionOptions.vb
Namespace Base.Utilities
Public Class ConnectionOption
Public Sub New (ByVal IsMetric as Boolean)
cmbBox.ItemsSource = New LinerTypesCollection(False)
End Sub
End Class
End Namespace
I have tried to change the "Base.Utilities" to "Base.Controls.Panels" to match and no avail, any ideas?
Thanks!
|
|
|
|
|
In VB.NET, classes with no namespace reside in the default namespace for the project.
Since your project is called ProjectBase, that's probably what the default namespace is set to. But you'll need to check the project properties to be sure.
How to: Change the Namespace for an Application (Visual Basic)[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Richard,
Thanks for replying!
That was my one of my initial assumptions as well, however; I have tried to do the following with my second class..
Namespace Base.Utilities
Public Class ConnectionOption
Public Sub New (ByVal IsMetric as Boolean)
cmbBox.ItemsSource = New ProjectBase.LinerTypesCollection(False)
End Sub
End Class
End Namespace
I have confirmed that the Root Namespace of the Project matches the what I'm trying to use, but alas, no avail. Intellisense won't pick it up as visible and I can't build it.
|
|
|
|
|
Try looking at your project in the Object Browser[^] to see if you can track down the class.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Richard,
I feel like a moron, but I knew it was something simple. Looking in the Object Browser as you recommended, it became clear that the files I was looking at were NOT in the same project.
They bled together pretty well in the Solution Explorer.
Regardless, thank you for taking the time to reply to my messages!
|
|
|
|
|
How to convert this codes to C# from VB6:
Hide Copy Code
Call rs.Open("SELECT DISTINCT Reci FROM " & CStr(duzina), Conn, adOpenKeyset)
rs.Move getRandom(0, rs.RecordCount - 1)
rec = rs.Fields(0).Value
Set rs = Conn.Execute("SELECT DISTINCT Reci FROM " & CStr(Len(r)))
|
|
|
|
|
|
I am using Access database!!!
|
|
|
|
|
|
|
It's both sad and frightening that this comes up so much on coding forums when people post their SQL code. +1 for fighting the good fight. I see it so much I'm almost numb to it now.
|
|
|
|
|
You would open a connection, then a issue a command against that connection, and then retrieving the results.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|