Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
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
v2
Comments
Karthik. A at 3-Jan-13 16:07pm
   
Okay, so what's the error ?
Member 9693583 at 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 at 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 at 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 at 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 at 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 at 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 at 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
0 OriginalGriff 280
1 Jochen Arndt 150
2 Kornfeld Eliyahu Peter 120
3 PIEBALDconsult 110
4 Richard MacCutchan 105
0 OriginalGriff 6,055
1 DamithSL 4,621
2 Maciej Los 4,087
3 Kornfeld Eliyahu Peter 3,500
4 Sergey Alexandrovich Kryukov 3,294


Advertise | Privacy | Mobile
Web02 | 2.8.141220.1 | Last Updated 3 Jan 2013
Copyright © CodeProject, 1999-2014
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