Solved: Help required in SQL Statement.

[abhay_547]

Hi All,

I have created a excel userform which pulls the data from sql server table on the basis of the parameters selected on the userform.
Following are the details of the same.

1) Mydata - Table which contains the data which I want to pull.
2) Country_Region Mapping - Table to populate listbox1 with country column from this table and used as a criteria in sql statment for pulling data from Mydata table.
3) Cost Element Mapping - Table to populate listbox2 with FSI_LINE3_code column from this table and used as a criteria in sql statment for pulling data from Mydata table.
4) Cost Center Mapping - Table to populate listbox4 with Sub Product UBR Code column from this table and used as a criteria in sql statment for pulling data from Mydata table.

Following is the code which I have as of now. Basically there are three joins required in the sql statement.

Join 1 : [VBA]mydata.[Company Code] = [country_Region mapping].[Company Code] 'Join Type : INNER JOIN [/VBA]

Join 2 : [VBA] mydata.[Cost Center] = [Cost Center Mapping].[Cost Center]'Join Type : INNER JOIN [/VBA]

Join 3 : [VBA] mydata.[Unique Indetifier 1] = [Cost Element Mapping].[CE_SR_NO]'Join Type : INNER JOIN [/VBA]

Below is the code which I have so far in my Userform background which works fine with the first join. I have bolded \ highlighted the sql statment in which I need help to incorporate other two joins and get the data from mydata on the basis of that :

[VBA]Private Sub CommandButton5_Click()
Dim selection As String
' Get the selected products escaping single quotes
'selection = Replace(UserForm2.listbox4.Value, "'", "''")
Dim lItem As Long

For lItem = 0 To ListBox4.ListCount - 1

If ListBox4.Selected(lItem) = True Then

selection = selection & "'" & Replace(ListBox4.List(lItem), "'", "''") & "',"
End If
Next

selection = Mid(selection, 1, Len(selection) - 1)

' Setup connection string
Dim connStr As String
connStr = "Provider=SQLOLEDB.1;Password=adminL;User ID=*****;Integrated Security=SSPI;" _
& "Persist Security Info=True;Initial Catalog=XXXXXX;" _
& "Data Source=XXXXXXXX"

' Setup the connection to the database
Dim connection As ADODB.connection
Set connection = New ADODB.connection
connection.ConnectionString = connStr
' Open the connection
connection.Open

' Open recordset.
Set Cmd1 = New ADODB.Command
Cmd1.ActiveConnection = connection
Cmd1.CommandText = "SELECT * FROM dbo.mydata t1 INNER JOIN dbo.[Cost Center Mapping] t2 ON t1.[Cost Center] = t2.[Cost Center] AND t2.[Sub Product UBR Code] IN (" & selection & ")"
Set Results = Cmd1.Execute()

' Clear the data from the active worksheet
Cells.Select
Cells.ClearContents

' Add column headers to the sheet
headers = Results.Fields.Count
For iCol = 1 To headers
Cells(1, iCol).Value = Results.Fields(iCol - 1).Name
Next

' Copy the resultset to the active worksheet
Cells(2, 1).CopyFromRecordset Results

' Stop running the macro

MsgBox "Data Extraction Successfully Completed"
Unload Me
End Sub[/VBA]


Thanks a lot for your help in advance.

[abhay_547]

Hi All,

Did anyone get the change to look into the above post. Please help....
I tried lot of things but it's not working. Anyone please help me I am completely stuck now.

[Gollem]

Hi,

this should be you basic join query(if my spelling is correct):

 
 
SELECT *
FROM Mydata t1, [Cost center mapping] t2, [Cost element mapping] t3, [Country region mapping] t4
WHERE t1.[cost center] = t2.[cost center] and t1.[unique identifier 1] = t3.[CE_SR_NO] and t1.[Company code] = t4.[Company Code]

First check 1 table join, then 2-3
My syntax is different from the classic innner join, but it's more clear for me this way.

Hope this helps you to solve your problem.

[abhay_547]

Hi Gollem,

Thanks a lot for your reply, As suggested, I tried with one join and it's working fine. Following is the code which I used :

[VBA]SELECT * FROM Mydata t1, [Cost center mapping] t2, [Cost element mapping] t3, [Country_region mapping] t4 WHERE t1.[cost center] = t2.[cost center] And t2.[Sub Product UBR Code] IN (" & selection & ") ' Selection text in bracket is actually the listbox value which are selected as parameters[/VBA]

