Auto-populate textbox question

Question

renzlo 4 Junior Poster in Training

12 Years Ago

Hi All,

I have 1 combo box and 1 textbox and an excel file. I need to populate the combo box through the data from my excel file which is EMP_ID and I managed to do that via OleDb.

This is the content of my excel file:

EMP_ID	SPIDOM	        Password
0969	nalburo	        123456
1840	molitan	        123456
GO89	amarasigan	123456
GO91	elubat	        123456
GO93	myocampo	123456
GO95	mtrillana	123456
GU49	ptordecillas	123456
GU50	jalmazan	123456
GU51	jluna	        123456

Now, I wanted my program to do this, when I select the the EMP_ID in my combo box, the textbox will be auto-filled up with the corresponding SPIDOM of the EMP_ID, how can I do that? For example, I've selected 0969 from the combo box, the textbox value will be nalburo.

Here's my current code:

Imports System.Data
Public Class authorization
    Private Sub authorization_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.FormBorderStyle = Windows.Forms.FormBorderStyle.None
        passTextbox.Enabled = False
        Try
            Dim MyConnection As System.Data.OleDb.OleDbConnection
            Dim DtSet As System.Data.DataSet
            Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
            MyConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='tnt.xls';Extended Properties=Excel 8.0;")
            MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection)
            MyCommand.TableMappings.Add("EMP_ID", "EMP_ID")
            DtSet = New System.Data.DataSet
            MyCommand.Fill(DtSet)
            ComboBox1.DataSource = DtSet.Tables(0)
            ComboBox1.DisplayMember = "EMP_ID"
            ComboBox1.SelectedIndex = -1
            MyConnection.Close()
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub
    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Me.Close()
        Me.Dispose()
    End Sub
    Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
        If Trim(ComboBox1.Text) = Nothing Then
            passTextbox.Enabled = False
        Else
            passTextbox.Enabled = True
            passTextbox.Focus()
        End If
    End Sub
    Private Sub ComboBox1_Validating(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles ComboBox1.Validating
        If Trim(ComboBox1.Text) = Nothing Then
            passTextbox.Enabled = False
        Else
            passTextbox.Enabled = True
            passTextbox.Focus()
        End If
    End Sub

End Class

Thanks in advance.

-renzlo

vb.net

3 Contributors
10 Replies
159 Views
1 Day Discussion Span
Latest Post 12 Years Ago Latest Post by renzlo

kingsonprisonic 42 Posting Whiz in Training

12 Years Ago

In ComboBox1_SelectedIndexChanged use a SqlCommand to find the SPIDOM.

Like "Select SPIDOM from table where EMP_ID='"+ComboBox1.Text.Trim+"'"

Now Execute the Command As Scaler

If you Execute it as Scaler you will get the SPIDOM of corresponding EMP_ID..

Now Display it in TextBox..

Simple...

Reverend Jim 4,968 Hi, I'm Jim, one of DaniWeb's moderators.

12 Years Ago

The combobox will be populated with the data in column 1. When an item in the combobox is selected, the corresponding value from column 2 will be displayed in the textbox.

Private Sub ComboBox1_SelectedValueChanged(sender As System.Object, e As System.EventArgs) Handles ComboBox1.SelectedValueChanged

    txtExcel.Text = ComboBox1.SelectedValue.ToString

End Sub

Private Sub btnLoadExcel_Click(sender As System.Object, e As System.EventArgs) Handles btnLoadExcel.Click

    Dim con As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\temp\test.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";")
    con.Open()

    Dim cmd As New OleDb.OleDbCommand("select * from [Sheet1$]", con)
    Dim da As New OleDb.OleDbDataAdapter(cmd)
    Dim dt As New DataTable
   
    da.Fill(dt)

    ComboBox1.DataSource = dt
    ComboBox1.DisplayMember = "EMP_ID"
    ComboBox1.ValueMember = "SPIDOM"

End Sub

Reverend Jim 4,968 Hi, I'm Jim, one of DaniWeb's moderators.

12 Years Ago

GU58 and GU67 do not appear in my copy of tnt.xls or in my combobox.

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

renzlo 4 Junior Poster in Training · Answer 1 · 2012-02-22T01:23:25+00:00

In ComboBox1_SelectedIndexChanged use a SqlCommand to find the SPIDOM.
Like "Select SPIDOM from table where EMP_ID='"+ComboBox1.Text.Trim+"'"
Now Execute the Command As Scaler
If you Execute it as Scaler you will get the SPIDOM of corresponding EMP_ID..
Now Display it in TextBox..
Simple...

