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

Click to download

If you just did a fresh install of Adobe ColdFusion 8 and Microsoft SQL Server 2000 and you tried to add a datasource but all you get is error. Its all because of the default MS SQL Server 2000 port which is 1433 is not a secure port in Windows XP.

To solve this problem you need to install the MS SQL Server 2000 SP4. This update will let Windows XP recognize the port 1433 as a secure port and will now let you connect your CFM datasource with MS SQL Server 2000.

Ever encountered this problem “Not associated with a trusted SQL Server connection”?

Well you probably didn`t specify “Mixed Mode authentication” during the installation.

Well this error occurs because by default SQL 2000 does not support SQL Server authentication and supports only Windows authentication.

To solve this problem Read more »

First is to create the connection string. You can store your connection string in a String data type.

Things that will be needed in your connection string is your Network Library, Data Source or the location of your database(ip), database the name of the database to be used, user id which is the username and lastly password.

For Example:

String cs = @”Network Library=DBMSSOCN;Data Source=localhost;database=test;User id=karlpox;Password=pogi;”

Next is to open a SQL connection. But you must import the sqlclient by adding this line to the top of your code.

using System.Data.SqlClient;

For Example:
SqlConnection c = new SqlConnection(cs);

Well these will let C# connect to the database. SQL statements not included in this post.

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.

WTF?!?!

Here`s a sample on how to update the database using Coldfusion.

Click here to download…

Heres a sample on how to insert data into the database using CFM.

Click to Download