Record Navigation

[Stoomis]

I am attempting to develop a new application using Visual Basic 6.0. The data for this application is stored in a MS Sql Server Database. I have no trouble connecting to the data using an ADO data control. However I do not want to use the control to navigate through the records. I would like to be able to type in the ID number and have the record retrieved and displayed on the screen. Any help is greatly appreciated.

 

[Dimandja]

I have no trouble connecting to the data using an ADO data control. However I do not want to use the control to navigate through the records.

What would you rather use?

 

[Stoomis]

My apologies for not being clearer. I want to make the ADO data control invisible and type in the ID number into a field and have it retrieve the record when the field looses focus. How do I go about doing this?

 

[fredericofonseca]

Don't use the control.

Just add a reference to the Microsoft ActivX Data Objects (2.7 or 2.8), and then use plain code with it.

sample.
Option Explicit
Dim DBConn As ADODB.Connection
Dim RS As ADODB.Recordset
Private Sub Form_Load()
Dim sSql As String
Set DBConn = New ADODB.Connection
Set RS = New Recordset

DBConn.CursorLocation = adUseServer
DBConn.Open "dsn=LocalServer;uid=frederico;pwd=frederico;database=factucli;"

Call load_data
End Sub

Private Sub load_data()
Dim sSql As String
sSql = "select * from demotbl order by pais where pais = '" & txt1box.text & "'"

RS.Open sSql, DBConn, adOpenStatic, adLockOptimistic
fpSpread2.MaxRows = RS.RecordCount
While Not RS.EOF
debug.print = rs.Fields(1).name
debug.print = rs.Fields(1).Value

RS.MoveNext
Wend

End Sub


A lot more versatible.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[Stoomis]

Frederico,

I have done as you have suggested and I get the following error: "Compile Error: User-defined type not defined" and it points to this line of the General Declarations:

"Dim DBConn As ADODB.Connection"

Any idea what the problem is?

 

[bobbarker]

Stoomis
Have you, as Frederico suggested added a reference to your Project for Microsoft ActiveX Data Objects?

 

[Stoomis]

Actually, I am not sure what he meant by that? Could you help me out. I am new to VB. Thanks

 

[johnwm]

Go to the Project Menu and select References

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

 

[fredericofonseca]

Stoomis,

As you really are a newbie to VB I STRONGLY advise you to go through all the VB examples on these forums/faq's that include ADODB.

There are plenty of things you can do and looking at loads of examples will help you.

Also search

http://www.deja.com

will give you plenty of examples, and you really need them.

My example is just a small sample of a recordset and a forward loop, and that will not work well with most DBgrids on the market (as it is defined using a servercursor).
So on this case if you with to use a data bound grid to load the records you would need to change
DBConn.CursorLocation = adUseServer
to
DBConn.CursorLocation = adUseClient

So you see that there are "minor" things that can affect your code, and looking at examples is really the best way to learn.



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com