0

Hy Everyone! I am trying to add some yes/no values into my sql database by using some radio buttons and checkboxes. when I click the insert button it does not show any error but also does not insert data into the table. I have a DBConn class that has following code:

class DBConn
    {
        DataSet ds;
        int c;
        SqlConnection sqlconn;
        SqlCommand sqlcmd;
        public static string connectionString = @"Data Source=user-PC;Initial Catalog=RecruitmentProject;Integrated Security=True";

        public bool IUD(String query)
        {
            try
            {
                sqlconn = new SqlConnection();
                sqlconn.ConnectionString = connectionString;
                sqlconn.Open();
                sqlcmd = sqlconn.CreateCommand();
                sqlcmd.CommandText = query;
                c = sqlcmd.ExecuteNonQuery();
                sqlconn.Close();
            }
            catch (Exception ex)
            {
                //MessageBox.Show(ex.Message);
            }
            if (c > 0)
                return true;
            else
                return false;
        }


following is my code for insertion:

public void ins_Skills()
        {
            String cnic = txtCNIC.Text;
            String added = lblLoginID.Text;

            string query = "Insert INTO ComputerSkills_Table (cnic,ComputerSkills,MSOffice,MSWord,MSExcell,MSPpt,MSAccess,Typing,EnglishType,UrduType,Networking,Programming,[Database],AddedBy) VALUES (@cnic, @ComputerSkills, @MSOffice, @MSWord, @MSExcell, @MSPpt, @MSAccess, @Typing, @EnglishType, @UrduType, @Networking, @Programming, @db, @AddedBy)";
        SqlCommand cmd = new SqlCommand(query, conn);

        //Pass values to Parameters
        if(cmbComp.Text != "No")
        {
            cmd.Parameters.AddWithValue("@cnic", txtCNIC.Text);
            cmd.Parameters.AddWithValue("@ComputerSkills", rbComp.Text);
            cmd.Parameters.AddWithValue("@MSOffice", rbOffice.Text);
            cmd.Parameters.AddWithValue("@MSWord", rbWord.Text);
            cmd.Parameters.AddWithValue("@MSExcell", rbExcell.Text);
            cmd.Parameters.AddWithValue("@MSPpt", rbPpt.Text);
            cmd.Parameters.AddWithValue("@MSAccess", rbAccess.Text);
            cmd.Parameters.AddWithValue("@Typing", rbTyping.Text);
            cmd.Parameters.AddWithValue("@EnglishType", rbEngType.Text);
            cmd.Parameters.AddWithValue("@UrduType", rbUrduType.Text);
            cmd.Parameters.AddWithValue("@Networking", rbNetwork.Text);
            cmd.Parameters.AddWithValue("@Programming", rbProgram.Text);
            cmd.Parameters.AddWithValue("@db", rbDB.Text);
            cmd.Parameters.AddWithValue("@AddedBy", lblLoginID.Text);
        }

        else if(cmbComp.Text == "No")
        {
            cmd.Parameters.AddWithValue("@cnic", txtCNIC.Text);
            cmd.Parameters.AddWithValue("@ComputerSkills", "No");
            cmd.Parameters.AddWithValue("@MSOffice", "No");
            cmd.Parameters.AddWithValue("@MSWord", "No");
            cmd.Parameters.AddWithValue("@MSExcell", "No");
            cmd.Parameters.AddWithValue("@MSPpt", "No");
            cmd.Parameters.AddWithValue("@MSAccess", "No");
            cmd.Parameters.AddWithValue("@Typing", "No");
            cmd.Parameters.AddWithValue("@EnglishType", "No");
            cmd.Parameters.AddWithValue("@UrduType", "No");
            cmd.Parameters.AddWithValue("@Networking", "No");
            cmd.Parameters.AddWithValue("@Programming", "No");
            cmd.Parameters.AddWithValue("@db", "No");
            cmd.Parameters.AddWithValue("@AddedBy", lblLoginID.Text);
        }


        try
        {
            conn.Open();
            Console.WriteLine(query);
            cmd.ExecuteNonQuery();
            MessageBox.Show("Records inserted");
            //q = db.IUD(query); //DBConn Function Call
        } // try

        catch (SqlException ex)
        {
            MessageBox.Show("Records not inserted", ex.StackTrace);
        } // catch
        finally
        {
            conn.Close();
        }

now it does not show the data inserted messagebox and neither does it show the not inserted messagebox, its as if the query is not being passed or called. can someone help?


EDIT : I went through it again and realized that the function was not getting called when the Yes radiobutton was marked on the previous form. now it does show the messagebox that "Data inserted" but when i check the table there are no values inserted at all.
code for taking value from radio button is:

private void rbNetwork_CheckedChanged(object sender, EventArgs e)
        {
            network = rbNetwork.Text;
        }

and then I put the network variable with the radiobutton value in the query as the value for network column. but I think the variable has null value thats why nothing is inserted into the table and the table remains as if it was just created with only 1 row and all the columns having null value.


EDIT: (2) I debugged my code again as recommended by some friends here and this is the exception i am getting:

A first chance exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll

Atif Ali
  • 71
  • 1
  • 8
  • What happens if you step through your code via debugger? Is the line `if (q == true)` ever reached? Is an exception thrown inside your `IUD` method? Is data actually inserted into your database? – nilsK Oct 24 '19 at 07:26
  • since no message box is showing, it means you get an Exception. Your catch clause in UID method is swallowing that exception – apomene Oct 24 '19 at 07:27
  • 2
    First of all i recommend you not to build a query this way. It's not really safe. Use SqlParameters or some other safer tool instead. You can learn about it here: https://csharp-station.com/Tutorial/AdoDotNet/Lesson06 And please insert your table's spec. – turanszkik Oct 24 '19 at 07:27
  • use break point and debug your code and check on catch block catch (Exception ex) on ex whats error you are ? in your insert code – Nits Patel Oct 24 '19 at 07:34
  • i think you are not passing parameters thats why u unable to insert data – Nits Patel Oct 24 '19 at 07:36
  • I edited my question, please check it now guys – Atif Ali Oct 24 '19 at 07:51
  • Make sure you are using [parameterized queries](https://stackoverflow.com/questions/35163361/how-can-i-add-user-supplied-input-to-an-sql-statement), as this could prevent [SQL injection](https://stackoverflow.com/questions/5468425/how-do-parameterized-queries-help-against-sql-injection). This may not solve your problem but at least increase its security. – Barrosy Oct 24 '19 at 07:55
  • I debugged my code as you guys had asked and it executed smoothly without any errors or issues and even shows the data inserted messagebox but does not insert data into the table, I have made another edit to show you guys how I am trying to get value from the radio button so you guys can tell if i am doing that wrong. – Atif Ali Oct 24 '19 at 08:03
  • 1
    Debug and add breakpoint to your the line where variable **query** is set. Copy the query into SQL Management Studio. Prove that no errors exist (ie. your query syntax is properly formulated) by executing the query. – demoncrate Oct 24 '19 at 08:13
  • how will it execute in sql when there are no variables in the sql server? – Atif Ali Oct 24 '19 at 09:08
  • As Democrats told, you will get the query with all the values instead of variable names. So you can directly execute the query in the sql studio. – Ajoe Oct 24 '19 at 11:08
  • Be sure that query is right, you can execute it first, then check whether you are forming it well in a string query, debug and check what is variable query holds actually. 1 more thing try to replace your single quotes in query with double. – Shilpa Oct 24 '19 at 19:15
  • I debugged my code step by step and i am getting this exception: A first chance exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll – Atif Ali Oct 25 '19 at 05:00
  • @turanszkik I have replace my query with parameterized query and it is giving the first chance exception, – Atif Ali Oct 25 '19 at 07:59
  • @AtifAli According to the link below, you must have forgotten to add some of the parameters to `sqlcmd`. You have to define the SqlParemeter's Id and the type of it, add to sqlcmd then you have to add a value to it. `cmd.Parameters.Add(new SqlParameter("@cid", SqlDbType.Int))` `cmd.Parameters("@cid").Value = Idvalue` [link](https://stackoverflow.com/q/13340890/10708630) – turanszkik Oct 25 '19 at 08:25
  • @AtifAli Please refresh your example source code (with parameterized version). Without it I can only guess what the problem can be. – turanszkik Oct 25 '19 at 08:28
  • @turanszkik refreshed my code – Atif Ali Oct 28 '19 at 05:30
  • @AtifAli I'm not really experienced with direct insertion to the database, my first question is what is that `[Database]` and what is it for in your query. And I think your exception can be contain more information about the problem. When you debugging it move your cursor over the `Exception` when you getting it. Than in the details of it you can find an `InnerException` field. I would try to dig down/in as much as I can for more information. – turanszkik Oct 28 '19 at 08:04
  • I''ll try that @turanszkik – Atif Ali Oct 29 '19 at 04:43

0 Answers0