By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,691 Members | 1,123 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,691 IT Pros & Developers. It's quick & easy.

SQL Query in Visual Basic Problem!

100+
P: 123
I'm trying to break up a sql query from being on long line, so it is easier to maintain for future users.

currently I have this:

Dim QrtTest As String
QrtTest = "SELECT dbo.Issues.ID, dbo.Types.Name AS Type, dbo.Issues.Synopsis, dbo.States.Name AS State, dbo.Users.Name AS [User], dbo.Projects.Name AS Projects FROM dbo.Issues INNER JOIN (dbo.Projects ON dbo.Issues.ProjectID = dbo.Projects.ID INNER JOIN dbo.States ON dbo.Issues.StateID = dbo.States.ID INNER JOIN dbo.Types ON dbo.Issues.TypeID = dbo.Types.ID INNER JOIN dbo.Users ON dbo.Issues.AssignedUserID = dbo.Users.ID"
With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _
"ODBC;DRIVER=SQL Server;SERVER=devsql2.corp.advertising.com;UID=<YO URNAME>;APP=Microsoft Office 2003;WSID=VSOMESWARAN;DATABASE=IntegrityManage" _
), Array("r;Trusted_Connection=Yes")), Destination:=Range("A1"), Sql:=QrtTest)
.SourceConnectionFile = _
"C:\Documents and Settings\vsomeswaran\Application Data\Microsoft\Queries\adserve closed defect count.dqy"
.Refresh BackgroundQuery:=False
End With

End Sub

As you see the sql statement is one long line, the basic query is as follows:

SELECT dbo.Issues.ID, dbo.Types.Name AS Type, dbo.Issues.Synopsis, dbo.States.Name AS State, dbo.Users.Name AS [User],
dbo.Projects.Name AS Projects
FROM dbo.Issues INNER JOIN
dbo.Projects ON dbo.Issues.ProjectID = dbo.Projects.ID INNER JOIN
dbo.States ON dbo.Issues.StateID = dbo.States.ID INNER JOIN
dbo.Types ON dbo.Issues.TypeID = dbo.Types.ID INNER JOIN
dbo.Users ON dbo.Issues.AssignedUserID = dbo.Users.ID

How do I break the SQL query up in a VB code?

Thanks,
Jul 2 '07 #1
Share this Question
Share on Google+
5 Replies


danp129
Expert 100+
P: 321
How do I break the SQL query up in a VB code?
Some people do it by hand and some people are lazy and make a little app to do it for them :) Either way... it looks similar to this in the end.

Expand|Select|Wrap|Line Numbers
  1. strSQL = "SELECT dbo.Issues.ID, dbo.Types.Name AS Type, dbo.Issues.Synopsis, dbo.States.Name AS State, dbo.Users.Name AS [User],"
  2. strSQL = strSQL & " dbo.Projects.Name AS Projects"
  3. strSQL = strSQL & " FROM dbo.Issues INNER JOIN"
  4. strSQL = strSQL & " dbo.Projects ON dbo.Issues.ProjectID = dbo.Projects.ID INNER JOIN"
  5. strSQL = strSQL & " dbo.States ON dbo.Issues.StateID = dbo.States.ID INNER JOIN"
  6. strSQL = strSQL & " dbo.Types ON dbo.Issues.TypeID = dbo.Types.ID INNER JOIN"
  7. strSQL = strSQL & " dbo.Users ON dbo.Issues.AssignedUserID = dbo.Users.ID"
  8.  
Jul 2 '07 #2

100+
P: 123
Thanks for the help,it worked.
Jul 2 '07 #3

100+
P: 123
take that back, now I'm getting an error stating that there is incorrect syntax and is high lighting the following:

Private Sub CommandButton1_Click()

Dim QrtTest As String
QrtTest = strsql = "SELECT dbo.Issues.ID, dbo.Types.Name AS Type, dbo.Issues.Synopsis, dbo.States.Name AS State, dbo.Users.Name AS [User],"
strsql = strsql & " dbo.Projects.Name AS Projects"
strsql = strsql & " FROM dbo.Issues INNER JOIN"
strsql = strsql & " dbo.Projects ON dbo.Issues.ProjectID = dbo.Projects.ID INNER JOIN"
strsql = strsql & " dbo.States ON dbo.Issues.StateID = dbo.States.ID INNER JOIN"
strsql = strsql & " dbo.Types ON dbo.Issues.TypeID = dbo.Types.ID INNER JOIN"
strsql = strsql & " dbo.Users ON dbo.Issues.AssignedUserID = dbo.Users.ID"
With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _
"ODBC;DRIVER=SQL Server;SERVER=devsql2.corp.advertising.com;UID=jma ntheiy;APP=Microsoft Office 2003;WSID=JMANTHEIY;DATABASE=IntegrityManage" _
), Array("r;Trusted_Connection=Yes")), Destination:=Range("A1"), Sql:=QrtTest)
.SourceConnectionFile = _
"C:\Documents and Settings\JMANTHEIY\Application Data\Microsoft\Queries\adserve closed defect count.dqy"
.Refresh BackgroundQuery:=False
End With

End Sub

What should I do?
Jul 2 '07 #4

danp129
Expert 100+
P: 321
With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _
"ODBC;DRIVER=SQL Server;SERVER=devsql2.corp.advertising.com;UID=jma ntheiy;APP=Microsoft Office 2003;WSID=JMANTHEIY;DATABASE=IntegrityManage" _
), Array("r;Trusted_Connection=Yes")), Destination:=Range("A1"), Sql:=QrtTest)

replace that with strSQL, or replace all the strSQL's with QrtTest
Jul 2 '07 #5

100+
P: 123
Thanks, I just replaced everything with what I originally had and it worked.
Jul 2 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.