Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2009
    Posts
    79

    Unanswered: Help required in SQL Statement.

    Hi All,

    I have below sql statement in my excel macro I want to define string for mydatabase name instead of hardcoding it's name in the below sql statement.

    My database name is : mydata
    I am trying get the above name from a combobox selection.

    Dim mydata As String
    mydata = Userform1.Combobox4.Value

    Can you guys tell me how to use the above string in the below sql statement so that it accepts the value selected by user in a combobox as a database table name.

    Code:
    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 = '" & ComboBox3.Value & "'AND mydata.[Document Type]= '" & Left(ComboBox11.Value, 2) & "'
    Thanks a lot for your help in advance.

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I assume that you just didn't copy the last quotation mark, but I added it anyway. First of all, the way you paste your code makes it very difficult to read, let alone understand, without copying it and reformatting it. If you really want some help, please take the trouble next time to make it as readable as possible. You'll be surprised how much quicker people will respond. So, here's what you submitted, plus the last missing quote:
    Code:
    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 = '" & ComboBox3.Value & "'
    AND mydata.[Document Type] = '" &Left(ComboBox11.Value, 2)& "'"
    
    Now, you're assigning the value of Userform1.Combobox4.Value to the local variable that you named "mydata". Yet, your question states that your database name is "mydata". At the same time, you're aliasing a table dbo.mydata in Data_SAP database as "mydata". I think you need to rethink your naming conventions before posting your code "as is", if you would like to get some helpful response.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    I agree with rdjabarov concerning your naming conventions and your code presentation, but here is what I think you are looking for . . .

    Code:
    Dim mydata As String
    mydata=Userform1.Combobox4.Value
    
    cmd1.CommandText=
    	"
    	SELECT	mydata.*
    		,CRM.Country
    		,CCM.[Sub Product UBR Code] 
    		,CEM.FSI_LINE3_code
    	FROM	" & mydata & ".dbo.mydata mydata 
    	INNER
    	JOIN	" & mydata & ".dbo.[Country_Region Mapping] CRM ON
    			CRM.[Company Code]=mydata.[Company Code]
    			AND CRM.Country IN(" & selection1 & ")
    	INNER
    	JOIN	" & mydata & ".dbo.[Cost Center mapping] CCM ON
    			CCM.[Cost Center]=mydata.[Cost Center]
    			AND CCM.[Sub Product UBR Code] IN(" & selection & ")
    	INNER
    	JOIN	" & mydata & ".dbo.[Cost Element Mapping] CEM ON
    			CEM.CE_SR_NO=mydata.[Unique Indentifier 1]
    			AND CEM.FSI_LINE3_code IN(" & selection2 & ")
    	WHERE	mydata.year='" & ComboBox4.Value & "'
    		AND mydata.period='" & ComboBox3.Value & "'
    		AND mydata.[Document Type]='" &Left(ComboBox11.Value, 2)& "'
    	"
    Ken

    Maverick Software Design

    (847) 864-3600 x2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •