-
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
-
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
Also, you must call
cnn.Close
before to call
Set cnn = Nothing
HTH
-
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
-
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
-
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
-
By zicq in forum Database
Replies: 2
Last Post: 05-18-2003, 10:16 PM
-
By Dan Staggs in forum Careers
Replies: 0
Last Post: 08-07-2002, 11:04 AM
-
Replies: 1
Last Post: 06-07-2002, 11:12 AM
-
By Aaron Coombs in forum ASP.NET
Replies: 2
Last Post: 09-26-2001, 03:29 AM
-
By myLittleTools.net in forum web.announcements
Replies: 0
Last Post: 03-10-2001, 12: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
-
Forum Rules
|
Development Centers
-- Android Development Center
-- Cloud Development Project Center
-- HTML5 Development Center
-- Windows Mobile Development Center
|