Rank: Newbie
Groups: Member
Joined: 3/29/2008
Posts: 1
Points: 3
|
I'm trying to populate the variable "STATUS" with the BEFORE value from TABLE1 to insert into TABLE2 once the update button is hit, but not sure how to do that. Attached is the stripped down version of code I'm working on. Sorry, but I'm new at this. Thanks in advance.
// some variable stuff
protected ErrorText ErrorText1;
protected System.Web.UI.WebControls.DropDownList DISP_CD;
public System.Web.UI.HtmlControls.HtmlInputText DISP_DOC;
protected System.Web.UI.HtmlControls.HtmlInputText DISP_DATE;
protected System.Web.UI.WebControls.DataList DataTagList;
protected System.Web.UI.WebControls.Button BtnUpd;
public string STATUS = string.Empty;
// some update stuff
private void UpdateTable()
{
using(DatabaseConnection conn = new DatabaseConnection())
{
try
{
conn.OpenConnection(devSettings.junk);
for (int i = 0; i < DataTagList.Items.Count; i++)
{
HtmlInputText textTag =
(HtmlInputText)DataTagList.Items[i].FindControl("TagList");
if (textTag.Value.Trim() != string.Empty)
{
GetOldStatus(conn, textTag.Value.Trim()); <---- Help me.
UpdateTable1(conn, textTag.Value.Trim());
UpdateTable2(conn, textTag.Value.Trim());
}
}
}
catch ( Exception ex )
{
conn.Rollback();
throw ex;
}
}
}
// some sql table stuff
private string GetOldStatus(DatabaseConnection conn, string tag)
{
StringBuilder sqlStr = new StringBuilder();
sqlStr.Append(" SELECT ");
sqlStr.Append(" STATUS AS STATUS"); <---- Help me.
sqlStr.Append(" FROM ");
sqlStr.Append(" TABLE1 ");
sqlStr.AppendFormat(" WHERE TAG in '{0}'", Functions.DBFormatUpper(tag));
conn.Update(sqlStr.ToString());
return sqlStr.ToString();
}
private string UpdateTable1(DatabaseConnection conn, string tag)
{
StringBuilder sqlStr = new StringBuilder();
sqlStr.Append(" UPDATE TABLE1 ");
sqlStr.AppendFormat(" SET DISP_DOC = '{0}',",Functions.DBFormatUpper(this.DISP_DOC.Value));
sqlStr.AppendFormat(" DISP_DATE = to_date('{0}', 'mm/dd/yyyy'),", DISP_DATE.Value);
sqlStr.AppendFormat(" STATUS = '{0}',", Functions.DBFormatUpper(DISP_CD.SelectedValue));
sqlStr.Append(" UPDT_DATE = sysdate ");
sqlStr.AppendFormat(" WHERE TAG in '{0}'", Functions.DBFormatUpper(tag));
sqlStr.Append(" AND STATUS in ('1','2','3')");
conn.Update(sqlStr.ToString());
return sqlStr.ToString();
}
private string UpdateTable2(DatabaseConnection conn, string tag)
{
StringBuilder sqlStr = new StringBuilder();
sqlStr.Append(" INSERT INTO TABLE2 (");
sqlStr.Append(" TAG");
sqlStr.Append(" ,DATE");
sqlStr.Append(" ,FIELD1");
sqlStr.Append(" ,FIELD2");
sqlStr.Append(" ,BEFORE"); <---- Help me.
sqlStr.Append(" ,AFTER");
sqlStr.Append(" ,USER");
sqlStr.Append(" )");
sqlStr.Append(" VALUES (");
sqlStr.AppendFormat(" '{0}'", Functions.DBFormatUpper(tag));
sqlStr.Append(" ,sysdate ");
sqlStr.Append(" ,'JUNK1'");
sqlStr.Append(" ,'JUNK2' ");
sqlStr.AppendFormat(" ,'{0}'", Functions.DBFormatUpper(this.STATUS)); <---- Help me.
sqlStr.AppendFormat(" ,'{0}'", Functions.DBFormatUpper(DISP_CD.SelectedValue));
sqlStr.AppendFormat(" ,'{0}'", Functions.DBFormatUpper(devState.UserId));
sqlStr.Append(" )");
conn.Update(sqlStr.ToString());
return sqlStr.ToString();
}
|
Rank: Administration
Groups: Administration
Joined: 9/17/2007
Posts: 14
Points: -11
Location: Nottingham
|
Hi, There are a few problems with the code. Firstly, the following: Code:private string GetOldStatus(DatabaseConnection conn, string tag)
{
StringBuilder sqlStr = new StringBuilder();
sqlStr.Append(" SELECT ");
sqlStr.Append(" STATUS AS STATUS");
sqlStr.Append(" FROM ");
sqlStr.Append(" TABLE1 ");
sqlStr.AppendFormat(" WHERE TAG in '{0}'", Functions.DBFormatUpper(tag));
conn.Update(sqlStr.ToString());
return sqlStr.ToString();
} You are not retrieving the Status from the query the sql statement above is: SELECT STATUS AS STATUS FROM TABLE 1 WHERE..... This should be: SELECT STATUS FROM TABLE 1 WHERE..... You don't need to assign an alias with the same name Next you need to get this value from the query, this is usually done by calling executescalar on a dbcommand object, you are calling Update on a DatabaseConnection object. Once retrieved, you can pass the value through to your methods
Agilis Software Forum Administrator
|
