Comments by Member 9693583 (Top 20 by date)

Yes I want to do this only bt having problem in procedure... error occured that procedure Allocation_call not found.. even though i was created it.

See my front end coding.... actually i want to select complaint no. from 1 dropdownlist(which comes from databse only) & Tech_name from another dropdownlist(which comes from database only) and insert the particular record of selected complaint no. in along with Tech_name in another table on button click

Code for ur reference


SqlConnection con;
SqlCommand cmd;
SqlDataReader dr;
private string s;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
ddlComplaint.Items.Insert(0, new ListItem("---Select---", "---Select---"));
FillDropDownList();
ddlAllow.Items.Insert(0, new ListItem("---Select---", "---Select---"));
FillTechnicianDropDownList();
}
}




// Fill Dropdownlist
public void FillDropDownList()
{
s = WebConfigurationManager.ConnectionStrings["Callregister_connectionstring"].ConnectionString;
con = new SqlConnection(s);
con.Open();
cmd = new SqlCommand("Select Complaint_no from Call_Reg where Status='Open'" , con);
dr = cmd.ExecuteReader();
while (dr.Read())
{
ddlComplaint.Items.Add(dr[0].ToString());
}
dr.Close();
con.Close();
}

// Show data in GridView
protected void btnSearch_Click(object sender, EventArgs e)
{
s = WebConfigurationManager.ConnectionStrings["Callregister_connectionstring"].ConnectionString;
con = new SqlConnection(s);
con.Open();
cmd = new SqlCommand("Select * from Call_Reg where Complaint_no='" + ddlComplaint.SelectedItem.ToString() + "'", con);
dr = cmd.ExecuteReader();
GridView2.DataSource = dr;
GridView2.DataBind();
dr.Close();
con.Close();
}

// Fill Technician
public void FillTechnicianDropDownList()
{
s = WebConfigurationManager.ConnectionStrings["Callregister_connectionstring"].ConnectionString;
con = new SqlConnection(s);
con.Open();
cmd = new SqlCommand("Select Fname from New_Tech", con);
dr = cmd.ExecuteReader();
while (dr.Read())
{
ddlAllow.Items.Add(dr[0].ToString());
}
dr.Close();
con.Close();
}

protected void btnAllo_Click(object sender, EventArgs e)
{
string var_comp_no;
string var_Tech_name;
var_comp_no = ddlComplaint.SelectedValue;
var_Tech_name=ddlAllow.SelectedValue;
SqlCommand cmd_insert = new SqlCommand();
SqlConnection con = new SqlConnection("SERVER=KSHITIJA-PC; Initial Catalog=CRM;Integrated Security=True");
cmd_insert.Connection = con;

cmd_insert.CommandType = CommandType.StoredProcedure;
cmd_insert.CommandText = "Allocate_Call";

cmd_insert.Parameters.Add(new SqlParameter("@comp_no", SqlDbType.Int));
cmd_insert.Parameters["@comp_no"].Value = var_comp_no; ;

cmd_insert.Parameters.Add(new SqlParameter("@Tech_name", SqlDbType.VarChar, 50));
cmd_insert.Parameters["@Tech_name"].Value = var_Tech_name;

try
{
con.Open();

int i = cmd_insert.ExecuteNonQuery(); ;
//int a = cmd1.ExecuteNonQuery();
if (i == 1)
{
ddlComplaint.SelectedItem.Text = "";
EO.Web.MsgBoxButton mb = new EO.Web.MsgBoxButton("OK");
MsgBox1.Show("Message: ", "Call Assigned to Technician", null, mb);
}

}
catch (Exception ex)
{
EO.Web.MsgBoxButton mb = new EO.Web.MsgBoxButton("OK");
MsgBox1.Show("Error: ", ex.Message, null, mb);

}
finally
{
con.Close();
}
}


& Code of stored procedure
CREATE PROCEDURE Alloc

is this case working when complaint_no & Tech_name selected from dropdownlist (Which is also comes from database only)