However when I tried to add an additional join in the same it didn't work. Following is the code where used two joins
[VBA]SELECT * FROM Mydata t1, [Cost center mapping] t2, [Cost element mapping] t3, [Country_region mapping] t4 WHERE t1.[cost center] = t2.[cost center] and t1.[Company code] = t4.[Company Code] And t2.[Sub Product UBR Code] IN (" & selection & ") And t4.[Country] IN (" & selection1 & ") ' Selection and Selection1 text in bracket is actually the listbox value which are selected as parameters[/VBA]

Thanks a lot for your help in advance.

[Gollem]

Hi,

what error do you get?

I would suggest first trying the joins without your listbox selection. So you can check if your basic query is working, check if data is returned. If this is working something is wrong with the selection part.

[abhay_547]

Hi Gollem,

Thanks a lot for your reply. I am not facing any error, It just doesn't give any data though the data exists for the combination / Parameters selected in Listboxes in my database. However As suggested by you, I tried to hardcode my parameters in the sql statement instead of selecting them through listbox and I am getting some 65536 lines of data (Till last row of mysheet) whereelse when I run the below sql statement in sql server 2005 (where I have my database). I get only 52 records for the parameter combination mentioned in the statement. It's behaving in a wierd manner. Following is my sql statement with hardcoded parameters.

[vba]SELECT * FROM Mydata t1, [Cost center mapping] t2, [Cost element mapping] t3, [Country_region mapping] t4 WHERE t1.[cost center] = t2.[cost center] And t1.[Company code] = t4.[Company Code] And t2.[Product UBR Code] = 'P_6664' And t4.[Country] = 'Malaysia'[/vba]

Please help.

Thanks a lot for your help in advance.

[Gollem]

Hi,

I'm getting out of suggestions now
If you copy your sql statement from your server into your code, you should get the same result... I see that in your first post you use

t2.[Product UBR Code] in

and in your sql-server

t2.[Product UBR Code] =

This is a difference of course.



A last thing to check perhaps your selection-variable contains 'P_6664'
If this is the case => ok

But if your variable contains P_6664
in this case you have to write your sql-part like this

"... t2.[Product UBR Code] = '" & selection & "'"

Hope this helps.

[abhay_547]

Hi Gollem,

Thanks for your reply, I tried the below statements but still doesn't work.

Statement 1 :
[vba]SELECT * FROM Mydata t1, [Cost center mapping] t2, [Cost element mapping] t3, [Country_region mapping] t4 WHERE t1.[cost center] = t2.[cost center] And t1.[Company code] = t4.[Company Code] And t2.[Sub Product UBR Code] IN '" & selection & "' And t4.[Country] IN '" & selection1 & "'[/vba]
Error Encountered after using the above statement is as below :
[vba]Runtime Error '-2147217900 (80040e14)':
Incorrect syntax near ". [/vba]

Statement 2:
[vba]SELECT * FROM Mydata t1, [Cost center mapping] t2, [Cost element mapping] t3, [Country_region mapping] t4 WHERE t1.[cost center] = t2.[cost center] And t1.[Company code] = t4.[Company Code] And t2.[Sub Product UBR Code] = '" & selection & "' And t4.[Country] = '" & selection1 & "'[/vba]

Error Encountered after using the above statement is as below :
[vba]Runtime Error '-2147217900 (80040e14)':
Incorrect syntax near 'P_0490' (This is first item which reflects in my listbox from which I am trying to do the selection) [/vba]

Please help...

Thanks a lot for your help in advance.

[Gollem]

If your complete line is like this:

 
Cmd1.CommandText = "SELECT * 
FROM Mydata t1, [Cost center mapping] t2, [Cost element mapping] t3, [Country_region mapping] t4 
WHERE t1.[cost center] = t2.[cost center] And t1.[Company code] = t4.[Company Code] And t2.[Sub Product UBR Code] In '" & selection & "' And t4.[Country] IN '" & selection1 & "'"

I don't have more options. It must be a small issue...

[abhay_547]

Hi Gollem,

Thanks a lot for your reply, It's still not working. I tried using the sql statement posted by you but I am facing the below error.

Error Encountered after using the above statement is as below :
[VBA]Runtime Error '-2147217900 (80040e14)':
Incorrect syntax near ". [/VBA]

