How to connecting to databases using SWFKit(SWFKit 2 and SWFKit Pro 2)

[SWFKit]

How to connecting to databases using SWFKit(SWFKit 2 and SWFKit Pro 2)

1. Connect to the database

Before a database can be accessed from a SWFKit app, a database connection has to be established.
The steps to make a database connection

i)Creating an ADO.Connection object

    var conn = new ActiveXObject("ADODB.Connection");

ii)Creating a connection string and open the connection

The connection string contains the provider, db name, etc.
E.g. To connect to a MS Access DB "c:\mydata\sample.mdb", the connection string is

var connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\mydata\sample.mdb";
    conn.open(connStr);

To connect to a MS SQL SERVER DB "Northwind" on server "mysite", the connection string is

var connStr = "Provider=SQLOLEDB;Server=mysite;Database=Northwind;User Id=MyId;Password=123aBc";
    conn.open(connStr);

SWFKit can connect to any database like VB, Delphi, VC or ASP can do.

How to ensure the connection has been made successfully?
You can handle the "ConnectComplete" event of the ADO.Connection object. The event fires when the connection is completed.

var conn = new ActiveXObject("ADODB.Connection");
    conn.connectOK = false;
    conn.ConnectComplete = function (err, status, cnt)
    {
        if (typeof err != "undefined")
        {
            trace(err.Description);

            return;
        }
        else
        {
            trace("Connect complete!");
            conn.connectOK = true;
        }

        trace("status: ", status.value);
    }

    var connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\mydata\sample.mdb";
    conn.open(connStr);

if (!conn.connectOK)
{
    trace("Failed to connect to the DB");
    return;
}

2. Getting Data from the Database

After the connection has been made, you can get data from database using the ADO.Recordset object.

i) Creating the ADO.Recordset object

var record = new ActiveXObject("ADODB.Recordset");

ii) Opening the ADO.Recordset object with a specified command string

record.Open("select id, name, age, sex, score from student", conn, /*adOpenKeyset*/1, /*adLockPessimistic*/2);

iii) Moving to the start of the record set

record.moveFirst();

iv) Fetching Data

while (!record.eof)
    {
        trace(record.Fields(0).Value.toString());
            trace(record.Fields(1).Value.toString());
        trace(record.Fields(2).Value.toString());
        trace(record.Fields(3).Value.toString());
        trace(record.Fields(4).Value.toString());

        record.moveNext();
    }

Tips:

1) Move to the start of the record set

record.MoveFirst();

2) Move to the end of the record set

record.MoveLast();

3) Move to the previous record

record.MovePrevious();
    if (record.bof()) record.MoveFirst();

4) Move to the next record

record.MoveNext();
    if (record.eof) record.MoveLast();

Links
ADO Reference
http://msdn.microsoft.com/library/de...dooverview.asp
ADO Tutorial
http://www.w3schools.com/ado/default.asp

[SWFKit]

Sample

MS Access sample

[SWFKit]

MS SQL Server sample. To test it, you must have a server installed MS SQL Server

[SWFKit]

With Flash 8 + SWFKit Pro 3(SWFKit 3), you can use ADO directly in Action Script. Please try the following sample out.

http://www.swfkit.com/forum/attachments/adodb.zip

[panjavishnu]

hi i am using swf kit 2.2. ur code is working to access data from ms access. but when i tried to update the database an error message is comng

I used this code:
var conn = new ActiveXObject("ADODB.Connection");
connstr = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source = D:\\student.mdb";
conn.open(connstr);

var record = conn.Execute("update stud set age = 30 where Id = 1");
fld = record.fields["age"];

trace(fld.value.toString());


conn.close();

And the errors are:

ADODB.Fields.Item cannot be found in the collection corresponding to the requested name or ordinal. See also C:\WINDOWS\HELP\ADO270.CHM.
Warning: using undefined varialbe "fld"
Warning: using undefined property "Value"
Warning; unknown method "toString"
undefined

Please help me this is in my project

[SWFKit]

The table name in your update statement "update stud set age = 30 where Id = 1" is incorrect. The table name should be "student" not "stud". Hence, the "execute" method will return an empty recordset, which does not contain an "age" field. When you try to get the value of the "age" field, it will say that the field cannot be found.

[panjavishnu]

Thank you for your immediate response.

But here 'stud' is my table name. I followed your suggestion but this time it is not retrieving data. When i use 'stud' in my statement it retrieving the data. so there is no problem with table name.

Code:
var conn = new ActiveXObject("ADODB.Connection");
connstr = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source = D:\\student.mdb";
conn.open(connstr);

var record = conn.Execute("select Name,age from stud");

var fld = record.Fields["age"];
var sname = record.Fields["Name"];

sname.value = "vishnu";
conn.update();
trace(sname.value.toString());
trace(fld.value.toString());

conn.close();

Output:
ADODB.Field:Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype.. See also: C:\WINDOWS\HELP\ADO270.CHM.
Warning: unknown method "update"
Warning: unknown method "toString"
undefined
30

In the above output 30 is from age in table 'stud'.
But when i am trying to update the data its not working.
INSERT is also not working.

Please give me a solution.
If possible please attach an example of database retrieving and updating in ms access.
Thank you again for your quick response.

[meester]

try this selection query
it works for me

var record = new ActiveXObject("ADODB.Recordset");
record.open("select Name,age from stud", conn, 1, 2);

you can find some info about cursortypes on:
http://www.w3schools.com/ado/met_rs_open.asp

[panjavishnu]

hey it worked thanku very much . and i also figured out the prob in my program . thank u for ur valuable response.

[jon]

I'm trying to connect to a web based MySQL server but i cant figure out the syntax for the connection. I have;

var conn = new ActiveXObject("ADODB.Connection");
var str = "PROVIDER=sqloledb;Data Source=http://cell-lms.com;Integrated Security=SSPI;Database=celllms_romsuser;UID=celllms_trial;PWD=trial";
conn.Open(str);

What might a correct Data Source look like? Are there any other feilds I need?

Thanks
Jon