How to Add edit & Del To This Project

Question

hi guy's ...
this project is a Log In user with permissions .

How to Add edit & Del To This Project --> Admin

How to Add edit : username & pass To This Project --> user

thank's

this code : http://s5.picofile.com/file/8141479300/User_Login.rar.html

Wim Sturkenboom · Answer

For your ADD method I've used the same approach as above to add the parameters. The first change was in the Execute method in the DataAccess class. You can add the below method to the DataAccess class. /// /// execute a nonquery sql statement with parameters /// /// sql statement /// required parameters pass null if no parameters are required public void Execute(string SQL Hashtable htParams) { com.CommandText = SQL; if (htParams != null) { foreach (DictionaryEntry de in htParams) { com.Parameters.AddWithValue(de.Key.ToString() de.Value); } } com.ExecuteNonQuery(); } The method now takes an additional parameter to pass the parameters. Similar as for your select I loop through them to add them to the SqlCommand. Next I modified the ADD method in Users.cs //public void ADD() public bool ADD(out string errmsg) { errmsg = ; try { DA.Connect(); string sql = Insert Into [User] (IDNamPasMnuStrBtn1Btn2); //sql += Values ({0}'{1}''{2}''{3}''{4}''{5}''{6}'); sql += Values (@pID@pNam@pPas@pMnu@pStr@pBtn1@pBtn2); //sql = string.Format(sql this.ID this.Nam this.Pas this.Mnu this.Str this.Btn1 this.Btn2); Hashtable htParams = new Hashtable(); htParams.Add(@pID this.ID); htParams.Add(@pNam this.Nam); htParams.Add(@pPas this.Pas); htParams.Add(@pMnu this.Mnu); htParams.Add(@pStr this.Str); htParams.Add(@pBtn1 this.Btn1); htParams.Add(@pBtn2 this.Btn2); DA.Execute(sql htParams); } catch (SqlException sex) // sql specific errors { // the error errmsg = sex.Message; // log to file; for you to implement // user friendly message; if you don't log to a file comment the below line out errmsg = An database error occured; return false; } catch (Exception ex) // other errors that may occur and throw exception { // the error errmsg = ex.Message; // log to file; for you to implement // user friendly message; if you don't log to a file comment the below line out errmsg = An error occured; return false; } finally { DA.Disconnect(); } return true; } The method now returns a bool to indicate if the action was successful or not and it takes an output message as a parameters. The complete method's content is 'embedded' in a try/catch. Note the addition of a 'finally' that takes care of the disconnect. In the last step I've modified button2_Click in Form3.cs to cater for the new ADD method. private void button2_Click(object sender EventArgs e) { string errmsg; Users us = new Users(); us.ID = Convert.ToInt32(textBox1.Text); us.Nam = textBox2.Text; us.Pas = textBox3.Text; us.Mnu = Convert.ToBoolean(comboBox1.Text); us.Str = Convert.ToBoolean(comboBox2.Text); us.Btn1 = Convert.ToBoolean(comboBox3.Text); us.Btn2 = Convert.ToBoolean(comboBox4.Text); if (us.ADD(out errmsg) == false) { MessageBox.Show(errmsg); } else { Form3_Load(null null); MessageBox.Show(add new user); } } I think you now have enough ammunition to do the DEL and EDT things yourself. My database table only contained the fields ID Nam and Pas and the code was tested against that using some modification on the above shown code. NOTE: I forgot to mention in the previous post that hashtables live in the namespace System.Collections. So you need to add e.g. a using to every file where they are used. e.g. like below using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data; using System.Data.SqlClient; using System.Collections;

Wim Sturkenboom · Answer

