AutoSuggest from ADODB

[woter324]

Hi,

I'm a VBScripter rather than a JavaScripter and I am stuggling on changing some GPL code to do what I need.

In my HTA, when I type in a text box it has a little dropdown that makes suggestions based on an array.

The original code can be found here.

The original code lists US states in an array, but I am trying to use an ADODB connection to pull the SiteName details from an Access database (I use this elsewhere in the script) using a System DSN connection. I've got it to connect to the database ok, but I now need to get it to iterate through the database. This is where I am thrown by JS :-(.

Here is the original array:

function StateSuggestions() {
StateSuggestionsDB
    this.states = [
        "Albion House", "Pembroke House", "Peppie Close", "Arkansas",
        "California", "Colorado", "Connecticut",
        "Delaware", "Florida", "Georgia", "Hawaii",
        "Idaho", "Illinois", "Indiana", "Iowa",
        "Kansas","blar blar blar..."];
}

And here is my attempt:

function StateSuggestions(){
adOpenForwardOnly = 0;
adLockReadOnly = 1;
adCmdText = 1;

var myConnect = "DSN=SiteDSN"; 

var ConnectObj = new ActiveXObject("ADODB.Connection");
var adoRecordSet = new ActiveXObject("ADODB.Recordset");
var sql = "SELECT SiteName FROM tblSite;";

ConnectObj.Open(myConnect);
adoRecordSet.Open(sql, ConnectObj, adOpenForwardOnly,adLockReadOnly,adCmdText);

alert ("recordCount: " + adoRecordSet("SiteName").value);
this.states = adoRecordSet("SiteName").value;

adoRecordSet.Close()
}

I think I may need to put the database records into an array within the function?

The alert return the last record and when I type in the text box I get:

Error: 'this.states[...]' is null or not an object

As always, I am very grateful for any help or pointers.

Many thanks

Tex

 

[woter324]

I've given it a shot myself, but it's now giving me an error at something that should be easy. An alert() box!

I've created a new file and function just to test this piece of code.

Here's my efforts:

testADO();

function testADO(){
adOpenForwardOnly = 0;
adLockReadOnly = 1;
adCmdText = 1;

var myConnect = "DSN=SiteDSN"; 

var ConnectObj = new ActiveXObject("ADODB.Connection");
var adoRecordSet = new ActiveXObject("ADODB.Recordset");
var sql = "SELECT SiteName FROM tblSite;";
var Site = ""
ConnectObj.Open(myConnect);
adoRecordSet.Open(sql, ConnectObj, adOpenForwardOnly,adLockReadOnly,adCmdText);

//var recordCount = adoRecordSet.Fields.Count;
//alert ("recordCount: " + adoRecordSet("SiteName").value);
//this.states = adoRecordSet("SiteName").value;
if (adoRecordSet.EOF != true)
{
	while (adoRecordSet.EOF != true) 
	{
		if (Site == "")
		{ 
			Site = adoRecordSet("SiteName");
			//alert("Site: " + Site);
		}
		else
		{
			Site = Site + ", " + adoRecordSet("SiteName");
		}
		adoRecordSet.MoveNext();
	}
}


if (Site == "")
{
	alert("no suggestions");
}
else
{
	//alert(Site);
	alert("fish");
}
adoRecordSet.Close();

}

The line alert("fish"); is giving me an "Object Expected" error. It's doing the simplest thing too.

If anyone has any ideas, I'd be most grateful if they would share.

Many thanks

MC & HNY

 

[woter324]

Running a .js file seems to use M$ jscript rather than javascript, so it doesn't like alert();. I put it in a test HTA and called the script onLoad and it works.

I've put my code back into the proper script and I'm now very close. I get the records in the drop down list, but all the sites are seen as one block, i.e. the text box only sees one record. How do I split the records?

Here is my latest code:

function StateSuggestions(){

adOpenForwardOnly = 0;
adLockReadOnly = 1;
adCmdText = 1;

var myConnect = "DSN=SiteDSN"; 

var ConnectObj = new ActiveXObject("ADODB.Connection");
var adoRecordSet = new ActiveXObject("ADODB.Recordset");
var sql = "SELECT SiteName FROM tblSite;";
var Site = ""
ConnectObj.Open(myConnect);
adoRecordSet.Open(sql, ConnectObj, adOpenForwardOnly,adLockReadOnly,adCmdText);

//var recordCount = adoRecordSet.Fields.Count;
//alert ("recordCount: " + adoRecordSet("SiteName").value);
//this.states = adoRecordSet("SiteName").value;
if (adoRecordSet.EOF != true)
{
	while (adoRecordSet.EOF != true) 
	{
		if (Site == "")
		{ 
			Site = adoRecordSet("SiteName");
			//alert("Site: " + Site);
		}
		else
		{
			Site = Site + ", " + adoRecordSet("SiteName");
		}
		adoRecordSet.MoveNext();
	}
}


if (Site == "")
{
	alert("no suggestions");
}
else
{
	//document.write(Site);
	//alert("fish");
	this.states = [Site];
}
adoRecordSet.Close();

}

Once again, any help would be gratefully received.

Thanks

Tex

 

[woter324]

Still struggling with the last bit, so a little bump.

Many thanks

HNY

 

[woter324]

Fixed it. For anyone that's interested:

function StateSuggestions(){

adOpenForwardOnly = 0;
adLockReadOnly = 1;
adCmdText = 1;

var myConnect = "DSN=SiteDSN"; 

var ConnectObj = new ActiveXObject("ADODB.Connection");
var adoRecordSet = new ActiveXObject("ADODB.Recordset");
var sql = "SELECT SiteName FROM tblSite;";

this.states = [];
ConnectObj.Open(myConnect);
adoRecordSet.Open(sql, ConnectObj, adOpenForwardOnly,adLockReadOnly,adCmdText);

	while (adoRecordSet.EOF != true) 
	{
		this.states[this.states.length]= adoRecordSet("SiteName").value;
		adoRecordSet.MoveNext();
	}

adoRecordSet.Close();

}