question

RanjithJain-0403 avatar image
0 Votes"
RanjithJain-0403 asked NaomiNNN commented

How to pass a unicode character as parameter for a store procedure in vb.net

0


i am trying to pass a list of ids as a parameter of a stored procedure from vb.net. And these IDs can have unicode characters as well.

EXEC sp_IMS_RP_GetItemDetail @ItemID = '''736'', ''φ736'''

i am aware that if the parameter is defined as nvarchar it will go through. but once the parameter value gets into the stored procedure the unicode character gets converted and it wont go through for further querying. i was able to find out and tested as well that if i pass the parameter value by appending a N character before the value, the unicode character doesnt get converted.

EXEC sp_IMS_RP_GetItemDetail @ItemID = N'''736'', ''φ736'''

But i am failing to find a way to append a N character programmatically from vb.net.

sql-server-transact-sqldotnet-visual-basic
· 7
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Below is my VB.net code.

     **Dim cmd As New SqlCommand
     cmd.CommandType = CommandType.StoredProcedure
     cmd.CommandText = "sp_IMS_RP_GetItemDetail"
     Dim itemid As String = "φ736"
     cmd.Parameters.AddWithValue("@ItemID", itemid)**

When i look up at cmd.tostring i get the command string as EXEC sp_IMS_RP_GetItemDetail @ItemID = 'φ736' which seems fine until now.

But when the value reaches my stored procedure the value get converted to f736. Below is the portion of stored procedure.

Create PROC [dbo].[sp_IMS_RP_GetItemDetail] (@ItemID as nvarchar(1000) )
AS
Begin
--
Print @ItemID
--
end

When the above stored procedure is excecuted i see the below result.
211848-image.png




1 Vote 1 ·

Maybe your current VB code can be adjusted. Show some details.


0 Votes 0 ·

Below is my VB.net code.

     **Dim cmd As New SqlCommand
     cmd.CommandType = CommandType.StoredProcedure
     cmd.CommandText = "sp_IMS_RP_GetItemDetail"
     Dim itemid As String = "φ736"
     cmd.Parameters.AddWithValue("@ItemID", itemid)**

When i look up at cmd.tostring i get the command string as EXEC sp_IMS_RP_GetItemDetail @ItemID = 'φ736' which seems fine until now.

But when the value reaches my stored procedure the value get converted to f736. Below is the portion of stored procedure.

Create PROC [dbo].[sp_IMS_RP_GetItemDetail] (@ItemID as nvarchar(1000) )
AS
Begin
--
Print @ItemID
--
end

When the above stored procedure is excecuted i see the below result.
211848-image.png




0 Votes 0 ·
image.png (16.9 KiB)

What do you mean "but once the parameter value gets into the stored procedure the unicode character gets converted and it wont go through for further querying"

Please post your stored proc code.

Also the results of SELECT @@VERSION.

0 Votes 0 ·

Below is my VB.net code.

     **Dim cmd As New SqlCommand
     cmd.CommandType = CommandType.StoredProcedure
     cmd.CommandText = "sp_IMS_RP_GetItemDetail"
     Dim itemid As String = "φ736"
     cmd.Parameters.AddWithValue("@ItemID", itemid)**

When i look up at cmd.tostring i get the command string as EXEC sp_IMS_RP_GetItemDetail @ItemID = 'φ736' which seems fine until now.

But when the value reaches my stored procedure the value get converted to f736. Below is the portion of stored procedure.

Create PROC [dbo].[sp_IMS_RP_GetItemDetail] (@ItemID as nvarchar(1000) )
AS
Begin
--
Print @ItemID
--
end

When the above stored procedure is excecuted i see the below result.
211848-image.png




0 Votes 0 ·

That is just the PRINT command changing it to non-unicode. That does not mean it is not working.

0 Votes 0 ·

Below is my VB.net code.

     **Dim cmd As New SqlCommand
     cmd.CommandType = CommandType.StoredProcedure
     cmd.CommandText = "sp_IMS_RP_GetItemDetail"
     Dim itemid As String = "φ736"
     cmd.Parameters.AddWithValue("@ItemID", itemid)**

