Vinoth Vinoth - 2 months ago 8
ASP.NET (C#) Question

Retrieve specific value in where clause in c#

I want to get cmdd(@PermissionAccessoageID)value in the code and how can achieve that.

I get this error


An exception of type 'System.ArgumentException' occurred in System.Data.dll but was not handled in user code

Additional information: No mapping exists from object type System.Data.SqlClient.SqlCommand to a known managed provider native type.


My code:

protected void Add_Click(object sender, EventArgs e)
{
DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[1] { new DataColumn("Pages") });

foreach (GridViewRow row in addGridView.Rows)
{
if (row.RowType == DataControlRowType.DataRow)
{
CheckBox chkRow = (row.Cells[1].FindControl("chkboxaddView") as CheckBox);
CheckBox chkRow1 = (row.Cells[2].FindControl("chkboxaddEdit") as CheckBox);
string aaa = row.Cells[0].Text;

if (chkRow.Checked)
{
con.Open();
var vaala = chkRow.Checked ? 1 : 0;
var vaalaa = chkRow1.Checked ? 1 : 0;
var val = chkaddisactive.Checked ? 1 : 0;
var vall = chkboxisactive.Checked ? 1 : 0;
string sqlcmd = "select AccesspageID From AccessPages where pages='" + row.Cells[0].Text + "'";

SqlCommand cmdd = new SqlCommand(sqlcmd, con);
cmdd.CommandType = CommandType.Text;
cmdd.ExecuteScalar();

string query = "add3tables"; //Stored procedure name
SqlCommand com = new SqlCommand(query, con); //creating SqlCommand object
com.CommandType = CommandType.StoredProcedure; //here we declaring command type as stored procedure

// adding parameters to SqlCommand below
com.Parameters.AddWithValue("@Name", txtaddrolename.Text.ToString());
string unique_string = Guid.NewGuid().ToString();
com.Parameters.AddWithValue("@RoleID ", unique_string);
com.Parameters.AddWithValue("@IsActive ", val);
com.Parameters.AddWithValue("@PermissionRoleID ", unique_string);
com.Parameters.AddWithValue("@View ", vaala);
com.Parameters.AddWithValue("@Edit ", vaalaa);
com.Parameters.AddWithValue("@PermissionAccessoageID ", cmdd);

com.ExecuteNonQuery();
con.Close();
}

if (chkRow1.Checked == false)
{
var vaala = chkRow.Checked ? 1 : 0;
var vaalaa = chkRow1.Checked ? 1 : 0;
var val = chkaddisactive.Checked ? 1 : 0;
var vall = chkboxisactive.Checked ? 1 : 0;

SqlCommand cmdd = new SqlCommand("select AccesspageID From AccessPages where pages='" + aaa + "'", con);
con.Open();

cmdd.CommandType = CommandType.Text;
string query = "add3tables"; //Stored procedure name
SqlCommand com = new SqlCommand(query, con); //creating SqlCommand object
com.CommandType = CommandType.StoredProcedure; //here we declaring command type as stored procedure

// adding parameters to SqlCommand below
com.Parameters.AddWithValue("@Name", txtaddrolename.Text.ToString());
string unique_string = Guid.NewGuid().ToString();
com.Parameters.AddWithValue("@RoleID ", unique_string);
com.Parameters.AddWithValue("@IsActive ", val);
com.Parameters.AddWithValue("@PermissionRoleID ", unique_string);
com.Parameters.AddWithValue("@View ", vaala);
com.Parameters.AddWithValue("@Edit ", vaalaa);
com.Parameters.AddWithValue("@PermissionAccessoageID ", cmdd);
com.ExecuteNonQuery();
con.Close();
}
}

Answer

You forget to use your result of the select statement and need to assign it to a variable.

In your first if block you actually execute it, but don't use the result. In your second if block you forgot the .ExecuteScalar() call.

object oAccesspageID = cmdd.ExecuteScalar();
int? AccesspageID = (int?)(!Convert.IsDBNull(oAccesspageID) ? oAccesspageID : null);
/* ... code ommitted*/

//assuming @PermissionAccessoageID is int
com.Parameters.AddWithValue("@PermissionAccessoageID ", AccesspageID.Value);

Since ExecuteScalar can return null, if no results have been found, you need to account for that in some way. You may want to add another if(AccesspageID.HasValue) { } call before invoking your SProc, to make sure it doesn't try to run with invalid values.

Comments