Share via

How I can use a function in vb similar to vlookup in excel?

Question

Sunday, April 19, 2009 9:21 AM

I am new to VB. I have been using excel extensively. In excel I use vlookup function to connect and retrieve data from other workbooks. I want to know how I can do it in VB. So far I have created a form. The form has say three text boxes viz (i) A unique Number (ii) Name (iii) Address. I want that when I write the unique number in the text box for unique number the other two text boxes i.e. of Name and of Address should be auto populated. I have also established and tested the connection with the access database file using ADO.NET. I do not know whether I need to write an SQL or LINQ query for this purpose or I should write some codes. What should i do it and how should i do it? Please help.

All replies (4)

Friday, April 24, 2009 3:00 AM âś…Answered | 1 vote

Hi Anand Chhangani,

If you want to get a certain record by unique number, you can write code like this:
You need two textboxes and one button first. And The ID is number only.

Imports System.Data.OleDb

Public Class Form1
    Dim conn As OleDbConnection
    Dim da As OleDbDataAdapter

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        conn = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=C:\Database1.mdb")
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        If TextBox1.Text = "" Then
            Return
        End If

        Dim id As Integer
        Try
            id = CInt(TextBox1.Text.Trim)
        Catch ex As Exception
            MsgBox("Please input number")
            Return
        End Try

        da = New OleDbDataAdapter("select * from Table1 where id = " & id, conn)

        Dim ds As New DataSet
        da.Fill(ds)

        If ds.Tables(0).Rows.Count = 0 Then
            MsgBox("No result")
        Else
            TextBox2.Text = ds.Tables(0).Rows(0)("Daycom")
        End If

        conn.Close()
    End Sub

End Class

If you want to make a numeric textbox, you can read this article:
http://msdn.microsoft.com/en-us/library/ms229644.aspx

Does this works for you? If you have any questions or concerns, please update the thread and we will have a further discussion.

 

 

Best Regards

Yichun Feng

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Sunday, April 19, 2009 10:12 AM

Hi Anamd,

vlookup in excel is a vertical lookup of information from a list of rows. The rows can be on any sheet at all. Your spot on this is exactly the same as a query on a database as the rows in an excel sheet are the same as the rows in a database table. 

How you have connected to the database depends on the approach you take to querying the database. If your new to VB then you've more than likely dragged the tables from the database onto a dataset designer. In this case the way to query the database is through table adapters. Worth your while searching on TableAdapter and reading up on them

In fact I've just done that and this is exactly what you need to read.

http://msdn.microsoft.com/en-us/library/bz9tthwx.aspx

http://msdn.microsoft.com/en-us/library/fksx3b4f.aspx

http://msdn.microsoft.com/en-us/library/ms171918.aspx

Hopefully these should get you started. I know you are new to VB but what your asking is very general, nothing wrong with that, but it makes it difficult to formulate a decent reply without writing an article or creating a screen cast. The above links will allow you to do some ground work.

Still do a search on TableAdapters as there is more information and examples out there.www.dsmyth.net | www.dsmyth.net/wiki

Wednesday, April 22, 2009 2:48 AM

Hi Anand Chhangani,

You add Microsoft .Office.Interop.Excel reference to use vlookup in VB.NET.
Click Project on the main menu -> Add Reference ->.NET tab ->Choose **Mcrosoft .Office.Interop.Excel

**Here is code example:http://www.codeproject.com/KB/vb/SearchExcel.aspx

If you want to test connection, there is an opiton:

        Dim connStr As String = "XXXXX"
        Dim conn As New SqlConnection(connStr)

        Try
            conn.Open()
            MsgBox("Connect Successfully")
            conn.Close()
        Catch ex As Exception
            MsgBox("Connect Faild")
        End Try

Does this works for you? If you have any questions or concerns, please update the thread and we will have a further discussion.

 

 

Best Regards

Yichun Feng

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Friday, April 24, 2009 2:04 AM

Thank you both Mr Derek Smyth and Mr Yichun Feng,
Dear Smyth as suggesed by you I am trying to learn more about table adapters and about how to use them through the references given by you.
Dear Feng I do not want to call vlookup in the vb.net, because when you call the vlookup, the databse attaces to your application and makes the application very bulky. In my case the databse is farely big.
I have already connected to the database and tested its connectivity. I have manully created three text boxes through tools and not created them by draging from data source window. Now what I want is when I write the ID i.e. unique number, the other two text boxes should be filled automatically from the database.
Thanks
and regards
Anand Chhangani
Anand

  • Last updated on