Well these is a sample project for inserting/selecting/updating/deleting data from the database(MS SQL Server 2000) using ColdFusion.

The zzz datasource uses the SchDb database in the MS SQL Server 2000. You just need to define the datasource in the ColdFusion administrator to enable it.

Click to Download

Example Table

to SELECT data i created this class called connect and the method and properties below.

private SqlDataReader _select;
public SqlDataReader select
{
get
{
return _select;
}
set
{
_select = value;
}
}

public void query_select(string query)
{
SqlConnection c = new SqlConnection(@”Network Library=DBMSSOCN;Data Source=localhost;database=philguard;User id=karlpox;Password=12345;”);

c.Open();

SqlCommand q = new SqlCommand(query, c);

SqlDataReader r = q.ExecuteReader();

this.select = r;
}

to use we do this. Just call the query_select method and pass on your query(SQL statement) to be executed.

connect newConnect = new connect();
newConnect.query_select(”SELECT * FROM position”);
while (newConnect.select.Read())
{
listBox1.Items.Add(newConnect.select["pos_name"].ToString());
}

All rows returned will be added in the listbox.

Second to add datas into the database add this method to your class.

public void query_add(string query)
{
SqlConnection c = new SqlConnection(@”Network Library=DBMSSOCN;Data Source=localhost;database=philguard;User id=karlpox;Password=12345;”);

c.Open();

SqlCommand q = new SqlCommand();

q.Connection = c;

q.CommandText = query;

q.ExecuteNonQuery();

c.Close();
}

And to use this method we do this.

connect newConnect = new connect();
newConnect.query_add(”INSERT INTO position (pos_name) VALUES (`” textBox1.Text “`)”);

The statement above will add the data(textBox1.text) into your position database. Take note that pos_id is auto increment so you don`t have to specify it.