Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

assigning a value to sqlcommand parameter

Posted on 2011-05-08
3
Medium Priority
?
501 Views
Last Modified: 2012-05-11
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 ...???
0
Comment
Question by:adamtrask
3 Comments
 
LVL 75

Accepted Solution

by:
käµfm³d   👽 earned 2000 total points
ID: 35715516
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
 
LVL 29

Expert Comment

by:Paul Jackson
ID: 35715552
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
 

Author Closing Comment

by:adamtrask
ID: 35715846
Thank you guys
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question