Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Question
Sunday, November 9, 2008 12:04 PM
First of all I m not entirely sure where i should post this topic. Although I guess this might be the right place. I am currently developing a custom user creation system and login. so far everything is working fine. A user enters his name and password and it saves it to the database. However I want to check whether this Username already exists in the database. but I have no idea how to code this in C# My guess are its something with a foreach loop. but I ve no idea where to start. here's my code.
1
2
3 using System;
4 using System.Data;
5 using System.Data.SqlClient;
6 using System.Globalization;
7 using System.Configuration;
8 using System.Web;
9 using System.Web.Security;
10 using System.Web.UI;
11 using System.Web.UI.WebControls;
12 using System.Web.UI.WebControls.WebParts;
13 using System.Web.UI.HtmlControls;
14
15 public partial class _Default : System.Web.UI.Page
16 {
17
18 protected void Page_Load(object sender, EventArgs e)
19 {
20
21 }
22 protected void Button1_Click(object sender, EventArgs e)
23 {
24 // create the connection
25 SqlConnection conn = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\DSusers.mdf;Integrated Security=True;User Instance=True");
26
27 // Check whether the name already exists in the database
28
// check whether the name is empty
29 if (TXTUserName.Text == "")
30 {
31 LBLdisplay.Text = "Please enter a desired Username";
32 }
33 else
34 {
35
36 // open the connection
37 conn.Open();
38
39 // create the query command and parameters
40 SqlCommand queryCommand = new SqlCommand("INSERT INTO DSusers (UserName, Password) VALUES (@UserName, @Password)"); // create a new sqlcommand
41 queryCommand.Parameters.Add("@UserName", TXTUserName.Text);
42 queryCommand.Parameters.Add("@Password", TXTPassword.Text);
43
44 // set the additional parameters for the queryCommand
45 queryCommand.CommandTimeout = 600; // set the timeout in second
46 queryCommand.Connection = conn; // set the connection for our query
47 queryCommand.CommandType = CommandType.Text;
48
49 // execute the query
50 queryCommand.ExecuteNonQuery();
51
52 // close the connection
53 conn.Close();
54
55 // everythign is okay so reset the label
56 LBLdisplay.Text = "Upload succesful";
57
58 // go to the table overview on UserOverview.ASPX
59 Response.Redirect("userOverview.aspx");
60 }
61 }
62 }
63
All replies (2)
Sunday, November 9, 2008 12:23 PM ✅Answered
Hi,
I have written a pseudocode method which isn't tested but should work. With this method you can check it the datafield already exists.
private Boolean exists()
{
SqlConnection conn = new SqlConnection("your Connectionstring");
SqlCommand cmd = new SqlCommand("Select Count(*) from DSusers where Username = @Username", conn);
SqlDataReader sReader = null;
Int32 numberOfRows = 0;
try
{
conn.Open();
sReader = cmd.ExecuteReader();
while (sReader.Read())
{
if (!(sReader.IsDBNull(0)))
{
numberOfRows = Convert.ToInt32(sReader[0]);
if (numberOfRows > 0)
{
return true;
}
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
return false;
}
Monday, November 10, 2008 12:27 AM ✅Answered
Hi,
Your table should be have username as primary key or have unique constraint in username column.
We can use SqlException to simplify the work and let Sql Server do the validation. :)
Try this :
1
2
3 using System;
4 using System.Data;
5 using System.Data.SqlClient;
6 using System.Globalization;
7 using System.Configuration;
8 using System.Web;
9 using System.Web.Security;
10 using System.Web.UI;
11 using System.Web.UI.WebControls;
12 using System.Web.UI.WebControls.WebParts;
13 using System.Web.UI.HtmlControls;
14
15 public partial class _Default : System.Web.UI.Page
16 {
17
18 protected void Page_Load(object sender, EventArgs e)
19 {
20
21 }
22 protected void Button1_Click(object sender, EventArgs e)
23 {
24 // create the connection
25 SqlConnection conn = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\DSusers.mdf;Integrated Security=True;User Instance=True");
26
27 // Check whether the name already exists in the database
28
// check whether the name is empty
29 if (TXTUserName.Text == "")
30 {
31 LBLdisplay.Text = "Please enter a desired Username";
32 }
33 else
34 {
35
36 // open the connection
37 conn.Open();
38
39 // create the query command and parameters
40 SqlCommand queryCommand = new SqlCommand("INSERT INTO DSusers (UserName, Password) VALUES (@UserName, @Password)"); // create a new sqlcommand
41 queryCommand.Parameters.Add("@UserName", TXTUserName.Text);
42 queryCommand.Parameters.Add("@Password", TXTPassword.Text);
43
44 // set the additional parameters for the queryCommand
45 queryCommand.CommandTimeout = 600; // set the timeout in second
46 queryCommand.Connection = conn; // set the connection for our query
47 queryCommand.CommandType = CommandType.Text;
48
try
{
49 // execute the query
50 queryCommand.ExecuteNonQuery();
51
52 // close the connection
53 conn.Close();
54
55 // everythign is okay so reset the label
56 LBLdisplay.Text = "Upload succesful";
57
58 // go to the table overview on UserOverview.ASPX
59 Response.Redirect("userOverview.aspx");
}
catch (SqlException sqe)
{
if(sqe.Number == 2627 || sqe.Number == 2601)
{
LBLdisplay.Text = "Username already exists";
}
}
finally
{
if(conn != null)
{ conn.Close(); }
}
60 }
61 }
62 }
63
Regards,
Shandy Hidayat