-
Jan 22nd, 2018, 08:09 AM
#1
Thread Starter
Junior Member
[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
-
Jan 22nd, 2018, 08:29 AM
#2
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:
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.
-
Jan 26th, 2018, 12:35 PM
#3
Thread Starter
Junior Member
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)
-
Jan 26th, 2018, 08:54 PM
#4
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.
-
Jan 30th, 2018, 01:55 PM
#5
Thread Starter
Junior Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|