When i look up at cmd.tostring i get the command string as EXEC sp_IMS_RP_GetItemDetail @ItemID = 'φ736' which seems fine until now.

But when the value reaches my stored procedure the value get converted to f736. Below is the portion of stored procedure.

Create PROC [dbo].[sp_IMS_RP_GetItemDetail] (@ItemID as nvarchar(1000) )
AS
Begin
--
Print @ItemID
--
end

When the above stored procedure is excecuted i see the below result.
211848-image.png




0 Votes 0 ·
ArtemKustikov-7649 avatar image
0 Votes"
ArtemKustikov-7649 answered Ebrahim10 commented

Try to use something like this:

 Dim cmd As New SqlCommand("sp_IMS_RP_GetItemDetail", conn)
 Dim ids() As String = { "736", "φ736"}

 Try
     cmd.CommandType = CommandType.StoredProcedure
     cmd.Parameters.AddWithValue("@ItemID", ids)
     Using r = cmd.ExecuteReader()

         if r.Read() then
            ...
         end if
    End Using
 Finally
     If cmd IsNot Nothing Then cmd.Dispose()
 End Try







· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@ArtemKustikov-7649 doing so, the parameter value "φ736" will be converted as f736 in sql server.

211736-image.png


0 Votes 0 ·
image.png (26.7 KiB)
DanGuzman avatar image DanGuzman RanjithJain-0403 ·

Strings in .NET are Unicode so if your run the parameterized query it should work. To pass a Unicode constant in an SSMS you need to prefix the literal with N, denoting a national (Unicode) string.

0 Votes 0 ·
ArtemKustikov-7649 avatar image
1 Vote"
ArtemKustikov-7649 answered RanjithJain-0403 published

OK, please check that variables in your stored procedure do have nvarchar types and appropriate collation, seems that your procedure converts Unicode to 8 bit

https://docs.microsoft.com/en-us/sql/t-sql/data-types/nchar-and-nvarchar-transact-sql?WT.mc_id=DP-MVP-5001259&view=sql-server-ver15

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Below is my VB.net code.

     **Dim cmd As New SqlCommand
     cmd.CommandType = CommandType.StoredProcedure
     cmd.CommandText = "sp_IMS_RP_GetItemDetail"
     Dim itemid As String = "φ736"
     cmd.Parameters.AddWithValue("@ItemID", itemid)**

When i look up at cmd.tostring i get the command string as EXEC sp_IMS_RP_GetItemDetail @ItemID = 'φ736' which seems fine until now.

But when the value reaches my stored procedure the value get converted to f736. Below is the portion of stored procedure.

Create PROC [dbo].[sp_IMS_RP_GetItemDetail] (@ItemID as nvarchar(1000) )
AS
Begin
--
Print @ItemID
--
end

When the above stored procedure is excecuted i see the below result.
211848-image.png




0 Votes 0 ·
TomPhillips-1744 avatar image
1 Vote"
TomPhillips-1744 answered OlafHelper-2800 commented

You do not pass 'N' prefix for variables. The "N" is for static strings to indicate they are unicode.

· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Below is my VB.net code.

     **Dim cmd As New SqlCommand
     cmd.CommandType = CommandType.StoredProcedure
     cmd.CommandText = "sp_IMS_RP_GetItemDetail"
     Dim itemid As String = "φ736"
     cmd.Parameters.AddWithValue("@ItemID", itemid)**

When i look up at cmd.tostring i get the command string as EXEC sp_IMS_RP_GetItemDetail @ItemID = 'φ736' which seems fine until now.

But when the value reaches my stored procedure the value get converted to f736. Below is the portion of stored procedure.

Create PROC [dbo].[sp_IMS_RP_GetItemDetail] (@ItemID as nvarchar(1000) )
AS
Begin
--
Print @ItemID
--
end

When the above stored procedure is excecuted i see the below result.
211848-image.png




0 Votes 0 ·

That is just the PRINT command converting your string to non-unicode. That does not mean it is not working.

If you query a column in the database, does it work?

0 Votes 0 ·

i think PRINT command too mislead me. I think this helped. but further in my stored procedure when i try to use the parameter as a query where clause and execute the stored procedure. i get another error.

212459-image.png


212474-image.png


