Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 164
  • Last Modified:

Spliting SQL?

How do you split a SQL statment over more than one line in VB? For example

Select("Field1, field2, field3, field4
From data
where field1 = "test")

what do I need to put at the end of these lines to stop errors?
0
mercia
Asked:
mercia
1 Solution
 
Ryan ChongCommented:
>>Select("Field1, field2, field3, field4
From data
where field1 = "test")
??

Should be like this?

"Select Field1, field2, field3, field4 From data where field1 = 'test'"
0
 
merciaAuthor Commented:
Sorry my actual query is longer than this and therefore needs to be split over more than one line, the sql shown was just an example.
0
 
SmallintCommented:
try

strSql = "Select(Field1, field2, field3, field4 " + _
         "From Data " + _
         "where field1 = 'test')"
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
merciaAuthor Commented:
Still get errors if I try this
0
 
TimCotteeCommented:
Hi mercia,

To split any statement over more than one line use & _

rst.Open "Select " & _
                 "Field1, Field2, Field3 " & _
                 "From MyTable " & _
                 "Where Field1 = 'Test'", _
                 cnn, adOpenStatic,adLockReadOnly

Tim Cottee MCSD, MCDBA, CPIM
http://www.timcottee.tk 

Brainbench MVP for Visual Basic
http://www.brainbench.com
0
 
diya123Commented:
hello merica,

try this one out.

1.  sql="Select Field1, field2, field3, field4" & _
2.    "From data " & _
3.    "where field1 = test"

or else

1.  sql="Select Field1, field2, field3, field4"
2.   sql =sql & "From data " 
3.   sql= sql & "where field1 = test"

if test is a string parameter which u want to check then change

line
 3. "where field1='" &test &"'"


happiness always...



0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I think you need the Carriage Return Line Feed in there (in vb, use vbCrLf predefined constant) :

st.Open "Select " & vbcrlf & _
                 "Field1, Field2, Field3 " & vbcrlf &  _
                 "From MyTable " & vbcrlf &  _
                 "Where Field1 = 'Test'" , _
                 cnn, adOpenStatic,adLockReadOnly
0
 
SmallintCommented:
try


strSql = "Select(Field1, field2, field3, field4 " + _
        "From Data " + _
        "where field1 = 'test')"

Be carefully to left one space between "+" and "_"
0
 
lucho_ndCommented:
try

strSQL = "SELECT Field1, Field1, Field1, Field1 " _
     & "FROM Table1, Table1, Table1 " _
     & "WHERE Field1 = '" & <VALUE> & "'"

or

strSQL = "SELECT Field1, Field1, Field1, Field1 "
strSQL = strSQL & "FROM Table1, Table1, Table1 "
strSQL = strSQL & "WHERE Field1 = '" & <VALUE> & "'"

good luck

lucho_nd
0
 
ceroCommented:
Hi Mercia,

If you use concatenation like

Sql = "Select Field1, Field2, " & _
      " Field3 From Table1, Table2 " 

And this sentence is very large, like you mention, you will obtain errors, so, the best way to achieve this is:

var = "Test"

Sql = "Select Field1, Field2, Field3, Field4 "
Sql = Sql & " From Data "
Sql = Sql & " Where Field1 = '" & var & "'"

'be careful of spaces when concatening

hope this helps,

cero
0
 
merciaAuthor Commented:
Thanks for help works, i thought there might be a shorter way of doing it, but has help me overcome problem.  Cheers
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now