Urgent...need help fixing this error!
Hello,
I get the following error:
System.InvalidOperationException: ExecuteNonQuery: Connection property has not been initialized.
at System.Data.OleDb.OleDbCommand.ValidateConnectionA ndTransaction(String method, Int32& localState)
at System.Data.OleDb.OleDbCommand.ExecuteReaderIntern al(CommandBehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
at SubmissionClearance.SubForm.btnSubmit_Click(Object sender, EventArgs e) in c:\inetpub\wwwroot\markelmarine\submissionclearanc e\subform.aspx.cs:line 253
Here is the code for the application:
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Text;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
namespace SubmissionClearance
{
/// <summary>
/// Summary description for WebForm1.
/// </summary>
public class SubForm : System.Web.UI.Page
{
protected System.Web.UI.HtmlControls.HtmlAnchor AgAcc;
protected System.Web.UI.HtmlControls.HtmlAnchor SubClr;
protected System.Web.UI.WebControls.DropDownList ddlInsured;
protected System.Web.UI.WebControls.TextBox txtFirst;
protected System.Web.UI.WebControls.TextBox txtLast;
protected System.Web.UI.WebControls.RequiredFieldValidator rfvtxtFirst;
protected System.Web.UI.WebControls.RegularExpressionValidat or revtxtDOB;
protected System.Web.UI.WebControls.Label lblInsured;
protected System.Web.UI.WebControls.Label lblFirst;
protected System.Web.UI.WebControls.Label lblLast;
protected System.Web.UI.WebControls.Label lblDOB;
protected System.Web.UI.WebControls.RequiredFieldValidator rfvtxtLast;
protected System.Web.UI.WebControls.RequiredFieldValidator rfvtxtDOB;
protected System.Web.UI.WebControls.RangeValidator rvtxtDOB;
protected System.Web.UI.WebControls.Label lblZip;
protected System.Web.UI.WebControls.TextBox txtZip;
protected System.Web.UI.WebControls.RequiredFieldValidator rfvtxtZip;
protected System.Web.UI.WebControls.RegularExpressionValidat or revtxtZip;
protected System.Web.UI.WebControls.Label lblYrCraft;
protected System.Web.UI.WebControls.TextBox txtYrCraft;
protected System.Web.UI.WebControls.Label lblYYYY;
protected System.Web.UI.WebControls.RangeValidator rvtxtYrCraft;
protected System.Web.UI.WebControls.RequiredFieldValidator rfvtxtYrCraft;
protected System.Web.UI.WebControls.RegularExpressionValidat or revtxtYrCraft;
protected System.Web.UI.WebControls.Label lblSSN;
protected System.Web.UI.WebControls.TextBox txtSSN;
protected System.Web.UI.WebControls.RegularExpressionValidat or revtxtSSN;
protected System.Web.UI.WebControls.Label lblAgent;
protected System.Web.UI.WebControls.TextBox txtAgent;
protected System.Web.UI.WebControls.Label lblLast4;
protected System.Web.UI.WebControls.RequiredFieldValidator rfvAgent;
protected System.Web.UI.WebControls.Label lblNewPur;
protected System.Web.UI.WebControls.DropDownList ddlNewPur;
protected System.Web.UI.WebControls.TextBox txtDOB;
protected System.Web.UI.WebControls.Button btnSubmit;
protected System.Web.UI.WebControls.ValidationSummary vsMakeSub;
protected System.Web.UI.WebControls.Label lblStatus;
protected System.Web.UI.WebControls.TextBox txtStatus;
private string sSqlCmd;
private string sConn;
System.Data.OleDb.OleDbConnection oleDBConn;
System.Data.OleDb.OleDbCommand oleDBCmd;
private string strAgentNumber;
protected System.Web.UI.WebControls.Button btnReset;
private HttpCookie cookie;
// private System.Data.OleDb.OleDbDataReader oleDBReader;
private void Page_Load(object sender, System.EventArgs e)
{
// Put user code to initialize the page here
rvtxtYrCraft.MaximumValue = DateTime.Today.Year.ToString();
rvtxtYrCraft.MaximumValue = ((int)((DateTime.Today.Year) + 1)).ToString();
OnInsuredSelect();
if (!CookieTest())
{
return;
}
strAgentNumber = cookie.Values["GeneralAgentNumber"];
}
private bool CookieTest()
{
cookie = Request.Cookies["MarkelMarine"];
if (cookie == null)
{
// Server.Transfer("../Agents/FormsLogin.aspx");
AgAcc.HRef = "../Agents/FormsLogin.aspx";
Response.Redirect(AgAcc.HRef,true);
return false;
}
return true;
}
private void OnInsuredSelect()
{
if (ddlInsured.SelectedValue == "Y")
{
txtFirst.Enabled = false;
txtLast.Enabled = false;
txtDOB.Enabled = false;
txtZip.Enabled = false;
txtSSN.Enabled = false;
txtYrCraft.Enabled = false;
txtAgent.Enabled = false;
ddlNewPur.Enabled = false;
btnSubmit.Enabled = false;
txtFirst.Text = string.Empty;
txtLast.Text = string.Empty;
txtDOB.Text = string.Empty;
txtZip.Text = string.Empty;
txtSSN.Text = "9999";
txtYrCraft.Text = string.Empty;
txtAgent.Text = string.Empty;
}
else
{
txtFirst.Enabled = true;
txtLast.Enabled = true;
txtDOB.Enabled = true;
txtZip.Enabled = true;
txtSSN.Enabled = true;
txtYrCraft.Enabled = true;
txtAgent.Enabled = true;
ddlNewPur.Enabled = true;
btnSubmit.Enabled = true;
}
}
#region Web Form Designer generated code
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: This call is required by the ASP.NET Web Form Designer.
//
InitializeComponent();
base.OnInit(e);
}
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.ddlInsured.SelectedIndexChanged += new System.EventHandler(this.ddlInsured_SelectedIndexC hanged);
this.btnSubmit.Click += new System.EventHandler(this.btnSubmit_Click);
this.btnReset.Click += new System.EventHandler(this.btnReset_Click);
this.Load += new System.EventHandler(this.Page_Load);
}
#endregion
private void ddlInsured_SelectedIndexChanged(object sender, System.EventArgs e)
{
OnInsuredSelect();
}
private void btnSubmit_Click(object sender, System.EventArgs e)
{
if (!CookieTest())
{
return;
}
Page.Validate();
for(int nCnt=0;nCnt < Page.Validators.Count; nCnt++)
{
if (!(Page.Validators[nCnt].IsValid))
{
return ;
}
}
try
{
string ssn= txtSSN.Text;
string zip= txtZip.Text;
string firstName= txtFirst.Text;
string lastName= txtLast.Text;
string dateOfBirth= txtDOB.Text;
string craftYear= txtYrCraft.Text;
string sStatus= "Y";
firstName= Globals.GetSafeSqlString(firstName);
lastName= Globals.GetSafeSqlString(lastName);
txtStatus.Visible = false;
if (ssn == string.Empty)
{
txtSSN.Text = "9999";
ssn="9999";
}
sConn = System.Configuration.ConfigurationSettings.AppSett ings["MyConnection"];
using(oleDBConn = new OleDbConnection(sConn))
{
oleDBConn.Open();
txtStatus.Text = "Accepted";
// New submission logic per Marine dept:
// 1) (First or last name) and date of birth and boat year
// -or-
// 2) Last name and (zipcode or ssn) and boat year
// Exact match on names.
StringBuilder sql=new StringBuilder();
sql.Append ("SELECT distinct Agent FROM SubmissionDetails ");
sql.AppendFormat("WHERE (((TRIM(FirstName)='{0}' or TRIM(LastName)= '{1}') and DateOfBirth =#{2}#) ",firstName,lastName,dateOfBirth);
sql.AppendFormat("or (TRIM(LastName)= '{0}' and (ZipCode={1} or SSN={2}))) ",lastName,zip,ssn);
sql.AppendFormat("and YearOfCraft ={0} and Status = 'Y' and ExpiryDate >= Date()",craftYear);
oleDBCmd = new OleDbCommand(sql.ToString(),oleDBConn);
//oleDBCmd.Connection = oleDBConn;
object result=oleDBCmd.ExecuteScalar();
if(result!=null)
{
//found a match
txtStatus.Text ="Already Reserved";
sStatus= "N";
if(strAgentNumber==result.ToString())
{
//current agent submitted a dup
txtStatus.Text ="Duplicate Entry";
return;
}
}
//end new submission logic
//log the submission requests
sSqlCmd = "INSERT INTO SubmissionDetails(FirstName,LastName,DateOfBirth,S SN,ZipCode," +
"YearOfCraft,NewPurchase,Agent,AgentInitial,Create By,UpdateBy,Status) " +
"Values('" + firstName + "','" + lastName + "','" + dateOfBirth + "'" +
"," + ssn + "," + zip + "," + craftYear +
",'" + ddlNewPur.SelectedValue + "','" + strAgentNumber + "','" + txtAgent.Text + "'" +
",'" + strAgentNumber + "','" + strAgentNumber + "','" + sStatus + "')";
//oleDBCmd = new OleDbCommand(sSqlCmd,oleDBConn);
oleDBCmd = new OleDbCommand(sSqlCmd);
oleDBCmd.ExecuteNonQuery();//line 253
oleDBConn.Close();
}
}
catch (Exception e1)
{
txtStatus.Text = e1.ToString();
//"not successful. An error occurred during submission."
}
finally
{
txtStatus.Visible = true;
}
}
private void btnReset_Click(object sender, System.EventArgs e)
{
txtFirst.Text = string.Empty;
txtLast.Text = string.Empty;
txtDOB.Text = string.Empty;
txtZip.Text = string.Empty;
txtSSN.Text = "9999";
txtYrCraft.Text = string.Empty;
txtAgent.Text = string.Empty;
txtStatus.Text = string.Empty;
}
}
}
So if someone could please help me fix the errorâ¦I have been working on this for the last 2 days but so far no luckâ¦thanksâ¦appreciate it!
Note: I have write (all permission) permission to the database in access so that I can insert data.
|
