trouble with form in C#

[adam]

Hi there
I've got a littel problem. I've samo form which should add data to database.
Everythinks goes OK, but I'd like to have some alert messege when samobody
would like to add another user with the same nick. Now my aplication in that
case goes down.
This is code

void Button1_Click(object sender, EventArgs e) { SqlConnection c =
baza_danych.Polacz();
SqlCommand cmd = new SqlCommand("insert into klient
(IDklienta,Nazwisko,Imie,Adres,Miasto,Haslo) values ( '"+ IDklienta.Text +"'
,'" + Nazwisko.Text + "','" + Imie.Text + "','" + Adres.Text + "','" +
Miasto.Text + "','" + Haslo.Text + "')", c);
SqlDataReader dr = cmd.ExecuteReader();
bool test; test=dr.HasRows; if (test==true) { dr.Read(); Label1.Text =
"choose another nick"; } else { Label1.Text = "You are added to database"; }
dr.Close();
c.Close(); }

TIA
Adam

 

[Reidar Husmo]

Without knowing the design of your datatable I cannot be sure, but a pretty
safe bet is that you get an SqlException because a duplicate row (nick is
the unique column?) already exists.
There are a number of ways to solve this.
1) Handle the exception - You should always have exception handlers, but
they should not be used to handle expected errors, but exceptional
circumstances.
2) Rewrite the code, using a stored procedure. The stored procedure will
typically do a query and only insert when needed. With your code you are
very vulnerable to Sql injection attacks.
3) Do a query for the nick first. Only insert if it does not already exist.
The third option is possibly quickest, the second is by far the best in
terms of security, transactions and performance. And to make your life even
easier, check out the data application block (from
www.microsoft.com/patterns, if I remember correctly, but also from
gotdotnet)

Reidar Husmo

 

[adam]

Thanks for a reply, but I'm just starting witch asp.net and I'haven't got
any idea how to corect my project. Would you mind if I send you my *.asp
file?
If not, give me your email adress
TIA
Adam

 

[MWells]

Adam,

Assuming that IDklienta is your nick (nickname? means username?), the
easiest approach is to just put a unique constraint on the
dbo.klient.IDklienta column. This ensures that any attempt to insert a new
row with the same value in that column will fail.

In your C#, code, you can catch the failure using try/catch

[PSEUDOCODE]

....
Label1.Text = ""; // clear the error

SqlConnection c = baza_danych.Polacz();

SqlCommand cmd = new SqlCommand("insert into klient
(IDklienta,Nazwisko,Imie,Adres,Miasto,Haslo) values ( '"+ IDklienta.Text +"'
,'" + Nazwisko.Text + "','" + Imie.Text + "','" + Adres.Text + "','" +
Miasto.Text + "','" + Haslo.Text + "')", c);

try
{
// attempt your insert here
cmd.ExecuteNonQuery ();

// insert was successful
Label1.Text = "You are added to database";
}
catch (SqlException ex)
{
// check specifically for a constraint violation error

// display an appropriate error message
Label1.Text = "choose another nick";
}
finally
{
c.Close();
}
....

I don't normally see a SqlDataReader and ExecuteReader() used on an insert
statement. I'm not sure whether it would work or not, but it's probably
cleaner to use ExecuteNonQuery () instead, as demonstrated above.

Also, do not forget the escape apostrophes in your strings. If a user
enters an apostrophe (') in one of your textboxes, your SQL string breaks
since apostrophes are the SQL string delimiter. You can solve this easily
e.g.;

IDklienta.Text.Replace("'","''"); // replace single apostrophe with
double-apostrophe

Although using paramaterized SQL would be a cleaner approach.

/// M

 

[adam]

Thanks a lot!
Adam

Adam,

Assuming that IDklienta is your nick (nickname? means username?), the
easiest approach is to just put a unique constraint on the
dbo.klient.IDklienta column. This ensures that any attempt to insert a new
row with the same value in that column will fail.

In your C#, code, you can catch the failure using try/catch

[PSEUDOCODE]

...
Label1.Text = ""; // clear the error

SqlConnection c = baza_danych.Polacz();

SqlCommand cmd = new SqlCommand("insert into klient
(IDklienta,Nazwisko,Imie,Adres,Miasto,Haslo) values ( '"+ IDklienta.Text +"'
,'" + Nazwisko.Text + "','" + Imie.Text + "','" + Adres.Text + "','" +
Miasto.Text + "','" + Haslo.Text + "')", c);

try
{
// attempt your insert here
cmd.ExecuteNonQuery ();

// insert was successful
Label1.Text = "You are added to database";
}
catch (SqlException ex)
{
// check specifically for a constraint violation error

// display an appropriate error message
Label1.Text = "choose another nick";
}
finally
{
c.Close();
}
...

I don't normally see a SqlDataReader and ExecuteReader() used on an insert
statement. I'm not sure whether it would work or not, but it's probably
cleaner to use ExecuteNonQuery () instead, as demonstrated above.

Also, do not forget the escape apostrophes in your strings. If a user
enters an apostrophe (') in one of your textboxes, your SQL string breaks
since apostrophes are the SQL string delimiter. You can solve this easily
e.g.;

IDklienta.Text.Replace("'","''"); // replace single apostrophe with
double-apostrophe

Although using paramaterized SQL would be a cleaner approach.

/// M

Hi there
I've got a littel problem. I've samo form which should add data to database.
Everythinks goes OK, but I'd like to have some alert messege when samobody
would like to add another user with the same nick. Now my aplication in that
case goes down.
This is code

void Button1_Click(object sender, EventArgs e) { SqlConnection c =
baza_danych.Polacz();
SqlCommand cmd = new SqlCommand("insert into klient
(IDklienta,Nazwisko,Imie,Adres,Miasto,Haslo) values ( '"+ IDklienta.Text +"'
,'" + Nazwisko.Text + "','" + Imie.Text + "','" + Adres.Text + "','" +
Miasto.Text + "','" + Haslo.Text + "')", c);
SqlDataReader dr = cmd.ExecuteReader();
bool test; test=dr.HasRows; if (test==true) { dr.Read(); Label1.Text =
"choose another nick"; } else { Label1.Text = "You are added to database"; }
dr.Close();
c.Close(); }

TIA
Adam