Insert to Access DB from C# webform

Insert to Access DB from C# webform

• magruder2

  Hi,

  This is the code for it. I've highlighted the connection string in bold, and the entries that must be inserted, however, the entries I chose to insert were from the text fields and dropdowns on the form, whereas what I really want is once these are submitted to the table, all the actual values from the table to be inserted to the database.

  private Job currentEditJob;

  private int index;

  private int genTotal, sickTotal, holTotal;

  // private string eid;

  protected void Page_Load(object sender, EventArgs e)

  {

  int d = DateTime.Now.DayOfYear;

  //Gives the day of this year

  int x = 7; //7 days in week

  lblWeekNo.Text = ((d / x) + 1).ToString();

  lblDate.Text = "" + DateTime.Now;

  ArrayList list = null;

  index = -1;

  //READ IN VARIABLES

  if (Request.QueryString.Count > 0)

  {

  string value = Request.QueryString.GetValues(0)[0];

  index = Int32.Parse(value);

  }

  string datacode;

  datacode = datasourceSurname.UpdateCommand;

  if (Session["list"] != null)

  {

  genTotal = 0;

  holTotal = 0;

  sickTotal = 0;

  list = new ArrayList();

  list = (ArrayList)Session["list"];

  /**

  * Go through list to add in each cell

  **/

  IEnumerator en = list.GetEnumerator();

  int count = 0;

  while (en.MoveNext())

  {

  Job j2 = (Job)en.Current;

  if (count == index)

  {

  currentEditJob = j2;

  }

  else

  {

  if (j2.getActivity().Equals("SICK"))

  {

  sickTotal += Int32.Parse(j2.getHours());

  }

  if (j2.getActivity().Equals("GEN"))

  {

  genTotal += Int32.Parse(j2.getHours());

  }

  if (j2.getActivity().Equals("HOLS"))

  {

  holTotal += Int32.Parse(j2.getHours());

  }

  TableRow myRow1 = new TableRow();

  TableCell c11 = new TableCell();

  c11.Text = j2.getID();

  TableCell c21 = new TableCell();

  c21.Text = j2.getDescription();

  TableCell c31 = new TableCell();

  c31.Text = j2.getActivity();

  TableCell c41 = new TableCell();

  c41.Text = j2.getHours();

  TableCell c51 = new TableCell();

  LinkButton lb = new LinkButton();

  lb.Text = "Delete";

  lb.PostBackUrl = "time.aspx index=" + count;

  c51.Controls.Add(lb);

  myRow1.Cells.Add(c11);

  myRow1.Cells.Add(c21);

  myRow1.Cells.Add(c31);

  myRow1.Cells.Add(c41);

  myRow1.Cells.Add(c51);

  Table1.Rows.Add(myRow1);

  count++;

  //set labels

  lblSick.Text = sickTotal.ToString();

  lblHol.Text = holTotal.ToString();

  lblGen.Text = genTotal.ToString();

  //Int32 TotalHours = sickTotal + genTotal + holTotal;

  int totalSum = sickTotal + holTotal + genTotal;

  lblTotalHours.Text = totalSum.ToString();

  //Response.Redirect("time.aspx");

  }

  }

  }

  if (index > -1)

  {

  list.RemoveAt(index);

  index = -1;

  Response.Redirect("time.aspx");

  }

  }

  protected void TextBox1_TextChanged(object sender, EventArgs e)

  {

  }

  protected void Button1_Click(object sender, EventArgs e)

  {

  Job j = new Job(ddlJobID.Text, ddlJobDesc.Text, lbActivity.Text, txtHours.Text);

  ArrayList list = new ArrayList();

  if (Session["list"] != null)

  {

  list = (ArrayList)Session["list"];

  }

  if (index > -1)

  {

  }

  else

  {

  TableRow myRow = new TableRow();

  TableCell c1 = new TableCell();

  c1.Text = j.getID();

  TableCell c2 = new TableCell();

  c2.Text = j.getDescription();

  TableCell c3 = new TableCell();

  c3.Text = j.getActivity();

  TableCell c4 = new TableCell();

  c4.Text = j.getHours();

  TableCell c5 = new TableCell();

  LinkButton lnkDelete = new LinkButton();

  lnkDelete.Text = "Delete";

  lnkDelete.PostBackUrl = "time.aspx index=" + list.Count;

  c5.Controls.Add(lnkDelete);

  myRow.Cells.Add(c1);

  myRow.Cells.Add(c2);

  myRow.Cells.Add(c3);

  myRow.Cells.Add(c4);

  myRow.Cells.Add(c5);

  Table1.Rows.Add(myRow);

  if (j.getActivity().Equals("SICK"))

  {

  sickTotal += Int32.Parse(j.getHours());

  lblSick.Text = sickTotal.ToString();

  }

  if (j.getActivity().Equals("GEN"))

  {

  genTotal += Int32.Parse(j.getHours());

  lblGen.Text = genTotal.ToString();

  }

  if (j.getActivity().Equals("HOLS"))

  {

  holTotal += Int32.Parse(j.getHours());

  lblHol.Text = holTotal.ToString();

  }

  list.Add(j);

  Session["list"] = list;

  int totalSum = holTotal + genTotal + sickTotal;

  lblTotalHours.Text = totalSum.ToString();

  }

  }

  protected void ddlJobID_SelectedIndexChanged(object sender, EventArgs e)

  {

  string selectedvalue = this.ddlJobID.SelectedValue.ToString();

  ddlJobDesc.SelectedIndex = ddlJobID.SelectedIndex;

  }

  protected void btnSend_Click(object sender, EventArgs e)

  {

  if (Page.IsValid)

  {

  //AddRecordToDatabase();

  }

  OleDbConnection DBConn;

  OleDbCommand DBcomm;

  OleDbDataReader DBReader;

  string sqlStr;

  DBConn = new OleDbConnection("provider=microsoft.jet.oledb.4.0;"

  + "Data Source= C:\\Documents and Settings\\Sarah Kelleher\\Desktop\\BIS4\\eTime\\App_Data\\time.mdb");

  DBConn.Open();

  sqlStr = "INSERT into tblEmp(EmployeeID, Date, JobID, Entry Type, WeekNo, Hours) Values (@FormView2.Value, @lblDate.Text, @c1, @c3, @lblWeekNo.Text, @c4)";

  DBcomm.ExecuteNonQuery;

  DBcomm = new OleDbCommand(sqlStr, DBConn);

  DBConn.Close;

  //submit to database...

  ArrayList l = null;

  if (Session["list"] != null)

  {

  l = (ArrayList)Session["list"];

  //get emp id

  // Session["username"] = lblUser.Text;

  String empID = "";

  String sql = "select * from tblEmp where Username='" + Session["username"].ToString() + "'";

  string eid = Session["username"].ToString();

  //lblTest.Text = eid;

  IEnumerator ene = l.GetEnumerator();

  while (ene.MoveNext())

  {

  Job j = (Job)ene.Current;

  //put it into the database.. for user id

  //create a sql statement

  //need to find the week number

  DateTime n = new DateTime();

  string sNow = "";

  sNow = n.ToShortDateString();

  }

  ///Response.Redirect("Manager.aspx");

  }

  }

  protected void btnLast_Click(object sender, EventArgs e)

  {

  Response.Redirect("lasttime.aspx");

  }

  protected void Button7_Click(object sender, EventArgs e)

  {

  Response.Redirect("PreviousEntries.aspx");

  }

  protected void datasourceSurname_Selecting(object sender, SqlDataSourceSelectingEventArgs e)

  {

  }

  
  

• Mark_A_Polson

  I don't understand what data you are prompting the user to insert, then bulk inserting. You are dynamically creating a table, but what values are in it. What is the underlying source that you want to insert

  
  

• barneyk

  Hey,

  You can use a gridview to show the values (like from a datatable, which you have to retain using session or cache; or use accessdatasource). Then, click a button for accepting it, then use the OleDbDataAdapter.Update or AccessDataSource to do the insertion into the DB.

  I don't understand why exactly you are doing, where the values are before and after, etc., so I can't help more.

  Brian

  
  
• Insert to Access DB from C# webform