Stored procedure reads variable incorrectly

[schick]

I call my 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 an apostrophe, the record is read incorrectly at that point. I'm trying to get vb to read the whole value, such as "O'malley" or "O'leary"

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]

Should I call my stored procedure differently ??? 
Please help!

 

[Niv3k]

Yes. When doing the connection.execute like that, you should wrap varchars with ' on both sides, Like so:

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

Then to handle a name like O'Leary, use a double apostrophe: '' NOT ". So the entire string would read like this:
SP_getMatch 12345, 'O''Leary', 54321, 'Mike'

Kevin

 

[schick]

That worked perfectly! Thank you!