Combobox to look up SQL(resolved)

[Beast777]

Maybe its just me but I have looked thru the forum found answers but cannot get any of the to work. Here is my latest problem.
I have a combobox called cboSN and I want to look up the field Service_Number from the table Persdata and populate that dropdown with all the numbers. I have an adodc and textboxs that are already working on the same form that connects to the database and scrolls through it. I am hoping to have the user pick the number and that will update the text box <-- that part I can do, but populating the list of the combo is beyond me. Any help would be nice, thanks.

[Ogmius]

What I typically do is start with something similar to below...

Adodc1.Recordset.MoveFirst
DataGrid1.Row = 0
DataGrid1.Col = 0 'Depending on the column number of the data your looking for
Do Until Adodc1.Recordset.EOF
     cboSN.Additem Datagrid1.text
     Adodc1.Recordset.MoveNext
Loop

What that will do is start at the first record, and add each record to your combo box one at a time, its all off the top of my head, so there may be something im missing, but that should work for you

[Beast777]

I am gettin an error "object required" with

DataGrid1.Row = 0

is that to be used with a combo box?

[Ogmius]

do you have a datagrid control tied to your addoc control?

Sorry I should have specified that you need to add an datagrid control with adodc1 as its datasource

[Beast777]

No I don't, although I just added one to see what it looks like. It returns all records in a single control. I have the standard adodc control at the bottom of the form and as I step through the records the text boxs change the data for each record. What I want to do is have on a new record page, a combo box that returns all numbers in a field. If I need a datagrid, can I use it that way and just have it not visible or is there a better way?

[Ogmius]

Yeah, that was the thought, have a separate adodc control and a separate datagrid control, and hide them , and use them just to populate your combo box, then what you can do is when someone selects numbers in your combo box, you send an SQL query to your adodc control, and have it populate the information based on the results

[techgnome]

Ogmius, do you do that EVERY time you need a combo populated?
That is freaking inefficient....

First, I'd like to mention that I do not condone the use of ADODC... it just leads to nothing but problems....
But you've already got it going it seems...
remove the code that references the grid... it serves no purpose...

Adodc1.Recordset.MoveFirst
Do Until Adodc1.Recordset.EOF
     Adodc.1.Recordset.Fields(" _ PUT YOUYR FIELD NAME HERE _ ").Value
     Adodc1.Recordset.MoveNext
Loop

TG

[Ogmius]

Well im still kind of a newb myself, but yes I use addoc and datagrid controls for all of my database driven applications, I find them easier to use, and easier to troubleshoot when certian things dont work the way I think they should

[Beast777]

I would like to thank you both for your help, new or not, you are better than I. I would be interested in seeing another method not using the adodc if you have the time. thanks again

[techgnome]

Originally posted by Ogmius
Well im still kind of a newb myself, but yes I use addoc and datagrid controls for all of my database driven applications, I find them easier to use, and easier to troubleshoot when certian things dont work the way I think they should

I understand that....
one more thing to be careful of when doing stuff like that.... when you move through the recordset, the grid moves as well... which means a small lag in the time of things being added to the combo.
A solution would be to hide the grid when doing this.

TG

[techgnome]

Originally posted by Beast777
I would like to thank you both for your help, new or not, you are better than I. I would be interested in seeing another method not using the adodc if you have the time. thanks again

I've got two "how-to" articles posted here that use ADO . Ignore the intro where I talk about SQL Server, it's really done w/ Access, but the actions are still the same either way. Anyways, they explain how to connect using ADO (not the ADODC) and get data.

TG

[Ogmius]

Honestly, whats the difference between using ADO and the ADODC controls? (obviously besides that one you are using a control and the other your not(I think?))

[techgnome]

The ADODC is an actual control... while ADO is just the object itself.
Here's where I've seen problems with the ADODC... you set the connection at design time, you bind controls to it, and you get a decent navigation system set up to flip through the records. Seems simple enough. And it gets deplyed. Then the database gets moved. The ADODC goes all hell in a handbasket because the DB is no longer it the "correct" spot. I've tried changing the DB location in an ADODC before. It wasn't a pretty sight. I effective had to remove it, and in the process loosing all commands and other crap.
Also it doesn't deal well with NULL values, especially when bound to text box. Text boxes cannot accept a NULL value. At least with ADO, you can check the value before setting it to the text box.
Also, with teh ADODC, your connection to the DB remains perpetually open. Which means if there are 100 people using the app, then you will have 100 connections open... With ADO, you can, connect, get the data, disconnect, then display the data. Your connection only remains open long enough to retrieve the data.

Hope that clears it up some

TG

[Ogmius]

Well....

1. I typically do Adodc1.Recordset.Close when im done pulling data from my database

2. I do connection strings within the app i.e.

With Adodc1
     .ConnectionString (blah blah blah)
End With

3. I always have an INI file or registry key that has the database location in it, so I can specify at run time where the database is instead of making it a perminent part of the conenction string...

I honestly dont know if that stuff makes a difference or not, but I know what you are saying... I never really got into using the Ado object, I was shown using the ADO control, I wasnt even aware that there was another way

[techgnome]

Well, I'd suggest taking a look at the articles I linked to in a previous post.
If all you are doing is using the ADODC to connect, get data, then close it, I would skip the ADODC all together - It's jsut adding overhead, you don't really need. Fortunately the ADODC it simply a control wrapper around ADO (it's intent was such that you could bind other controls to it and implement record/page flipping - sortof like the way forms work in Access) which means most of your code can remain intact.

But, that's just my two cents worth. Your mileage may vary. Tax, license and documentation fees not included. Some assembly required. Use of this product maybe hazardous to your heath.

TG

[Ogmius]

LOL, ok, well I will check them out, like I said, I didnt even realize that there was an easier way to do it