Board index MS SQL Server

newbie proc question

newbie proc question

Post by Jazz » Wed, 26 Mar 2003 14:21:39



I am having trouble getting some code to run

working with SL Server 2000 and Vb 6.0

trying to make the following work... but it doesn't...
why not??

'A sample procedure call from my database

   SELECT     SiteIdass
   From tblTempTrans

   return 0

' end sample

'note that if I do a similar call passing no parameters
it works

Public Function runCMDbySiteIDAS(ByRef objConn As
ADODB.Connection, _
      ByVal strCommand As String, _
      ByVal iIn) As Long

        Dim objCMD As New ADODB.Command
        Set objCMD.ActiveConnection = objConn
        objCMD.CommandText = strCommand

        With objCMD.Parameters
           .Append objCMD.CreateParameter("RC",
adInteger, adParamReturnValue)
           .Append objCMD.CreateParameter("ProdNo",
adInteger, adParamInput, , iIn)
        End With
        objCMD.Execute

   set objcmd=nothing

end function


but clearly I am ( or so I think!)

help please

 
 
 
Top

newbie proc question

Post by sampang » Wed, 26 Mar 2003 14:35:11


Pls provide the VB code which calls the Function runCMDbySiteIDAS.

Thanx
Srinivas


> I am having trouble getting some code to run

> working with SL Server 2000 and Vb 6.0

> trying to make the following work... but it doesn't...
> why not??

> 'A sample procedure call from my database

>    SELECT     SiteIdass
>    From tblTempTrans

>    return 0

> ' end sample

> 'note that if I do a similar call passing no parameters
> it works

> Public Function runCMDbySiteIDAS(ByRef objConn As
> ADODB.Connection, _
>       ByVal strCommand As String, _
>       ByVal iIn) As Long

>         Dim objCMD As New ADODB.Command
>         Set objCMD.ActiveConnection = objConn
>         objCMD.CommandText = strCommand

>         With objCMD.Parameters
>            .Append objCMD.CreateParameter("RC",
> adInteger, adParamReturnValue)
>            .Append objCMD.CreateParameter("ProdNo",
> adInteger, adParamInput, , iIn)
>         End With
>         objCMD.Execute

>    set objcmd=nothing

> end function

> the system keeps telling me that I am not pulling in

> but clearly I am ( or so I think!)

> help please


 
 
 
Top

newbie proc question

Post by Jazza » Thu, 27 Mar 2003 06:51:32


The connection itself works fine.. I know this because I created a dummy
procedure with no params to it, and the
procedure call then worked fine....

sample connection string layout as follows....

  Set objConn = New ADODB.Connection

   With objConn
        .Provider = "sqloledb.1"
' Specify the OLE DB provider.
        .Properties("Data Source").Value = "number"              ' Set
SQLOLEDB connection properties.
        .Properties("Initial Catalog").Value = "database using"
' Set SQLOLEDB connection properties.
        .Properties("Integrated Security").Value = "SSPI"
        .Properties("User ID").Value = "Username"
        .Properties("Password").Value = "<PassWord>"
        .Open
        .CommandTimeout = 1000

   End With

   If objConn.State = adStateOpen Then
      MsgBox "it Opened successfully"
   End If

and the connection does open..

Jazza

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 
Top

newbie proc question

Post by Bob Barrow » Thu, 27 Mar 2003 08:09:58


I'm assuming strCommand contains "up_example ". You need to set the
objCMD.CommandType to adCmdStoredProc:

objCMD.CommandType = adCmdStoredProc

Bob Barrows


> I am having trouble getting some code to run

> working with SL Server 2000 and Vb 6.0

> trying to make the following work... but it doesn't...
> why not??

> 'A sample procedure call from my database

>    SELECT     SiteIdass
>    From tblTempTrans

>    return 0

> ' end sample

> 'note that if I do a similar call passing no parameters
> it works

> Public Function runCMDbySiteIDAS(ByRef objConn As
> ADODB.Connection, _
>       ByVal strCommand As String, _
>       ByVal iIn) As Long

>         Dim objCMD As New ADODB.Command
>         Set objCMD.ActiveConnection = objConn
>         objCMD.CommandText = strCommand

>         With objCMD.Parameters
>            .Append objCMD.CreateParameter("RC",
> adInteger, adParamReturnValue)
>            .Append objCMD.CreateParameter("ProdNo",
> adInteger, adParamInput, , iIn)
>         End With
>         objCMD.Execute

>    set objcmd=nothing

> end function

> the system keeps telling me that I am not pulling in

> but clearly I am ( or so I think!)

> help please

 
 
 
Top

newbie proc question

Post by Jazza » Thu, 27 Mar 2003 09:00:58


Thanks Bob,

I normally do set the commandtype to adcmdstoredproc,

forgot to do it this time... thus it didn't work!!
made changes and it works now

Jazza

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 
Top

1. Newbie Question about ProC/C++ v2.2

The problem is that I have a function that has a parameter,
and I would like that parameter value to be used in a SELECT
statement to my ORACLE table so that I can retrieve multiple
records.

I want to be able to pass to the GetChildrenNodes an integer
and get all of those out of my database.

Below is my sample pre-compiled source code (I deleted all of
the header files I included, but basically this is the source
code I want to Pre-Compile)  See below the test code for the
errors I am getting....

EXEC SQL BEGIN DECLARE SECTION;
  VARCHAR  sqlstmt[STMT_LEN];
  VARCHAR  name[NAME_LEN];
  int      idx;
  int      parent;
EXEC SQL END DECLARE SECTION;

void GetChildrenNodes( const int& parentnode)
{

    strcpy((char *)sqlstmt.arr, "SELECT name, idx, parent FROM
           my_tree WHERE parent = :v1");
    sqlstmt.len = (short) strlen((char *)sqlstmt.arr);
    EXEC SQL PREPARE S FROM :sqlstmt;
    EXEC SQL DECLARE C CURSOR FOR S;
    EXEC SQL OPEN C USING :parentnode;
    EXEC SQL WHENEVER NOT FOUND DO break;
    while (1)
    {
        EXEC SQL FETCH C INTO :name, :idx, :parent;
        name.arr[name.len] = '\0';
        cout << (char *)name.arr << idx << parent <<endl;
    }
    EXEC SQL CLOSE C;
    EXEC SQL COMMIT RELEASE;
    return;

void main()
{
    GetChildrenNodes(0);
    GetChildrenNodes(1);
    GetChildrenNodes(2);

1st) This will not pre-compile
2nd) When I declare a new variable in the declare section
     called tempparentnode and then set
     tempparentnode=parentnode and subsitute the line:
         EXEC SQL OPEN C USING :parentnode;
     with
         EXEC SQL OPEN C USING :tempparentnode;
     STILL DOESN'T WORK, I get an oracle error of:
         ORA-01405: fetched column value is NULL

Any ideas as to what I am missing or a better way to do things.

Tim

2. copy database via DTS

3. Newbie Question - dual Select in Stored Proc

4. Remote Servers/Users Question

5. newbie-- how to return proc values to another proc to be used in a cursor

6. Year 2000

7. Newbie question on ProC/C++

8. ADO Errors collection

9. newbie - stored proc question

10. newbie stored proc questions

11. Calculate Business Days Stored Proc Problem (Newbie Question)

12. Newbie stored proc syntax question

13. newbie trying to execute a stored proc from within a stored proc


All times are UTC
Board index