"could not find stored procedure" that used with transaction

"could not find stored procedure" that used with transaction

Post by robgrue » Sat, 07 Feb 2004 00:37:44


If you are trying to use transactions, you might want to have a look at
this sample: http://www.yqcomputer.com/
is to only execute the stored proceedure otherwise you'll probably want to
do something like what's described within Q321902
( http://www.yqcomputer.com/ ).

Thanks! Robert Gruen
Microsoft, VB.NET

This posting is provided "AS IS", with no warranties, and confers no rights.


--------------------
| Thread-Topic: "could not find stored procedure" that used with transaction
| thread-index: AcPraEonnhZxZn1gRRuvNjwLn85o6A==
| X-Tomcat-NG: microsoft.public.vsnet.debugging
| From: "=?Utf-8?B?SGFycnkgUGFyaw==?=" < XXXX@XXXXX.COM >
| Subject: "could not find stored procedure" that used with transaction
| Date: Wed, 4 Feb 2004 13:46:05 -0800
| Lines: 21
| Message-ID: < XXXX@XXXXX.COM >
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.vsnet.debugging
| Path: cpmsftngxa07.phx.gbl
| Xref: cpmsftngxa07.phx.gbl microsoft.public.vsnet.debugging:4888
| NNTP-Posting-Host: tk2msftcmty1.phx.gbl 10.40.1.180
| X-Tomcat-NG: microsoft.public.vsnet.debugging
|
| "could not find stored procedure"
when i tried to make functions that insert and return back values,
i made some stored procedure.
and that procedure have index like ;1, ;2 etc

and i tried to make function to work with transactions.
but it's not working.
vs c# de *** return only "could not find stored procedure"

here is what i tried to test

using transaction, command, ExecuteScalar, commandtext : e.Message :
"could not find stored procedure"
using transaction, command, ExecuteNonQuery, commandtext : e.Message :
"could not find stored procedure"
using command, ExecuteScalar : working well
using transaction, command, Parameters, ExecuteNonQuery, different stored
procedure name : working well
using command, Parameters, ExecuteNonQuery, different stored procedure name
: working well

i think if i use transaction,
it's working not well..

can you let me know how can i solve this problem?
|
 
 
 

"could not find stored procedure" that used with transaction

Post by SGFycnkgUG » Sat, 07 Feb 2004 03:01:11

Thanks Robert

i tried to make with your advice

but i want to know when i tried to use transaction, stored procedure together
at this time i need to add parameters object to work well

how can i make program with transaction, stored procedure and without parameters object.

i made simple table and stored procedur
=========================================
create table Test(a varchar(10), b varchar(10)
g

create procedure spTestS
@A VARCHAR(10)
@B VARCHAR(10
a
INSERT INTO TEST(A, B
VALUES(@A, @B
G
==========================================

and here is what i tested for this proble

==========================================
public Form1(

InitializeComponent()

/
// TODO: Add any constructor code after InitializeComponent cal
/

string strSQL = "spTestSP 'aa', 'bb'"

//1.SP without transaction : work wel
SP_WithOut_Tran(strSQL, new SqlConnection(Form1.strLocalCnnString))

//2.SP with transaction (Error.. but i really want to use this way...
//Message: "Could not find stored procedure 'spTestSP 'aa', 'bb''.
//Tran_SP(strSQL, new SqlConnection(Form1.strLocalCnnString))

//3.SP with transaction and parameters : work wel
Tran_SP_Param(new SqlConnection(Form1.strLocalCnnString))



public void SP_WithOut_Tran(string strSql, SqlConnection cnn

bool blnStateOpen = false

SqlCommand cmd = new SqlCommand(strSql, cnn)
if (cmd.Connection.State == ConnectionState.Closed

cmd.Connection.Open()
blnStateOpen = true


tr

cmd.ExecuteNonQuery()

catch(Exception e

string s = e.Message

finall

if (blnStateOpen
cmd.Connection.Close()



public void Tran_SP(string strSql, SqlConnection cnn

cnn.Open()
SqlTransaction trans
trans = cnn.BeginTransaction(IsolationLevel.Serializable, "HarryTest")

SqlCommand cmd = new SqlCommand(strSql, cnn)
cmd.Transaction = trans
cmd.CommandType = CommandType.StoredProcedure;

tr

cmd.ExecuteNonQuery()
trans.Commit()

catch(Exception e

trans.Rollback("HarryTest")
string s = e.Message

finall

cnn.Close()



public void Tran_SP_Param(SqlConnection cnn

cnn.Open()
SqlTransaction trans
trans = cnn.BeginTransaction(IsolationLevel.Serializable, "HarryTest")

SqlCommand cmd = new SqlCommand("", cnn)
cmd.Transaction = trans
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "spTestSP";

SqlParameter[] SqlParam = new SqlParameter[2]
SqlParam[0] = new SqlParameter("@a", SqlDbType.Char, 10, ParameterDirection.Input,
true,0,0,"ReceiveNo", DataRowVersion.Current, "aa");
cmd.Parameters.Add(SqlParam[0])

SqlParam[1] = new SqlParameter("@b", SqlDbType.Char, 8, ParameterDirection.Input,
true,0,0,"ProductNo", DataRowVersion.Current, "bb");
cmd.Parameters.Add(SqlParam[1])

tr

cmd.ExecuteNonQuery()
trans.Commit()

catch(Exception e

trans.Rollback("HarryTest")
string s = e.Message

finall

cnn.Close()


========================================

how can i solve this problem..

can you advise me again

Thanks

Harr