My Stored procedure always returned the same value

9 Dec 2004 2:53 AM

Peter

Hi, there

I have created an stored procedure using the DDL below for my MS Access
Database and no error occurs. Also it can create an stored procedure if I
changed the parameter from "[zSampleName]" to ""@zSampleName".

          OleDbcmd.CommandText = _
          "CREATE PROCEDURE udpGetSampleIDByName" & vbCrLf & _
          "([zSampleName] VarChar(64))" & vbCrLf & _
          "AS" & vbCrLf & _
          "Select zSampleID FROM T1 " & _
          "Where zSampleName =[zSampleName]"

          OleDbcmd.ExecuteNonQuery()

Then I called the stored procedure like this. What bothered me these days is

: The tempID always got the same value "1" no matter what parameter value I

set (a, b or c).
        With OleDbcmd
            .Connection = connNoTran' This is an active oledbconnection
            .CommandType = CommandType.StoredProcedure
            .CommandText = "udpGetSampleIDByName"

            .Parameters.Add("[zSampleName]", OleDbType.VarChar)
            .Parameters(0).Value = "a"

            dim tempID as integer
            Dim dr as new OleBbDataReader = OleDb.ExecuteReader
            If dr.Read() Then
                tempID = sdr(0)
            Else
                tempID = 0
            End If

A sample of T1 table is:

zSampleName(primary key) zSampleID
       a                      1
       b                      2
       c                      3
       d                      4
       .                      .
       .                      .
       .                      .

Thanks in advance

'---------------------------------------------------------------------------
---------------
Background:

All my codes have been tested under SQL Server2000 and VB.NET last month.
But I decided to turn to MS Access after reading many stuffs from internet.
Because my project is a desktop program and it doesn't need to consider
concurrence. Due to the one file mechanism MS Access has many advantages in
terms of ease of deployment and maintenance.

Peter

10 Dec 2004 9:18 AM

Peter

Hi,

I always debug with a normal select statement and then make a stored
procedure from it.I have tested my select statement many times and every
time it always return the value I want. But things get worse when I created
a stored procedure and called it. The stored procedure always returned the
same value no matter what parameter value I passed to it. I wonder if there
are some subtle differences in stored procedure between MS Access2000 and
SQL Server2000 .

There is few information about how to create and call a MS Access stored
procedure with ADO.NET. Most of them is for SQL Server. In fact, all my
codes have been tested well under SQL Server2000 and VB.NET several days
ago. But I decided to turn to MS Access after reading many stuffs from
internet.Because my project is a desktop program and it doesn't need to
consider concurrency violation. Due to the single file storage mechanism MS
Access has many advantages in terms of deployment and maintenance.

Peter