Need Help in SQL Statement. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need Help in SQL Statement.

Author

Topic

abhay_547
Starting Member

1 Post

Posted - 2010-05-22 : 03:18:36

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 : [CODE]mydata.[Company Code] = [country_Region mapping].[Company Code] 'Join Type : INNER JOIN [/CODE]
Join 2 : [CODE] mydata.[Cost Center] = [Cost Center Mapping].[Cost Center]'Join Type : INNER JOIN [/CODE]
Join 3 : [CODE] mydata.[Unique Indetifier 1] = [Cost Element Mapping].[CE_SR_NO]'Join Type : INNER JOIN [/CODE]
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 :

[CODE]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[/CODE]

Thanks a lot for your help in advance.:)

Abhay

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources