My Stored procedure always returned the same value

[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

 

[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