Can you provide a sample? Sorry I'm new in sqlcommand.

renzlo 4 Junior Poster in Training · Answer 2 · 2012-02-22T01:28:31+00:00

The combobox will be populated with the data in column 1. When an item in the combobox is selected, the corresponding value from column 2 will be displayed in the textbox.

Private Sub ComboBox1_SelectedValueChanged(sender As System.Object, e As System.EventArgs) Handles ComboBox1.SelectedValueChanged

    txtExcel.Text = ComboBox1.SelectedValue.ToString

End Sub

Private Sub btnLoadExcel_Click(sender As System.Object, e As System.EventArgs) Handles btnLoadExcel.Click

    Dim con As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\temp\test.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";")
    con.Open()

    Dim cmd As New OleDb.OleDbCommand("select * from [Sheet1$]", con)
    Dim da As New OleDb.OleDbDataAdapter(cmd)
    Dim dt As New DataTable
   
    da.Fill(dt)

    ComboBox1.DataSource = dt
    ComboBox1.DisplayMember = "EMP_ID"
    ComboBox1.ValueMember = "SPIDOM"

End Sub

thank you jim, this seems to work but the output in textbox is not 100% accurate, I've noticed that some value was inserted in combo box which is not present in the excel file, I've attached the excel file here.

Here's my modified code using the one you suggested:

Imports System.Data
Public Class authorization
    Private Sub authorization_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.FormBorderStyle = Windows.Forms.FormBorderStyle.None
        spidomTextbox.Text = ""
        passTextbox.Enabled = False
        Try
            Dim con As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=tnt.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";")
            con.Open()
            Dim cmd As New OleDb.OleDbCommand("select * from [Sheet1$]", con)
            Dim da As New OleDb.OleDbDataAdapter(cmd)
            Dim dt As New DataTable
            da.Fill(dt)
            ComboBox1.DataSource = dt
            ComboBox1.DisplayMember = "EMP_ID"
            ComboBox1.ValueMember = "SPIDOM"
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub
    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Me.Dispose()
        Me.Close()
    End Sub
    Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
        If Trim(ComboBox1.Text) = Nothing Then
            passTextbox.Enabled = False
        Else
            passTextbox.Enabled = True
            passTextbox.Focus()
        End If
    End Sub
    Private Sub ComboBox1_SelectedValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedValueChanged
        spidomTextbox.Text = ComboBox1.SelectedValue.ToString
    End Sub
    Private Sub ComboBox1_Validating(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles ComboBox1.Validating
        If Trim(ComboBox1.Text) = Nothing Then
            passTextbox.Enabled = False
        Else
            spidomTextbox.Text = ComboBox1.SelectedValue.ToString
            passTextbox.Enabled = True
            passTextbox.Focus()
        End If
    End Sub
End Class

score 0 · Answer 3 · 2012-02-22T04:32:03+00:00

I ran it here and the values matched exactly. No extra values that were not in the table.

You might want to ask a moderator to remove the Excel table that you attached. It looks like it contains actual user data (names etc) and they might not appreciate having their data posted. Even just posting a list of names might be an oops. Next time you might want to overwrite the names with dummy data. Just a suggestion.

renzlo 4 Junior Poster in Training · Answer 4 · 2012-02-22T06:29:17+00:00

I ran it here and the values matched exactly. No extra values that were not in the table.
You might want to ask a moderator to remove the Excel table that you attached. It looks like it contains actual user data (names etc) and they might not appreciate having their data posted. Even just posting a list of names might be an oops. Next time you might want to overwrite the names with dummy data. Just a suggestion.

I'm gonna give it a try. It's kinda weird, why my combo box have extra values, by the way what platform you're using? I'm using Windows XP. Don't worry the attached names are dummy names.

score 0 · Answer 5 · 2012-02-22T06:40:14+00:00

Windows 7 Pro, Visual Studio 2010 and MS SQL Server. I didn't intend to rag on you about the names. I just thought I'd mention it. I see it wasn't necessary.

If there is the possibility of duplicate entries in the spreadsheet you might try "select distinct" and see if you get the same number of records returned. Although, as I said, I didn't see any duplicates.

renzlo 4 Junior Poster in Training · Answer 6 · 2012-02-22T07:03:43+00:00

I have run again my program but GU58 and GU67 is in the combo box but not in excel file, it's kinda weird, what went wrong?

Please see attached image.

Thanks for usual support Jim.

renzlo 4 Junior Poster in Training · Answer 7 · 2012-02-22T20:57:17+00:00

thanks, I've rewritten my codes and it's now fixed. Thank you very much Jim.