Solved

Last name with single quote

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
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…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

756 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