0 Votes 0 ·
Show more comments
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered RanjithJain-0403 published

It would have helped if you had shown the VB code where you submit the command. But if your VB code is producing

EXEC sp_IMS_RP_GetItemDetail @ItemID = '''736'', ''φ736'''

you are doing it wrong.

The code should look something like this:

cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText sp_IMS_RP_GetItemDetail
cmd.Parameters.Add("@ItemID", SqlDbTypes.NVarChar, 20).Value = "φ736'"
cmd.ExecuteReader   ' Or DataAdatperFill or whatever  you like.

Here you specify the data type of the parameter, and in this case as nvarchar.

Above Artem use AddWithValue, which is OK for calls to stored procedures, but it has some very bad effects when you use CommandType.Text. So best is to stay away from it.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Below is my VB.net code.

     **Dim cmd As New SqlCommand
     cmd.CommandType = CommandType.StoredProcedure
     cmd.CommandText = "sp_IMS_RP_GetItemDetail"
     Dim itemid As String = "φ736"
     cmd.Parameters.AddWithValue("@ItemID", itemid)**

When i look up at cmd.tostring i get the command string as EXEC sp_IMS_RP_GetItemDetail @ItemID = 'φ736' which seems fine until now.

But when the value reaches my stored procedure the value get converted to f736. Below is the portion of stored procedure.

Create PROC [dbo].[sp_IMS_RP_GetItemDetail] (@ItemID as nvarchar(1000) )
AS
Begin
--
Print @ItemID
--
end

When the above stored procedure is excecuted i see the below result.
211848-image.png




0 Votes 0 ·
NaomiNNN avatar image
0 Votes"
NaomiNNN answered ErlandSommarskog commented

Instead of using AddWithValue change this line to

cmd.Parameters.Add("@ItemID", SqlDbType.NVachar, 1000).value = @itemId

Double check the correct syntax for VB.NET, but as Erland said, AddWithValue may be your problem, the best method is to explicitly list the type and length of the parameter using more precise Parameters.Add method.

Also, if possible, don't use sp_ for procedure names. Try using usp_ instead for the prefix where u stands for user-defined stored procedure.

· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I think this helped. but further in my stored procedure when i try to use the parameter as a query where clause and execute the stored procedure. i get another error.

212459-image.png


212474-image.png


0 Votes 0 ·
image.png (40.7 KiB)
image.png (27.3 KiB)

There is no reason in your proc to be using dynamic SQL. In the code you posted.

You need to debug your proc before your VB code will work. Your query does not return a field named "Item".

0 Votes 0 ·

The tricky part for me is, i dont have debugger in my sql server. the only option is to use the PRINT statements to debug. But for unicode characters that would result an error as it gets converted to lower case.

0 Votes 0 ·
Show more comments
TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered RanjithJain-0403 commented

I am not sure that you really have an issue or if you are only confusing matters. If you have a real issue - then it is not the you are posting about, and I will try to explain why.

First, we have some SQL:

CREATE TABLE Test (str  nvarchar(20) NOT NULL, int int NOT NULL)
INSERT Test (str, int)
   VALUES(N'φ736', 10), (N'f736', 656)
go
CREATE PROCEDURE TestSP @str nvarchar(20) AS
   SELECT int FROM Test WHERE str = @str
go
EXEC TestSP N'φ736'

Create this in tempdb.

Observe that the value we get back is 10. We would get 656 if the phi is mutilated.

Then we have this very simple VB .NET program:

Imports System.Data
Imports System.Data.SqlClient

Module Test

Public Sub Main()

    Dim cn      As New SqlConnection, _
        strConn As String


    '  Connection string, change server and database!
    strConn = "Integrated Security=SSPI;Data Source=(local);Initial Catalog=tempdb"

    Try
        cn.ConnectionString = strConn
        cn.Open()
    Catch ex As Exception
        Console.Writeline(ex.Message, "Connection failed!")
        cn = Nothing
        Exit Sub
    End Try

    Dim cmd As SqlCommand = cn.CreateCommand()
    
    cmd.CommandType = System.Data.CommandType.StoredProcedure
    cmd.CommandText = "TestSP"
    Dim itemid As String = "φ736"
    cmd.Parameters.AddWithValue("@str", itemid)

    Dim retval As Int32 = cmd.ExecuteScalar()

    System.Console.WriteLine("Retval: " & retval.ToString())

    
    cn.Close()
    cn.Dispose()
    
