Not retrieving values

[Nishanth]

Hi All,

I am facing a very peculiar problem, I am executing a stored procedure
residing in the Sybase database. When I do execute the Stored
Procedure from SQL Advantage I can retrexecve the values, but when I
execute the same from the .net code (VS.net 2003 C sharp) with the
same set of parameters I don't retrieve any values. Please help me
figure out the same.

Thanks in advance,
Nishanth

 

[RobinS]

It would be easier to figure out what was wrong if you posted the code you
are using to retrieve the data.

Robin S.
Ts'i mahnu uterna ot twan ot geifur hingts uto.

 

[Nishanth]

This is the code that is used to directly connect to the db

private void CallSp()
{
DataSet dsProject = new DataSet() ;
string startDate = "06/21/2002" ;
string EndDate = "";
string AccountType = "A";

string UserId = "" ;
string strMess = "";

try
{
string constr = string.Empty;
constr = "Provider=Sybase.ASEOLEDBProvider.2;Initial
Catalog=testdb;Password=testpass;User ID=testuser;Data
Source=IISS;Persist Security Info=True;";

OleDbConnection conn = new OleDbConnection(constr);

if(conn.State != ConnectionState.Open)
conn.Open();

OleDbCommand adptComm = new
OleDbCommand("sp_crm_top_holding",conn);
adptComm.CommandType = CommandType.StoredProcedure;
OleDbParameter parameter = adptComm.Parameters.Add(
"@from_date", OleDbType.VarChar);
parameter.Value = startDate ;

parameter = adptComm.Parameters.Add(
"@to_date", OleDbType.VarChar);
parameter.Value = EndDate ;

parameter = adptComm.Parameters.Add(
"@acct_type", OleDbType.VarChar);
parameter.Value = AccountType ;

parameter = adptComm.Parameters.Add(
"@username", OleDbType.VarChar);
parameter.Value = UserId ;

parameter = adptComm.Parameters.Add(
"@messagenet_num", OleDbType.VarChar);
parameter.Value = strMess ;

OleDbDataAdapter adpt=new OleDbDataAdapter(adptComm);

adpt.Fill(dsProject);
conn.Close();
Response.Write(dsProject.Tables[0].Rows.Count);

}
catch(Exception ex)
{
Response.Write(ex.Message) ;
}

}

 

[RobinS]

Okayu, I don't see any obvious reasons that wouldn't work.

Have you managed to read from the Sybase database using .Net at all?

Robin S.

 

[Nishanth]

Okayu, I don't see any obvious reasons that wouldn't work.

Have you managed to read from the Sybase database using .Net at all?

Robin S.

This is the code that is used to directly connect to the db

private void CallSp()
{
DataSet dsProject = new DataSet() ;
string startDate = "06/21/2002" ;
string EndDate = "";
string AccountType = "A";

string UserId = "" ;
string strMess = "";

try
{
string constr = string.Empty;
constr = "Provider=Sybase.ASEOLEDBProvider.2;Initial
Catalog=testdb;Password=testpass;User ID=testuser;Data
Source=IISS;Persist Security Info=True;";

OleDbConnection conn = new OleDbConnection(constr);

if(conn.State != ConnectionState.Open)
conn.Open();

OleDbCommand adptComm = new
OleDbCommand("sp_crm_top_holding",conn);
adptComm.CommandType = CommandType.StoredProcedure;
OleDbParameter parameter = adptComm.Parameters.Add(
"@from_date", OleDbType.VarChar);
parameter.Value = startDate ;

parameter = adptComm.Parameters.Add(
"@to_date", OleDbType.VarChar);
parameter.Value = EndDate ;

parameter = adptComm.Parameters.Add(
"@acct_type", OleDbType.VarChar);
parameter.Value = AccountType ;

parameter = adptComm.Parameters.Add(
"@username", OleDbType.VarChar);
parameter.Value = UserId ;

parameter = adptComm.Parameters.Add(
"@messagenet_num", OleDbType.VarChar);
parameter.Value = strMess ;

OleDbDataAdapter adpt=new OleDbDataAdapter(adptComm);

adpt.Fill(dsProject);
conn.Close();
Response.Write(dsProject.Tables[0].Rows.Count);

}
catch(Exception ex)
{
Response.Write(ex.Message) ;
}

}

Nope this is the first time I am using Sybase database with .Net. I
think it has to something with Null strings that are being passed as
parameters to the database.

Nishanth.

 

[RobinS]

Okayu, I don't see any obvious reasons that wouldn't work.

Have you managed to read from the Sybase database using .Net at all?

Robin S.
--------------------------------------------------"Nishanth"

This is the code that is used to directly connect to the db

