Solved

Last name with single quote

Posted on 2010-09-21
5
171 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 143

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 143

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 143

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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
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…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

831 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