how to connect/save/update data to the database ?

[BOS]

Hi there,

I just create a form that contains name, Address, City, State, Zip, Question Checked box, and dropdown list selection for the user to fill-out the answer in the texbox, checkbox or choose the selection the option...

For example here is the Form properties I setup:

Text="Name" Text ID="LabelName" TextBox="TextBoxName"
Text="Address" Text ID="LabelAddress" TextBox="TextBoxAddress"
Text="City" Text ID="LabelCity" TextBox="TextBoxCity"
Text="State" Text ID="LabelState" TextBox="TextBoxState"
Text="Zip" Text ID="LabelZip" TextBox="TextBoxZip"
Text="QuestionAText" Text ID="LabelQuestionAText" CheckBox ="TextBoxQuesAYes"
Text="QuestionAText" Text ID="LabelQuestionAText" CheckBox ="TextBoxQuesANo"
Text="Favorite Color" Text ID="LabelFavoriteColor" List List=" red, white, blue"
l

I also create the access database with field names corresponding to the form above

Information.mdb <----Access database named Information, Here are the fieldnames
:
ID ------> type autonum
Name ----->type text
Address ----->type text
City ----->type text
State ----->type text
Zip ----->type text
Name ----->type text
QuestionAYes ------------->type checkbox
QuestionANo ------------->type checkbox
Favorite_Color---------->type text


I can run it to the web on the local host and type in the information in it, but when I click "Submit Button" I want it to update those information and save to the access database when click Submit button. I use OleDbconnection to connect it to the access database and it shows that the link like this Provider=Microsoft.Jet.OLEDB.4.0;Data Source="C:\Documents and Settings\BOS\My Documents\Visual Studio 2005\Projects\PersonalInformation\Information.mdb"

So how do I set up the Olecommand, databind, update, and insert those information into the access database? Can someone show me the code in ASP.NET IN C# Please ? I'm very new to all this :-) thanks in advance

[BOS]

Here is my code, I wrote it in ASP.NET IN C# , but it's not connecting to the access database eventhough I create the field names corresponding to the values of the textbox. Maybe someone can show an error or a better way to connect /save/update the data, thanks in advance

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Configuration;

namespace FORM
{
/// <summary>
/// Summary description for WebForm1.
/// </summary>
public class WebForm1 : System.Web.UI.Page
{
protected System.Web.UI.WebControls.Button ButtonSubmit;
protected System.Web.UI.WebControls.Button ButtonClear;
protected System.Web.UI.WebControls.TextBox TextBoxName;
protected System.Web.UI.WebControls.TextBox TextBoxAddress;
protected System.Web.UI.WebControls.TextBox TextBoxCity;
protected System.Web.UI.WebControls.TextBox TextBoxState;
protected System.Web.UI.WebControls.TextBox TextBoxZip;
protected System.Web.UI.WebControls.CheckBox No;
protected System.Web.UI.WebControls.CheckBox Yes;
private ArrayList nameList;


private void Page_Load(object sender, System.EventArgs e)
{
/*if (!Page.IsPostBack)
{


// Put user code to initialize the page here
}*/
}

#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.ButtonClear.Click += new System.EventHandler(this.ButtonClear_Click);
this.ButtonSubmit.Click += new System.EventHandler(this.ButtonSubmit_Click);
this.Load += new System.EventHandler(this.Page_Load);

}
#endregion


private void getinformation ()
{
string myConnString;
OleDbConnection myConnection;
OleDbCommand myCommand;
OleDbDataReader myReader;
string dbPath = ConfigurationSettings.AppSettings.Get("FormDb");
string retVal;
int i;
try
{
//nameList = new ArrayList();
//emailList = new ArrayList();
string mySelectQuery = "Select * from Information";
myConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dbPath;
myConnection = new OleDbConnection(myConnString);
myCommand = new OleDbCommand(mySelectQuery, myConnection);
myConnection.Open();
myReader = myCommand.ExecuteReader();
i = 0;
while (myReader.Read())
{
TextBoxName.Add (myReader["Name"] + "");
TextBoxAddress.Add( myReader["Address"] + "");
TextBoxCity.Add( myReader["City"] + "");
TextBoxState.Add( myReader["State"] + "");
TextBoxZip.Add( myReader["Zip"] + "");
Yes.Add( myReader["CheckBoxYes"] + "");
No.Add( myReader["CheckBoxNo"] + "");
i++;
}
retVal = i.ToString();
myReader.Close();
myConnection.Close();
}
catch (Exception e)
{
retVal = ("Error: " + e.Message + "dbPath = " + dbPath);
}
return retVal;
}


}

