How to edit and delete in sql server

ma ouml

9y1k

Hi friends

I hope you are well

I have a problem is that I had a table named authorization in this structure

create table autorisation

(

Id_autorisation int primary key identity NOT NULL,

Id_user int foreign key references utilisateur (Id_user),

Id_site int foreign key references site (Id_site),

Id_profil int foreign key references profil (Id_profil)

)

In visual studio I have this interface

And in the display I two not display Id_autorization and when I want to click on the modifier

it does not work with this request Program.cmd.CommandText = "authorization

update

in Id_site = (select top 1 Id_site site from the site where Libelle_site = '" + comboBox2.Text + "'),

 Id_user = (select top 1 utilisateur User_name = '"+ comboBox1. Text +"'),

Id_profil = (select top 1 Id_profil from profil where Libelle_profile = '"+ comboBox3.Text +"')

where Id_user = (select top 1 utilisateur User_name = @par1)"; Program.cmd.Parameters.AddWithValue("@par1", comboBox1.Text);
Program.cmd.ExecuteNonQuery (); 
the same problem in delete 
Program.cmd.CommandText = "delete from autorisation where Id_user = (select top 1 utilisateur User_name = @par1)"; Program.cmd.Parameters.AddWithValue("@par1", comboBox1.Text);
Program.cmd.Parameters.AddWithValue("@del", comboBox1.Text); 
Program.cmd.ExecuteNonQuery();
In short how to do the deletion and modification without writing in the clause where Id_autorisation = ....
And thanks in advance friends

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

ma ouml9y ago
The more the teacher we are in the process of filling the comboBox with the values string for example on the link of Id_user I fill the combobox with User_name more it is not the problem
The problem is in the clause where
This is when I wanted to modify instead of modifying the base of Id_authorisation of two-Modified using another choice using the other's help I found a comment on the table Authorisation. I replied using User Name but I do not know how to do
Please can you help me to resolve problem
Nilesh Shah9y ago
then your query is not able to get the user id

I mean
select top 1 Id_user from utilisateur where User_name = @par1

this query does not return anything

instead of using so many subqueries, why can't you populate the combobox with key-value pairs i.e. the text is user name and the value is Id_user

this way you will not have to use subquery and you can directly use comboBox3.SelectedValue
ma ouml9y ago
Is there is another method for editing and deleting without working with Id_autorisation
ma ouml9y ago
The problem is that nothing updated and nothing is deleted
Nilesh Shah9y ago
what happens when you run it? what error is thrown?
ma ouml9y ago
table name "autorisation "
and my quries is :
update ===>
Program.cmd.CommandText="update autorisation set Id_site = (select top 1 Id_site from site where Libelle_site = '"+comboBox2.Text+"') , Id_user = (select top 1 Id_user from utilisateur where User_name = '"+comboBox1.Text+"') , Id_profil = (select top 1 Id_profil from profil where Libelle_profil = '"+comboBox3.Text+"') where Id_user = (select top 1 Id_user from utilisateur where User_name = @par1)";
Program.cmd.Parametrers.AddWithValue("@par1",comboBox1.Text);
Program.cmd.ExecuteNonQuery();

delete ====>
Program.cmd.CommandText = "delete from autorisation where Id_user = (select top 1 Id_user from utilisateur where User_name = @par2)";
Program.cmd.Parametrers.AddWithValue("@par2",comboBox1.Text);
Program.cmd.ExecuteNonQuery();
Nilesh Shah9y ago
There are obvious mistkaes in your code.

First you used different table names
In this line:
"it does not work with this request Program.cmd.CommandText = "authorization"
and in this line"
"Program.cmd.CommandText = "delete from autorisation where"
What is your table name? authorization or autorisation ?

Second, your queries are incorrect.

Third, you wrote:
"Program.cmd.Parameters.AddWithValue("@del", comboBox1.Text);"
But there is no @del parameter in your query