Thanks a lot for your help in advance.

[abhay_547]

Hi All,

Did anyone get the chance to look into the above post.


Thanks a lot for your help in advance.

[abhay_547]

Hi All,

Finally I am through with my SQL statement. Following is my working code :

[VBA]Private Sub CommandButton5_Click()
Dim sht As Worksheet

Workbooks.Add
'Selection String for Sub Product UBR Code
Dim selection As String
Dim lItem As Long
For lItem = 0 To ListBox4.ListCount - 1
If ListBox4.Selected(lItem) = True Then
selection = selection & "'" & Replace(Left(ListBox4.List(lItem), 6), "'", "''") & "',"
End If
Next
selection = Mid(selection, 1, Len(selection) - 1)

'Selection String For Country
Dim selection1 As String
Dim lItem1 As Long
For lItem1 = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(lItem1) = True Then
selection1 = selection1 & "'" & Replace(ListBox1.List(lItem1), "'", "''") & "',"
End If
Next
selection1 = Mid(selection1, 1, Len(selection1) - 1)


Dim selection2 As String
Dim lItem2 As Long
For lItem2 = 0 To ListBox2.ListCount - 1
If ListBox2.Selected(lItem2) = True Then
selection2 = selection2 & "'" & Replace(Left(ListBox2.List(lItem2), 11), "'", "''") & "',"
End If
Next
selection2 = Mid(selection2, 1, Len(selection2) - 1)

' Setup connection string
Dim connStr As String
Dim myservername As String
Dim mydatabase As String
Dim myuserid As String
Dim mypasswd As String

myservername = ThisWorkbook.Sheets(1).Cells(1, 3).Value
mydatabase = ThisWorkbook.Sheets(1).Cells(1, 5).Value
myuserid = ThisWorkbook.Sheets(1).Cells(1, 1).Value
mypasswd = ThisWorkbook.Sheets(1).Cells(1, 2).Value
connStr = "Provider=SQLOLEDB.1;DRIVER=SQL Native Client;Password=" & mypasswd & ";Persist Security Info=false;User ID=" & myuserid & ";Initial Catalog=" & mydatabase & ";Data Source=" & myservername & ";"


' Setup the connection to the database
Dim connection As ADODB.connection
Set connection = New ADODB.connection
connection.ConnectionString = connStr
' Open the connection
connection.Open

' Open recordset.
Set cmd1 = New ADODB.Command
cmd1.ActiveConnection = connection

cmd1.CommandText = "SELECT mydata.*, CRM.Country, CCM.[Sub Product UBR Code], CEM.FSI_LINE3_code FROM Data_SAP.dbo.mydata mydata INNER JOIN Data_SAP.dbo.[Country_Region Mapping] CRM ON (mydata.[Company Code] = CRM.[Company Code])INNER JOIN Data_SAP.dbo.[Cost Center mapping] CCM ON (mydata.[Cost Center] = CCM.[Cost Center])INNER JOIN Data_SAP.dbo.[Cost Element Mapping] CEM ON (mydata.[Unique Indentifier 1] = CEM.CE_SR_NO)WHERE CRM.Country IN (" & selection1 & ") AND CCM.[Sub Product UBR Code] IN (" & selection & ") AND CEM.FSI_LINE3_code IN (" & selection2 & ")AND mydata.year = '" & ComboBox4.Value & "' AND mydata.period between '" & ComboBox2.Value & "' AND '" & ComboBox3.Value & "'"

Debug.Print cmd1.CommandText
Set Results = cmd1.Execute()

If Results.EOF Then
' Recordset is empty
MsgBox "No results."
Debug.Print cmd1.CommandText
Else



' Clear the data from the active worksheet
Cells.Select
Cells.ClearContents

While Not Results.EOF

' Add column headers to the sheet
headers = Results.Fields.Count
For iCol = 1 To headers
Cells(1, iCol).Value = Results.Fields(iCol - 1).Name
Next

' Copy the resultset to the active worksheet
Cells(2, 1).CopyFromRecordset Results, 65535

'add another sheet if we're not at the end of the recordset
If Not Results.EOF Then Sheets.Add

Wend

End If
' Stop running the macro
MsgBox "Data Extraction Successfully Completed"

Unload Me
End Sub[/VBA]

Thanks a lot for your help.