how to link combo box(drop down) and SQL(database values)?

[Kandukuri_raghavendra]

how to connect to SQL and show the result in the drop down?

 Dim myHWSETConn,myHWSETCommand,HWSETRS
  HWsetSelection = val(HWsetSelection)
 Const DB_CONNECT_STRING1 = "Provider=SQLOLEDB.1;Data Source=FWATRGSQLVCL2;Initial Catalog=Cal1;user id ='Caluser';password='caluser'"
  Set myHWSETConn = CreateObject("ADODB.Connection")
  Set myHWSETCommand = CreateObject("ADODB.Command" )
  myHWSETConn.Open DB_CONNECT_STRING1
  Set myHWSETCommand.ActiveConnection = myHWSETConn
  myHWSETCommand.CommandText = "SELECT [hw_fullname] FROM [Cal].[dbo].[Req_Simple] where Fwid= "&HWsetSelection&" "
   Set HWSETRS = myHWSETCommand.Execute

I have this code which fetches the values from SQL but i am having trouble putting them on to dropdown, i tried

 call NewHwset.Items.Add(HWSETRS)

but it says, type mismatch "NewHwset.Items.Add"

Any help is highly appreciated,
Thank you.

[PahlM]

Kandukuri,

Are you able to see the values at all from the SQL database? I mean, outside of trying to put them in a drop down, can we read them at all?

Regards,

Michael Pahl

Technical Support  Engineer

National Instruments

[AndreasK]

dim HWSETRS : Set HWSETRS = myHWSETCommand.Execute
if NOT (HWSETRS.BOF or HWSETRS.EOF) then
  dim columnValues : columnValues = HWSETRS.GetRows(-1, 0, 0)
  dim columnValue : for each columnValue in columnValues
    call NewHwset.Items.Add(columnValue)
  Next
end if

something like this might work.

For further information you need to check the ADO recordset documentation.

https://docs.microsoft.com/de-de/sql/ado/reference/ado-api/getrows-method-ado?view=sql-server-2017

https://www.w3schools.com/asp/ado_ref_recordset.asp

[Kandukuri_raghavendra]

yes@Michael Pahl

HWSETRS 

this object is holding the result, i tried showing them in the msg box, it should actually return 2 records but its returning one, so the connection does work, may be i need to make few modifications for it to show both the records. 

[Kandukuri_raghavendra]

Hi,
what does 'column value' doing here?

[Kandukuri_raghavendra]

@ AndreasK

its throwing the error saying"argument not optional:NewHwset.Items.Add" 

[AndreasK]

I am not sure which Add method you are using.

Please check the help for it.

If it is an ItemList

  dim nrVal : nrVal = 1
  dim columnValue : for each columnValue in columnValues
    call NewHwset.Items.Add(columnValue, nrVal)
    nrVal = nrVal + 1
  Next

might work.