problem with ADO find function

[damonh78]

Hi,

I am taking values from a database and using them to populate three textboxes using the code below; Basically SettingID is numerical value and settingValue contains a filepath string. SO in the below code when a form loads i want the 3 file path strings to be taken from the directory and placed in the 3 relevant textboxes. My problem is that when I run the code in my VB project, even though they have different values in the database txtDownload.Text and txtUploaded.Text end up with the value that resides in the recordset containing the value for SettingID = 4, which is what txtUploaded.Text should be but not txtDownload.Text. The first value txtUpload.Text contains the correct value.

Any help would be greatly appreciated,

John

AdodcSettings.Recordset.MoveFirst
AdodcSettings.Recordset.Find "SettingID = 1"
txtUpload.Text = AdodcSettings.Recordset.Fields("SettingValue")
uploadString = txtUpload.Text
AdodcSettings.Recordset.MoveFirst
AdodcSettings.Recordset.Find "SettingID = 2"
txtDownload.Text = AdodcSettings.Recordset.Fields("SettingValue")
downloadString = txtDownload.Text
AdodcSettings.Recordset.MoveFirst
AdodcSettings.Recordset.Find "SettingID = 4"
txtUploaded.Text = AdodcSettings.Recordset.Fields("SettingValue")
uploadedString = txtUploaded.Text

 

[jebenson]

Do you have your textboxes bound to the ADODC? Because if you do, every time you move around in the ADODC's recordset - such as with a MoveFirst or a Find - the data in the bound textboxes will change to reflect the current row being accessed in the ADODC. The soultion is to unbind your textboxes from the ADODC.


I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[damonh78]

cheers,

that works, I had been binding everything to the ADODC in my project. Is the best thing since I am only using one DB for the project to do instead to open a connection in the main form using somthing like:

'open connection to database
connCSV.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
& path & ";Persist Security Info=False"

and then use the recordset open command everytime I want to access a table somewhere in the project, like below?

getRecordSet.Open "Select * FROM tblCustomer", _
connCSV, adOpenStatic, dbOpenDynaset, adCmdText

thanks again,

john

 

[jebenson]

Doing all of the connection/querying/etc. in code has always been my preferred method of database access, because IMHO it provides much greater flexibility and control of exactly what is happening.


I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[damonh78]

In that case how do I code a datagrid instead of creating the ADODC component and then using this as the datasource for the datagrid? What becomes the datasource, etc?

 

[jebenson]

You should be able to set the DataGrid's DataSource in code:

Dim rs As New Recordset

rs.Open("Select * from Table1", MyConn)

Set DataGrid1.DataSource = rs

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[BobRodes]

I agree with jebenson, that you should use code to do this rather than the data control. Also, see faq222-6008 for reasons not to put the New keyword on the Dim line.

HTH

Bob

 

[damonh78]

Cheers for the help guys, I still seem to be having a problem when I use the following code, it brings up the datagrid but there is no data present apart from the column heading name. I create a blank datagrid component on a form called datagrid1 and then use the code, is this the right method for doing this?

Private Sub Form_Load()

Dim rs As New Recordset
Dim connCSV As New ADODB.Connection
Dim adcomm As New ADODB.Command
Dim path As String

path = "C:\database.mdb"

'open connection to database
connCSV.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
& path & ";Persist Security Info=False"

rs.Open "Select CustomerNumber FROM tblCustomer", _
connCSV, adOpenStatic, adLockReadOnly, adCmdText

Set DataGrid1.DataSource = rs

With DataGrid1
.Columns.Add 0
.Columns(0).Caption = "CustomerNumber"
.Columns(0).DataField = "CustomerNumber"
.Columns(0).Width = 2000
End With

End Sub

 

[jebenson]

I think a couple of the statements in yout With block are not needed:

With DataGrid1
.Columns(0).Width = 2000
End With

You will already have a CustomerNumber column in the DataGrid after assigning the recordset, and its caption will be the field name (i.e., "CustomerNumber"). The datafield is already assigned in the Set statement, so the only thing you really need is the line setting the column's width.


I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson