Solved

Last name with single quote

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
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…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

863 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now