hi, See my front end coding.... actually i want to select complaint no. from 1 dropdownlist(which comes from databse only) & Tech_name from another dropdownlist(which comes from database only) and insert the particular record of selected complaint no. in along with Tech_name in another table on button click

Code for ur reference
SqlConnection con;
SqlCommand cmd;
SqlDataReader dr;
private string s;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
ddlComplaint.Items.Insert(0, new ListItem("---Select---", "---Select---"));
FillDropDownList();
ddlAllow.Items.Insert(0, new ListItem("---Select---", "---Select---"));
FillTechnicianDropDownList();
}
}




// Fill Dropdownlist
public void FillDropDownList()
{
s = WebConfigurationManager.ConnectionStrings["Callregister_connectionstring"].ConnectionString;
con = new SqlConnection(s);
con.Open();
cmd = new SqlCommand("Select Complaint_no from Call_Reg where Status='Open'" , con);
dr = cmd.ExecuteReader();
while (dr.Read())
{
ddlComplaint.Items.Add(dr[0].ToString());
}
dr.Close();
con.Close();
}

// Show data in GridView
protected void btnSearch_Click(object sender, EventArgs e)
{
s = WebConfigurationManager.ConnectionStrings["Callregister_connectionstring"].ConnectionString;
con = new SqlConnection(s);
con.Open();
cmd = new SqlCommand("Select * from Call_Reg where Complaint_no='" + ddlComplaint.SelectedItem.ToString() + "'", con);
dr = cmd.ExecuteReader();
GridView2.DataSource = dr;
GridView2.DataBind();
dr.Close();
con.Close();
}

// Fill Technician
public void FillTechnicianDropDownList()
{
s = WebConfigurationManager.ConnectionStrings["Callregister_connectionstring"].ConnectionString;
con = new SqlConnection(s);
con.Open();
cmd = new SqlCommand("Select Fname from New_Tech", con);
dr = cmd.ExecuteReader();
while (dr.Read())
{
ddlAllow.Items.Add(dr[0].ToString());
}
dr.Close();
con.Close();
}

protected void btnAllo_Click(object sender, EventArgs e)
{
string var_comp_no;
string var_Tech_name;
var_comp_no = ddlComplaint.SelectedValue;
var_Tech_name=ddlAllow.SelectedValue;
SqlCommand cmd_insert = new SqlCommand();
SqlConnection con = new SqlConnection("SERVER=KSHITIJA-PC; Initial Catalog=CRM;Integrated Security=True");
cmd_insert.Connection = con;

cmd_insert.CommandType = CommandType.StoredProcedure;
cmd_insert.CommandText = "Allocate_Call";

cmd_insert.Parameters.Add(new SqlParameter("@comp_no", SqlDbType.Int));
cmd_insert.Parameters["@comp_no"].Value = var_comp_no; ;

cmd_insert.Parameters.Add(new SqlParameter("@Tech_name", SqlDbType.VarChar, 50));
cmd_insert.Parameters["@Tech_name"].Value = var_Tech_name;

try
{
con.Open();

int i = cmd_insert.ExecuteNonQuery(); ;
//int a = cmd1.ExecuteNonQuery();
if (i == 1)
{
ddlComplaint.SelectedItem.Text = "";
EO.Web.MsgBoxButton mb = new EO.Web.MsgBoxButton("OK");
MsgBox1.Show("Message: ", "Call Assigned to Technician", null, mb);
}

}
catch (Exception ex)
{
EO.Web.MsgBoxButton mb = new EO.Web.MsgBoxButton("OK");
MsgBox1.Show("Error: ", ex.Message, null, mb);

}
finally
{
con.Close();
}
}


& Code of stored procedure
CREATE PROCEDURE Allocate_call
AS
BEGIN
Declare @comp_no as numeric(10)
Declare @Tech_name as varchar(50)

select @comp_no = Complaint_no from Call_Reg

Thanks for ur suggestion. its working when we parse the stored procedure but its not working while executing query