Select statement

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

Guest

Hello!

I am converting 2 Select statements written in VB ASP into C# ASP.NET.
Please help.

Set rsPPO = Server.CreateObject("ADODB.Recordset")
sPPO = "SELECT a.line_no, a.loc_no, a.qty_ord, a.po_no, a.unit_price,
a.tot_qty_rec, " _
& " to_char(a.last_rec_date,'mm/dd/yyyy') last_rec_date, " _
& " to_char(a.due_date,'mm/dd/yyyy') due_date, b.item_no, b.vendor_item_no,
" _
& " b.manufacturer_item_no, b.vendor_no " _
& "FROM po_line a, im_vendor_item b " _
& "WHERE b.item_no = a.item_no And b.vendor_item_no = a.vendor_item_no And
b.vendor_no = a.vendor_no" _
& " And Upper(b.manufacturer_item_no) Like Upper('" & Request("mfrpartNo")
& "%')" _
& " And Upper(b.item_no) Like Upper('" & Request("dpartNo") & "%')" _
& " And Upper(b.vendor_item_no) Like Upper('" & Request("vendPartNo") & "%')"

rsPPO.Open sPPO,db,,,adCmdText
Do While Not rsPPO.EOF
rsPPO.MoveNext
rc = rc + 1


-----------------------------------------------------------------------------------------------------

Set rsOrdNo = Server.CreateObject("ADODB.Recordset")
sOrdNo = "SELECT b.ord_no FROM po_line_dist a ,oe_line b where a.po_no ='"
& rsPPO("po_no") & "'"_
& " And a.line_no = " & rsPPO("line_no") & " And a.key_1 = b.ord_no(+) And
a.key_2 = b.line_no(+)"
'response.write (sOrdNo)
rsOrdNo.Open sOrdNo,db,,,adCmdText
 
this is sorta informally typed.. i may have made some errors.. but you
get the gist.
you may wanna read up a little on ado.net, it's a little different from
the recordsets and such that you're used to with ADO.

string strConnect = "Data Source=yourServer;Initial
Catalog=yourDB;Integrated Security=SSPI; Connection Timeout=120";

string strSql= "select a.line_no, a.loc_no, a.qty_ord, a.po_no,
a.unit_price, a.tot_qty_rec, "
+ " convert(char(10,a.last_rec_date,101) last_rec_date,
convert(char(10,a.due_date,10) due_date, b.item_no, "
+ " b.vendor_item_no, b.manufacturer_item_no, b.vendor_no "
+ " FROM po_line a, im_vendor_item b " _
+ " WHERE b.item_no = a.item_no And b.vendor_item_no = a.vendor_item_no
And b.vendor_no = a.vendor_no" _
+ " And Upper(b.manufacturer_item_no) Like Upper('" +
Request.QueryString["mfrpartNo"]
+ "%')"
+ " And Upper(b.item_no) Like Upper('" + Request.QueryString["dpartNo"]
+ "%')" _
+ " And Upper(b.vendor_item_no) Like Upper('" +
Request.QueryString["vendPartNo"] + "%')"

SqlDataReader oDataReaderPPO;
SqlConnection oConn = new SqlConnection(strConnect);
SqlCommand oComm = new SqlCommand(strSql, oConn);
oComm.CommandTimeout = 120;
oConn.Open();
oDataReaderPPO = oComm.ExecuteReader(CommandBehavior.CloseConnection);
int rc = 0;

if (oDataReaderPPO.HasRows)
{
while(oDataReaderPPO.Read())
{
rc++;
}
}

similarly.. your second sql string should look something like this,
pass it to another
command object and return a datareader object for further processing


string strSqlOrdNo = "SELECT b.ord_no FROM po_line_dist a ,oe_line b
where a.po_no ='" + oDataReaderPPO["po_no"].ToString() + "' And
a.line_no = " + oDataReaderPPO["line_no"].ToString() + " And a.key_1 =
b.ord_no And
a.key_2 = b.line_no"
 
Back
Top