Results 1 to 5 of 5

Thread: [RESOLVED] Using variables in OleDb.OleDbDataAdapter

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Sep 2009
    Posts
    20

    Resolved [RESOLVED] Using variables in OleDb.OleDbDataAdapter

    Hello VBF,

    I am using this line, to import data from an excel file, into a datagridview:

    Code:
    MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection)
    My problem is that the sheet of the excel file is hardcoded. I have already made a function that imports all sheets of my excel file into a listbox, and from there to a textbox, but I cannot figure out how to replace the [Sheet1$] with a variable or even the text from my textbox or listbox.

    Now I feel kinda dumb... What am I missing?

    Any help would be appreciated

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Using variables in OleDb.OleDbDataAdapter

    The SQL part is simply a String, so you can treat it like any other String. How would you usually create a String with a variable? E.g.
    vb.net Code:
    1. Dim sql = String.Format("SELECT * FROM [{0}$]", sheetName)
    By the way, you really shouldn't use 'MyCommand' as the name of a variable that refers to a data adapter. That is an appropriate name for a variable that refers to a command. The SelectCommand, InsertCommand, UpdateCommand and DeleteCommand properties of the data adapter refer to OleDbCommand objects so to call the adapter 'MyCommand' has the potential to cause some confusion.

    I'd also suggest that you import the System.Data.OleDb, either at the project level or the file level, so you don't have to prefix each type from that namespace. Even if you don't though, the System.Data namespace is already imported by default, so you only need the OleDb part.

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Sep 2009
    Posts
    20

    Re: Using variables in OleDb.OleDbDataAdapter

    Thank you for your reply jmcilhinney. I appreciate it very much. It send me in the right direction.

    What I ended up doing what this:
    Code:
            Dim OleCommand As New OleDb.OleDbCommand
            OleCommand.Connection = MyConnection
            Dim CommandString As String
            CommandString = "SELECT * FROM [" + TextBox1.Text + "]"
            OleCommand.CommandText = CommandString
    Dim MyCommand = New System.Data.OleDb.OleDbDataAdapter(OleCommand)

  4. #4
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: [RESOLVED] Using variables in OleDb.OleDbDataAdapter

    You should not use + to concatenate Strings in VB.NET. It is literally the addition operator. Addition of two Strings is implemented as concatenation so it will usually be OK but there are situations where it will behave differently. If what you actually want is concatenation then you should use the concatenation operator, i.e. &. That will always behave the right way so you can never make a mistake with it.

  5. #5

    Thread Starter
    Junior Member
    Join Date
    Sep 2009
    Posts
    20

    Re: [RESOLVED] Using variables in OleDb.OleDbDataAdapter

    Thank you for the advise. I have updated my code, using '&' instead and it works fine

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width