How to write sql queries which take input from cell of an excel sheet

Question

I want to write a sql query which take an input from cell of an excel sheet.

For example if there are 3 columns like EmpID,EmpName,Salary,Address in the database and I would like to connect with the sql server database and update the database using a button in the excel sheet.

I wrote a code like this

Dim rst As ADODB.Recordset
Dim Cnxn As ADODB.Connection
Dim strCnxn As String
Dim strSQL As String

' Open connection
Set Cnxn = New ADODB.Connection
strCnxn = "Provider=SQLOLEDB;" & _
          "Data Source=ABC\SQLEXPRESS;" & _
          "Initial Catalog=Trail;" & _
          "Integrated Security=SSPI;" & _
          "Application Name=MyExcelFile"

Cnxn.Open strCnxn
Set rst = New ADODB.Recordset
Dim Ename As Variant
Ename = Worksheets("DBSheet").Cells(2, 2).Value
Dim Eno As Variant
Eno = Int(Worksheets("DBSheet").Cells(2, 1).Value)
strSQL = "UPDATE Employee SET EmpName='Micro' WHERE EmpID=1"
rst.Open strSQL, Cnxn, adOpenDynamic, adLockOptimistic, adCmdText
Cnxn.Close
Set rst = Nothing
Set Cnxn = Nothing

Here in the above code I Wrote a Update query like SET EmpName='Micro' WHERE EmpID=1, here i mentioned the EmpName and EmpID manually rather than that I would like to populate the update query with the cell values of an excel sheet like

strSQL = "UPDATE Employee SET EmpName=Worksheets("AB').Cells(2,2).Value WHERE EmpID=Worksheets("AB').Cells(2,1).Value"

But this is not working because , the EmpName and EmpID values are coming in the format of ""(double quotes)to the query but sqlquery would not accept any thing in that format in order to execute it should in the format of ''(single quotes).

So If any one know how to populate cell values of an excel sheet to the sql query please suggest me how to do that.

user2084375 · Accepted Answer · 2013-03-19 11:20:24Z

2

Use This Code

strSQl="UPDATE Employee SET EmpName='" & sheets("AB").Cells(2,2).Value & "' WHERE EmpID='" & sheets("AB").Cells(2,1).Value & "'"

answered Mar 19, 2013 at 11:20

user2084375

Sign up to request clarification or add additional context in comments.

Comments

user1644564 · Accepted Answer · 2013-03-20 14:16:07Z

1

Use Parameters:

1) create a command object and initialise it.

Dim cmd as adodb.command

set cmd = new adodb.command

2) change your SQL:

strSQL = "UPDATE Employee SET EmpName= ? WHERE EmpID = ? "

3) create your parameters from your command object using the .createParameter function in the command object.

dim parId as adodb.parameter
dim parEmp as adodb.parameter

set parId = new adodb.parameter
set parEmp = new adodb.parameter

set parId = cmd.createParameter(...)
set parEmp = cmd.createParameter(...)

4) add the parameter to the parameters collection in the command object.

cmd.parameters.append(parId)
cmd.parameters.append(parEmp)

Note: add the parameters in the same order they are in the SQL statement.

5) open your recordset

rst.open cmd

6) loop and stuff

7) close and de-initialise everything.

answered Mar 20, 2013 at 14:16

user1644564

3953 silver badges11 bronze badges

1 Comment

user1644564 Over a year ago

Ooops not thinking there... Obviously the SQL should be a SELECT for opening a recordset. But if you just want to run an update then do that with the Exec in command object. Sorry.

Collectives™ on Stack Overflow

How to write sql queries which take input from cell of an excel sheet

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related