As you seem to have taken my advice about parameterized queries here is the first example. This also demonstrates some other techniques related to error reporting. /// /// execute a parameterized query /// /// sql statement /// parameters; if query does not require parameters set to null /// return erro message /// null on error else datatable with result public DataTable SELECT(string SQL Hashtable htParams out string errmsg) { errmsg = ; try { DataTable dt = new DataTable(); // set the query com.CommandText = SQL; // set the parameters if (htParams != null) { foreach (DictionaryEntry de in htParams) { com.Parameters.AddWithValue(de.Key.ToString() de.Value); } } // fill the datatable da.Fill(dt); // return the result return dt; } catch (SqlException sex) // sql specific errors { // the error errmsg = sex.Message; // log to file; for you to implement // user friendly message; if you don't log to a file comment the below line out errmsg = An database error occured; return null; } catch (Exception ex) // other errors that may occur and throw exception { // the error errmsg = ex.Message; // log to file; for you to implement // user friendly message; if you don't log to a file comment the below line out errmsg = An error occured; return null; } } The above is an extension on your select method in the DataAccess class. You can add it to the DataAccess class. It takes two additional parameters. The first additional parameter is a hashtable that is used to pass parameters. A hashtable contains a set of entries with a key and a value. The name is the name of a parameter (e.g. @pID) and the value is the value that I want to pass (e.g. 1). The code loops through the entries and adds the parameters to the SqlCommand. I'm very fond of a hashtable for this as it allows to pass a variable number of parameters so it does not really matter how many parameters the sql statement requires. The second additional parameter is an output parameter that is used to return error messages. I've 'embedded' the code in a try/catch to catch possible errors. Using try/catch makes your code more solid and prevents the end user from seeing errors that they don't understand. If you don't implement a logging to file comment out the lines that create the user friendly error message. There are two catches; the first one catches sql specific errors (just because this relates to SQL) and the second one any other exceptions that might occur. Next I started running through your code and made a few changes in Users.cs (method Login) to demonstrate how you can pass the parameters. public bool? Login(string Name string Pass out string errmsg) { DA.Connect(); //string sql = Select Count(*) From [User] Where Nam = '{0}' And Pas = '{1}'; string sql = Select Count(*) From [User] Where Nam = @pNam And Pas = @pPas; //sql = string.Format(sql Name Pass); Hashtable htParams = new Hashtable(); htParams.Add(@pNam Name); htParams.Add(@pPas Pass); DataTable dt = new DataTable(); dt = DA.SELECT(sql htParams out errmsg); DA.Disconnect(); // something went wrong if (dt == null) { return null; } bool Enter = false; if (dt.Rows[0][0].ToString() == 1) { Enter = true; } return Enter; } Note the use of bool? instead of bool in the method's declaration; this allows us to return null. The method also has an additional parameter to pass a possible error message. I've commented out some of your statements and replaced them with mine; this might make it easier for you to follow the changes. The first change is the select statement that now takes parameters. The second change is the creation of a hashtable and adding the parameters to it. the third change is to use the new method that we've created in DataAccess. As it's useless to have errors and don't check for them and log/report the error I've added a check ( if (dt == null )). If there is an error we 'stop' (return) The last change is in the button2_Click method to cater for the changes in earlier Login method. private void button2_Click(object sender EventArgs e) { // this can hold a possible error message string errmsg; Users us = new Users(); // check if there is a valid login bool? rc = us.Login(textBox2.Text textBox1.Text out errmsg); if (rc == null) { MessageBox.Show(errmsg); } else { //if (us.Login(textBox2.Text textBox1.Text out errmsg) == true) if (rc == true) { us.Sath(textBox2.Text); this.Hide(); Form2 f2 = new Form2(); f2.Show(); } else MessageBox.Show(unvalid pass); } } First of all we need to define a variable to hold possible error messages. Note the use of bool? instead of bool (again). We will check if it's null and display an error message if there is an error. I hope that this explains how to use parameterized queries. Compile and run there should not be a difference in behaviour to your original code. In the next step delete your original select method in DataAccess compile and fix all errors. If a query does not need parameters pass null for htParameters. The next post will contain an updated ADD method to make use of parameterized queries.

Wim Sturkenboom · Answer

For the format exception I have assumed that the first column of your datagrid contained the ID. As that is not the case the code will not work. Change the index to 6 (the last column) or to the name of the column (possibly ID). us.ID = Convert.ToInt32(dr.Cells[6].toString()); or us.ID = Convert.ToInt32(dr.Cells[ID].toString()); I will see if I have time to look at the SQL issue. Does that happen in both codes (EDT and DEL) or only in one specific one? One note: please don't use screenshots; copy and paste code and errors into your posts. I prefer to see them with a courier new font or a different color so they are easily recognizable.

ghasem deh · Answer

hi ... i can not set Key --> edit & del ! please send full Code ... thank you

Wim Sturkenboom · Answer

I'm not sure how you see that this works. I think this is how you want it to work: Edit User clicks edit button Data from selected row in datagrid is copied to textboxes etc above it. Data is modified User clicks set to save data in database I'll leave the implementation of button1_Click to you; you can have a look at button3_Click lower down. private void button1_Click(object sender EventArgs e) { // copy from datarow to edit 'window' } private void button2_Click(object sender EventArgs e) { Users us = new Users(); us.ID = Convert.ToInt32(textBox1.Text); us.Nam = textBox2.Text; us.Pas = textBox3.Text; us.Mnu = Convert.ToBoolean(comboBox1.Text); us.Str = Convert.ToBoolean(comboBox2.Text); us.Btn1 = Convert.ToBoolean(comboBox3.Text); us.Btn2 = Convert.ToBoolean(comboBox4.Text); if (us.ID == 0) { us.ADD(); Form3_Load(null null); MessageBox.Show(add new user); } else { us.EDT(); Form3_Load(null null); MessageBox.Show(update user); } } The above assumes 0 is an invalid ID (e.g. when the ID column is an autoincrement column. Delete User clicks delete button Selected datarow in datagrid is deleted private void button3_Click(object sender EventArgs e) { Users us = new Users(); // get ID from datarow in datagrid DataGridViewRow dr = dataGridView1.CurrentRow; us.ID = Convert.ToInt32(dr.Cells[0].ToString()); // alternatively copy from datarow to edit 'window' //us.ID = Convert.ToInt32(textBox1.Text); us.DEL(); Form3_Load(null null); MessageBox.Show(delete user); } Some advise Use decent names for your controls that describe their functions or what they store. Call a delete button e.g. btnDel instead of button3 and a textbox that stores a name txtName instead of textBox2. And if you need support from forums where the main language is English please use English names ;) The same applies to the methods for the click events. This will happen automatically if you give the button a decent name before you create the click event. Your query to add a user is prone to sql injection; it implies if somebody enters e.g. a specially crafted name all records can be deleted or tables can be dropped. Read up on parameter queries and sql injection. I hope the above helps you on the way. You need to finetune it further (e.g. add a 'new' button to clear the edit 'window' and set ID to 0) Note that the above code snippets are not tested.

ghasem deh · Answer

hi ... I can not do anything The error is : http://s5.picofile.com/file/8141787718/read.png http://s5.picofile.com/file/8141787900/U003ntitled.png http://s5.picofile.com/file/8141787968/Unerrtitled.png http://s5.picofile.com/file/8141787992/Untitlerr2ed.png

Wim Sturkenboom · Answer

What do you mean by 'I can not set the key --> edit & del'?