Record from database into a listbox 1

[Jomercat]

Hi all,

It has been a while since I do not visit this forum, as I was programing in VB 6 most of the time. But now I want to continue learning Fox Pro. What I want to do is to connect to a SQL server retrieve some records and add one of the fields to a list box. Could anybody help me?

Any help would be appreciated.

Jose.

 

[Mike Lewis]

Jose,

Welcome back.

I suggest you start by reading the VFP help file topics on remote access, especially remote views. Once you know how to create a view, you can use it more or less like a table, including using it to populate a listbox.

Once you've got started, come back with any specific questions. When you do, it would be helpful to know which back end server you are working with.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[alvechurchdata]

add one of the fields to a list box

Just a note that you don't have to use AddItem to populate a listbox line by line like in VB. Set the RowSourceType property of the listbox and the listbox will populate itself from a table, an SQL statement, an array, or from several other options.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[darrellblackhawk]

Here's one of the many, many, ways of accomplishing this
using a DSNless connection to SQLServer:

local o
o = createobject("PopListFromSQLServer")
o.show(1)

define class PopListFromSQLServer as form


  height = 193
  width = 337
  docreate = .t.
  autocenter = .t.
  caption = "Populate Listbox from SQL Server"
  name = "frmPopListFromSQLServer"

  dimension aEmployees[1] && Rowsource for the listbox


  add object lstemployees as listbox with ;
    rowsourcetype = 5, ;
    rowsource = "thisform.aEmployees", ;
    height = 168, ;
    left = 24, ;
    top = 24, ;
    width = 192, ;
    integralheight = .t., ;
    name = "lstEmployees"


  add object label1 as label with ;
    autosize = .t., ;
    backstyle = 0, ;
    caption = "Employees", ;
    height = 17, ;
    left = 24, ;
    top = 5, ;
    width = 64, ;
    name = "lblEmployees"


  procedure load
    this.GetEmployeeNames()
  endproc

  protected procedure GetEmployeeNames
    * It's much better to create a connection pool to SQL Server
    * at some higher level and then to request a connection from
    * the pool. Building and tearing down connections on the fly
    * is verrrrry slow....

    * There are so many ways of accomplishing this,
    * it'd be ridiculous to try and cover them here :-)

    #define adOpenForwardOnly 0
    #define adLockReadOnly 1
    #define adCmdText 1

    local oConnection, cConnectionString, oEmployees, cSelectStatement
    oConnection = createobject("adodb.connection")

    * Using integrated security
    cConnectionString = ;
      "driver={SQL Server};server=[red]YOUR_SERVER_NAME_HERE[/red];database=Pubs"

    oConnection.ConnectionTimeout = 10 && Bump up for internet or slow intranet connections

    oConnection.open(cConnectionString)
    * Check connection state here <-

    oEmployees = createobject("ADODB.Recordset")

    cSelectStatement = "select fname, minit, lname from employee order by lname, fname"

    oEmployees.CursorType = adOpenForwardOnly
    oEmployees.LockType =  adLockReadOnly

    oEmployees.open(cSelectStatement, oConnection, , , adCmdText)
    * Check query execution state here <-

    local i, cFName, cMName, cLName

    i = 0

    do while !oEmployees.eof

      i = i + 1

      dime this.aEmployees[i]

      cFName = allt(oEmployees.fields[0].value)
      cMName = allt(oEmployees.fields[1].value)
      cLName = allt(oEmployees.fields[2].value)

      this.aEmployees[i] = ;
        cFName + " " + ;
        iif(!empty(cMName), cMName + ". ", "") + ;
        cLName

      oEmployees.MoveNext()

    enddo

    oEmployees.close
    oConnection.close
    oEmployees  = null
    oConnection = null

  endproc

enddefine

 

[letsgotovegas]

Hi Darrell

Just curious. Would o.Employees!FieldName of worked also??

Could you of used the "!" bang syntax for recordset!Fieldname...



Kind Regards
John

To much of a good thing - is a good thing!

 

[Jomercat]

Hi all,

Thanks a lot for the reply to this question. I will try what was suggested and get back to you.

Again, thank you very much!!!!

Jose.

 

[darrellblackhawk]

letsgotovegas in answer to your question: NO.

This is FoxPro code, so it doesn't know about the VB syntax

Darrell

 

[Jomercat]

Hi all,

Thanks for the help provoded!!!

darrellblackhawk- Thanks for the code you provided. It really helped me.

This is the code I modified to get the list populated and it is working fine.

do While !oCustomers.eof

Thisform.list1.AddItem(oCustomers.Fields("custcar").Value)

oCustomers.MoveNext()

enddo

Just a comment!

alvechurchdata mentioned that I do not have to use the AddItem to populate the listbox. Is it that I cannot use it or I do not have to use it?

Again, the code I provided above uses the Additem and it is working fine for me.

Thans much!!!

Jose.

 

[alvechurchdata]

Is it that I cannot use it or I do not have to use it?

Fox has an AddItem method but it's usually easier to use the RowSource property if your list items already exist in a table or view.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[Olaf Doschke]

Hi Jose,

if you're reading in MS SQL Server data as an ADODB-Recordset, as the code of darrellblackhawk does, you can't simply use the Recordsource, because you need a foxpro cursor/view or table to do this.

It's possible to read data from a remote database as a foxpro view or cursor with a remote view or with SQLEXEC() and the like.

But if it works, don't fix it.

Bye, Olaf.

 

[Jomercat]

Thanks OlafDoschke!

Since I am a beginer, I would like to know how to create the remote view programatically and add the records to the list box, combo box, etc. in addition of knowing how to use the ADODB.

Thanks all for the help!!!!!

Jose.