End Sub

End Module

I have also attached this as a file. Note that you need to rename to remove the .txt extension. Also note that you may need to change the server name in the connection string.
212244-slaskvb.txt

When I run the program, the output is 10. If you get a different output, this would be because you saved the code as an ANSI file. You need to store it as a Unicode file in UTF-16 or UTF-8.

You have been showing us screenshots from SSMS. Indeed, if you run the procedure as

EXEC TestSP 'φ736'

You will get back 656 (unless you have a UTF8 collation). The data type of a string literal with N in from is varchar. With N it is nvarchar.

When you use AddWithValue, it infers the data type from the second parameter. Because .NET only have Unicode strings, AddWithValue will never infer varchar.

It is also worth observing that when you call a procedure with CommandType.StoredProcedure, there is never any EXEC command. Instead the procedure is called through RPC, Remote Procedure Call. Or more precisely, the clients sends the procedure name, and the values together with the type information. But there is no SQL command per se.

If you are indeed getting incorrect results from your program, there is something more to it. For instance, if you running your program as a console-mode program, that program will not faithfully reproduce the output from that PRINT command, because the command-line window is not Unicode aware.

So if you have a real problem with your VB program, please explain what it is.



slaskvb.txt (1.8 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

i think my PRINT statement mislead me. but further in my stored procedure when i try to use the parameter as a query where clause and execute the stored procedure. i get another error.

212459-image.png


212474-image.png


0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered NaomiNNN converted comment to answer

As Tom says, there is absolutely no need to use dynamic SQL in your store procedure. Just write it as:

CREATE PROCEDURE Name @ItemID nvarchar(1000) AS
SELECT ...
FROM  tbl
WHERE itemID = @itemID

If the idea is that you want to pass a comma-separated list to the procedure, I have a short article on my web site where you can learn how to do this: https://www.sommarskog.se/arrays-in-sql.html.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Actually my original stored procedure is huge with lot of cursors, temp tables. i just tried to show the first portion of my procedure that was causing issue.

0 Votes 0 ·

This is my actual Stored proc where i am multiple parameters and for item id it would be comma separated list of item ids.212497-sp.txt


0 Votes 0 ·
sp.txt (8.8 KiB)
NaomiNNN avatar image
0 Votes"
NaomiNNN answered ErlandSommarskog commented

I didn't look into your procedure code, but you can use STRING_SPLIT function to separate your list of items into a table. What you posted as the beginning of the SP is already not written correctly. Even if you would need to resort to dynamic SQL, you need to use it properly. The SQL variable needs to be declared as nvarchar(max). But most likely you don't need dynamic SQL in your procedure even if number of parameters is big and you're passing comma-delimited list of items.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

i see string_split is part sql 2016 and above. but we work on sql 2008 r2 and above.

0 Votes 0 ·

In that case you use a user-defined functoin, see https://www.sommarskog.se/arrays-in-sql.html. And you do this, no matter if you are building the query dynamically, or you are using static SQL with OPTION (RECOMPILE).

it was a neat presentation.

Thank you!



0 Votes 0 ·
ErlandSommarskog avatar image
1 Vote"
ErlandSommarskog answered RanjithJain-0403 commented

Just as you don't inline parameter values in VB code, but use the parameter object, you don't inline parameter values when you build dynamic SQL strings in stored procedures, but here as well, you should parameterise your statements.

As it happens, this is a topic that I have covered extensively, and I have a longer article on my web site that discusses how to build these dynamic searches: https://www.sommarskog.se/dyn-search.html. You will learn dynamic SQL is only one possibility, and quickly looking at your code, I think that static SQL + OPTION (RECOMPILE) can be a better option in your case.

If you prefer to watch a video, you can see my presentation here: https://www.youtube.com/watch?v=4H9eE1-28yg.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Nice presentation!

0 Votes 0 ·

it was a neat presentation.
i feel using sql variable you wont be able to use it with IN WHERE Clause. correct me if i am wrong.

0 Votes 0 ·