Pulling @variables from a SQL query/sproc into vb/vba vairable


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 5 of 5

Thread: Pulling @variables from a SQL query/sproc into vb/vba vairable

  1. #1
    Join Date
    Apr 2009
    Posts
    3

    Unhappy Pulling @variables from a SQL query/sproc into vb/vba vairable

    Been struggling with this for a few hours now.

    I have a SQL query which gives three vairables (however I can get it to push these vairables out in a 'Select @StatusOn, @StatusOff, @StatusTest' at the end. (this is stored as strSQL)

    My code is as follows then I will go through what I have tried

    *******************************************************
    Dim cnn As New ADODB.Connection
    Dim cmd As New ADODB.Command
    Dim prm As New ADODB.Parameter

    With cnn
    .Open "DRIVER={SQL Server};SERVER=MYSERVER;DATABASE=MYDB;"
    End With

    With cmd
    .ActiveConnection = cnn
    .CommandTimeout = 0
    .CommandType = adCmdText
    .CommandText = strSQL
    End With



    cmd.Execute
    Set prm = cmd.CreateParameter("StatusOn", adDate, adParamOutput, , **@SatausOn**)

    cmd.Parameters.Append prm

    Set prm = cmd.CreateParameter("StatusOff", adDate, adParamOutput, , **@StatusOff**)

    cmd.Parameters.Append prm

    Set prm = cmd.CreateParameter("StatusTest", adDate, adParamOutput, , **@StatusTest**)
    cmd.Parameters.Append prm
    Set cnn = Nothing

    *******************************************************

    I have pushed the SQL and got it to dump it out then ran it manually and the SQL is bringing back values

    I have tested the the parameters are being created and appended to the cmd howver I cant get the prm to be filled with the output value of the query.
    I dont know what to put in **@StatusOn**
    I could get the query to return a single record at a time, however this sits within a loop and dont want to hit the DB 3 times per loop

    The query can either put out records as variables or in a single row with 3 fields.

    I am at a loss with this, I know it has to be possible

    Any help would be fantastic

  2. #2
    Join Date
    Mar 2009
    Location
    Italy - Breganze (VI)
    Posts
    120
    Why you call cmd.Execute before to pass parameters?

    You should be call next CreateParameter. Like:

    Code:
    Set prm = cmd.CreateParameter("StatusOn", adDate, adParamOutput, , **@SatausOn**)
    and so on...
    then
    Code:
    cmd.Execute
    Also, you must call
    cnn.Close
    before to call
    Set cnn = Nothing

    HTH

  3. #3
    Join Date
    Apr 2009
    Posts
    3
    I have tried that, however it still wont populate, i need to assign the parameter with a value from the query (where **@StatusOn** is) I have even put Now() to pass the a date through and this works fine

  4. #4
    Join Date
    Mar 2009
    Location
    Italy - Breganze (VI)
    Posts
    120
    You code is wrong:

    Set prm = cmd.CreateParameter("StatusOn", adDate, adParamOutput, , **@SatausOn**)


    This is correct:

    Set prm = cmd.CreateParameter("@StatusOn", adDate, adParamOutput, , SatausOn)

    Where SatausOn (or StatusOn?) is the value you pass to parameter.

    HTH

  5. #5
    Join Date
    Apr 2009
    Posts
    3

    Red face Params are fine

    The param code is fine as have past non query vlaues into them and managed to pull a vlaue out of the object
    Have had some more time to look at this today.

    It seems there is issue with my SQL (eventhough it works fine when I place it into Query Analyser. the rst is just not getting populated hence why methods such as rst.fields(0).value through an 'does not exist' error

    To create the strSQL I am concatinationg allot so there is allot of strSQL = strSQL & "more sql"

    The string is large enough to take the entire code as got it to dump it into a cell on the spreadsheet and then pasted and copied it in to query analyser and works fine.

    So have broken it down and even added got it to make a temp table on the SQL server insert values, select the values and then drop table (resource heavy I know but good to test) I am doing these under different commands each at a time as follws

    Code:
    With cmd
    .ActiveConnection = cnn
    .CommandTimeout = 0
    .CommandType = adCmdText
    .CommandText = strSETUP
    .Execute
    End With
    
    With cmd
    .ActiveConnection = cnn
    .CommandTimeout = 0
    .CommandType = adCmdText
    .CommandText = strINSERT
    End With
    
    With rst
    .Open strSELECT, cnn, adOpenStatic
    End With
    
    With cmd
    .ActiveConnection = cnn
    .CommandTimeout = 0
    .CommandType = adCmdText
    .CommandText = strDROP
    End With
    Everything else tests out fine strSETUP is a create table string, strINSERT is the insert string and so on

    the issue seems to be with running the bulk of the load which is in strINSERT as follows

    Code:
    strINSERT = strINSERT & "Declare @ProviderID as int Declare @StartState as varchar(15) Declare @StartStateDate as datetime "
    strINSERT = strINSERT & "Declare @StartDate as datetime Declare @StatusStartDate as datetime Declare @StatusEndDate as datetime "
    strINSERT = strINSERT & "Declare @StatusLength as datetime Declare @StatusOFF as datetime Declare @StatusOn as datetime "
    strINSERT = strINSERT & "Declare @StatusTest as datetime Declare @Status as varchar(15) Declare @Count as int "
    strINSERT = strINSERT & " Set @Providerid = " & intProviderID & " Set @StartState = (select top 1 originalstatus from tbl_apfadminlog where datestamp >=GetDate()-31 and ProviderID = @Providerid order by datestamp asc)"
    strINSERT = strINSERT & " Set @StartStateDate = (select top 1 datestamp from tbl_apfadminlog where datestamp >=GetDate()-31 and ProviderID = @Providerid order by datestamp asc)"
    strINSERT = strINSERT & " Set @StartDate = GetDate()-31 Set @StartStateDate = (select top 1 datestamp from tbl_apfadminlog where datestamp >=GetDate()-31 and ProviderID = @Providerid order by datestamp asc )"
    strINSERT = strINSERT & " Set @Count = (select count(datestamp) from tbl_apfadminlog where DateStamp > @StartDate and ProviderID = @Providerid) Set @StatusOff = 0"
    strINSERT = strINSERT & " Set @StatusOn = 0 Set @StatusTest = 0 Set @StatusLength = @StartStateDate - @StartDate"
    strINSERT = strINSERT & " IF @StartState = 'On' Set @StatusOn = @StatusOn + @StatusLength Else IF @StartState = 'Off' Set @StatusOff = @StatusOff + @StatusLength Else"
    strINSERT = strINSERT & " IF @StartState = 'On Test Partner' Set @StatusTest = @StatusTest + @StatusLength Else IF @StartState = 'ISPREVIEW' Set @StatusTest = @StatusTest + @StatusLength"
    strINSERT = strINSERT & " While @Count > 0 Begin Set @Count = (select count(datestamp) from tbl_apfadminlog where DateStamp > @StartDate and ProviderID = @Providerid)"
    strINSERT = strINSERT & " Set @Status = (Select top 1 UpdatedStatus from tbl_apfadminlog where DateStamp > @StartDate  and ProviderID = @Providerid)"
    strINSERT = strINSERT & " Set @StatusStartDate = (Select top 1 datestamp from tbl_apfadminlog where DateStamp > @StartDate  and ProviderID = @Providerid)"
    strINSERT = strINSERT & " Set @StartDate = (Select top 1 datestamp from tbl_apfadminlog where DateStamp > @StartDate  and ProviderID = @Providerid)"
    strINSERT = strINSERT & " Set @StatusEndDate = (Select top 1 datestamp from tbl_apfadminlog where DateStamp > @StartDate  and ProviderID = @Providerid)"
    strINSERT = strINSERT & " IF @StatusEndDate is NULL Set @StatusEndDate = GetDate() Set @StatusLength = @StatusEndDate-@StatusStartDate"
    strINSERT = strINSERT & " IF @Status = 'On' Set @StatusOn = @StatusOn + @StatusLength Else IF @Status = 'Off' Set @StatusOff = @StatusOff + @StatusLength Else"
    strINSERT = strINSERT & " IF @Status = 'On Test Partner' Set @StatusTest = @StatusTest + @StatusLength Else IF @Status = 'ISPREVIEW' Set @StatusTest = @StatusTest + @StatusLength"
    strINSERT = strINSERT & " End INSERT INTO #Status Select @StatusOn,@StatusOff,@StatusTest "
    This is the same as the original code I started with however it didn't originaly have the insert as there wasn't a table (obviously)

    for easier reading thats

    Code:
    Declare @ProviderID as int 
    Declare @StartState as varchar(15) 
    Declare @StartStateDate as datetime 
    Declare @StartDate as datetime 
    Declare @StatusStartDate as datetime 
    Declare @StatusEndDate as datetime 
    Declare @StatusLength as datetime 
    Declare @StatusOFF as datetime 
    Declare @StatusOn as datetime 
    Declare @StatusTest as datetime 
    Declare @Status as varchar(15) 
    Declare @Count as int  Set @Providerid = 11 
    Set @StartState = (select top 1 originalstatus from tbl_apfadminlog where datestamp >=GetDate()-31 and ProviderID = @Providerid order by datestamp asc) 
    Set @StartStateDate = (select top 1 datestamp from tbl_apfadminlog where datestamp >=GetDate()-31 and ProviderID = @Providerid order by datestamp asc) 
    Set @StartDate = GetDate()-31 
    Set @StartStateDate = (select top 1 datestamp from tbl_apfadminlog where datestamp >=GetDate()-31 and ProviderID = @Providerid order by datestamp asc ) 
    Set @Count = (select count(datestamp) from tbl_apfadminlog where DateStamp > @StartDate and ProviderID = @Providerid) Set @StatusOff = 0 
    Set @StatusOn = 0 Set @StatusTest = 0 
    Set @StatusLength = @StartStateDate - @StartDate IF @StartState = 'On' Set @StatusOn = @StatusOn + @StatusLength Else IF @StartState = 'Off' 
    Set @StatusOff = @StatusOff + @StatusLength Else IF @StartState = 'On Test Partner' 
    Set @StatusTest = @StatusTest + @StatusLength Else IF @StartState = 'ISPREVIEW' 
    Set @StatusTest = @StatusTest + @StatusLength While @Count > 0 
    Begin 
    Set @Count = (select count(datestamp) from tbl_apfadminlog where DateStamp > @StartDate and ProviderID = @Providerid) 
    Set @Status = (Select top 1 UpdatedStatus from tbl_apfadminlog where DateStamp > @StartDate  and ProviderID = @Providerid) 
    Set @StatusStartDate = (Select top 1 datestamp from tbl_apfadminlog where DateStamp > @StartDate  and ProviderID = @Providerid) 
    Set @StartDate = (Select top 1 datestamp from tbl_apfadminlog where DateStamp > @StartDate  and ProviderID = @Providerid) 
    Set @StatusEndDate = (Select top 1 datestamp from tbl_apfadminlog where DateStamp > @StartDate  and ProviderID = @Providerid) IF @StatusEndDate is NULL 
    Set @StatusEndDate = GetDate() 
    Set @StatusLength = @StatusEndDate-@StatusStartDate IF @Status = 'On' Set @StatusOn = @StatusOn + @StatusLength Else IF @Status = 'Off' 
    Set @StatusOff = @StatusOff + @StatusLength Else IF @Status = 'On Test Partner' Set @StatusTest = @StatusTest + @StatusLength Else IF @Status = 'ISPREVIEW' Set @StatusTest = @StatusTest + @StatusLength End 
    Select @StatusOn,@StatusOff,@StatusTest
    The issue seems to be I just cant get this to transact using the .execute method. It just skips right over it even when I am not expecting any result from it. Unfortunatly I dont have the option of creating a sproc with this in a just calling that.

    Is there a constraint on how big that command is, it doesn't lag when running it through Query analyser. (shouldnt be a problem)

    Can I not execute this query through VBA (surely not)

    This feels like a brick wall

    Many Thanks in Advance


    Getting closer to what the problem is I think

Similar Threads

  1. Replies: 2
    Last Post: 05-18-2003, 11:16 PM
  2. SQL Server Developer in Calgary Canada
    By Dan Staggs in forum Careers
    Replies: 0
    Last Post: 08-07-2002, 12:04 PM
  3. Help with looping a dynamic sql string
    By jeff in forum ASP.NET
    Replies: 1
    Last Post: 06-07-2002, 12:12 PM
  4. treeview without using ActiveX
    By Aaron Coombs in forum ASP.NET
    Replies: 2
    Last Post: 09-26-2001, 04:29 AM
  5. myLittleAdmin SQL Server version
    By myLittleTools.net in forum web.announcements
    Replies: 0
    Last Post: 03-10-2001, 01:35 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center