Combobox Add items from database

[mavtcr]

I have gone through the articles appeared in this forum on the above Area.But it didn't helped me much,As I am new to VB.net.
In VB6 I have the following code and it works well.

Private Sub CmbBank_GotFocus()
 CmbBank.BackColor = vbYellow
 LblMsg = "Select Bank"
    Dim A, B, C As Integer
 CmbBank.Clear
 CmbBank.Text = "SELECT BANK NAME"
 Set RS = New ADODB.Recordset
 If RS.State = adStateOpen Then RS.Close
RS.Open "SELECT * FROM Receipts", CN, adOpenStatic, adLockOptimistic
   If TxtMode = "A" Then
     Do Until RS.EOF
   CmbBank.AddItem RS!Bank
  RS.MoveNext
   Loop
     Else
   Do Until RS.EOF
   A = Len(Left(RS!Bank, 25))
   A = 25 - A
   B = Len(Left(RS!Acno, 16))
   B = 16 - B
   C = Len(Left(RS!Amount, 10))
   C = 10 - C
  CmbBank.AddItem RS!Bank & Space(A) & Space(1) & (RS!Acno) & Space(B) & Space(1) & (RS!Amount)
  RS.MoveNext
 Loop
  End If
End Sub

The combo box is filled with three columns of the Table-Bank,Acno,Amount
When it comes to VB.net What will be the code?
Any one please help me..

[Minimalist]

You do need to learn the syntax of the net framework. Here is good start of how to handle the combobox methods:
http://msdn.microsoft.com/en-us/library/system.windows.forms.combobox(v=vs.110).aspx

[mavtcr]

Thank you minimalist.. Many times you helped me much..thank you
I tried the following code.It works,but only one record is added as item in the combobox.I want all the records from the table-Receipts.Will you help me with the necessary code.

        CN.Open()
        SQL = "SELECT Bank,Acno from Receipts"
        DA = New OleDb.OleDbDataAdapter(SQL, CN)
        DA.Fill(DS, "Deposit")
        If TxtMode.Text = "A" Then
        CmbBank.Items.Add(DS.Tables("Deposit").Rows(0).Item("Bank"))
        End If

[Minimalist]

Well, try the same loop as before and see if it works.

[mavtcr]

I tried But not working. My code is here

 Dim CN As New OleDb.OleDbConnection
 Dim DBProvider As String
 Dim DBSource As String
 Dim DS As New DataSet
 Dim DA As OleDb.OleDbDataAdapter
 Dim SQL As String
 DBProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"
 DBSource = "Data Source=C:\Users\bennet\Documents\Visual Studio 2012\Projects\MavDeposit\MavDeposit\Deposit.mdb"
 CN.ConnectionString = DBProvider & DBSource
 CN.Open()
 SQL = "SELECT Bank,Acno from Receipts"
 DA = New OleDb.OleDbDataAdapter(SQL, CN)
 DA.Fill(DS, "Deposit")
 If TxtMode.Text = "A" Then
 Do Until DS.EOF
 CmbBank.Items.Add(DS.Tables("Deposit").Rows(0).Item("Bank"))
 DS.MoveNext()
 Loop
 End If

The following error is shownError1'EOF' is not a member of 'System.Data.DataSet'.Error2'MoveNext' is not a member of 'System.Data.DataSet'.

[cgeier]

This post may help you: Click Here

[Minimalist]

O.K here are 2 different approaches: The first from the last link:

Dim rdr As OleDbDataReader = cmd.ExecuteReader()
        Do While rdr.Read
            lvwResults.Items.Add(New ListViewItem({rdr("au_lname"), rdr("au_fname"), rdr("phone")}))
        Loop

and the other I am using at the moment:

 oledbAdapter.SelectCommand = New OleDb.OleDbCommand(statement.CommandText, conn)
            oledbAdapter.Fill(ds)
            oledbAdapter.Dispose()
            conn.Close() 'only retruns blocknames

                For i = 0 To ds.Tables(0).Rows.Count - 1
                    str = CStr((ds.Tables(0).Rows(i).Item(0)))
                    TBlocks.ComboBox1.Items.Add(str)
                Next

[mavtcr]

Dear .. Your code confuses me..Will you modify my code and show me.I tried the second one,Then on line 1.statement,commandtext,line 7.str,line 8.TBlocks .. At these places error is shown.
Excuse my beginner stage...

[Minimalist]

Obviously you just can't post my code into ypur program. You need to modify it so it does suit your databse. O.K. I give it shot. If it works it will only return one column which shoiuld be printed in zhe direct window with the debug.print command. This is a great help to debug and to see from where your progam goes wrong. Alos remember the .item(0).item(1).item(2) etc.

Dim CN As New OleDb.OleDbConnection
 Dim DBProvider As String
 Dim DBSource As String
 Dim DS As New DataSet
 Dim DA As OleDb.OleDbDataAdapter
 Dim SQL As String
 DBProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"
 DBSource = "Data Source=C:\Users\bennet\Documents\Visual Studio 2012\Projects\MavDeposit\MavDeposit\Deposit.mdb"
 CN.ConnectionString = DBProvider & DBSource
 CN.Open()
 SQL = "SELECT Bank,Acno from Receipts"
 DA = New OleDb.OleDbDataAdapter(SQL, CN)
 DA.Fill(DS)
 DA.dispose
 CN.close
 Dim Iint as Integer = 0
 Dim str as String
 If TxtMode.Text = "A" Then
 For Iint = 0 to ds.Tables(0).Rows.Count - 1
 str= Trim(Cstr(DS.Tables(0).Rows(Iint).Item(0)))
 'CmbBank.Items.Add(DS.Tables(0).Rows(Iint).Item(0))
 Debug.Print(str)
 Next
 End If

are your columns in the record

[mavtcr]

Dear Minimalist..It is wonderful it works.. but I have few more doubts
1.When I repeat the operation before exiting from the form, CmbBank is filled with repetition of earlier items ie.Each time the items are doubled.Before cmbbank.items.add ,the cmbbank should be cleared of existing items.I tried,but couldn't succeed
2.I want to fill the combo cmbbank with three columns-Bank,Acno,Amount.On KeyDown the first column will go to textbox1,second to textbox2. That I succeeded.But my problem is the text of first column is not of uniform width-First row contains 25 characters,while second contains only 5 characters like that.So when I select the first record It works perfectly.But when I select the second one the text of two columns are gone to the textbox1 itself and the textbox2 becomes blank.In VB6 it works perfectly.See my code in earlier posting.I expect my problem is clear to you.is there any way out...My code is here

For Iint = 0 To DS.Tables(0).Rows.Count - 1
str = CmbBank.Items.Add((DS.Tables("Deposit").Rows(Iint).Item("Bank")) & " " & DS.Tables("Deposit").Rows(Iint).Item("Acno"))
Debug.Print(str)
Next
End If
Private Sub CmbBank_KeyDown(sender As Object, e As KeyEventArgs) Handles CmbBank.KeyDown
TxtBank.Focus()
TxtBank.Text = Microsoft.VisualBasic.Left(CmbBank.Text, 25)
TxtAcno.Text = Mid(CmbBank.Text, 25, 17)
CmbBank.Visible = False
End Sub

Any way thank you.....

[Minimalist]

First you should clear your combo:
CmbBank.Items.clear
Than you build your string:

str = ((DS.Tables("Deposit").Rows(Iint).Item("Bank")) & " " & DS.Tables("Deposit").Rows(Iint).Item("Acno"))

than you add this to your combo:

CmbBank.Items.Add(str)

Also I would think about to use the split string method instead of using Left() and Mid() functions. To the length of your strings - 25, 5 I can't say anything because I don't know the text that is in there. Left(text,25) returns the first 25 characters of the text and Mid(text,25,17) is wrong.

[mavtcr]

Thank you Minimalist.. The first portion is OK.Regarding the second portion..I was just trying to use the code of VB6 in VB.net.See my first posting above.It works well in VB6.We can fix the width of the column,whether it contains 5 characters or maximum characters. When the characters are less the balance is filled with spaces.The second column starts from 26 th character(After the maximum of the first column).But here it is not working. Is there any way to fix it?.

[Minimalist]

Position 25 is either the last letter of the first string or an empty space. Starting with the next string at position 25 gives you either the last letter of the first string or string2 starts with an empty space. So before you work out the length of your strings use Trim(text1) and Trim(text2) to get rid of empty spaces or start text2 t position 26.

[mavtcr]

