Solved

Last name with single quote

Posted on 2010-09-21
5
173 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
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…

738 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