problem on passing value
I have a problem regarding passing parameter from excel spreadsheet to a
backend sql database to refresh data. For example, I already add a text box in an excel spreadsheet to let me pass in whatever production year I needed e.g. 1998, 1999, 2000, 2001, ....... etc. as a parameter. but without success. Can anyone suggest a code from excel text box sending any of the year parameter to the database to refresh the data in excel according to the year entered by the user. Sample of my code is shown below. ' Create RecordSet Set Recordset = New ADODB.Recordset With Recordset ' Filter Src = "SELECT * FROM STOCKMASTER WHERE PRODUCTION_YEAR = 2002;" .Open Source:=Src, ActiveConnection:=Connect ' Write the field names For Col = 0 To Recordset.Fields.Count - 1 Range("A1").Offset(0, Col).Value = Recordset.Fields(Col).Name Next ' Write the recordset Range("A1").Offset(1, 0).CopyFromRecordset Recordset End With Set Recordset = Nothing Connect.Close Set Connect = Nothing Can someone help me to solve this problem and any solution provided will be highly appreciated. |
problem on passing value
If I understand you correctly... Where you currently have a hard coded year
in your SQL statement you want to use the value of a cell... Src = "SELECT * FROM STOCKMASTER WHERE PRODUCTION_YEAR = " & sheets("Sheet1").range("A1") & ";" HTH "alvin" wrote: I have a problem regarding passing parameter from excel spreadsheet to a backend sql database to refresh data. For example, I already add a text box in an excel spreadsheet to let me pass in whatever production year I needed e.g. 1998, 1999, 2000, 2001, ....... etc. as a parameter. but without success. Can anyone suggest a code from excel text box sending any of the year parameter to the database to refresh the data in excel according to the year entered by the user. Sample of my code is shown below. ' Create RecordSet Set Recordset = New ADODB.Recordset With Recordset ' Filter Src = "SELECT * FROM STOCKMASTER WHERE PRODUCTION_YEAR = 2002;" .Open Source:=Src, ActiveConnection:=Connect ' Write the field names For Col = 0 To Recordset.Fields.Count - 1 Range("A1").Offset(0, Col).Value = Recordset.Fields(Col).Name Next ' Write the recordset Range("A1").Offset(1, 0).CopyFromRecordset Recordset End With Set Recordset = Nothing Connect.Close Set Connect = Nothing Can someone help me to solve this problem and any solution provided will be highly appreciated. |
All times are GMT +1. The time now is 11:58 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com