pick value from listbox and use in sql query as column name.query dynamically pick column name from variable

Question

I have VB.net app that when run you select from listbox values that should then be used in select query.Then click save command button. see vb code snippet below

transport = lsttrans.SelectedItem
    leave = lstleave.SelectedItem

then query like below.Note transport and leave are dummy columns not on existing employee table.

select employeeid,'' as transport_allowance,'' as leave from employees

i wish to somehow create new query to be something close to the below using my selected values in listbox.

select employeeid,transport,leave from employees

To do that you would need to use dynamic SQL. Please read up on that. — shree.pat18
– shree.pat18, Commented May 9, 2014 at 6:29

Emmanuel Palogan · Accepted Answer · 2014-05-09 11:28:33Z

1

Use String.Format(). Here is the example assuming transport and leave variables are already declared:

Dim myQueryTemplate As String = "SELECT employeeid, {0}, {1} FROM employees"
Dim myFinalQuery as String = String.Format(myQueryTemplate, transport, leave)

If you will debug that, the value of myFinalQuery will be:

"SELECT employeeid, transport, leave FROM employees"

Add more if needed. From my given example, the next parameter will be {2} and so on.

answered May 9, 2014 at 11:28

Emmanuel Palogan

716 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jmcilhinney · Accepted Answer · 2014-05-09 07:30:14Z

1

Do you already know how to retrieve data from a database in VB.NET? If not then you should read up on ADO.NET.

Assuming that you know how ADO.NET works, your SQL is simply a String, so you can build it up from parts the same way you can any other String. In this case, I would suggest calling the String.Format method to insert the column names into a query template.

answered May 9, 2014 at 7:30

jmcilhinney

54.9k5 gold badges30 silver badges52 bronze badges

1 Comment

GotaloveCode Over a year ago

yes I do thanks.dint know how to use the string.format feature though but now I do

Collectives™ on Stack Overflow

pick value from listbox and use in sql query as column name.query dynamically pick column name from variable

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related