Help Needed to build a dynamic SQL query string in VB.NET

[isaackhazi]

Im currently working on a project to build an application for Windows Mobile 6. Im just stuck with this stupid issue:

I have a SQL query string:
Dim connectionString2 As String = "Data source = " + path + "\HC.sdf"
Dim cmdText2 = "SELECT * FROM BigC_Rangsit_0_"

I want to substitute the table name ie. BigC_Rangsit_0_ with a variable which contains the table name.

say, during runtime the user selects a table from the dropdown list , that variable should be substituted with the selected table name dynamically in the SELECT statement.

Dim cmdText2 = "SELECT * FROM "selected table name VARiable""

well, I need the right syntax for this.

[Teme64]

Use Replace and placeholder for table name:

Dim cmdText2 As String
cmdText2 = "SELECT * FROM XTABLEX "

and later replace

cmdText2 = cmdText2.Replace("XTABLEX", strTableName)

where strTableName is obtained from the user (or dropdown box)

[isaackhazi]

Thanx....Ill try that out and let you know..

[isaackhazi]

The problem is that im declaring the variable cmdText2 in a calss declaration and not in a function. So when i try to use it ie.

Dim cmdText2 as String
cmdText2 = "SELECT * FROM XTABLEX"

in the second line it says "Declaration Expected"

DO you have any other solution. Your help will be greatly appreciated.

[Teme64]

Ok. You had cmdText2 = "SELECT * FROM XTABLEX" in class declaration and it gave you that error. Dim cmdText2 as String = "SELECT * FROM XTABLEX" works in class declaration?

To get it to work in application you have to have some function/sub where to do replacing. Is this possible? You mentioned dropdown box:

Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
    '
    ' Get table name from dropdown box, build SQL and...
  Dim strSQL As String
  strSQL = cmdText2.Replace("XTABLEX", strTableName)
  ' Use strSQL

End Sub

Can you use it in this way?

[isaackhazi]

Yeah Yeah... Thanks a lot . I did exactly that before you posted your reply. Thanks anyways , your first post gave me a head start. Thanx a lot . appreciate it.

Now i have a different problem regarding SQL scripts. may be you could help me out with that one. I have posted the question on Wed Development -> Databases -> MS SQL. Would be of great help if you could help me out.

Thanx again for every thing. Appreciate it.

[Sheryl99]

Not sure if this is what you need, but maybe it will help.

Dim strTable as String = "MyTable"
Dim strSQL as String = "SELECT * FROM " & strTable & ";"

result:

SELECT * FROM MyTable;

[Teme64]

Yeah Yeah... Thanks a lot . I did exactly that before you posted your reply. Thanks anyways , your first post gave me a head start. Thanx a lot . appreciate it.

Now i have a different problem regarding SQL scripts. may be you could help me out with that one. I have posted the question on Wed Development -> Databases -> MS SQL. Would be of great help if you could help me out.

Thanx again for every thing. Appreciate it.

Ok, see http://www.daniweb.com/forums/post700670.html#post700670

[longstrd]

Years later - your reply is still helping. I did not know the replace function/method yet.

Solved my issue. Allowing somone to type in or use a drop down so they could search the table for a field with a value.

thanks - d.s.l. ( longstrd )