In stored procedure i am getting error

Question

0.00/5 (No votes)

See more:

Hi In stored procedure i pass parameter as nvarchar,for testing purpose i add Print,below this i get error in SP

-- Exec SPDelFile N'รู้จักกับ Dreamweaver.docx', N'รู้จักกับw',N'รู้จักกับ Dreamweaver.docx',N'รู้จักกับ\รู้จักกับ\รู้จักกับ Dreamweaver.docx',N'รู้จักกับw\รู้จักกับ\รู้จักกับ',N'รู้จักกับ\รู้จักกับ\รู้จักกับ Dreamweaver.docx',N'รู้จักกับw\รู้จักกับ\รู้จักกับ\รู้จักกับ Dreamweaver.docx',N'รู้จักกับw'

After execut SP,below i get Print result

C#

Delete from [รู้จักกับw] where [File Name] = รู้จักกับ Dreamweaver.docx and [File Path] = รู้จักกับ\รู้จักกับ\รู้จักกับ Dreamweaver.docx

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'Dreamweaver'.

This is my SP and in same window for testing i execute using Exec

SQL

USE [Employee]
GO
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
ALTER PROCEDURE [dbo].[SPDelFile]
 	 @TblName nvarchar(256),
	 @FileName1 nvarchar(256),
	 @FilePath1 nvarchar(256),
	 @TblNameIndx nvarchar(256)
AS
 	   Declare @DelTblPos nvarchar(max); 
		SET @DelTblPos = 'Delete from ['+ @TblName +'] where [File Name] = '+ @FileName1 +' and [File Path] = ' + @FilePath1 ; 
        Print @DelTblPos;
		EXEC (@DelTblPos);  
		
		Declare @DelIndexing nvarchar(max); 
		SET @DelIndexing = 'Delete from [FTIndexing] where [Filename] = '+ @FileName1 +' and [Filepath] = ' + @FilePath1 + ' and [Database] = ' + @TblNameIndx ; 
	    Print @DelIndexing;
	    EXEC (@DelIndexing);  
			
	RETURN

		-- Exec SPDelFile N'รู้จักกับ Dreamweaver.docx', N'รู้จักกับw',N'รู้จักกับ Dreamweaver.docx',N'รู้จักกับ\รู้จักกับ\รู้จักกับ Dreamweaver.docx',N'รู้จักกับw\รู้จักกับ\รู้จักกับ',N'รู้จักกับ\รู้จักกับ\รู้จักกับ Dreamweaver.docx',N'รู้จักกับw\รู้จักกับ\รู้จักกับ\รู้จักกับ Dreamweaver.docx',N'รู้จักกับw'  - > this is problem

-- Exec SPDelFile N'รู้จักกับ กับ.docx', N'รู้จักกับw',N'รู้จักกับ กับ.docx',N'รู้จักกับ\รู้จักกับ\รู้จักกับ กับ.docx',N'รู้จักกับw\รู้จักกับ\รู้จักกับ',N'รู้จักกับ\รู้จักกับ\รู้จักกับ กับ.docx',N'รู้จักกับw\รู้จักกับ\รู้จักกับ\รู้จักกับ กับ.docx',N'รู้จักกับw'  - > this is problem  - > this is Working

All parameters declare as nvarchar.

Pls reply me how to solve this.
The above values are Thai language,if all are in English then no problem

Note:if file name like this รู้จักกับ Dreamweaver.docx --- > Problem
if like this Dreamweaver.docx--> no Problem

Regards
Aravind

What I have tried:

Note:if file name like this รู้จักกับ Dreamweaver.docx --- > Problem
if like this Dreamweaver.docx--> no Problem

Posted 9-Feb-16 20:54pm

Aravindba

Updated 9-Feb-16 22:39pm

v4

Add a Solution

Comments

Maciej Los 10-Feb-16 4:13am

Improve your question and show us the body of SP.

Aravindba 10-Feb-16 4:32am

question updated,pls check and reply

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2016-02-09T21:42:00

Solution 1

Quotes, my friend, you need quotes.
Try:

SQL

DELETE FROM [รู้จักกับw] WHERE [File Name] = N'รู้จักกับ Dreamweaver.docx' AND [File Path] = N'รู้จักกับ\รู้จักกับ\รู้จักกับ Dreamweaver.docx'

Since you are using Thai characters, the "N" prefix indicates to SQL that it's a Unicode string.

Posted 9-Feb-16 21:42pm

OriginalGriff

Comments

Aravindba 10-Feb-16 4:19am

Yes,i know that,in above i post how i execute SP in SP window,SP name is SPDelFile,
Using Exec can execute sp directly,i thought ur well know

If file name or path fully Thai/ English not problem,but if partial Thai and English ,then problem

