MSSQL 2005 Stored Procedure call

[dickep]

I know this has been asked, but have not seen an answer that I can understand/use

My function is below.

Func mQueryDatabase(ByRef $Cat, ByRef $Attrib, $DefaultValue = 100, $DefaultUsed = 0)
    $oADODB = _SQL_Startup()
    If $oADODB = $SQL_ERROR Then MsgBox(0 + 16 + 262144, "Error", _SQL_GetErrMsg())

    If _sql_Connect($oADODB, "Server", "MyDB", "UserName", "MyPassword") = $SQL_ERROR Then
        MsgBox(0 + 16 + 262144, "Error", _SQL_GetErrMsg())
        _SQL_Close()
        Exit
    EndIf
    
    $FNPSelect1 = "SELECT Col3 FROM MyParameters "
    $FNPSelect2 = "WHERE Cat = '" & $Cat & "' AND Attrib = '" & $Attrib & "';"

    
    _SQL_Close()

EndFunc

Now, I just want to execute the stored procedure "spGetSomething" with the parameters above. I have gotten this far, but am not sure how to continue. I just want to get the single value I am looking for (namely just the "Col3" value that matches the "WHERE" statement).

I am using _sql.au3 for this and have looked at the _SQL_FetchData and _SQL_Execute but either I am too dense to understand how to use these or they really cannot be used like I want.

Let me know what I can do to finish up this function - it will be used many times if I can get it to work.

Thanks

E

[Zedna]

Search forum for these pieces of code

$oConn = ObjCreate("ADODB.Connection")
$oConn.Open($DSN)
$oConn.Execute($SQL)

[dickep]

Thanks but the search engine did not like it when I put in "$oConn.Execute($SQL)" in the search box. Came back with message ::No results found for '$oconnexecute$sql'.::

Maybe need some help with the search function - can't seem to do what I would like to do.

E

[Zedna]

Then just search for

ADODB.Connection

[dickep]

Tried but it seems the internal search engine keeps dropping the period(.) and makes it one word. Any more ideas? I am very frustrated on the search ability here.

E

[Zedna]

Tried but it seems the internal search engine keeps dropping the period(.) and makes it one word. Any more ideas? I am very frustrated on the search ability here.

E

Search must have changed somehow :-(

Search for :

objcreate ADODB Connection

And also search in Examples section only.

[dickep]

Zedna, tried your suggestions. Did not get any results that pertain to how to call a stored procedure that returns values. Stored procedures can return values but can't figure out how to access that - calling it correctly might help.

Thanks all

E

[Zedna]

I will not waste my time by teaching you how to search by google/Autoit forum.

Another tip for you:

http://www.w3schools.com/ado/met_conn_execute.asp

http://www.w3schools.com/ado/met_rs_open.asp

[Zedna]

Example code snippet from my working script how it can be called in AU3 (this can be accomodated also for stored procedures -->adCmdText change to adCmdStoredProc)

$oConn = ObjCreate("ADODB.Connection")
    $oConn.ConnectionTimeout = 3 ; default is 15 s 
    
    $oRS = ObjCreate("ADODB.Recordset")
    $oRS.CursorType = 3 ; adOpenStatic
    $oRS.LockType = 3 ; adLockOptimistic

    $DSN = "Driver={SQL Server};Server=%server%;Database=%databaze%;Uid=%jmeno%;Pwd=%heslo%;"
    $DSN = StringReplace($DSN,"%server%", $ini_server)
    $DSN = StringReplace($DSN,"%databaze%", $ini_databaze)
    $DSN = StringReplace($DSN,"%jmeno%", $ini_jmeno)
    $DSN = StringReplace($DSN,"%heslo%", $ini_heslo)
    
    $oConn.Open($DSN)

    $rowcount = -1
    $SQL_command = "..."
    $oConn.Execute($SQL_command, $rowcount, 129) ; adCmdText=1 + adExecuteNoRecords=0x80 (128)

Edited December 5, 2010 by Zedna