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.