Like Operator to find fields with spaces (ie. "van Damne") 1

[schick]

I'm calling a stored proc in vb like this:

objConn.Execute ("SP_getMatch " & rs2!brok_no & ", " & rs2!lastname & ", " & rs2!contact_no & ", " & rs2!Firstname & "")
[\code]
When one of the fields being passed contains a period or a space, the record is truncated at that point. I'm trying to get vb to ignore spaces and periods.

Here is my stored proc:
[code]
CREATE PROCEDURE sp_GetMatch 
(@cBrok_no int,@cLastname varchar(15),@cContact_no int,@cFirstName varchar(15)) as

    begin
       update office
       set cust_no = @cContact_no
       where  Brok_no = @cbrok_no and 		
	  (contact like @cfirstname + " " + @clastname  
	   or Contact like @cLastname + "%"
	   or contact like @cfirstname + '%' + @clastname
	  ) 
    end
return (0)	
[\code]

 

[balves]

You should be putting single quotes around the string values.

objConn.Execute ("SP_getMatch " & rs2!brok_no & ", '" & rs2!lastname & '", " & rs2!contact_no & ", '" & rs2!Firstname & "'&quot

 

[schick]

VB didn't accept the syntax given.
When I step through vb it shows the correct values...
It seems as though it's in my stored procedure.

 

[rac2]

I believe that stored procedures require parentheses around the argument list as well as single-quotes around strings -

objConn.Execute ("SP_getMatch(" & rs2!brok_no & ", '" & rs2!lastname & '", " & rs2!contact_no & ", '" & rs2!Firstname & "')&quot

 

[schick]

That helped 85% of my problem!
Now vb is giving an error when the variable contains an apostrophe.

(IE. O'Connell)

 

[balves]

You'll need to call the VB function Replace() to replace a single quote with 2 single quotes in your name strings that you are passing to the stored proc.