CODEWITHSUNDEEP
sqlvbaexcelwhere-clause
TAG
TAG

Reputation: 15

VBA SQL query with WHERE clause

I'm trying to write VBA code to get SQL data into Excel. Everything works fine except the WHERE condition. I think the problem may be with quotation. This is my query:

Sub Engineering_Milestone()

Dim v_project As String
Dim cn  As ADODB.Connection
Dim rs  As ADODB.Recordset
Dim sql As String

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset

v_project = Worksheets("Parameters").Range("B1").Value

'cn.Open "Provider = x.1; Data Source=x; Initial Catalog=x; Integrated Security=x"


cn.Open "Provider = Sx; Data Source=x; Initial Catalog=x; Integrated Security=x"

Worksheets("Engineering_Milestone").Range("A2:G5000").ClearContents

sql = " SELECT A.ENGINEER_ID, B.[Description], B.BUDGET_APPROVED, A.MILESTONE, A.[DESCRIPTION], A.PCT_COMPLETE, A.SCHEDULE_DATE FROM X as A  Inner Join X as B on A.ENGINEER_ID = B.ENGINEER_ID WHERE B.Project_ID = " & "'" & v_project & "'" and A.Project_ID = " & "'" & v_project & "'"

rs.Open sql, cn

Sheets("Engineering_Milestone").Cells(2, 1).CopyFromRecordset rs

rs.Close
cn.Close

End Sub

It works fine when the SQL query has one condition i.e ...where B.Project_ID = " & "'" & v_project & "'" (without second condition -> and A.Project_ID = " & "'" & v_project & "'").

I'm very new to this so would be grateful if anyone can help...Many thanks.

Upvotes: 1

Views: 14867

Answers (4)

Cetin Basoz
Cetin Basoz

Reputation: 23797

As I said never write SQL code by string concatenation, use parameters. After seeing your code it is now a little bit easier:

Sub Engineering_Milestone()

Dim v_project As String
Dim cn  As ADODB.Connection
Dim rs  As ADODB.Recordset
Dim sql As String
Dim cmd as ADODB.Command

Set cn = New ADODB.Connection

v_project = Worksheets("Parameters").Range("B1").Value

'cn.Open "Provider = x.1; Data Source=x; Initial Catalog=x; Integrated Security=x"


cn.Open "Provider = Sx; Data Source=x; Initial Catalog=x; Integrated Security=x"

Worksheets("Engineering_Milestone").Range("A2:G5000").ClearContents

sql = "SELECT A.ENGINEER_ID, B.[Description], B.BUDGET_APPROVED, " & _
      "   A.MILESTONE, A.[DESCRIPTION], A.PCT_COMPLETE, A.SCHEDULE_DATE" & _
      " FROM X as A" & _
      " Inner Join X as B " & _
      "   on A.ENGINEER_ID = B.ENGINEER_ID and B.Project_ID = A.Project_ID" & _
      " WHERE B.Project_ID = ?"

  set cmd = New ADODB.Command
  cmd.ActiveConnection = cn
  cmd.CommandText = sql
  cmd.Parameters.Append cmd.CreateParameter("@projectId", adVarchar)
  cmd.Parameters("@projectId").Value = v_project

  Set rs = cmd.Execute()

  Sheets("Engineering_Milestone").Cells(2, 1).CopyFromRecordset rs

rs.Close
cn.Close

End Sub

NOTE: Your SQL is really vague. You are doing a self join just to create some kind of cartesian join? Probably in fact engineerId, projectId combinations are unique. If that is correct than you could simplify your SQL:

sql = "SELECT ENGINEER_ID, [Description], BUDGET_APPROVED, " & _
      "   MILESTONE, [DESCRIPTION], PCT_COMPLETE, SCHEDULE_DATE" & _
      " FROM X" & _
      " WHERE Project_ID = ?"

Upvotes: 2

Cetin Basoz
Cetin Basoz

Reputation: 23797

Never write SQL code like that concatenating strings. Instead simply use parameters. ie: (say vProject is integer)

.. where B.Project_ID = ? And A.Project_ID = ?

cmd.Parameters.Append .CreateParameter("@projectId", adInteger, adParamInput, 0, vProject)
cmd.Parameters.Append .CreateParameter("@projectId", adInteger, adParamInput, 0, vProject)

Note: cmd is your ADODB.Command object that you use for your command.

Upvotes: 0

Parfait
Parfait

Reputation: 107577

Consider SQL parameterization, the industry best practice when passing values into SQL queries -not just in VBA or your database but across all langauge interfaces to any databases. This process is more readable and maintainable as you no longer worry about quotes. Plus, code (SQL query) is separated from data (VBA variables).

Using ADO, parameters can be defined and set using the Command Object.

Dim v_project As String, sql As String
Dim cn  As New ADODB.Connection
Dim rs  As New ADODB.Recordset
Dim cmd As New ADODB.Command

v_project = Worksheets("Parameters").Range("B1").Value

cn.Open "Provider = Sx; Data Source=x; Initial Catalog=x; Integrated Security=x"

' PREPARED STATEMENT WITH QMARK PLACEHOLDERS
sql = "SELECT A.ENGINEER_ID, B.[Description], B.BUDGET_APPROVED, A.MILESTONE," _
       & "    A.[DESCRIPTION], A.PCT_COMPLETE, A.SCHEDULE_DATE" _
       & " FROM X AS A INNER JOIN X as B ON A.ENGINEER_ID = B.ENGINEER_ID" _
       & " WHERE B.Project_ID = ? AND A.Project_ID = ?"

' COMMAND OBJECT 
Set cmd = New ADODB.Connection

With cmd
    .ActiveConnection = cn    ' CONNECTION OBJECT
    .CommandText = sql
    .CommandType = adCmdText

    ' BINDING PARAMETERS
    .Parameters.Append .CreateParameter("a_projid", adVarChar, adParamInput, , v_project)
    .Parameters.Append .CreateParameter("b_projid", adVarChar, adParamInput, , v_project)
End With

' ASSIGN TO RECORDSET
Set rs = cmd.Execute

With Worksheets("Engineering_Milestone")
     .Range("A2:G5000").ClearContents
     .Cells(2, 1).CopyFromRecordset rs
End With

rs.Close: cn.Close
Set cmd = Nothing: Set rs = Nothing: Set cn = Nothing

Upvotes: 1

ashleedawg
ashleedawg

Reputation: 21619

You only provided a half of one line of code so I'm can only guess that this is what you're trying for:

"where B.Project_ID = '"& v_project &"'& And A.Project_ID = ' & v_project  "'"

Strings can be confusing when entering/exiting multiple types of quotes, but when you're troubleshooting a problem building a string, start be remove all the variables and just using a hard-coded SQL string.

Once that's working, start replacing the values with variables (and appropriate quotes) one at a time.

Upvotes: 2

Related Questions