Assign SQL Variable to VBA Variable

[WaveOut]

I'm executing the following query:

mysql = "local oilhours real;oilhours = 0;for(select * from aggregates where name = 'AA-FO-MFM' and period = 600 and avg > 15 and (ts > '01-jan-04' and ts < '01-feb-04') do oilhours = oilhours + 1;end;"


when I execute the above query .... oilhours = 36.6 which is correct. Now what I want to do is in my VBA code I want to assign the value of oilhours to the variable myoilhours. How is this done. I think it is going to require parameters but not sure.

Thanks,
David

 

[fredericofonseca]

You should use parameters, but you don't have to.

ssql = "select * from tbl where tblfield = '" & text1.value & "'"

where text1.value can be anything from a text box value to a grid cell or any type of object/variable value.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[WaveOut]

Frederico,
Thanks for the help. But what I was wanting to do was take the value of oilhours and place it in a VBA variable. Oilhours is a sql variable and I need to get it's contents into a VBA variable.

I need something like this:

mysql = "local oilhours real;oilhours = 0;for(select * from aggregates where name = 'AA-FO-MFM' and period = 600 and avg > 15 and (ts > '01-jan-04' and ts < '01-feb-04') do oilhours = oilhours + 1;end;"

' now oilhours has the data I need so I need to do something like

dim myoilhours as integer
myoilhours = oilhours

Now I might be going about this all the wrong way. If so please let me know.

David

 

[PHV]

If your DBMS is SQL-Server, you may consider creating stored procedure with input and output parameters and then playing with ADO.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[fredericofonseca]

David,

Sorry, I didn't look to the SQL well.

Stored procedure as PHV said is the only way.

Works with most DB's, but they are set-up differently in each one.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[WaveOut]

Ok now I'm trying a diffrent route. I've saved the following function to the sqlserver ProcedureDef area:

FUNCTION Oil_Hours(start_time timestamp, end_time timestamp, item char(35))
local lOilHours integer;
for(select * from aggregates where name = item and period = 600 and avg > 15 and ts > start_time and ts < end_time)

do
lOilHours = lOilHours + 1;
end
return lOilHours/60; 'I need this returned value
end


I've check the above stored function and it is working correctly. Now I need to access this stored function via VBA. Here is what I have so far:

'start vba code snippet
'i've already opened my ado connection

set cmd = new ADODB.Command

With cmd
.ActiveConnection = ADO_Connection
.CommandText = "Oil_Hours"
.CommandType = adCmdStoredProc

.Parameters.Append .CreateParameter("output",adInteger,adParamReturnValue)
.Parameters.Append .CreateParameter ("start_time",adDate, adParamInput, , "01-may-03")
.Parameters.Append .CreateParameter ("end_time", adDate, adParamInput, ,"01-jan-04")
.Parameters.Append .CreateParameter ("item", adChar, adParamInput, , "AA-FO-MFM-F")
.Parameters.Refresh
.Execute

' Now to display the returned results
MsgBox (.Parameters.Item(0).Value)

End With

' end VBA code snippet

Now when I exectue the above code I get the following error message:
Run-time error '3708'
Parameter object is improperly defined. Inconsistent or incomplete information was provided. The following line is then highlighted

.Parameters.Append .CreateParameter ("item", adChar, adParamInput, , "AA-FO-MFM-F")

I've tried changing the adChar to adVarChar, but I get the same error. How can I get the returned value of the Stored Function back to VBA?

Any help is much appreciated!!!

David

 

[fredericofonseca]

try and define the size of the variable, as you ommited then all.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[WaveOut]

Thanks Frederico that did the trick!

David