BOIT
asked on
Why is string passed from Access VBA to SQL Server Stored Procedure losing the chr(13) from vbcrlf
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!)
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
ASKER
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.
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.
Sure, if you could post a link to it here then I can find it easier :o)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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 &