来自 SQL Server 2005 Express 表的 VFP 组合框内容

Question

我仍然是 VFP 的新手，我正在寻找有关如何将 SQL Server 表字段拉入 VFP 组合框（或其他对象，如果更好的话）的建议和建议，就像自动完成一样，但来自 sql server 数据库。

我的表中有大约 2 列和 1000 行，组合框应该只显示第二列字段供用户选择，但使用所选的第一列字段记录到另一个表中。我希望你能明白。

预先感谢您的反馈。

Answer 1

我会稍微不同地处理它，因为你想要两列......一列显示，一列显示实际数据。组合框可以直接绑定到表或游标（游标只不过是一个临时表，关闭时会自动删除）。

在组合框的 INIT() 中，我将对 SQL 数据库运行查询，但只需获取这两列...

* -- 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 

Answer 2

好吧，下面是我如何以编程方式执行此操作，假设表单上有一个名为“cboSQL”的组合框，并且此代码位于表单 Init() 方法中（这只执行组合中的一列，但请检查 AddItem() 下的 VFP 帮助） :

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