Iterate through datareader & add hyperlink

[saynords]

Hi What is the best way to iterate through a datareader? I only seem to be getting one record back when there should be two, I need to print all records from SQL statement out and add a hyperlink to it.
Thanks in advance code so far below:




protected void btnSearch_Click(object sender, EventArgs e)
{
hpResult.Text = "";
hpResult.NavigateUrl = "";
OracleConnection con = new OracleConnection();
con.ConnectionString = ConfigurationManager.ConnectionStrings["NewExpedioDevString"].ConnectionString;
// string cmdQuery = "SELECT R_EXPEDIOREFERENCE,R_CUSTOMERREFERENCE,R_TITLE, R_REQUESTSTATUS, R_REQUESTTASK, CR_DOPID FROM aradmin.exp__request WHERE (r_source = 'KANA' AND R_EXPEDIOREFERENCE LIKE '" + txtExRef.Text + "')";
string cmdQuery = "SELECT R_EXPEDIOREFERENCE,R_CUSTOMERREFERENCE,R_TITLE, R_REQUESTSTATUS, R_REQUESTTASK, CR_DOPID FROM aradmin.exp__request WHERE (r_source = 'KANA' AND (R_EXPEDIOREFERENCE='VCC00000560' OR R_EXPEDIOREFERENCE='VCC00000569'))";
OracleCommand cmd = new OracleCommand(cmdQuery);
try
{
con.Open();
cmd.Connection = con;
cmd.CommandType = CommandType.Text;
OracleDataReader reader = cmd.ExecuteReader();
lblResult.Text = "No Results Found!";
while (reader.Read())
{
Response.Write(reader["R_EXPEDIOREFERENCE"].ToString() + "</br>");
hpResult.Text = reader["R_EXPEDIOREFERENCE"].ToString();
hpResult.NavigateUrl = "default.aspx?ERID=" + reader["R_EXPEDIOREFERENCE"].ToString();
lblResult.Text = "";
pnlSearchResults.Controls.Add(hpResult);
pnlSearchResults.Visible = true;

}

}

catch (Exception ex)
{
lblResult.Text = ex.Message;
}

finally
{
cmd.Dispose();
con.Close();
con.Dispose();
}


}

[Plater]

What you have seems like it should work. Although if there was more then one record, your link thing looks like it will just overwrite itself.
Are you sure you should be getting more then one record back?
Have you executed your exact query string in an sql window to see what the real results are?

[saynords]

yeah definitely 2 records, made sure so I knew what I should expect back.
I have now decided to bind the sql to a datagrid instead and use teh hyperlink field which is working but looks messy as I can;t seem to get rid of the automatically generated header texts it creates (I have added some bound fields with tidy header texts to replace the actual column names bit they just appear in addition to the actual column names :O( an advice?
thanks for such a quick response.Debs

[Plater]

Well in your sql query, remove that poorly named columns or cast them as a better named column.

Such as with:

SELECT id, fname, lname, username FROM peopletable;
 

could be:

SELECT id as [Person ID], fname as [First Name], lname as [Last Name], username as [Username] FROM peopletable;
 

[saynords]

Brilliant thank you !
sorry to ask seemingly stupid questions but i'm relatively new to all this! thanks
again
Debs

[saynords]

Keep getting ORA-00923: FROM keyword not found where expected when I put in the aliases....

string cmdQuery = "SELECT R_EXPEDIOREFERENCE AS [EXPEDIOREF],R_CUSTOMERREFERENCE AS [CUSTOMERREF],R_TITLE AS [TITLE], R_REQUESTSTATUSAS [STATUS], R_REQUESTTASK AS [TASK], CR_DOPID AS [CRDOPID] FROM aradmin.exp__request WHERE (r_source = 'KANA' AND (R_EXPEDIOREFERENCE='VCC00000560' OR R_EXPEDIOREFERENCE='VCC00000569'))";

can you see anything wrong with this?
(I tried it using the aliases in the where clause too but same result)

[Plater]

Hmm oracle's SQL may not support using the [ ] brackets or using the AS keyword. (I should have paid more attention to which DB type you were using).

You could pop over to the oracle section and check out, because the SQL looks good enough to my eyes.