Click here to Skip to main content
15,881,757 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am passing Persian text as value of sqlparameter to a parameterized query in asp.net/vb.net
but returns no row - the same query i run in ssms - gives result rows. Don't understand where is the mistake. Database already collated 'Persian_100_ci_ai.
Vb.net:
VB
Dim cmd As New SqlCommand
     Dim dt As New DataTable
     Dim dr As SqlDataReader
     dt.TableName = "temp"
     Try
         If Not conn.State = ConnectionState.Closed Then conn.Close()
         If conn.State = ConnectionState.Closed Then conn.Open()
         cmd.Connection = conn
         Dim qry As String =  "Select * from users WHERE [Name]=@UserName AND [Pwd]=@Password"
         cmd.commandtext = qry
         cmd.Parameters.Add("@UserName", SqlDbType.NVarChar, 50).Value = "ادمین"
         cmd.Parameters.Add("@Password", SqlDbType.NVarChar, 50).Value = "ادمین"
         dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
            If dr.HasRows Then
                dt.Load(dr)
            End If

            Return dt
        Catch ex As Exception
            Return Nothing
        Finally
            dt = Nothing
            cmd.Connection = Nothing
            cmd.Parameters.Clear()
            cmd.Dispose()
        End Try

While in SSMS:
SQL
declare @UserName nvarchar(50) = 'ادمين'
declare @Password nvarchar(50)= 'ادمين'
select * from Users where [name]=@UserName and [Pwd] = @Password


Interesting part when in vb.net I create query embedding criteria and not passed as parameter - it works:
VB
Dim cmd As New SqlCommand
        Dim dt As New DataTable
        Dim dr As SqlDataReader
        Dim pLoginName As String = "ادمین"
        Dim pPassword As String = "ادمین"
        dt.TableName = "temp"
        Try
            If Not conn.State = ConnectionState.Closed Then conn.Close()
            If conn.State = ConnectionState.Closed Then conn.Open()
            cmd.Connection = conn
            Dim qry As String ="Select * from users WHERE [Name]='" & pLoginName & "' AND [Pwd]='" & pPassword & "'"
 cmd.CommandText = qry
           dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
            If dr.HasRows Then
                dt.Load(dr)
            End If

            Return dt
        Catch ex As Exception
            Return Nothing
        Finally
            dt = Nothing
            cmd.Connection = Nothing
            cmd.Parameters.Clear()
            cmd.Dispose()
        End Try
Posted
Updated 2-Nov-14 18:44pm
v3
Comments
Kornfeld Eliyahu Peter 3-Nov-14 4:43am    
Can you see with the profiler what the actual query created by your code?
Maciej Los 4-Nov-14 3:42am    
Please, do not repost!

What about the AddWithValue function? I.e.
cmd.Parameters.AddWithValue("@UserName", "ادمین")
 
Share this answer
 
Comments
Maciej Los 4-Nov-14 3:43am    
Bernard, please read his previous question.
Bernhard Hiller 4-Nov-14 3:49am    
oh dear, a duplicate in fact. Thanks for the hint.
Maciej Los 4-Nov-14 3:56am    
Have you seen His comments?
Please, see PIEBALD... answer (solution 3). I think, he hit a 10.
As PIEBALDconsult had mentioned in this[^] answer, remove this line:
VB
dt = Nothing

from Finally block.

It destroys your table!

Do not repost!
 
Share this answer
 
Comments
shailesh_pujara 4-Nov-14 6:14am    
but before going to finally - dr.hasrows is false.
Maciej Los 4-Nov-14 6:30am    
Have you tried solution1?
shailesh_pujara 4-Nov-14 9:46am    
yes - datatable get filled only when datareader.hasrows - I set debug point there and checked - datareader.hasrows = false. In any case if it is success full - datatable return before the final statement hits.
shailesh_pujara 4-Nov-14 9:53am    
Actually I realize final statement is worthless - because I return empty or filled datatable if no exception arise and nothing in case of exception. So final has no impact in the code. Actually code should be
Dim dt as new datatable '' will be empty - nothing
Try
fill table
Catch
Finally
dispose connection, command objects
end try
return dt ( empty in case of no rows return or exception arise)

But this way also, the datatable is empty as cmd.executereader() return no row.
Maciej Los 4-Nov-14 10:44am    
OK. Create simple form with 2 textboxes. Read values from textboxes to the variables and try to execute code. Let's see what happen...
Thanks all for your answers.

I found where I was wrong. There were two databases - English and Persian. The connection class which I have created to connect to appropriate database has bug and sometime due to bug it was connecting to English database i/o Persian.

Now I resolved the bug. Thanks to my colleague Chintan Bilimoria, for pointing out - whether you are selecting right database?
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900