Dear .. I tried it.But no success.The problem remains with Populating items in the combobox.When there are several records in the table the combobox is in a Zigzag manner. The columns are not aligned vertically correctly,because in the first column,for different records, width is different.My table in the database has three columns-1.Bank-max 25 characters 2.Acno-maximum 15 chars 3.Amount-max 10 chars.Suppose I have three records in the table.The first one first column has 25 chars,the second one first column 5 chars,third first column 20 chars.When these records are populated in the combobox in the second row secondcolumn starts from 6th position,in the third row second column starts from 21 st position there by the combo looking in a zigzag manner.
I think my problem is clear to you.If you make a small project of your own,while considering all my above postings,It will be more clear.Being an experienced person I am sure you will get a solution for me. Thank you.

[Minimalist]

If your code worked well in vb6 than it means you would have used a mono spaced font, otherwise you would not have been able to line colums as you would like? A mono spaced font gives the same space to every charcter, that means a "W" takes as much space as an "I". In not mono spaced fonts these charcters take up different space and you won't be able to line up the text exactly. Check out the vbTab function: Dim text As String = "abc".Insert(1, vbTab) or check out the more modern way here:
http://msmvps.com/blogs/deborahk/archive/2009/07/21/formatting-text-files.aspx
http://www.dotnetperls.com/string-format-vbnet
If this doen't help I will write a small project and check it out.

[Minimalist]

O.K., using the examples from vb.net it is very easy to format columns, but you need to set the font of the combobox to a mono spaced font. I used Courier New. This is all the code you need:

Public Class Form1

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Dim str1 As String = "WWWWWWWWWWWWWWW"
        Dim str2 As String = "Window"
        Dim str3 As String = "OnceAndTwice"
        Dim str4 As String = "Hello"
        Dim str5 As String = "one"
        Dim str6 As String = "Onehundredthousand"
        Dim format As String = "{0,-20} {1,-20} {2,-20}"
        Dim line1 As String = String.Format(format, str1, str2, str3)
        Dim line2 As String = String.Format(format, str5, str6, str2)
        Dim line3 As String = String.Format(format, str3, str5, str1)

        ComboBox1.Items.Add(line1)
        ComboBox1.Items.Add(line2)
        ComboBox1.Items.Add(line3)
    End Sub
End Class

andtheresult looks like on the jpeg.

[mavtcr]

Wonderful.... Dear Mininmalist I got it. Your posting helped me much.. I have no words to thank you. Thank you..Thank you Thank you..

 Private Sub CmbBank_GotFocus(sender As Object, e As EventArgs) Handles CmbBank.GotFocus
 CmbBank.BackColor = Color.Yellow
 LblMsg.Text = "Select Bank Name"
 Dim CN As New OleDb.OleDbConnection
 Dim DBProvider As String
 Dim DBSource As String
 Dim DS As New DataSet
 Dim DA As OleDb.OleDbDataAdapter
 Dim SQL As String
 DBProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"
 DBSource = "Data Source=C:\Users\bennet\Documents\Visual Studio 2012\Projects\MavDeposit\MavDeposit\Deposit.mdb"
 CN.ConnectionString = DBProvider & DBSource
 CN.Open()
 SQL = "SELECT *  from Receipts"
 DA = New OleDb.OleDbDataAdapter(SQL, CN)
 DA.Fill(DS, "Deposit")
 DA.Dispose()
 CN.Close()
 Dim Iint As Integer = 0
 Dim str As String
 CmbBank.Items.Clear()
 CmbBank.Text = "SELECT BANK NAME"
 If TxtMode.Text = "A" Then
 For Iint = 0 To DS.Tables(0).Rows.Count - 1
 str = (DS.Tables("Deposit").Rows(Iint).Item("Bank")) ' & " " & DS.Tables("Deposit").Rows(Iint).Item("Acno"))
 CmbBank.Items.Add(str)
 Next
 Else
 Dim Format As String = "{0,-26} {1,-17} {2,10}"
 For Iint = 0 To DS.Tables(0).Rows.Count - 1
 str = String.Format(Format, (DS.Tables("Deposit").Rows(Iint).Item("Bank")), (DS.Tables("Deposit").Rows(Iint).Item("Acno")), (DS.Tables("Deposit").Rows(Iint).Item("Amount")))
 CmbBank.Items.Add(str)
 Next
 End If
 End Sub

I think this will be very helpful to new beginners in VB.net