Check if username exists before pressing submit button

Question

i have this code-behind:

protected void cmdSave_Click(object sender, EventArgs e)
{

    string sFilePath = Server.MapPath("Database3.accdb");
    OleDbConnection Conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + sFilePath + ";Persist Security Info=False;");
    using (Conn)
    {
        Conn.Open();
        OleDbCommand myCommand = new OleDbCommand("SELECT COUNT(*) FROM colaborador WHERE username=@username", Conn);
        myCommand.Parameters.Add("?", OleDbType.VarChar).Value = HttpContext.Current.User.Identity.Name;
        int totalRegistos = (int)myCommand.ExecuteScalar();
        if (totalRegistos > 0)
        {
            //      user already answered
            lblInfo0.Text = "The user already asnwered";
        }
        else
        {
            //      the user didn't asnwered

            string insertCmd = "INSERT INTO colaborador(Empresa,Empresa2,Telemovel,username) VALUES (@Empresa,@Empresa2,@Telemovel,@username)";
               // insere na tabela colaborador os campos empresa, empres2, user os valores @ 
            {
                OleDbCommand myCommand2 = new OleDbCommand(insertCmd, Conn);
                myCommand2.Parameters.AddWithValue("@Empresa", empresa.Text);
                myCommand2.Parameters.AddWithValue("@Empresa2", empresa2.Text);
                myCommand2.Parameters.AddWithValue("@Telemovel", telemovel.Text);
                myCommand2.Parameters.AddWithValue("@username", HttpContext.Current.User.Identity.Name);
                Response.Write(myCommand.ExecuteNonQuery());
                lblInfo.Text = "Data saved!";
                lblInfo.ForeColor = System.Drawing.Color.Green;
            }
        }
    }
}

this working fine with no errors and save into db also if the username exist say a message "user already answered"

however i need press submit button.

there's any way to say the message (if the username already exist) before field the text.box? how can i change my code to do that?

by writing code? https://msdn.microsoft.com/en-us/library/5011f09h.aspx — Marc B, Jul 20 '15 at 16:56
Well.. http://stackoverflow.com/questions/31452903/verify-if-username-already-answered-and-display-database-aspc and http://stackoverflow.com/questions/31515456/verify-if-username-exist-to-show-the-database-or-sho-table-to-field and http://stackoverflow.com/questions/31519336/if-username-not-exist-insert-into-else-update-display-info — Soner Gönül, Jul 20 '15 at 16:57
@SonerGönül lol... no lucky so far... can't making working... :( — KikoFHM, Jul 20 '15 at 17:01
Start to learn how to make an [UPDATE query](http://www.w3schools.com/SQl/sql_update.asp), then run that query. If the record that you want to update doesn't exist, the ExecuteNonQuery call will return with 0, in that case execute the INSERT query. — Steve, Jul 20 '15 at 17:07
@Steve i already start learning sql query :) but i still have problems understanding ASP. That's my biggest problem. You guide me to sql statement but how can I insert into my code? it just write in code or code behind? did I need write between <% for example? or script? that's my biggest problem don't know how to use my research :( — KikoFHM, Jul 21 '15 at 10:03
There are several ways to do this in ASP. You can drag over an AccessDataSource control to the ASP markup code, then use it as a datasource for a formview, listview, etc. Or, you can do it in codebehind as with the example you posted. You can do an update statement exactly as you have done the insert. If it fails then perform the insert. But you will need some kind of unique key to find the record to be inserted if it exists (e.g.., Update table set field=value WHERE mykey=ID). — pghcpa, Jul 25 '15 at 22:24

score 0 · Accepted Answer · answered Jul 27 '15 at 16:26

if (!IsPostBack)
{
    string sFilePath = Server.MapPath("Database3.accdb");
    OleDbConnection Conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + sFilePath + ";Persist Security Info=False;");
    using (Conn)
    {
        Conn.Open();
        OleDbCommand myCommand = new OleDbCommand("SELECT COUNT(*) FROM colaborador WHERE username=@username", Conn);
        myCommand.Parameters.Add("?", OleDbType.VarChar).Value = HttpContext.Current.User.Identity.Name;
        int totalRegistos = (int)myCommand.ExecuteScalar();
        if (totalRegistos > 0)
        {
                // Já registado
                lblInfo0.Text = "O username já existe na base de dados";

                empresa.Enabled = false;
                empresa2.Enabled = false;
                telemovel.Enabled = false;
                cmdSave.Visible = false;
        }
    }
}

Check if username exists before pressing submit button

1 Answers1