Solved

Last name with single quote

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

For most people, the WrapPanel seems like a magic when they switch from WinForms to WPF. Most of us will think that the code that is used to write a control like that would be difficult. However, most of the work is done by the WPF engine, and the W…
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…
This video discusses moving either the default database or any database to a new volume.

758 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

19 Experts available now in Live!

Get 1:1 Help Now