here is the error that came up after attempting to execute my website. This is related to my previous question but different objective this time. Here is the link if you want to refer back to it. How to code a nested sql statement to get row number of a specific item in mssql?
Below is an image of the server error.
Its referring to the Int32 count = (Int32)cmd.ExecuteScalar(); that i wrote in my code. Here is the full code. I have also added a comment to where the error originated.
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
public partial class _Default : System.Web.UI.Page
{
SqlConnection con, con1;
SqlCommand cmd, cmd1;
DataSet ds, ds1;
private int _x;
public int X
{
get { return _x; }
set { _x = value; }
}
public _Default()
{
con = new SqlConnection();
con.ConnectionString = ConfigurationManager.ConnectionStrings["GuitarItemsDBConnectionString2"].ToString();
cmd = new SqlCommand();
ds = new DataSet();
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack) {
bindgridviewguitaritems();
}
}
public void getRowNumber(string brand, string model)
{
string query = string.Format("SELECT Rn FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY id) AS Rn FROM guitarItems WHERE brand LIKE '{0}') x WHERE x.Model LIKE '{1}'",brand,model);
con.Open();
cmd.Connection = con;
cmd.CommandText = query;
Int32 count = (Int32)cmd.ExecuteScalar(); //<----Here is the error
X = count;
con.Close();
}
//Start of Gridview Code for Guitar Items
private void bindgridviewguitaritems()
{
con.Open();
cmd.CommandText = "SELECT * FROM [guitarItems]";
cmd.Connection = con;
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
con.Close();
GridView1.DataBind();
}
protected void GridViewBtn1_Click(object sender, EventArgs e)
{
Button btn = sender as Button;
GridViewRow gridrow = btn.NamingContainer as GridViewRow;
int id = Convert.ToInt32(GridView1.DataKeys[gridrow.RowIndex].Value.ToString());
string name = GridView1.Rows[gridrow.RowIndex].Cells[3].Text;
string model = GridView1.Rows[gridrow.RowIndex].Cells[4].Text;
getRowNumber(name,model);
Label1.Text = X.ToString();
Label2.Text = name;
Label3.Text = model;
con.Open();
cmd.CommandText = "DELETE FROM [guitarItems] WHERE id=" + id;
cmd.Connection = con;
int a = cmd.ExecuteNonQuery();
con.Close();
if (a > 0)
{
bindgridviewguitaritems();
}
System.IO.File.Delete(@"C:\Users\User1\Documents\Visual Studio 2015\WebSites\MusicStore\Pages\GuitarItems" + name + "Details" + id + ".aspx");
System.IO.File.Delete(@"C:\Users\User1\Documents\Visual Studio 2015\WebSites\MusicStore\Pages\GuitarItems" + name + "Details" + id + ".aspx.cs");
}
//End of Gridview Code for Guitar Items
Feel free to suggest alternative solutions to this problem. By the way, this is only a small program for testing out things. If all goes well, I might incorporate some of it in my actual project(except the aspects where it is not parameterized).
