I am trying to pass the results of what is selected in a list box to sql through an ado statement, when i select one result i can pass the value with no issue, when i select multiple item i get a run time error incorrect syntax near ';' i am guessing it is passing the data as a csv to sql and the where statement does not like that as criteria but i am not sure how to handle that either in vba or on the sql side thanks so much
here is my vba code from access
Private Sub Command49_Click()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strItems As String
Dim intItem As Integer
For intItem = 0 To List45.ListCount - 1
If List45.Selected(intItem) Then
strItems = strItems & List45.Column(0, intItem) & ";"
End If
Next intItem
Dim SQLServer As String
Dim Catalog As String
Dim SQLString As String
Dim SQLInvNo As String
Dim refnumber As String
refnumber = strItems
SQLServer = "Gretzky\RNYSQLAccess"
Catalog = "14dec2012"
SQLString = "Exec sp_test " & refnumber & " "
'Create a new ADO Connection object
Set cn = New ADODB.Connection
'Use the Access 10 and SQL Server OLEDB providers to
'open the Connection
'You will need to replace MySQLServer with the name
'of a valid SQL Server
With cn
.Provider = "Microsoft.Access.OLEDB.10.0"
.Properties("Data Provider").Value = "SQLOLEDB"
.Properties("Data Source").Value = SQLServer
.Properties("Integrated Security").Value = "SSPI"
.Properties("Initial Catalog").Value = Catalog
.Open
End With
'Create an instance of the ADO Recordset class, and
'set its properties
Set rs = New ADODB.Recordset
With rs
Set .ActiveConnection = cn
.Source = SQLString
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.Open
End With
'Set the form's Recordset property to the ADO recordset
' Set Me.Recordset = rs
'Set rs = Nothing
'Set cn = Nothing
here is the simple sql sp
USE [14dec2012]
GO
/****** Object: StoredProcedure [dbo].[sp_DeltaClear] Script Date: 09/11/2013 09:35:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter procedure [dbo].[sp_test]
@refnumber int
As
Begin
update inventory
set no_bgs_in = 100
where [reff number] = @refnumber
end
here is my vba code from access
Private Sub Command49_Click()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strItems As String
Dim intItem As Integer
For intItem = 0 To List45.ListCount - 1
If List45.Selected(intItem) Then
strItems = strItems & List45.Column(0, intItem) & ";"
End If
Next intItem
Dim SQLServer As String
Dim Catalog As String
Dim SQLString As String
Dim SQLInvNo As String
Dim refnumber As String
refnumber = strItems
SQLServer = "Gretzky\RNYSQLAccess"
Catalog = "14dec2012"
SQLString = "Exec sp_test " & refnumber & " "
'Create a new ADO Connection object
Set cn = New ADODB.Connection
'Use the Access 10 and SQL Server OLEDB providers to
'open the Connection
'You will need to replace MySQLServer with the name
'of a valid SQL Server
With cn
.Provider = "Microsoft.Access.OLEDB.10.0"
.Properties("Data Provider").Value = "SQLOLEDB"
.Properties("Data Source").Value = SQLServer
.Properties("Integrated Security").Value = "SSPI"
.Properties("Initial Catalog").Value = Catalog
.Open
End With
'Create an instance of the ADO Recordset class, and
'set its properties
Set rs = New ADODB.Recordset
With rs
Set .ActiveConnection = cn
.Source = SQLString
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.Open
End With
'Set the form's Recordset property to the ADO recordset
' Set Me.Recordset = rs
'Set rs = Nothing
'Set cn = Nothing
here is the simple sql sp
USE [14dec2012]
GO
/****** Object: StoredProcedure [dbo].[sp_DeltaClear] Script Date: 09/11/2013 09:35:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter procedure [dbo].[sp_test]
@refnumber int
As
Begin
update inventory
set no_bgs_in = 100
where [reff number] = @refnumber
end