CODEWITHSUNDEEP
c#sqlasp.net-mvcwebmatrix
Jakob Handberg Lystbæk
Jakob Handberg Lystbæk

Reputation: 83

Search for string in SQL table unsuccessful

I'm trying to search a sql table for a specific string, and return the number of observations found. It keeps returning -1 though, whether the string is in the table or not. Here's my code:

@{
    Layout = "~/_Layout.cshtml";
    Page.title = "TestArea";


    var db = Database.Open("Cafeen");
    string SearchWord = "Jolly";

    var msg = db.Execute("SELECT COUNT(*) FROM Products WHERE ProductName = @SearchWord");

}

<p>@msg</p>

Should I perhaps use something other than COUNT(*)? What is the significance of -1? I would have assumed the expression to return 0 if the string can't be found.

Upvotes: 1

Views: 75

Answers (3)

Salah Akbari
Salah Akbari

Reputation: 39966

In db.Execute and SqlCommand.ExecuteNonQuery:

For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. When a trigger exists on a table being inserted or updated, the return value includes the number of rows affected by both the insert or update operation and the number of rows affected by the trigger or triggers. For all other types of statements like SELECT, the return value is -1. If a rollback occurs, the return value is also -1.

Have a look at the following links may be helpful:

https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery.aspx

How to Identify whether SQL job is successfully executed or not in C#

Upvotes: 0

Alexander Bell
Alexander Bell

Reputation: 7918

Pertinent to the SQL Database, there is: 

SqlCommand.ExecuteScalar Method ()

(re: https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executescalar(v=vs.110).aspx)

Otherwise, refer to Database.QueryValue Method (re: https://msdn.microsoft.com/en-us/library/webmatrix.data.database.queryvalue(v=vs.111).aspx)

Both methods return a scalar value from the first column/ first row.

Also, instead of COUNT(*) in SQL statement you can use COUNT(1) for better performance.

Hope this may help.

Upvotes: 0

Steve
Steve

Reputation: 216313

You are using the WebMatrix.Data namespace. In this context you should call the QuerySingle method not the Execute one because, as many have already stated, that method is for not returning rows data.

The Execute method is used to perform non-query commands on a database, such as the SQL Drop, Create, Delete, Update, and Insert commands.

Moreover I suggest to change your query statement to a more performant one

var db = Database.Open("Cafeen");
string SearchWord = "Jolly";
string cmdText = @"IF EXISTS(SELECT 1 FROM Products 
                             WHERE ProductName = @searchWord)
                      SELECT 1 ELSE SELECT 0";
int exists = Convert.ToInt32(db.QuerySingle(cmdText, SearchWord));
.....

Upvotes: 1

Related Questions