Ad:

VB.NET Discussion Thread
Unsolved
Views: 5505

Sep 24th, 2008

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

Expand Post »

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.

isaackhazi

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

22 posts
since Sep 2008

Permalink

Sep 24th, 2008

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

Use Replace and placeholder for table name:

VB.NET Syntax (Toggle Plain Text)
Dim cmdText2 As String
cmdText2 = "SELECT * FROM XTABLEX "
Dim cmdText2 As String
cmdText2 = "SELECT * FROM XTABLEX "

and later replace

VB.NET Syntax (Toggle Plain Text)
cmdText2 = cmdText2.Replace("XTABLEX", strTableName)
cmdText2 = cmdText2.Replace("XTABLEX", strTableName)

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

Teme64

Reputation Points: 218

Solved Threads: 201

Veteran Poster

Offline

1,024 posts
since Aug 2008

Permalink

Sep 24th, 2008

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

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

isaackhazi

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

22 posts
since Sep 2008

Permalink

Sep 25th, 2008

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

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.

isaackhazi

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

22 posts
since Sep 2008

Permalink

Sep 26th, 2008

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

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:

VB.NET Syntax (Toggle Plain Text)
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
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?

Teme64

Reputation Points: 218

Solved Threads: 201

Veteran Poster

Offline

1,024 posts
since Aug 2008

Permalink

Sep 26th, 2008

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

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.

isaackhazi

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

22 posts
since Sep 2008

Permalink

Sep 28th, 2008

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

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;

Sheryl99

Reputation Points: 10

Solved Threads: 0

Light Poster

Offline

27 posts
since Sep 2008

Permalink

Sep 28th, 2008

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

Click to Expand / Collapse Quote originally posted by 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.

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

Teme64

Reputation Points: 218

Solved Threads: 201

Veteran Poster

Offline

1,024 posts
since Aug 2008

Permalink

Jun 20th, 2011

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

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 )

longstrd

Reputation Points: 7

Solved Threads: 0

Newbie Poster

Offline

5 posts
since Jun 2011

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in VB.NET Forum Timeline: Launch with file

Next Thread in VB.NET Forum Timeline: ReportViewer Won't Update

DaniWeb Message

Cancel Changes

User Name
Password