Last name with single quote

I have a vb page that passes in  a variable to my stored procedure...

VB  
blah...blah..blah...
oCmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@lastName", System.Data.SqlDbType.NVarChar, 50)).Value = m_LastName  (defined as private string)
The stored procedure is recieving it as
@lastName nvarchar(50),

However...it is bombing when the last name contains a single quote....like "O'Conner"

Any ideas?
Larry Bristersr. DeveloperAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
thanks.

well, that code cannot raise the error.
di you ahve a trigger on the table you insert to?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
that means the problem is in the stored procedure, because as you use parameters, those as such cannot be the issue
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
aka the procedure is doing dynamic sql ... please show the relevant code
0
 
Larry Bristersr. DeveloperAuthor Commented:
angelll
Attached is the code

ALTER PROCEDURE [dbo].[sp_structured_DealInsert] (@leadGuid varchar(36),
			@firstName nvarchar(50), 
			@lastName nvarchar(50), 
			@middleName nvarchar(50), 
			@street nvarchar(50), 
			@city nvarchar(50), 
			@county nvarchar(50), 
			@state nchar(2), 
			@zipCode nvarchar(10), 
			@phone nvarchar(20), 
			@email nvarchar(50), 
			@DOB datetime,
			@SSN nvarchar(50),
			@salesPerson nvarchar(50),
			@lifeContingent bit,
			@annuitantAmt money,
			@annuitantRate float,
			@fees money,
			@fedRate float,
			@issuerId int, 
			@brokerId int,
			@aggregateAmt money 
		)

AS
Begin
	Declare @id int
	Declare @contractid varchar(36)
	Set @contractid = NewID()
End

Begin
INSERT INTO [CRMPROD_01].[dbo].[p_deals]
           ([leadID]
		  ,[firstName]
          ,[lastName]
          ,[middleName]
          ,[street]
          ,[city]
		  ,[county]
          ,[state]
          ,[zipCode]
          ,[phone]
          ,[email]
          ,[DOB]
          ,[SSN]
          ,[rep]
          ,[lifeContingent]
          ,[AnnuitantAmt]
          ,AnnuitantAmtAdjusted
          ,NumDebitedPmts
          ,AnnuitantAmtDebit
          ,AnnuitantNet
		  ,[AnnuitantContr]
          ,[AnnuitantRate]
          ,[fees]
          ,[fedRate]
          ,[issuerId]
          ,[brokerId]
		  ,[funderID]
          ,[funderAmtNet]
		  ,[funderAmtDiscounted]
		  ,[funderRateNet]
          ,[lifeinsurerid]
		  ,[lifeinsurancestatusid]
          ,[ocid]
		  ,[status]
		  ,[incidentstate]
		  ,[successrating]
		  ,[aggregateAmt])
     VALUES (
           @leadGuid,
		   @firstName, 
           @lastName,	
           @middleName,
           @street,
           @city,
		   @county,
           @state,
           @zipCode,
           @phone,
           @email,
           @DOB,
           @SSN,
           @salesPerson,
           @lifeContingent,
           @annuitantAmt,
           @annuitantAmt,		--AnnuitantAmtAdjusted
		   0,					--NumDebitedPmts
		   0,					--AnnuitantAmtDebit
		   @annuitantAmt,		--AnnuitantNet
		   @annuitantAmt,
           @annuitantRate,
           @fees,
           @fedRate,
           @issuerId,
           @brokerId,
		   10,
           0,
		   0,
		   0,
           5,
		   9,
           34,
		   'App Rcvd',
		   'XX',
		   '-1',
			@aggregateAmt)
--Grab new identity for other events
Set @id = Scope_Identity()
End

Open in new window

0
 
Larry Bristersr. DeveloperAuthor Commented:
angelIII:

That's exactly what I thought...as a matter of dfact I set these things up based on input from you over 7 years ago.

Based on your answer, When I went and questioned the user again...turns out it was a completly different page.  

Thanks for the verification
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.