Code to Query SQL Server with a parameter value (entered into an Input Box), and have that value als

D

Doctorjones_md

I'm wondering how BEST to accomplish this.

I have a EXCEL workbook which populates data to, and retrieves data from SQL
Server.

I currently have VBA code which effectively sends the data from the EXCEL
worksheet to SQL Server tables -- no problem with this.

I currently have VBA code which effectively retrieves data from SQL Server
and displays that data back into the worksheet -- no problem with this
either.

I'm currently using a Command Button (on-click event) and the code below to
query SQL Server to determine the Max Version Number and add 1 to that
value. This value is then passed to a worksheet cell, and rolled up to SQL
Server along with the rest of the data to be submitted -- no problem with
this either.

Here's my "GenerateVersionNumber" code ...
===============================
Public Sub cmdGenerateVersionNumber_Click()
' Create a connection object.

Dim cnExcel As ADODB.Connection

Set cnExcel = New ADODB.Connection



' Provide the connection string.

Dim strConn As String



'Make Version the active sheet & Clear Data

'ThisWorkbook.Worksheets("Version").Range("A2:A150").Clear



'Use the SQL Server OLE DB Provider.

strConn = "PROVIDER=SQLOLEDB;"



'Connect to the XXXX database on the XXXX Server.

strConn = strConn & "DATA SOURCE=xx.x.xx.xx;INITIAL CATALOG=XXXX;" & _

"User Id=xxxx;" & _

"Password=xxxx"

'Now open the connection.

cnExcel.Open strConn



On Error Resume Next



' Create a recordset object.

Dim ProdID As String

Dim sqlCommand As String

Dim rsExcel As ADODB.Recordset

Set rsExcel = New ADODB.Recordset

ProdID = InputBox("Enter Product ID.")



sqlCommand = "SELECT IsNull (max(Version), 0) + 1 FROM Products WHERE
[ProductID] = '" + ProdID + "'"

With rsExcel

' Assign the Connection object.

.ActiveConnection = cnExcel

' Extract the required records.

.Open sqlCommand

' Copy the records into cell B2 on Sheet15 (Product Tracking Overall)

Sheet15.Range("D2").CopyFromRecordset rsExcel



' Tidy up

.Close

End With



cnExcel.Close

Set rsExcel = Nothing

Set cnExcel = Nothing

End Sub

==============================

Here's what I'm tring to do ....

I don't want the Sale Team to have to enter ProductID more than once.
ProductID is a field that needs to be entered on a worksheet, and it's also
a Query Parameter which I send to SQL Server to generate my One-Up Version
Number. How can I modify my "GenerateVersionNumber" sub (shown above) to
not only query SQL Server with the ProductID parameter entered in the Input
Box, but to also send the value entered in the Input Box to a particular
cell on a selected worksheet?



Thanks in advance for any assistance/advice offered.
 
G

Guest

Try this:

Worksheets("Sheet1").Range("A1").value = ProdID

Replace "Sheet1" w/whatever sheet is appropriate and "A1" with a cell w/in
that ws.

HTH

Cheers,
socratis

Doctorjones_md said:
I'm wondering how BEST to accomplish this.

I have a EXCEL workbook which populates data to, and retrieves data from SQL
Server.

I currently have VBA code which effectively sends the data from the EXCEL
worksheet to SQL Server tables -- no problem with this.

I currently have VBA code which effectively retrieves data from SQL Server
and displays that data back into the worksheet -- no problem with this
either.

I'm currently using a Command Button (on-click event) and the code below to
query SQL Server to determine the Max Version Number and add 1 to that
value. This value is then passed to a worksheet cell, and rolled up to SQL
Server along with the rest of the data to be submitted -- no problem with
this either.

Here's my "GenerateVersionNumber" code ...
===============================
Public Sub cmdGenerateVersionNumber_Click()
' Create a connection object.

Dim cnExcel As ADODB.Connection

Set cnExcel = New ADODB.Connection



' Provide the connection string.

Dim strConn As String



'Make Version the active sheet & Clear Data

'ThisWorkbook.Worksheets("Version").Range("A2:A150").Clear



