Check database with multiline textbox values

Question

0.00/5 (No votes)

See more:

Hello Friends,
i am stuck with a question where i have two tables client and company.
company table has column id and company
client has three column client_id , client_name and company_id

on page i have two text box one says company and other is client , textbox which takes values for client is multiline textbox (which is a need here).

everything is done, insert is working fine, delete is working fine but while updating i have question how to delete those client which are stored in the table client but after updating it they should not be there.

C#

public void isclientpresent(int selectecustomer, string clientname)
 {
     SqlConnection con = new SqlConnection(@"Data Source=AASHISH\SQLEXPRESS;Initial Catalog=customerdb;Integrated Security=True");
     con.Open();
     SqlCommand cmd = new SqlCommand("Select * from client where company_id='"+selectecustomer+"'and client_name='"+clientname+"'", con);
     SqlDataAdapter sqlda = new SqlDataAdapter(cmd);
     DataSet sqlds = new DataSet();
     sqlda.Fill(sqlds);
     con.Close();

     if (sqlds.Tables[0].Rows.Count > 0)
     {
         //
     }
     else
     {
         insertclient(selectecustomer, clientname);
     }

 }



protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
 {
     int selectedcustomer = Convert.ToInt16(GridView1.Rows[e.RowIndex].Cells[3].Text);

     string[] allLines = txtclients.Text.Split('\n');
     foreach (string text in allLines)
     {
         isclientpresent(selectedcustomer, text);
     }

 }

 public void insertclient(int selectedcompany, string selectedclient)
 {
     SqlConnection con = new SqlConnection(@"Data Source=AASHISH\SQLEXPRESS;Initial Catalog=customerdb;Integrated Security=True");
     con.Open();
     SqlCommand cmd = new SqlCommand("Insert into client (client_name,company_id) VALUES('" + selectedclient+ "',"+selectedcompany+")", con);
     cmd.ExecuteNonQuery();
     con.Close();

 }

What I have tried:

When i select update GridView1_RowUpdating is shooted and one by one make to database to check if they exist there or not . if not then they call insert query.

but what if value is there is database but not sent from array to check .

Can anyone help me on this

Posted 15-Aug-16 6:31am

niceaashish

Updated 17-Aug-16 7:11am

Add a Solution

Comments

Richard Deeming 15-Aug-16 13:42pm

Your code is vulnerable to SQL Injection[^]. NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]

Vincent Maverick Durano 17-Aug-16 12:59pm

adding to SQL Injection, I would also suggest you to always use the "using block" when dealing with objects that eat resources, such as: SqlConnection and SqlCommand

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Vincent Maverick Durano · Answer 1 · 2016-08-17T07:11:00

Quote:
delete those client which are stored in the table client but after updating it they should not be there.

You just need to re-bind your GridView to reflect the changes you've made. So when you Update or Delete data, do something like this:

C#

private void BindGrid(){          
      using (SqlConnection sqlConn = new SqlConnection
            (ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString)){
                string sql = "SELECT * FROM YourTableName";
                using(SqlCommand sqlCmd = new SqlCommand(sql,sqlConn)){
                    sqlConn.Open();
                    using(SqlDataAdapter sqlAdapter = new SqlDataAdapter(sqlCmd)){
                        sqlAdapter.Fill(dt);
                    }
                }
            }

            if(dt.Rows.Count > 0){
                GridView1.DataSource = dt;
                GridView1.DataBind();
            } 
}

then at RowUpdating and RowDeleting events, you simply call the method BindGrid() to refresh your Grid with the changes:

C#

protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e){
   //Your existing code
   BindGrid();
}

Please do take note of the following:

(1) Store your connection string in your web.config, rather than hard-coding it in your code behind.

(2) Always use Parameter Queries when passing parameters to your SQL query. Here's another reference that you may want to look at: Protect Your Data: Prevent SQL Injection[^]

(3) Remember to use the "Using Block" when dealing with objects that eat resources, such as: SqlConnection and SqlCommand