Executing stored procedure in access vba when a space in in the search string

[clukem]

Hello

I have the following code (see below). PhoneSearch is a stored procedure on the SQL server. I'm calling the procedure from Access vba and storing the results in an access form



Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset


Set cnn = New ADODB.Connection


cnn.ConnectionString = "Driver={SQL Server};Server=LAPTOP-A35N8041;Database=PhoneDB;Trusted_Connection=yes"




cnn.Open


Set rs = New ADODB.Recordset
Set rs = cnn.Execute("Exec dbo.PhoneSearch " & [DeviceNumber])



The code works well until there is a space in DeviceNumber. So, ATN102 works fine, but ATN 102 causes an error. Is there a way to get this working with a space between ATN and 102? I have quite a lot of other data in the table like this. It would take too long to remove the spaces. The procedure when executed in SQL server works fine


Regards




Chris

[CJ_London]

Try

cnn.Execute("Exec dbo.PhoneSearch ‘“ & [DeviceNumber] & “‘“)

I’m on my phone so using smart quotes - change them if you copy/paste

[Minty]

Any string literal should be passed in with single quotes as a delimeter in SQL server.

I'm quite surprised it has worked at all without them previously.

[clukem]

Try

cnn.Execute("Exec dbo.PhoneSearch ‘“ & [DeviceNumber] & “‘“)

I’m on my phone so using smart quotes - change them if you copy/paste

That worked well thanks. Wonder why it didn't have problems with letters, but did with spaces?

[Minty]

That worked well thanks. Wonder why it didn't have problems with letters, but did with spaces?

Without the delimiters it wouldn't know if AB CD12 was one or two parameters.

I'm still really puzzled how it accepted AB1234 instead of the more correct 'AB1324'

[clukem]

Without the delimiters it wouldn't know if AB CD12 was one or two parameters.

I'm still really puzzled how it accepted AB1234 instead of the more correct 'AB1324'

I assumed it was because it was enclosed in square brackets. Not sure. I'm still quite new to this