Click here to Skip to main content
12,067,351 members (55,116 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: SQL-server-2005 C# ASP.NET , +
Hi,

I am trying below code to display Complaint_no from Call_reg table as well as tech_name from new_tech table from database and after selecting complaint no. from dropdownlist, i want to choose technician name from another dropdownlist and then on button click to be inserted in Call_Allocation table

On button click i am unable to write query for inserting the same.

Error code mentioned as bold.

Can somebody help me please....


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)
    {
        SqlConnection con = new SqlConnection("SERVER=KSHITIJA-PC; Initial Catalog=CRM;Integrated Security=True");
        //SqlCommand cmd = new SqlCommand("Update Call_reg set status='Assigned' Where Complaint_no= '" + ddlComplaint.SelectedItem.ToString() + "'", con);
        SqlCommand cmd = new SqlCommand(" insert into Call_Allocation values('select a.Fname from New_Tech a join Call_reg b on a.Fname=b.Complaint_no '", con);
        try
        {
            con.Open();
 
            int i = cmd.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);
 
        }
        con.Close();
    }
Posted 3-Jan-13 11:01am
Edited 3-Jan-13 11:07am
Karthik. A37.7K
v2
Comments
Karthik. A 3-Jan-13 16:07pm
   
Okay, so what's the error ?
Member 9693583 3-Jan-13 16:15pm
   
There dont have any error but data has to be inserted on button click. there is something wrong in this query but i dont understand what it is?

insert into Call_Allocation values('select a.Fname from New_Tech a join Call_reg b on a.Fname=b.Complaint_no '", con);

Or i am using join instead of subquery... is that a problem
pls help me

thanks n regards,
kshitija
Karthik. A 3-Jan-13 16:51pm
   
Select statement w/in single quotes seems wrong, if that's not what you want to insert. Why don't you try running the query in ms sql server before using it in your program ?

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

You're putting your select statement in quotes, so your sql is passing SQL as a string as a value to insert. That is not what you want, surely.
  Permalink  
Comments
Member 9693583 3-Jan-13 16:55pm
   
Hi, Thanks for reply...

Actually I want run this type of query

SqlCommand cmd = new SqlCommand(" insert into Call_Allocation values((select '" + ddlAllow.SelectedItem.ToString() + "' where Complaint_no= '" + ddlComplaint.SelectedItem.ToString() + "' ))", con);

Error occured : Subqueries are not allowed in this context. Only scalar expressions are allowed


& one more thing i dont have Tech_name field in Call_Reg table but it is there in Tech_Allocation table & i want to be inserted in Call_updation table
Christian Graus 3-Jan-13 17:14pm
   
It looks very much to my like my prior advice was correct. You need to do this in a stored proc. Why are you still trying to do this, and yet don't care enough to try to follow the advice you were given ?
Member 9693583 3-Jan-13 17:23pm
   
Thanks for advice... but this is not the case actually i dint use store proc. yet. thats y i m neglecting or something... i m student and this is my collage project & have to submit by tommarow so trying wht i know but there is also problem bad luck.. :(
Christian Graus 3-Jan-13 17:28pm
   
That's correct. You need to use a stored proc, you can't do this in inline SQL. That's what the error message means. So, make it a proc and it will work.
Member 9693583 3-Jan-13 17:34pm
   
Thanks... Will Try it..

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web03 | 2.8.160208.1 | Last Updated 3 Jan 2013
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100