Solved

Why is string passed from Access VBA to SQL Server Stored Procedure losing the chr(13) from vbcrlf

Posted on 2008-06-10
4
434 Views
Last Modified: 2012-05-05
I have simple stored procedure to create a new customer record if one with a matching name and postcode does not automatically exist. The procedure is called from Microsoft Access using VBA. The address is a string with the lines seperated by vbcrlf.

When the procedure is called from Access the address ends up with lines seperated only by char(10). The char(13) part of the vbcrlf is lost somehow. If I test the procedure from SQL Server management studio it works fine.

Any ideas on how I can get the full newline control sequence into the database (I could add a few lines to insert a char(13) in front of any orphaned char(10) but that is not elegant!)
ALTER PROCEDURE [dbo].[CUSTOMERINSERT] @NAME VARCHAR(50),@ADDRESS VARCHAR(2000),@POSTCODE VARCHAR(50),@ID INT OUTPUT

AS

BEGIN

	SELECT @ID=ID FROM CUSTOMER WHERE [NAME]=@NAME AND POSTCODE=@POSTCODE

	IF @ID IS NULL

		BEGIN

			INSERT INTO CUSTOMER ([NAME],ADDRESS,POSTCODE) VALUES (@NAME,@ADDRESS,@POSTCODE)

			SET @ID=SCOPE_IDENTITY()

		END

	IF @ID IS NULL

		SET @ID=0

END

Open in new window

0
Comment
Question by:BOIT
  • 2
  • 2
4 Comments
 
LVL 7

Expert Comment

by:Chrisedebo
ID: 21752331
I believe that appearances can be deceptive.

when you use vbcrlf to concatenate strings, and then pass them to SQL via a stored procedure, I think it will just use SQL's new line character (char 10), this will however appear to be correct when testing the procdure in the management studio.

Try using the character codes when calling the stored proc

ie char(13) & char(10) &
instead of vbcrlf & 
0
 

Author Comment

by:BOIT
ID: 21757785
I tried concatenating the string using chr(13) & chr(10) instead of vbcrlf but it makes no difference.

I have just realised that I have made a mistake in outlining this question. The actual technology I am using for this bit of the project is a web service written in vb in asp.net. Therefore the stored procedure is being called from iis on the target host.

As this changes the title of the question and quite possibly the solution I am going to repost the question and request a close on this one.

Thank you for your comment Chrisedebo and I will welcome your input on the reposted question if possible.

0
 
LVL 7

Expert Comment

by:Chrisedebo
ID: 21758231
Sure, if you could post a link to it here then I can find it easier :o)
0
 

Accepted Solution

by:
BOIT earned 0 total points
ID: 21758339
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

867 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

12 Experts available now in Live!

Get 1:1 Help Now