The above SP execute successfully if file name is full Thai

Exec SPDelFile N'รู้จักกับ Dreamweaver.docx', N'รู้จักกับw',N'รู้จักกับ Dreamweaver.docx',N'รู้จักกับ\รู้จักกับ\รู้จักกับ Dreamweaver.docx',N'รู้จักกับw\รู้จักกับ\รู้จักกับ',N'รู้จักกับ\รู้จักกับ\รู้จักกับ Dreamweaver.docx',N'รู้จักกับw\รู้จักกับ\รู้จักกับ\รู้จักกับ Dreamweaver.docx',N'รู้จักกับw'

Here got quotes and "N" prefix
Inside sp i worte this queries

@FileName nvarchar(256),
@TblName nvarchar(256),
@FileName1 nvarchar(256),
@FilePath nvarchar(256),
@DBPath nvarchar(256),
@FilePath1 nvarchar(256),
@SecPath nvarchar(256),
@TblNameIndx nvarchar(256)
Declare @DelTblPos nvarchar(max);
SET @DelTblPos = 'Delete from ['+ @TblName +'] where [File Name] = '+ @FileName1 +' and [File Path] = ' + @FilePath1 ;
Print @DelTblPos;
EXEC (@DelTblPos);

Declare @DelIndexing nvarchar(max);
SET @DelIndexing = 'Delete from [FTIndexing] where [Filename] = '+ @FileName1 +' and [Filepath] = ' + @FilePath1 + ' and [Database] = ' + @TblNameIndx ;
Print @DelIndexing;
EXEC (@DelIndexing);

But in Print result i get without "N" and single quotes

OriginalGriff 10-Feb-16 4:36am

That's because you don't add the quotes to the NVARCHAR string.
If you write
SET @A = 'Hello there'
SET @X = 'DELETE FROM MyTable WHERE MyCOlumn = ' + @A + ' AND MyOtherColum = 6'
Then @X contains
DELETE FROM MyTable WHERE MyColumn = Hello there AND MyOtherColumn = 6
When it needs to contain
DELETE FROM MyTable WHERE MyColumn = N'Hello there' AND MyOtherColumn = 6
To do that, you need to change the SET statement:
SET @X = N'DELETE FROM MyTable WHERE MyColumn = N''' + @A + N''' AND MyOtherColum = 6'

Aravindba 10-Feb-16 4:52am

Hi i am change like this
Declare @DelTblPos nvarchar(max);
SET @DelTblPos = N'Delete from ['+ @TblName +'] where [File Name] = N'''+ @FileName1 + N''' and [File Path] = N''' + @FilePath1 +'';
Print @DelTblPos;
EXEC (@DelTblPos);

Declare @DelIndexing nvarchar(max);
SET @DelIndexing = N'Delete from [FTIndexing] where [Filename] = N'''+ @FileName1 + N''' and [Filepath] = N''' + @FilePath1 + N''' and [Database] = N''' + @TblNameIndx + '';
Print @DelIndexing;
EXEC (@DelIndexing);

and i get this is error

Delete from [รู้จักกับw] where [File Name] = N'รู้จักกับ Dreamweaver.docx' and [File Path] = N'รู้จักกับ\รู้จักกับ\รู้จักกับ Dreamweaver.docx
Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string 'รู้จักกับ\รู้จักกับ\รู้จักกับ Dreamweaver.docx'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'รู้จักกับ\รู้จักกับ\รู้จักกับ Dreamweaver.docx'.
Delete from [FTIndexing] where [Filename] = N'รู้จักกับ Dreamweaver.docx' and [Filepath] = N'รู้จักกับ\รู้จักกับ\รู้จักกับ Dreamweaver.docx' and [Database] = N'รู้จักกับw
Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string 'รู้จักกับw'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'รู้จักกับw'.

if i add N''' and end of query i get this error
Msg 137, Level 15, State 2, Procedure SPDelFile, Line 28
Must declare the scalar variable "@DelIndexing".
Msg 137, Level 15, State 2, Procedure SPDelFile, Line 29
Must declare the scalar variable "@DelIndexing".

Is it N and 3 single quotes ? or 1 double quotes and 1 single quotes ?

OriginalGriff 10-Feb-16 5:05am

Look at what you wrote...
What do your strings end with?
What does '' mean inside a string?
If you want to put a closing quote, on it's own, at the end of a string you need
''''
not
''
The first quote starts a string.
The second and third indicate "insert a quote into the string"
The fourth terminates it.

Aravindba 10-Feb-16 5:14am

Yes,sorry,now working fine ,thanks a lot,Thank u very much for quick replies.

OriginalGriff 10-Feb-16 5:18am

You're welcome!