private string InsertInformation(string myConnString)
{
//string myConnString;
OleDbConnection myConnection;
OleDbCommand myCommand;
OleDbDataReader myReader;
string dbName = ConfigurationSettings.AppSettings.Get("FormDb"); //declare in Web.config <add key="FormDb" value="C:\Inetpub\wwwroot\FORM\Form.mdb"/>
string myUpdateQuery, mySelectQuery;
string retVal;
try
{
mySelectQuery = "Select Form, Name = " + "'" + iptName.Value.Trim() + "'";
myConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dbName;
myConnection = new OleDbConnection(myConnString);
myCommand = new OleDbCommand(mySelectQuery, myConnection);
myConnection.Open();
myReader = myCommand.ExecuteReader();
if (myReader.Read())
{
myUpdateQuery = "Update Form Set Name='" + TextBoxName.Value.Trim() + "' Where Form= '" + TextBoxName.Value.Trim() + "'";
myUpdateQuery = "Update Form Set Address='" + TextBoxAddress.Value.Trim() + "' Where Form= '" + TextBoxAddress.Value.Trim() + "'";
}
else
{
myUpdateQuery = "INSERT INTO Form (Name, Address, City, State, Zip, CheckboxYes, CheckboxNo ) Values (";
myUpdateQuery = myUpdateQuery + "'" + TextBoxName.Value.Trim() + "',";
myUpdateQuery = myUpdateQuery + "'" + TextBoxAddress.Value.Trim() + "')";
myUpdateQuery = myUpdateQuery + "'" + TextBoxCity.Value.Trim() + "')";
myUpdateQuery = myUpdateQuery + "'" + TextBoxState.Value.Trim() + "')";
myUpdateQuery = myUpdateQuery + "'" + TextBoxZip.Value.Trim() + "')";
myUpdateQuery = myUpdateQuery + "'" + Yes.Trim() + "')";
myUpdateQuery = myUpdateQuery + "'" + No.Trim() + "')";
}
myReader.Close();
if (myConnection.State != ConnectionState.Open)
myConnection.Open();
OleDbCommand newCommand = new OleDbCommand(myUpdateQuery, myConnection);
newCommand.ExecuteNonQuery();
myConnection.Close();
retVal = "Success: InsertRequestorInfor";
}
catch (Exception e)
{
retVal = ("Error: " + e.Message + "dbPath = " + dbName);
}
return retVal;
}


private void ButtonClear_Click(object sender, System.EventArgs e)
{
TextBoxName.Text = " ";
TextBoxAddress.Text = " ";
TextBoxCity.Text = " ";
TextBoxState.Text = " ";
TextBoxZip.Text = " ";
Yes.Text = " ";
No.Text = " ";

}

private void ButtonSubmit_Click(object sender, System.EventArgs e)
{
string retVal;

if (TextBoxName.text == null) || (TextBoxAddress.text == null)|| (TextBoxCity.text == null)||
(TextBoxState.text == null)|| (TextBoxZip.text == null)|| (Yes.text == null)||(No.text == null)
{
retVal = " Please fill in all the information";
}
else
{
getinformation ();
InsertInformation ();
}



}
}

[Kara Hewett]

The connection to the database should use OLEDB.

Sub DB_Utility()
  Dim strConnection as String = "Provider=Microsoft.Jet.OLEDB.4.0;"
    strConnection += "Data Source=Northwind.mdb"
    data_src.text = strConnection
  Dim strSQL as string = "SELECT FirstName, LastName FROM Employees"
  Dim strResultsHolder as string
 
  Dim objConnection as New OledbConnection(strConnection)
  Dim objCommand as New OledbCommand(strSQL, objConnection)
  Dim objDataReader as OledbDataReader
 
  try
    objConnection.Open()
    con_open.text="Connection opened successfully.<br />"
  objDataReader = objCommand.ExecuteREader()
 
  Do While objDataReader.Read()=True
    strResultsHolder +=objDataREader("FirstName")
    strResultsHolder +="&nbsp;"
    strResultsHolder +=objDataREader("LastName")
    strResultsHolder +="<br>"
  Loop
 
  objDataReader.Close()
    objConnection.Close()
    con_close.text="Connection closed.<br>"
  divListEmployees.innerHTML = strResultsHolder
  catch e as Exception
    con_open.text="Connection failed to open successfully.<br>"
    con_close.text=e.ToString()
  end try
end Sub