VFP ComboBox Content from SQL Server 2005 Express table

I'm still newbie in VFP, I'm looking for advices and suggestio开发者_JS百科ns on how to pull SQL Server table fields into VFP combobox(or other objects as well, if its better), just like auto-complete but from sql server database.

I have about 2 columns and 1000 rows inside the table, the combobox should only show the second columns field for user to choose but use the chosen first column field to be recorded to another table. I hope you get the idea.

Thanks in advance for your feedback.

I would handle it a little differently since you want BOTH columns.. one shown, and one for the actual data. Comboboxes can be directly bound to a table or cursor (cursor is nothing more than a temp table that automatically gets erased when closed).

In the INIT() of your combobox, I would run your query to your SQL database, but just grab those two columns...

* -- Try to connect and get a connection handle.
lnHandle = Sqlstringconnect( YourConnectionString )
if lnHandle < 1
  */ Return, get out, unable to get handle to SQL server
  messagebox( "Unable to connect to SQL" )
  return
end 

lcSQLCmd = "Select ShowThisColumn, ButUseThisColumn from YourDatabase.dbo.YourTable"
lnSQLResult = SQLExec( lnHandle, lcSQLCmd, "C_ChoicesFromSQL" )
sqldisconnect( lnHandle )
if lnSQLResult < 1
  messagebox( "Unable to retrieve data" )
  return
endif

*/ Ok, we have the data, now the binding.  VFP Can set the row source directly
*/ to the ALIAS ("C_ChoicesFromSQL") from the SQL query directly, no need to scan
*/ and add items.  Just tell it both columns.
This.ColumnCount = 2   && You will bind BOTH columns to the combobox
This.BoundColumn = 2   && You want the data from the SECOND column bound for storage
This.BoundTo     = .T.  && Yes, bind to whatever the Control Source of the combobox

*/ Here's the trick.  Set the column widths = whatever you have visible on the form
*/ for the first column, and  the second column has a visible width of 0
This.ColumnWidths = alltrim( str( This.Width )) + ",0"

*/ Now, the row source... 
This.RowSource = "C_ChoicesFromSQL.ShowThisColumn, ButUseThisColumn"
This.RowSourceType = 2  && bound to alias

*/ Fixed dropdown LIST, dont allow others to type in their own values
*/ but only choose from the data available in your source.
This.Style = 2 

Well, here's how I'd do it programmatically assuming a combobox called 'cboSQL' on the form, and this code in the form Init() method (this only does one column in the combo but check the VFP help under AddItem() ):

Local lnHandle, lnResult As Integer
Local lcConnstring, lcCommand As String
Local llReturn As Boolean

    * -- Do the default behaviour.
    llReturn = DoDefault()

    If llReturn

        * -- Try to connect to a local SQL Express 2008 instance.
        lcServername = ".\SQLEXPRESS"
        lcDbname = "umbraco"
        lcConnstring = [server=]+ lcServername+ [;driver={SQL Server};database=]+ lcDbname
        lcConnstring = lcConnstring + [;DSN='';Trusted_Connection=Yes]

        * -- Try to connect and get a connection handle.
        lnHandle = Sqlstringconnect(lcConnstring)

        * -- Got a connection ?
        If lnHandle > 0

            * -- Run a query, put the results in a VFP cursor called 'results'.
            lcCommand = "select top 1000 logComment from [umbraco].[dbo].[umbracoLog]"
            lnResult = SQLExec(lnHandle, lcCommand, "results")

            If lnResult = -1
                Messagebox("Error running SELECT.", 48, "Error!")
            Else

                * -- Got some rows, populate combobox.
                Select results
                Scan

                    * -- Transform() just stringifies the column.
                    Thisform.cboSql.AddItem(Transform(results.logComment))

                Endscan

            Endif

            * -- Tidy up.
            Use In Select("results")
            SQLDisconnect(lnHandle)

        Else

            Messagebox("Couldn't connect to server.", 48, "Error!")
            llReturn =.F.

        Endif

    Endif

Return llReturn