Why does the stored procedure always return the same value?

P

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 ,c or other string value).
With OleDbcmd
.Connection = connNoTran' This is an active oledbconnection
.CommandType = CommandType.StoredProcedure
.CommandText = "udpGetSampleIDByName"

.Parameters.Add("[zSampleName]", OleDbType.VarChar)
.Parameters(0).Value = "a"' or "b" ,"c", "dtertwer"

Dim tempID as integer
Dim dr as new OleBbDataReader = OleDb.ExecuteReader
If dr.Read() Then
tempID = dr(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
 
H

Herfried K. Wagner [MVP]

Peter said:
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".
Click to expand...

Notice that there is a separate group for ADO.NET-related questions
available:

=> microsoft.public.dotnet.framework.adonet.
 
C

Cor Ligthert

Peter,

When you have a problem, why do you than first not do it just with a normal
select statement.
When that is done, you can make always a stored procedure from it.

And than as everybody else does it something as

That cost a lot less time and needs less debuging time.

Select zSampleID FROM T1 Where zSampleName = @zSampleName"
Just my idea

Cor


Peter said:
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 ,c or other string value).
With OleDbcmd
.Connection = connNoTran' This is an active oledbconnection
.CommandType = CommandType.StoredProcedure
.CommandText = "udpGetSampleIDByName"

.Parameters.Add("[zSampleName]", OleDbType.VarChar)
.Parameters(0).Value = "a"' or "b" ,"c", "dtertwer"

Dim tempID as integer
Dim dr as new OleBbDataReader = OleDb.ExecuteReader
If dr.Read() Then
tempID = dr(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
Click to expand...
 
P

Peter

Cor

Like you said, I always debug with a normal select statement and then make a
stored procedure from it. But do you think a stored procedure will always
work if the relevant select statement can work well?

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
think there must be 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
 
P

Peter

I see, I added it in the ADONET newsgroup yesteday before attaching here but
no response till now.
 
C

Cor Ligthert

Peter,

Without debuging this is always troublefull,

However you said you did it with SQL, was that with SQLClient because as far
as I can see is the only thing really different in coding SQLClient and
OleDB the behaviour of the commandparameters.

In SQLClient they are by name and in OleDB by the sequence they exist in the
selectstatement.
And you use your name zsample twice, did you try my suggestion to use
another name.

This is just a gues by the way, I did not test it.

Cor
 
P

Peter

Cor,

You are right. The only thing really different in coding SQLClient and
OleDB is the behaviour of the commandparameters.In SQLClient they are by
name and in OleDB by the sequence they exist in the select statement.

I have made my stored procedure codes working well by discarding the
parameter declaration line of the SQL string "([zSampleName] VarChar(64))" &
vbCrLf & _

Thanks for your help.

Peter
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

My Stored procedure always returned the same value 1
How to excute a MS Access Stored procedure? 1
getting table adapter return values from stored procedures? 1
Stored Procedure Parameters 1
C# passes parameter but return value always -1 3
How do you call a stored procedure that returns a value? 3
Entity Framework Return Value from Stored Procedure - Getting Error 1
Stored procedure not returning a value in @RETURN_VALUE 3

Top