'Use the SQL Server OLE DB Provider.

strConn = "PROVIDER=SQLOLEDB;"



'Connect to the XXXX database on the XXXX Server.

strConn = strConn & "DATA SOURCE=xx.x.xx.xx;INITIAL CATALOG=XXXX;" & _

"User Id=xxxx;" & _

"Password=xxxx"

'Now open the connection.

cnExcel.Open strConn



On Error Resume Next



' Create a recordset object.

Dim ProdID As String

Dim sqlCommand As String

Dim rsExcel As ADODB.Recordset

Set rsExcel = New ADODB.Recordset

ProdID = InputBox("Enter Product ID.")



sqlCommand = "SELECT IsNull (max(Version), 0) + 1 FROM Products WHERE
[ProductID] = '" + ProdID + "'"

With rsExcel

' Assign the Connection object.

.ActiveConnection = cnExcel

' Extract the required records.

.Open sqlCommand

' Copy the records into cell B2 on Sheet15 (Product Tracking Overall)

Sheet15.Range("D2").CopyFromRecordset rsExcel



' Tidy up

.Close

End With



cnExcel.Close

Set rsExcel = Nothing

Set cnExcel = Nothing

End Sub

==============================

Here's what I'm tring to do ....

I don't want the Sale Team to have to enter ProductID more than once.
ProductID is a field that needs to be entered on a worksheet, and it's also
a Query Parameter which I send to SQL Server to generate my One-Up Version
Number. How can I modify my "GenerateVersionNumber" sub (shown above) to
not only query SQL Server with the ProductID parameter entered in the Input
Box, but to also send the value entered in the Input Box to a particular
cell on a selected worksheet?



Thanks in advance for any assistance/advice offered.
Click to expand...
 
D

Doctorjones_md

Thanks Socratis -- your suggestion worked like a champ! :)
=========================================
Socratis said:
Try this:

Worksheets("Sheet1").Range("A1").value = ProdID

Replace "Sheet1" w/whatever sheet is appropriate and "A1" with a cell w/in
that ws.

HTH

Cheers,
socratis

Doctorjones_md said:
I'm wondering how BEST to accomplish this.

I have a EXCEL workbook which populates data to, and retrieves data from
SQL
Server.

I currently have VBA code which effectively sends the data from the EXCEL
worksheet to SQL Server tables -- no problem with this.

I currently have VBA code which effectively retrieves data from SQL
Server
and displays that data back into the worksheet -- no problem with this
either.

I'm currently using a Command Button (on-click event) and the code below
to
query SQL Server to determine the Max Version Number and add 1 to that
value. This value is then passed to a worksheet cell, and rolled up to
SQL
Server along with the rest of the data to be submitted -- no problem
with
this either.

Here's my "GenerateVersionNumber" code ...
===============================
Public Sub cmdGenerateVersionNumber_Click()
' Create a connection object.

Dim cnExcel As ADODB.Connection

Set cnExcel = New ADODB.Connection



' Provide the connection string.

Dim strConn As String



'Make Version the active sheet & Clear Data

'ThisWorkbook.Worksheets("Version").Range("A2:A150").Clear



'Use the SQL Server OLE DB Provider.

strConn = "PROVIDER=SQLOLEDB;"



'Connect to the XXXX database on the XXXX Server.

strConn = strConn & "DATA SOURCE=xx.x.xx.xx;INITIAL CATALOG=XXXX;" & _

"User Id=xxxx;" & _

"Password=xxxx"

'Now open the connection.

cnExcel.Open strConn



On Error Resume Next



' Create a recordset object.

Dim ProdID As String

Dim sqlCommand As String

Dim rsExcel As ADODB.Recordset

Set rsExcel = New ADODB.Recordset

ProdID = InputBox("Enter Product ID.")



sqlCommand = "SELECT IsNull (max(Version), 0) + 1 FROM Products WHERE
[ProductID] = '" + ProdID + "'"

With rsExcel

' Assign the Connection object.

.ActiveConnection = cnExcel

' Extract the required records.

.Open sqlCommand

' Copy the records into cell B2 on Sheet15 (Product Tracking Overall)

