I need a way to check if a record exists in a table, so I can use that to run some logic on my page (See code below)
protected void GalleryGrid_SelectedIndexChanged(object sender, EventArgs e)
{
string sqlConnString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
string selectLikes = "SELECT LikedBy, picURL " + "FROM piclikes " + "WHERE LikedBy=@LikedBy and picURL=@picURL";
string likedBy = Page.User.Identity.Name.ToString();
string picURL = ((Label)GalleryGrid.SelectedItem.FindControl("picurlLabel")).Text;
using (SqlConnection sqlConn = new SqlConnection(sqlConnString))
{
using (SqlCommand selectCommand = new SqlCommand(selectLikes, sqlConn))
{
try
{
selectCommand.Parameters.Add(new SqlParameter("@picURL", SqlDbType.NVarChar, 50));
selectCommand.Parameters["@picURL"].Value = picURL;
selectCommand.Parameters.Add(new SqlParameter("@LikedBy", SqlDbType.NVarChar, 50));
selectCommand.Parameters["@LikedBy"].Value = likedBy;
sqlConn.Open();
// IF a a record matching the query string is not found, do this
if (selectCommand.ExecuteNonQuery() != 0)
{
LikeButton.Enabled = false;
}
//if a record is found, do this
else
{
LikeButton.Enabled = true;
}
selectCommand.Connection.Close();
sqlConn.Dispose();
}
catch (SqlException ex)
{
StatusLabel.Text = "There was an error: " + ex.ToString();
}
}
}
I correct myself guys, really crazy, don't know what I was drinking while writing, ExecuteNonQuery() is for query with no results. WoW I can't beleive it. Sorry everyone, really. This is to correct the false statement below.
ExecuteNonQuery() returns the number of rows affected by the query, so if 0 then, there are no records as a result of your query, if not, then there is. I would use ExecuteNonQuery() > 0 as a verification.
Another option, is to use the count function with ExecuteScalar()
ExecuteNonQuery will always return -1 for SELECT statement (http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery.aspx). If you only want to check if any row with such conditions exists, it will be better if you use following
query (it will aslo have better efficency on db side):
SELECT COUNT(1) FROM PICLIKES WHERE LikedBy = @LikedBy AND picURL = @picURL
Now you can use ExecuteScalar to achieve your goal:
//At least one row exists
if (Convert.ToInt32(selectCommand.ExecuteScalar()) != 0)
LikeButton.Enabled = false;
//No rows exists
else
LikeButton.Enabled = true;
Please indicate "Mark as Answer" if a post has answered the question.
Yet another developer blog <-- visit my blog
ExecuteNonQuery will always return -1 for SELECT statement (http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery.aspx). If you only want to check if any row with such conditions exists, it will be better if you use following
query (it will aslo have better efficency on db side):
SELECT COUNT(1) FROM PICLIKES WHERE LikedBy = @LikedBy AND picURL = @picURL
Now you can use ExecuteScalar to achieve your goal:
//At least one row exists
if (Convert.ToInt32(selectCommand.ExecuteScalar()) != 0)
LikeButton.Enabled = false;
//No rows exists
else
LikeButton.Enabled = true;
This was perfect. worked first time. Thank you, and everyone else who tried to help.
anogio
Member
69 Points
38 Posts
Need to check for null return on ExecuteNonQuery()
Jan 10, 2012 11:33 AM|LINK
I need a way to check if a record exists in a table, so I can use that to run some logic on my page (See code below)
protected void GalleryGrid_SelectedIndexChanged(object sender, EventArgs e) { string sqlConnString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; string selectLikes = "SELECT LikedBy, picURL " + "FROM piclikes " + "WHERE LikedBy=@LikedBy and picURL=@picURL"; string likedBy = Page.User.Identity.Name.ToString(); string picURL = ((Label)GalleryGrid.SelectedItem.FindControl("picurlLabel")).Text; using (SqlConnection sqlConn = new SqlConnection(sqlConnString)) { using (SqlCommand selectCommand = new SqlCommand(selectLikes, sqlConn)) { try { selectCommand.Parameters.Add(new SqlParameter("@picURL", SqlDbType.NVarChar, 50)); selectCommand.Parameters["@picURL"].Value = picURL; selectCommand.Parameters.Add(new SqlParameter("@LikedBy", SqlDbType.NVarChar, 50)); selectCommand.Parameters["@LikedBy"].Value = likedBy; sqlConn.Open(); // IF a a record matching the query string is not found, do this if (selectCommand.ExecuteNonQuery() != 0) { LikeButton.Enabled = false; }//if a record is found, do this else { LikeButton.Enabled = true; } selectCommand.Connection.Close(); sqlConn.Dispose(); } catch (SqlException ex) { StatusLabel.Text = "There was an error: " + ex.ToString(); } } }hans_v
All-Star
35986 Points
6550 Posts
Re: Need to check for null return on ExecuteNonQuery()
Jan 10, 2012 11:38 AM|LINK
ExecuteNonQuery returns the number of records affected by an action query (INSERT, UPDATE, DELETE), a SELECT query will always return -1
If you want to check if a record exists, use
string selectLikes = "SELECT COUNT(*) FROM piclikes WHERE LikedBy=@LikedBy and picURL=@picURL";
.....
int recordcount = (int)cmd.ExecuteScalar();
tehremo
Star
10540 Points
1704 Posts
Re: Need to check for null return on ExecuteNonQuery()
Jan 10, 2012 11:40 AM|LINK
I think you have your logic backwards in the if/else. ExecuteNonQuery() will return the number of rows affected, so your if should read:
if (selectCommand.ExecuteNonQuery() == 0) { LikeButton.Enabled = false; } else { LikeButton.Enabled = true; }formationusa
Participant
1290 Points
215 Posts
Re: Need to check for null return on ExecuteNonQuery()
Jan 10, 2012 11:43 AM|LINK
Hello anogio,
does your code work ?
I cannot see the issue with it.
I correct myself guys, really crazy, don't know what I was drinking while writing, ExecuteNonQuery() is for query with no results. WoW I can't beleive it. Sorry everyone, really. This is to correct the false statement below.
ExecuteNonQuery() returns the number of rows affected by the query, so if 0 then, there are no records as a result of your query, if not, then there is. I would use ExecuteNonQuery() > 0 as a verification.
Another option, is to use the count function with ExecuteScalar()
I'm not sure I fully understand the context.
Kind Regards,
http://oudinia.blogspot.com
tpeczek
Contributor
2112 Points
260 Posts
Re: Need to check for null return on ExecuteNonQuery()
Jan 10, 2012 11:47 AM|LINK
ExecuteNonQuery will always return -1 for SELECT statement (http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery.aspx). If you only want to check if any row with such conditions exists, it will be better if you use following query (it will aslo have better efficency on db side):
Now you can use ExecuteScalar to achieve your goal:
//At least one row exists if (Convert.ToInt32(selectCommand.ExecuteScalar()) != 0) LikeButton.Enabled = false; //No rows exists else LikeButton.Enabled = true;Yet another developer blog <-- visit my blog
anogio
Member
69 Points
38 Posts
Re: Need to check for null return on ExecuteNonQuery()
Jan 10, 2012 11:48 AM|LINK
anogio
Member
69 Points
38 Posts
Re: Need to check for null return on ExecuteNonQuery()
Jan 10, 2012 11:53 AM|LINK
This was perfect. worked first time. Thank you, and everyone else who tried to help.