Solved

Last name with single quote

Posted on 2010-09-21
5
170 Views
Last Modified: 2012-06-22
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?
0
Comment
Question by:lrbrister
  • 3
  • 2
5 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33725385
that means the problem is in the stored procedure, because as you use parameters, those as such cannot be the issue
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33725387
aka the procedure is doing dynamic sql ... please show the relevant code
0
 

Author Comment

by:lrbrister
ID: 33725414
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
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 33725482
thanks.

well, that code cannot raise the error.
di you ahve a trigger on the table you insert to?
0
 

Author Closing Comment

by:lrbrister
ID: 33725558
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

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question