Click here to Skip to main content
15,906,567 members
Please Sign up or sign in to vote.
1.00/5 (4 votes)
See more:
C#
SqlConnection cnn = new SqlConnection("Data Source=HAIER-PC;Initial Catalog=casemanegement;Integrated Security=True");
String sql = null;
sql = "insert into caseProfile ([file_number], [title_of_case], [nature_of_case], [district_id], [court_id], [institution_date], [appeal_number], [matter_in_dispute], [brief_on_case], [department_recomendation], [section_id]) values(@fn,@toc,@noc,@di,@ci,@id,@an,@mid,@boc,@dr,@si)";
warningmessage.Text = " Connection open hogaya hy";
using (cnn)
{
    try
    {
        cnn.Open();
        int di = 0;
        int ci = 0;
        int si = 0;
        loginform ln = new loginform();
        SqlCommand dis = new SqlCommand("Select district_id from District where (DistrictName='" + district.SelectedItem.ToString() + "'", cnn);
        SqlDataReader disr = dis.ExecuteReader();
        if (disr.HasRows)
        {
            while (disr.Read())
            {
                di = Int32.Parse(disr["district_id"].ToString());
                warningmessage.Text = "districtid::::::" + di;
            }

        }
        SqlCommand cid = new SqlCommand("Select court_id from courtName where (court_name='" + courtName.SelectedText + "'", cnn);
        SqlDataReader csr = cid.ExecuteReader();
        while (csr.Read())
            ci = Convert.ToInt32(csr["court_id"]);

        SqlCommand sid = new SqlCommand("Select section_id from login where (user_id='" + ln.textBox1.Text + "'", cnn);
        SqlDataReader ssr = sid.ExecuteReader();
        while (ssr.Read())
            si = Convert.ToInt32(ssr["section_id"]);
        //warningmessage.Text = "Testing  ::::::";
        using (SqlCommand cmd = new SqlCommand(sql, cnn))
        {
            cmd.Parameters.Add("@fn", SqlDbType.NVarChar).Value = fileNumber.Text;
            cmd.Parameters.Add("@toc", SqlDbType.NVarChar).Value = titleOfCase.Text;
            cmd.Parameters.Add("@noc", SqlDbType.NVarChar).Value = natureOfCase.Text;
            cmd.Parameters.Add("@di", SqlDbType.Int).Value = di;
            cmd.Parameters.Add("@ci", SqlDbType.Int).Value = ci;
            cmd.Parameters.Add("@id", SqlDbType.Date).Value = institutionDate.Text;
            cmd.Parameters.Add("@an", SqlDbType.NVarChar).Value = appealNumber.Text;
            cmd.Parameters.Add("@mid", SqlDbType.NVarChar).Value = matterInDispute.Text;
            cmd.Parameters.Add("@boc", SqlDbType.NVarChar).Value = briefOnCase.Text;
            cmd.Parameters.Add("@dr", SqlDbType.NVarChar).Value = departmentRecomendation.Text;
            cmd.Parameters.Add("@si", SqlDbType.Int).Value = si;
            int rowAdded = cmd.ExecuteNonQuery();
            if (rowAdded > 0)
                warningmessage.Text = "Record Inserted::::::" + rowAdded;
            else
                warningmessage.Text = "No Record Inserted::::::" + rowAdded;
        }
    }
    catch (Exception ex)
    {
        warningmessage.Text = "Error:" + ex.Message;
    }
}


What I have tried:

Always shows error... Kindly fix it
Posted
Updated 16-Oct-19 13:24pm
v2
Comments
RickZeeland 16-Oct-19 6:51am    
And what is the exact error message ?
ZurdoDev 16-Oct-19 7:03am    
And how can we fix it?
srko 17-Oct-19 4:33am    
Please add the error so that we can exactly guide you on this.

1 solution

Don't do it like that.
You have code there - your INSERT code - that uses parameterized queries. So do everything else so badly? Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead.

When you concatenate strings, you cause problems because SQL receives commands like:
SQL
SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'
The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:
SQL
SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'
Which SQL sees as three separate commands:
SQL
SELECT * FROM MyTable WHERE StreetAddress = 'x';
A perfectly valid SELECT
SQL
DROP TABLE MyTable;
A perfectly valid "delete the table" command
SQL
--'
And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.

So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?

And then work out just why you are opening an SqlDataReader on a connection then trying to INSERT without closing the reader first? That won't work! And why are you doing that reader code anyway - it's the least efficient way to get the last value and won't necessarily give you the right result anyway ... particularly once you get out of dev and into production where strange intermittent problems will result in a badly mangled database that is a horrorshow to fix.
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900