assigning a value to sqlcommand parameter

Hello experts,

In retrieving data from Sql server database I use the following Sqlcommand:
 
comm = New SqlCommand("SELECT COUNT(*) FROM Main where tType=@tType And dDate=@dDate And sSubject like 'compass%'", con)


I then use parameters to enter the data type and values for dDate and tType.

My problem is  that I need to do the same with sSubject, so that the Sqlcommand would look like the following:

comm = New SqlCommand("SELECT COUNT(*) FROM Main where tType=@tType And dDate=@dDate And sSubject=@sSubject", con)

The difficulty here is assigning a value to the sSubject parameters:

  comm.Parameters.AddWithValue("@sSubject", System.Data.SqlDbType.NVarChar)
   comm.Parameters("@sSubject").Value = "like 'compass%'"


Obviously the way I entered the value is wrong because it is not working.
But how else can I assign a value using "Like" followed by the word that should be searched and the "%" to indicate any other word that follow ...???
adamtraskAsked:
Who is Participating?
 
käµfm³d 👽Connect With a Mentor Commented:
When you use parameters within your SQL query, a textual substitution occurs. So where you have the following:

"SELECT COUNT(*) FROM Main where tType=@tType And dDate=@dDate And sSubject=@sSubject"

Open in new window

AND

 comm.Parameters("@sSubject").Value = "like 'compass%'"

Open in new window


What you end up with on the database is something like this:

SELECT COUNT(*) FROM Main where tType='some value' And dDate='some other value' And sSubject='like ''compass%'

Open in new window


So you can see that you never actually get the condition you are seeking. It's a bit more than a simple textual substitution, but for our purposes here, you can think of it as such.

My suggestion would be to come up with a way to use two different versions of your query: one for "like" searches and one for "equality" searches. For example:
void someFunc(bool IsLikeSearch)
{
    if (IsLikeSearch)
    {
        comm = New SqlCommand("SELECT COUNT(*) FROM Main where tType=@tType And dDate=@dDate And sSubject LIKE @sSubject", con)
        comm.Parameters.AddWithValue("@sSubject", System.Data.SqlDbType.NVarChar)
        comm.Parameters("@sSubject").Value = "compass%"
    }
    else
    {
        comm = New SqlCommand("SELECT COUNT(*) FROM Main where tType=@tType And dDate=@dDate And sSubject=@sSubject", con)
        comm.Parameters.AddWithValue("@sSubject", System.Data.SqlDbType.NVarChar)
        comm.Parameters("@sSubject").Value = "compass"
    }
}

Open in new window

0
 
Paul JacksonSoftware EngineerCommented:
change to :

comm = New SqlCommand("SELECT COUNT(*) FROM Main where tType=@tType And dDate=@dDate And sSubject like + '%' + @sSubject + '%'", con)

comm.Parameters.AddWithValue("@sSubject", "compass")

0
 
adamtraskAuthor Commented:
Thank you guys
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.