subdigital
September 6th, 2004, 01:30 PM
I have a user control that will display 1 note (passed by note_id) and all replies to that note. The control works fine if I just display something like this:
<QC:Note runat="server" note_id="7" />
but my application will need to put this control inside of a repeater, to display all notes specific to an entity... here's some psuedo-code:
<repeater (bound to a datareader that returns all notes for this entity)>
<QC:Note note_id= (this_note_id) />
</repeater>
I get an error when doing this, invalid attempt to FieldCount when datareader is closed
as far as I can tell, I've not closed any datareaders at all (so they are closed when the connection is closed. I've checked for mismatching .open() and .close() statements, but haven't seen any. What am I doing wrong?
here is my user control's script block:
<Script language="c#" runat="server">
public string backcolor;
public string noteid;
public int note_id;
public string typeid;
private bool refresh = true;
void AddReply(object s, EventArgs e)
{
//hide reply panel
pnlAddReply.Visible=false;
//show replies panel
pnlShowReplies.Visible=true;
//if text was entered add reply to database
if(txtReplyNoteText.Text != "")
{
string sql;
SqlConnection link = new SqlConnection(ConfigurationSettings.AppSettings["connectionstring"]);
SqlCommand cmdGetParentNote = new SqlCommand("SELECT * FROM NotesGeneral WHERE NoteID = @nid", link);
SqlCommand cmdInsertReplyNote;
SqlDataReader dtrNote;
cmdGetParentNote.Parameters.Add("@nid", note_id);
HttpCookie ck = Request.Cookies["account"];
string accountid = ck["account"];
link.Open();
dtrNote = cmdGetParentNote.ExecuteReader();
dtrNote.Read();
sql = "INSERT INTO NotesGeneral(NoteText, NoteDate, Author, NoteType, QCNoteTypeID, Refund, ResponseID, jobid)" +
" VALUES(@notetext, @notedate, @author, @notetype, @qcnotetypeid, @refund, @responseID, @jobid);";
cmdInsertReplyNote = new SqlCommand(sql, link);
cmdInsertReplyNote.Parameters.Add( "@notetext", txtReplyNoteText.Text );
cmdInsertReplyNote.Parameters.Add( "@notedate", DateTime.Now );
cmdInsertReplyNote.Parameters.Add( "@author", accountid );
cmdInsertReplyNote.Parameters.Add( "@notetype", Convert.ToInt32(dtrNote["NoteType"].ToString()) );
if( dtrNote["QCNoteTypeID"].ToString()=="" )
cmdInsertReplyNote.Parameters.Add( "@qcnotetypeid", DBNull.Value);
else
cmdInsertReplyNote.Parameters.Add( "@qcnotetypeid", Convert.ToInt32(dtrNote["QCNoteTypeID"].ToString()) );
SqlParameter parm = new SqlParameter( "@refund", SqlDbType.Money, 8);
parm.Value = 0.0;
cmdInsertReplyNote.Parameters.Add(parm);
cmdInsertReplyNote.Parameters.Add( "@responseID", note_id);
cmdInsertReplyNote.Parameters.Add("@jobid", SqlDbType.Int);
if( dtrNote["jobid"].ToString() == "" )
cmdInsertReplyNote.Parameters["@jobid"].Value = DBNull.Value;
else
cmdInsertReplyNote.Parameters["@jobid"].Value = Convert.ToInt32( dtrNote["jobid"].ToString() );
cmdInsertReplyNote.ExecuteNonQuery();
refresh = true;
link.Close();
SqlCommand cmdInsertReply = new SqlCommand(sql, link);
BindReplies(note_id);
}
}
string GetName(int id)
{
SqlConnection link = new SqlConnection(ConfigurationSettings.AppSettings["connectionstring"]);
SqlCommand cmdGetName = new SqlCommand("SELECT name, last FROM Entities WHERE Account=@id", link);
SqlDataReader dtrName;
string strRetVal = "";
cmdGetName.Parameters.Add("@id", id);
link.Open();
dtrName = cmdGetName.ExecuteReader();
if( dtrName.Read() )
{
strRetVal = dtrName["name"].ToString();
}
link.Close();
return strRetVal;
}
void ShowAddReply(Object s, EventArgs e)
{
pnlAddReply.Visible = true;
lnkReply.Visible = true;
}
void ShowReplies(Object s, EventArgs e)
{
if(pnlShowReplies.Visible == false)
{
pnlShowReplies.Visible = true;
lnkShowReplies.Text = "[hide replies]";
}
else
{
pnlShowReplies.Visible = false;
lnkShowReplies.Text = String.Format("[{0} replies]", GetReplyCount(note_id));
}
}
int GetReplyCount(int noteID)
{
SqlConnection link = new SqlConnection(ConfigurationSettings.AppSettings["connectionstring"]);
SqlCommand cmdGetCount = new SqlCommand("SELECT COUNT(*) as num_replies FROM NotesGeneral WHERE ResponseID = @noteid;", link);
cmdGetCount.Parameters.Add("@noteid", noteID);
int retval;
link.Open();
retval = Convert.ToInt32(cmdGetCount.ExecuteScalar());
link.Close();
return retval;
}
void BindResponsible(int noteID)
{
SqlConnection link = new SqlConnection(ConfigurationSettings.AppSettings["connectionstring"]);
string sql = "SELECT notesgeneral.noteid ,ISNULL(name,'no data') as name "+
"FROM NotesGeneral "+
"left outer join entities_notes_join on notesgeneral.noteid=entities_notes_join.noteid "+
"left outer join entities on entities.account=entities_notes_join.account "+
"where notesgeneral.noteid=" + noteID;
SqlCommand cmdGetResponsible = new SqlCommand(sql, link);
link.Open();
ddResponsible.DataSource = cmdGetResponsible.ExecuteReader();
ddResponsible.DataTextField= "name";
ddResponsible.DataBind();
link.Close();
}
string GetPriorityLevel(string iD)
{
SqlConnection dbConnection = new SqlConnection(ConfigurationSettings.AppSettings["connectionstring"]);
string queryString = "select prilevel from notesprioritytypes WHERE privalue ='"+iD+"'";
//Response.Write("<br>"+queryString);
System.Data.IDbCommand dbCommand = new System.Data.SqlClient.SqlCommand();
dbCommand.CommandText = queryString;
dbCommand.Connection = dbConnection;
dbConnection.Open();
string csrp;
try
{ csrp = (string) dbCommand.ExecuteScalar();
}
catch(Exception exp)
{ csrp ="";
}
dbConnection.Close();
return csrp;
}
void BindReplies(int noteID)
{
SqlConnection link = new SqlConnection(ConfigurationSettings.AppSettings["connectionstring"]);
SqlCommand cmdGetReplies = new SqlCommand("SELECT NoteDate, Author, NoteText FROM NotesGeneral WHERE ResponseID=@nid", link);
cmdGetReplies.Parameters.Add("@nid", noteID);
link.Open();
dtlReplies.DataSource = cmdGetReplies.ExecuteReader();
dtlReplies.DataBind();
link.Close();
}
void Page_Load()
{
Response.Write("note control loading page..");
if( refresh )
{
refresh = false;
Response.Write("entering note control with noteid: " + noteid);
note_id = Convert.ToInt32(noteid);
lnkReply.Visible=true;
pnlAddReply.Visible=true;
//load note from database
SqlConnection link = new SqlConnection(ConfigurationSettings.AppSettings["connectionstring"]);
string queryString = "SELECT notesgeneral.noteid,notetext,notedate,author,notetype,priority,CAST (refund as VARCHAR) as refund, "+
"ISNULL(qcnotetypeid, 0) AS qcnotetypeid ,responseid,"+
"type,notetypeid,ISNULL(category, '-- no data --') as category "+
"FROM NotesGeneral "+
"inner join notestypes on notesgeneral.notetype=notestypes.notetypeid "+
"left outer join qcnotescategories on notesgeneral.qcnotetypeid=qcnotescategories.categoryid "+
"where notesgeneral.noteid=" + note_id;
SqlCommand cmdGetNote = new SqlCommand(queryString, link);
SqlDataReader dtrNote;
link.Open();
dtrNote = cmdGetNote.ExecuteReader();
if( dtrNote.Read() )
{
lblNoteBody.Text = dtrNote["notetext"].ToString();
lblQCNoteDate.Text = dtrNote["notedate"].ToString();
lblQCNoteType.Text = dtrNote["Type"].ToString();
int AuthID = Convert.ToInt32(dtrNote["author"].ToString());
HttpCookie cookie = Request.Cookies["account"];
int loggedinID = 1;
if(AuthID == loggedinID)
pnlEdit.Visible = true;
else
pnlEdit.Visible = false;
lblQCNoteAuthor.Text = GetName(AuthID);
lblQCCategory.Text = dtrNote["Category"].ToString();
lblPriority.Text = GetPriorityLevel(dtrNote.GetString( dtrNote.GetOrdinal("Priority") ));
lblQCRefundLoss.Text = String.Format("{0:c}", Convert.ToDouble( dtrNote["Refund"].ToString() ));
}
link.Close();
BindResponsible(note_id);
int replyCount = GetReplyCount(note_id);
//if there are replies to this note
if(replyCount > 0)
{
//show number of replies in link text
lnkShowReplies.Text = String.Format("{0} replies", replyCount);
//make it visible
lnkShowReplies.Visible = true;
//bind replies to the datalist under this note
BindReplies(note_id);
}
else
{
//do not show link button for replies
lnkShowReplies.Visible = false;
}
lnkReply.Visible = true;
pnlAddReply.Visible = false;
refresh = false;
}
}
</Script>
any replies are greatly appreciated
<QC:Note runat="server" note_id="7" />
but my application will need to put this control inside of a repeater, to display all notes specific to an entity... here's some psuedo-code:
<repeater (bound to a datareader that returns all notes for this entity)>
<QC:Note note_id= (this_note_id) />
</repeater>
I get an error when doing this, invalid attempt to FieldCount when datareader is closed
as far as I can tell, I've not closed any datareaders at all (so they are closed when the connection is closed. I've checked for mismatching .open() and .close() statements, but haven't seen any. What am I doing wrong?
here is my user control's script block:
<Script language="c#" runat="server">
public string backcolor;
public string noteid;
public int note_id;
public string typeid;
private bool refresh = true;
void AddReply(object s, EventArgs e)
{
//hide reply panel
pnlAddReply.Visible=false;
//show replies panel
pnlShowReplies.Visible=true;
//if text was entered add reply to database
if(txtReplyNoteText.Text != "")
{
string sql;
SqlConnection link = new SqlConnection(ConfigurationSettings.AppSettings["connectionstring"]);
SqlCommand cmdGetParentNote = new SqlCommand("SELECT * FROM NotesGeneral WHERE NoteID = @nid", link);
SqlCommand cmdInsertReplyNote;
SqlDataReader dtrNote;
cmdGetParentNote.Parameters.Add("@nid", note_id);
HttpCookie ck = Request.Cookies["account"];
string accountid = ck["account"];
link.Open();
dtrNote = cmdGetParentNote.ExecuteReader();
dtrNote.Read();
sql = "INSERT INTO NotesGeneral(NoteText, NoteDate, Author, NoteType, QCNoteTypeID, Refund, ResponseID, jobid)" +
" VALUES(@notetext, @notedate, @author, @notetype, @qcnotetypeid, @refund, @responseID, @jobid);";
cmdInsertReplyNote = new SqlCommand(sql, link);
cmdInsertReplyNote.Parameters.Add( "@notetext", txtReplyNoteText.Text );
cmdInsertReplyNote.Parameters.Add( "@notedate", DateTime.Now );
cmdInsertReplyNote.Parameters.Add( "@author", accountid );
cmdInsertReplyNote.Parameters.Add( "@notetype", Convert.ToInt32(dtrNote["NoteType"].ToString()) );
if( dtrNote["QCNoteTypeID"].ToString()=="" )
cmdInsertReplyNote.Parameters.Add( "@qcnotetypeid", DBNull.Value);
else
cmdInsertReplyNote.Parameters.Add( "@qcnotetypeid", Convert.ToInt32(dtrNote["QCNoteTypeID"].ToString()) );
SqlParameter parm = new SqlParameter( "@refund", SqlDbType.Money, 8);
parm.Value = 0.0;
cmdInsertReplyNote.Parameters.Add(parm);
cmdInsertReplyNote.Parameters.Add( "@responseID", note_id);
cmdInsertReplyNote.Parameters.Add("@jobid", SqlDbType.Int);
if( dtrNote["jobid"].ToString() == "" )
cmdInsertReplyNote.Parameters["@jobid"].Value = DBNull.Value;
else
cmdInsertReplyNote.Parameters["@jobid"].Value = Convert.ToInt32( dtrNote["jobid"].ToString() );
cmdInsertReplyNote.ExecuteNonQuery();
refresh = true;
link.Close();
SqlCommand cmdInsertReply = new SqlCommand(sql, link);
BindReplies(note_id);
}
}
string GetName(int id)
{
SqlConnection link = new SqlConnection(ConfigurationSettings.AppSettings["connectionstring"]);
SqlCommand cmdGetName = new SqlCommand("SELECT name, last FROM Entities WHERE Account=@id", link);
SqlDataReader dtrName;
string strRetVal = "";
cmdGetName.Parameters.Add("@id", id);
link.Open();
dtrName = cmdGetName.ExecuteReader();
if( dtrName.Read() )
{
strRetVal = dtrName["name"].ToString();
}
link.Close();
return strRetVal;
}
void ShowAddReply(Object s, EventArgs e)
{
pnlAddReply.Visible = true;
lnkReply.Visible = true;
}
void ShowReplies(Object s, EventArgs e)
{
if(pnlShowReplies.Visible == false)
{
pnlShowReplies.Visible = true;
lnkShowReplies.Text = "[hide replies]";
}
else
{
pnlShowReplies.Visible = false;
lnkShowReplies.Text = String.Format("[{0} replies]", GetReplyCount(note_id));
}
}
int GetReplyCount(int noteID)
{
SqlConnection link = new SqlConnection(ConfigurationSettings.AppSettings["connectionstring"]);
SqlCommand cmdGetCount = new SqlCommand("SELECT COUNT(*) as num_replies FROM NotesGeneral WHERE ResponseID = @noteid;", link);
cmdGetCount.Parameters.Add("@noteid", noteID);
int retval;
link.Open();
retval = Convert.ToInt32(cmdGetCount.ExecuteScalar());
link.Close();
return retval;
}
void BindResponsible(int noteID)
{
SqlConnection link = new SqlConnection(ConfigurationSettings.AppSettings["connectionstring"]);
string sql = "SELECT notesgeneral.noteid ,ISNULL(name,'no data') as name "+
"FROM NotesGeneral "+
"left outer join entities_notes_join on notesgeneral.noteid=entities_notes_join.noteid "+
"left outer join entities on entities.account=entities_notes_join.account "+
"where notesgeneral.noteid=" + noteID;
SqlCommand cmdGetResponsible = new SqlCommand(sql, link);
link.Open();
ddResponsible.DataSource = cmdGetResponsible.ExecuteReader();
ddResponsible.DataTextField= "name";
ddResponsible.DataBind();
link.Close();
}
string GetPriorityLevel(string iD)
{
SqlConnection dbConnection = new SqlConnection(ConfigurationSettings.AppSettings["connectionstring"]);
string queryString = "select prilevel from notesprioritytypes WHERE privalue ='"+iD+"'";
//Response.Write("<br>"+queryString);
System.Data.IDbCommand dbCommand = new System.Data.SqlClient.SqlCommand();
dbCommand.CommandText = queryString;
dbCommand.Connection = dbConnection;
dbConnection.Open();
string csrp;
try
{ csrp = (string) dbCommand.ExecuteScalar();
}
catch(Exception exp)
{ csrp ="";
}
dbConnection.Close();
return csrp;
}
void BindReplies(int noteID)
{
SqlConnection link = new SqlConnection(ConfigurationSettings.AppSettings["connectionstring"]);
SqlCommand cmdGetReplies = new SqlCommand("SELECT NoteDate, Author, NoteText FROM NotesGeneral WHERE ResponseID=@nid", link);
cmdGetReplies.Parameters.Add("@nid", noteID);
link.Open();
dtlReplies.DataSource = cmdGetReplies.ExecuteReader();
dtlReplies.DataBind();
link.Close();
}
void Page_Load()
{
Response.Write("note control loading page..");
if( refresh )
{
refresh = false;
Response.Write("entering note control with noteid: " + noteid);
note_id = Convert.ToInt32(noteid);
lnkReply.Visible=true;
pnlAddReply.Visible=true;
//load note from database
SqlConnection link = new SqlConnection(ConfigurationSettings.AppSettings["connectionstring"]);
string queryString = "SELECT notesgeneral.noteid,notetext,notedate,author,notetype,priority,CAST (refund as VARCHAR) as refund, "+
"ISNULL(qcnotetypeid, 0) AS qcnotetypeid ,responseid,"+
"type,notetypeid,ISNULL(category, '-- no data --') as category "+
"FROM NotesGeneral "+
"inner join notestypes on notesgeneral.notetype=notestypes.notetypeid "+
"left outer join qcnotescategories on notesgeneral.qcnotetypeid=qcnotescategories.categoryid "+
"where notesgeneral.noteid=" + note_id;
SqlCommand cmdGetNote = new SqlCommand(queryString, link);
SqlDataReader dtrNote;
link.Open();
dtrNote = cmdGetNote.ExecuteReader();
if( dtrNote.Read() )
{
lblNoteBody.Text = dtrNote["notetext"].ToString();
lblQCNoteDate.Text = dtrNote["notedate"].ToString();
lblQCNoteType.Text = dtrNote["Type"].ToString();
int AuthID = Convert.ToInt32(dtrNote["author"].ToString());
HttpCookie cookie = Request.Cookies["account"];
int loggedinID = 1;
if(AuthID == loggedinID)
pnlEdit.Visible = true;
else
pnlEdit.Visible = false;
lblQCNoteAuthor.Text = GetName(AuthID);
lblQCCategory.Text = dtrNote["Category"].ToString();
lblPriority.Text = GetPriorityLevel(dtrNote.GetString( dtrNote.GetOrdinal("Priority") ));
lblQCRefundLoss.Text = String.Format("{0:c}", Convert.ToDouble( dtrNote["Refund"].ToString() ));
}
link.Close();
BindResponsible(note_id);
int replyCount = GetReplyCount(note_id);
//if there are replies to this note
if(replyCount > 0)
{
//show number of replies in link text
lnkShowReplies.Text = String.Format("{0} replies", replyCount);
//make it visible
lnkShowReplies.Visible = true;
//bind replies to the datalist under this note
BindReplies(note_id);
}
else
{
//do not show link button for replies
lnkShowReplies.Visible = false;
}
lnkReply.Visible = true;
pnlAddReply.Visible = false;
refresh = false;
}
}
</Script>
any replies are greatly appreciated