Conversion of an asp page query into asp.net page query [Resolved]

Posted by Anu_dgr8 under Sql Server on 8/9/2010 | Views : 1432 | Status : [Member] | Replies : 8
i have an asp page in which i have a query something like this:

sql="select a.aid,a.empno as aempno,b.bid,b.empno as bempno 
from allocation a ,build b
where a.someid=b.someid"
set rs=cn.execute(sql)

if not rs.eof then
if(eval(rs("aid"))<> eval(rs("aempno")) then
pendingAt="Person1";
endif
endif

can somebody help me in converting this query into a query for asp.net page?(in sql server 2005)

thanks in advance

Thanks and Regards,
Hoosha Boosha



Responses

Posted by: Shankul2784 on: 8/9/2010 [Member] Bronze

Up
0
Down

Resolved

Hi,

Following code will help you. I am just confused with pendingAt statement. Please go through following statements-

DECLARE CursorName CURSOR READ_ONLY FOR
select a.aid,a.empno as aempno,b.bid,b.empno as bempno
from allocation a ,build b
where a.someid=b.someid

OPEN CursorName
FETCH NEXT FROM CursorName INTO @aid, @aempno

WHILE @@FETCH_STATUS <> -1
BEGIN
if @aid <> @aempno
begin

//write code for pendingAt
set @pendingAt="Person1"


end

FETCH NEXT FROM CursorName INTO @aid, @aempno
END
CLOSE CursorName
DEALLOCATE CursorName


I hope, it will help you.


Thanks & Regards,

Shailesh

Anu_dgr8, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sundermagar on: 8/9/2010 [Member] Starter

Up
0
Down
Hi,

I m not sure if u asking to automatic tool to convert your asp query to asp.net. But, here is what the .net code look like for your above code.
Please define the connection string in Web.Config file like this:

<connectionStrings>
<add name="conn" connectionString="Data Source=SQLServerName; Initial Catalog=DATABASE NAME; User ID=SQLUID; PWD=SQLPASSWORD; "/>
</connectionStrings>

using(SqlConnection Connection = new SqlConnection(ConfigurationManager.ConnectionString["conn"].ConnectionString))
{
string Query = "select a.aid,a.empno as aempno,b.bid,b.empno as bempno from allocation a ,build b where a.someid=b.someid";
string PendingAt = string.Empty;
SqlDataAdapter adp = new SqlDataAdapter(qury, Connection);
DataSet ds = new DataSet();
adp.Fill(ds);
foreach (DataRow r in ds.Tables[0].Rows.Count)
{
if (r["aid"].ToString() != r["aempno"].ToString())
{
PendingAt += "Person 1";
}
}
}


Hope this helps you.

Thanks,

Sunder Magar

Anu_dgr8, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Anu_dgr8 on: 8/9/2010 [Member] Starter

Up
0
Down
thanks both of you

Thanks and Regards,
Hoosha Boosha

Anu_dgr8, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Anu_dgr8 on: 8/9/2010 [Member] Starter

Up
0
Down
@shankul: it worked..in addition to this i want to return the value of @pendingAt to asp.net page..cn u help me?

Thanks and Regards,
Hoosha Boosha

Anu_dgr8, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Shankul2784 on: 8/9/2010 [Member] Bronze

Up
0
Down



just write at the end select @variablename. it will show the result.

Thanks & Regards,

Shailesh

Anu_dgr8, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Anu_dgr8 on: 8/9/2010 [Member] Starter

Up
0
Down
yes i wrote select @variablename..it worked...but the above procedure has one more select query(which i have not shown),and in data layer i have called this procedure like this:

return SqlHelper.ExecuteDataset(connString, CommandType.StoredProcedure, "ProcName", sqlparameter).Tables[0];..now this is returning only the result of 1st query..how do i get the result of 2nd query also??

Thanks and Regards,
Hoosha Boosha

Anu_dgr8, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Shankul2784 on: 8/9/2010 [Member] Bronze

Up
0
Down

Use the data adaptor to catch the results.

You will get many results at a time.

Thanks & Regards,

Shailesh

Anu_dgr8, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Anu_dgr8 on: 9/3/2010 [Member] Starter | Points: 25

Up
0
Down
Can we write a cursor for an insert statement?if yes, how?

Thanks and Regards,
Hoosha Boosha

Anu_dgr8, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response