Using a VB SCript to call Sql View with Parameter

I have a view..VWOpenOrders...

.SELECT     SUM(SorDetail.MOrderQty * SorDetail.MPrice) AS Line, ArCustomer.Name
FROM         SorMaster INNER JOIN
                      ArCustomer ON SorMaster.Customer = ArCustomer.Customer LEFT OUTER JOIN
                      SorDetail ON SorMaster.SalesOrder = SorDetail.SalesOrder
WHERE     (SorMaster.OrderStatus <> '9')
GROUP BY  ArCustomer.Name...

and the Corresponding VB Script is

dim objConnection, objRecordSet

Set objConnection = CreateObject("ADODB.Connection")

Set objRecordSet = CreateObject("ADODB.Recordset")
 

objConnection.Open = "Provider=SQLOLEDB;Data Source=VMXP-SYSSQL08;Initial Catalog=SysproOutdoors;User ID=sa;pwd=100399"

objRecordSet.Open "select Name,Line from VWOpenOrders", objConnection, adOpenStatic, adLockOptimistic


How can i have a parameter field based on entry for ArCustomer.Name
Michael KatzAsked:
Who is Participating?
 
Metehan OzculluConnect With a Mentor SQL Service ManagerCommented:
sorry my fault try this

dim objConnection, objRecordSet, tparameter

Set objConnection = CreateObject("ADODB.Connection")

Set objRecordSet = CreateObject("ADODB.Recordset")

tparameter=""

objConnection.Open = "Provider=SQLOLEDB;Data Source=VMXP-SYSSQL08;Initial Catalog=SysproOutdoors;User ID=sa;pwd=100399
objRecordSet.Open "select Name,Line from VWOpenOrders where ArCustomer.Name='"&tparameter&"'", objConnection, adOpenStatic, adLockOptimistic
0
 
Metehan OzculluSQL Service ManagerCommented:
dim objConnection, objRecordSet, tparameter

Set objConnection = CreateObject("ADODB.Connection")

Set objRecordSet = CreateObject("ADODB.Recordset")

tparameter=""

objConnection.Open = "Provider=SQLOLEDB;Data Source=VMXP-SYSSQL08;Initial Catalog=SysproOutdoors;User ID=sa;pwd=100399"

objRecordSet.Open "select Name,Line from VWOpenOrders", objConnection, adOpenStatic, adLockOptimistic

objRecordSet.Open "select Name,Line from VWOpenOrders where ArCustomer.Name='"&tparameter&"', objConnection, adOpenStatic, adLockOptimistic
0
 
Metehan OzculluSQL Service ManagerCommented:
last line should be

objRecordSet.Open "select Name,Line from VWOpenOrders where ArCustomer.Name='"&tparameter&"'", objConnection, adOpenStatic, adLockOptimistic
0
 
Michael KatzAuthor Commented:
I am getting an error that says  'Operaion is not allowed when the object is open'

Any thoughts??
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.