asked on

Large SQL Query Text in String Variable

Hi All
I use VB.Net in VS2005 and create my own query string in many of my applications to create a dataset etc. for example when I build a new SQL String I start
Dim SQL ="Select * From Table" 'new line
SQL+= " Where This ='" & TextBox.Text & "'" etc.

But sometimes I have a pre-defined query already written but not stored as a View in SQL Server, which is quite big, say 70 lines long. And from time to time I can amend the query in SQL Designer to change it when necessary. My question (eventually :) ) is can I create a very large string varaible without having to split it line by line like this

Dim SQL = "Select BLAH BLAH
BLAH BLAH BLAH
BLAH BLAH BLAH"

Instead of

Dim SQL = "Select BLAH BLAH"
SQL += "BLAH BLAH BLAH"
SQL += "BLAH BLAH BLAH"

I hope I have made sense..

Kind regards

Derek.

kworks

sure you can. But you would be using this form...

Dim SQL = "Select BLAH BLAH _
BLAH BLAH BLAH _
BLAH BLAH BLAH"

aikimark

use line continuation and string literal concatenation

Dim SQL = "Select BLAH BLAH " & _
"BLAH BLAH BLAH " & _
"BLAH BLAH BLAH "

Open in new window

kworks

"Select BLAH BLAH " & _
Right. forgot the &

sorry

Delboy

ASKER

Hi kworks

When I added this question the points somehow jumped into the 500 option and not the 50.. I now have no points left :)

I should have said I considered your suggestion already that in the question and wondered if there was anything else as still putting in the &_ on each line is still a task.. I am currently using a spreadsheet to add the " and " &_ to each line and copying it to my VS code window..

Sorry and can I delete this question and ask again with the correct points?

ASKER CERTIFIED SOLUTION

kworks

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

aikimark

you might have cleaner code if you stored the SQL in an external file, database.

leonstryker

aikimark is correct on the storing the string somewhere else. you should also note that there is a limitation on the number of times you can use _ for a single string in VB6 and VBA.

aikimark

@leon

I'm pretty sure this is a VB.Net environment, based on the DIM statement example posted and the question tag. I'm not sure what the .Net limit is on continued statement lines.

leonstryker

I know he is doing it in .Net, am not sure if such limitation exists or not in VB.Net.

Delboy

ASKER

Thanks for your comments guys, and thank you for changing my points aikimark. I use a stored query in a database already for my webserver data and that seems to work quite well.. I like the external file option too.. maybe I can store it in a text file somewhere which would make it easier for me to ammend the query when need be..

I will wait another day and then split the points(increased to 100) with aikimark and kworks.. :)

Regards,

Derek

SOLUTION

aikimark

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Delboy

ASKER

Hi aikimark

My application connects to 2 databases. My first connection opens my webserver database holding connection details for the second connection after login details have been accepted. At this point I hold a few queries in some fields on my server which a user can access, and they are used to query the second connection. These queries will change quite often(at the moment I cannot get them finalised which is why they are changing a lot) and I do not want my clients to have to update their software each time a change is made. It is a pian at the moment but it will cease :) at some point..

Reading your response a bit more, can you save a view and just recall the query text in VB without running it? I will find out and if this is so, I can certainly store the query as a view in my datebase and retrieve it and change it as necessary..

Thanks again..

Derek.

aikimark

As long as your SQL is less than 4000 characters, you can retrieve a view's SQL with
Information_Schema.[viewname].VIEW_DEFINITION

Delboy

ASKER

Increasing points... thank you very much gents

Delboy

ASKER

Fab