private void CallSp()
{
DataSet dsProject = new DataSet() ;
string startDate = "06/21/2002" ;
string EndDate = "";
string AccountType = "A";

string UserId = "" ;
string strMess = "";

try
{
string constr = string.Empty;
constr = "Provider=Sybase.ASEOLEDBProvider.2;Initial
Catalog=testdb;Password=testpass;User ID=testuser;Data
Source=IISS;Persist Security Info=True;";

OleDbConnection conn = new OleDbConnection(constr);

if(conn.State != ConnectionState.Open)
conn.Open();

OleDbCommand adptComm = new
OleDbCommand("sp_crm_top_holding",conn);
adptComm.CommandType = CommandType.StoredProcedure;
OleDbParameter parameter = adptComm.Parameters.Add(
"@from_date", OleDbType.VarChar);
parameter.Value = startDate ;

parameter = adptComm.Parameters.Add(
"@to_date", OleDbType.VarChar);
parameter.Value = EndDate ;

parameter = adptComm.Parameters.Add(
"@acct_type", OleDbType.VarChar);
parameter.Value = AccountType ;

parameter = adptComm.Parameters.Add(
"@username", OleDbType.VarChar);
parameter.Value = UserId ;

parameter = adptComm.Parameters.Add(
"@messagenet_num", OleDbType.VarChar);
parameter.Value = strMess ;

OleDbDataAdapter adpt=new OleDbDataAdapter(adptComm);

adpt.Fill(dsProject);
conn.Close();
Response.Write(dsProject.Tables[0].Rows.Count);

}
catch(Exception ex)
{
Response.Write(ex.Message) ;
}

}

Nope this is the first time I am using Sybase database with .Net. I
think it has to something with Null strings that are being passed as
parameters to the database.

Nishanth.

Have you considered passing those empty strings as DBNull.Value instead?

Robin S.
Ts'i mahnu uterna ot twan ot geifur hingts uto.

 

[Nishanth]

Okayu, I don't see any obvious reasons that wouldn't work.
Have you managed to read from the Sybase database using .Net at all?
Robin S.
--------------------------------------------------"Nishanth"

This is the code that is used to directly connect to the db
private void CallSp()
{
DataSet dsProject = new DataSet() ;
string startDate = "06/21/2002" ;
string EndDate = "";
string AccountType = "A";
string UserId = "" ;
string strMess = "";
try
{
string constr = string.Empty;
constr = "Provider=Sybase.ASEOLEDBProvider.2;Initial
Catalog=testdb;Password=testpass;User ID=testuser;Data
Source=IISS;Persist Security Info=True;";
OleDbConnection conn = new OleDbConnection(constr);
if(conn.State != ConnectionState.Open)
conn.Open();
OleDbCommand adptComm = new
OleDbCommand("sp_crm_top_holding",conn);
adptComm.CommandType = CommandType.StoredProcedure;
OleDbParameter parameter = adptComm.Parameters.Add(
"@from_date", OleDbType.VarChar);
parameter.Value = startDate ;
parameter = adptComm.Parameters.Add(
"@to_date", OleDbType.VarChar);
parameter.Value = EndDate ;
parameter = adptComm.Parameters.Add(
"@acct_type", OleDbType.VarChar);
parameter.Value = AccountType ;
parameter = adptComm.Parameters.Add(
"@username", OleDbType.VarChar);
parameter.Value = UserId ;
parameter = adptComm.Parameters.Add(
"@messagenet_num", OleDbType.VarChar);
parameter.Value = strMess ;
OleDbDataAdapter adpt=new OleDbDataAdapter(adptComm);
adpt.Fill(dsProject);
conn.Close();
Response.Write(dsProject.Tables[0].Rows.Count);
}
catch(Exception ex)
{
Response.Write(ex.Message) ;
}
}

Nope this is the first time I am using Sybase database with .Net. I
think it has to something with Null strings that are being passed as
parameters to the database.

Nishanth.

Have you considered passing those empty strings as DBNull.Value instead?

Robin S.
Ts'i mahnu uterna ot twan ot geifur hingts uto.

Yes and still no success.. I am still not able to figure out the
solution for this problem.

 

[Kalpesh]

While, I havent worked with Sybase - is there something like "sql
profiler", which tells you what sql statements are being executed
against any DB?
This will help determine, how the stored procedure is called. You
might need to check that & find where the problem could be

Hope this helps
Kalpesh

 

[Nishanth]

While, I havent worked with Sybase - is there something like "sql
profiler", which tells you what sql statements are being executed
against any DB?
This will help determine, how the stored procedure is called. You
might need to check that & find where the problem could be

Hope this helps
Kalpesh

Kalpesh, if you find out about the same please do let me know, that
also will be of great help in trying to know how the sp is called and
the parameters being passed to the db can be found out.

 

[Ravichandran J.V.]

Hey

From what I could figure out from a quick look at your code is that
although you have added the parameters to the Command object, you set
its value after adding it. I m not sure (since I do not have VS.Net
right now to check) but i think you should add the value to the
parameters and then add it.

with regards,


J.V.Ravichandran
- http://www.geocities.com/
jvravichandran
- Or, just search on "J.V.Ravichandran"
at http://www.Google.com

 

[RobinS]

That's a good catch! I bet that *is* his problem.

Robin S.