ADO.NET: .Net SqlClient Data Provider Could not find stored proce

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I'm having a rough time calling a sproc on a SQL-2000 database from my
front-end code. I run the code posted below; an exception is thrown at
cmd.ExecuteReader();

The weird thing is that if I copy the code from SQL Profiler and paste it
into Query Analyzer, the procedure executes fine. I've even tweaked my
connection string in Web.Config to use the sa login, with the same result.

An extra set of eyeballs would be much appreciated; I'm fairly well
exasperated at this point.

Thanks in advance,

Andre Ranieri

//numeric: Query Account Key
sSQL = "MyAccount_LoginAccount;";

//open connection, if closed
if (cn.State == ConnectionState.Closed)
cn.Open();

cmd = new SqlCommand(sSQL, cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@Account", SqlDbType.VarChar, 75).Value = sAccountKey;
cmd.Parameters.Add("@PWord", SqlDbType.VarChar, 75).Value = sPassword;
cmd.CommandTimeout = 0;
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
if (dr["Password"].ToString().Trim() == sPassword)
{
DataRow r = myDS.Password.NewRow();
r["AccountKey"] = Convert.ToInt32(dr["AccountKey"]);
r["Type"] = dr["Type"].ToString().Trim();
r["Branch"] = dr["Branch"].ToString().Trim();
myDS.Password.Rows.Add(r);
}
}
if (dr.IsClosed == false)
dr.Close();


Login.aspx.cs.Page_Load ..Net SqlClient Data Provider Could not find stored
procedure 'MyAccount_LoginAccount'. at
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior,
RunBehavior runBehavior, Boolean returnStream) at
System.Data.SqlClient.SqlCommand.ExecuteReader() at
Senske.NET.Secure.Login.Authenticate(String sAccountKey, String sPassword) in
c:\inetpub\wwwroot\senske.net\secure\login.aspx.cs:line 449
 
Thank you very much for your reply.

That's where I've spent most of my time looking because logically this is a
permissions issue.

The stored procedure is a tweak to a fairly well established website; the
conn string originates in web.config and has been working fine for some time
now.
if I create a watch on the cmd.SqlConnection.ConnectionString at the failure
point, the string appears to be correct.

Any advice would be much appreciated.

Andre


ViewState said:
Do you have permission to execute the procedure???
is your connection string right
Andre Ranieri said:
Hi,

I'm having a rough time calling a sproc on a SQL-2000 database from my
front-end code. I run the code posted below; an exception is thrown at
cmd.ExecuteReader();

The weird thing is that if I copy the code from SQL Profiler and paste it
into Query Analyzer, the procedure executes fine. I've even tweaked my
connection string in Web.Config to use the sa login, with the same result.

An extra set of eyeballs would be much appreciated; I'm fairly well
exasperated at this point.

Thanks in advance,

Andre Ranieri

//numeric: Query Account Key
sSQL = "MyAccount_LoginAccount;";

//open connection, if closed
if (cn.State == ConnectionState.Closed)
cn.Open();

cmd = new SqlCommand(sSQL, cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@Account", SqlDbType.VarChar, 75).Value = sAccountKey;
cmd.Parameters.Add("@PWord", SqlDbType.VarChar, 75).Value = sPassword;
cmd.CommandTimeout = 0;
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
if (dr["Password"].ToString().Trim() == sPassword)
{
DataRow r = myDS.Password.NewRow();
r["AccountKey"] = Convert.ToInt32(dr["AccountKey"]);
r["Type"] = dr["Type"].ToString().Trim();
r["Branch"] = dr["Branch"].ToString().Trim();
myDS.Password.Rows.Add(r);
}
}
if (dr.IsClosed == false)
dr.Close();


Login.aspx.cs.Page_Load ..Net SqlClient Data Provider Could not find
stored
procedure 'MyAccount_LoginAccount'. at
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
cmdBehavior,
RunBehavior runBehavior, Boolean returnStream) at
System.Data.SqlClient.SqlCommand.ExecuteReader() at
Senske.NET.Secure.Login.Authenticate(String sAccountKey, String sPassword)
in
c:\inetpub\wwwroot\senske.net\secure\login.aspx.cs:line 449
Click to expand...
Click to expand...
 
FYI,

I solved the problem. Out of habit I had included a semicolon at the end of
my sSQL string, this apparently caused the stored procedure not to be
correctly identified between ADO.NET and SQL.

Thanks,

Andre Ranieri
 
Back
Top