Sheet15.Range("D2").CopyFromRecordset rsExcel



' Tidy up

.Close

End With



cnExcel.Close

Set rsExcel = Nothing

Set cnExcel = Nothing

End Sub

==============================

Here's what I'm tring to do ....

I don't want the Sale Team to have to enter ProductID more than once.
ProductID is a field that needs to be entered on a worksheet, and it's
also
a Query Parameter which I send to SQL Server to generate my One-Up
Version
Number. How can I modify my "GenerateVersionNumber" sub (shown above) to
not only query SQL Server with the ProductID parameter entered in the
Input
Box, but to also send the value entered in the Input Box to a particular
cell on a selected worksheet?



Thanks in advance for any assistance/advice offered.
Click to expand...
Click to expand...
 
G

Guest

hi,
after ProdID = InputBox("Enter Product ID.") you could put something like
this...

Range("A1").Value = ProdID
or
Cells(1, 1).Value = ProdID

regards
FSt1

Doctorjones_md said:
I'm wondering how BEST to accomplish this.

I have a EXCEL workbook which populates data to, and retrieves data from SQL
Server.

I currently have VBA code which effectively sends the data from the EXCEL
worksheet to SQL Server tables -- no problem with this.

I currently have VBA code which effectively retrieves data from SQL Server
and displays that data back into the worksheet -- no problem with this
either.

I'm currently using a Command Button (on-click event) and the code below to
query SQL Server to determine the Max Version Number and add 1 to that
value. This value is then passed to a worksheet cell, and rolled up to SQL
Server along with the rest of the data to be submitted -- no problem with
this either.

Here's my "GenerateVersionNumber" code ...
===============================
Public Sub cmdGenerateVersionNumber_Click()
' Create a connection object.

Dim cnExcel As ADODB.Connection

Set cnExcel = New ADODB.Connection



' Provide the connection string.

Dim strConn As String



'Make Version the active sheet & Clear Data

'ThisWorkbook.Worksheets("Version").Range("A2:A150").Clear



'Use the SQL Server OLE DB Provider.

strConn = "PROVIDER=SQLOLEDB;"



'Connect to the XXXX database on the XXXX Server.

strConn = strConn & "DATA SOURCE=xx.x.xx.xx;INITIAL CATALOG=XXXX;" & _

"User Id=xxxx;" & _

"Password=xxxx"

'Now open the connection.

cnExcel.Open strConn



On Error Resume Next



' Create a recordset object.

Dim ProdID As String

Dim sqlCommand As String

Dim rsExcel As ADODB.Recordset

Set rsExcel = New ADODB.Recordset

ProdID = InputBox("Enter Product ID.")



sqlCommand = "SELECT IsNull (max(Version), 0) + 1 FROM Products WHERE
[ProductID] = '" + ProdID + "'"

With rsExcel

' Assign the Connection object.

.ActiveConnection = cnExcel

' Extract the required records.

.Open sqlCommand

' Copy the records into cell B2 on Sheet15 (Product Tracking Overall)

Sheet15.Range("D2").CopyFromRecordset rsExcel



' Tidy up

.Close

End With



cnExcel.Close

Set rsExcel = Nothing

Set cnExcel = Nothing

End Sub

==============================

Here's what I'm tring to do ....

I don't want the Sale Team to have to enter ProductID more than once.
ProductID is a field that needs to be entered on a worksheet, and it's also
a Query Parameter which I send to SQL Server to generate my One-Up Version
Number. How can I modify my "GenerateVersionNumber" sub (shown above) to
not only query SQL Server with the ProductID parameter entered in the Input
Box, but to also send the value entered in the Input Box to a particular
cell on a selected worksheet?



Thanks in advance for any assistance/advice offered.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Drop-Down List 4
Dynamic List Box using VBA 3
Imprt data from SQL based on cell input 3
VBA Excel - Select a value from one cell in Excel to update a SQL table 1
External query of SQL server 1
SQL Server -- Bulk Insert from Excel to SQL Server 5
VBA to SQL DB with user input and results to sheet 7
Need Help With ADO Recordset and Loop 1

Top