Hi Everyone,
I have a code which checks the data if already exist before you can insert to the database. and now my problem is that when after editing. After editing my existing data from the database it keeps on showing the message that it already exist. Can you please help me with a solution that will prevent it from preventing after editing. Here are my C# code below;
private void btnSave_Click(object sender, RoutedEventArgs e)
{
//This checks for empty fields or wrong fields
#region Validations for input
if (txtFullNames.Text.Length == 0)
{
Errormessage.Text = "Please enter your fullname";
txtFullNames.Focus();
}
else if(!Regex.IsMatch(txtFullNames.Text, @"^[a-zA-Z]"))
{
Errormessage.Text = "Fullname must not contain a number";
}
else if (txtSurname.Text.Length == 0)
{
Errormessage.Text = "Please enter your Surname";
txtSurname.Focus();
}
else if (!Regex.IsMatch(txtSurname.Text, @"^[a-zA-Z]"))
{
Errormessage.Text = "Surname must not contain a number";
}
else if (txtEmail.Text.Length == 0)
{
Errormessage.Text = "Please enter your Email Address";
txtEmail.Focus();
}
else if (!Regex.IsMatch(txtEmail.Text, @"^[a-zA-Z][\w\.-]*[a-zA-Z0-9]@[a-zA-Z0-9][\w\.-]*[a-zA-Z0-9]\.[a-zA-Z][a-zA-Z\.]*[a-zA-Z]$"))
{
Errormessage.Text = "Please enter you valid email address";
txtEmail.Focus();
}
else if (cboGender.SelectedIndex == 0)
{
Errormessage.Text = "Please select your Gender";
cboGender.Focus();
}
else if (cboCompany.SelectedIndex == 0)
{
Errormessage.Text = "Please select your Company";
cboCompany.Focus();
}
else if (txtUserName.Text.Length == 0)
{
Errormessage.Text = "Please enter your Username";
txtUserName.Focus();
}
else if (txtPassword.Password.Length == 0)
{
Errormessage.Text = "Please enter your password";
txtPassword.Focus();
}
else if (txtConfirmPassword.Password.Length == 0)
{
Errormessage.Text = "Please confirm your password";
txtConfirmPassword.Focus();
}
else if (txtPassword.Password != txtConfirmPassword.Password)
{
Errormessage.Text = "Both password must match";
}
#endregion
else
{
SqlConnection oConnection = new SqlConnection(_ConnectionString);
SqlCommand Check_Exist = new SqlCommand("SELECT UserName from UserEnrollment WHERE username = @UserName AND Password = @Password", oConnection);
Check_Exist.Parameters.AddWithValue("@UserName", txtUserName.Text);
Check_Exist.Parameters.AddWithValue("@Password", txtPassword.Password);
oConnection.Open();
SqlDataReader reader = Check_Exist.ExecuteReader();
if (reader.HasRows)
{
MessageBox.Show("Username and Password already exist!", "Error Message", MessageBoxButton.OKCancel, MessageBoxImage.Error);
}
else
{
int Gender = 0;
bool passed = int.TryParse(cboGender.SelectedValue.ToString(), out Gender);
int Company = 0;
passed = int.TryParse(cboCompany.SelectedValue.ToString(), out Company);
if (passed)
{
try
{
switch (g_i_commitType)
{
case 1:
{
bool _var = _da.InsertData(UserID,
txtUserName.Text,
txtPassword.Password,
txtFullNames.Text,
txtSurname.Text,
txtEmail.Text,
Gender,
Company
);
} break;
case 2:
{
bool _var = _da.EditData(UserId,
txtUserName.Text,
txtPassword.Password,
txtFullNames.Text,
txtSurname.Text,
txtEmail.Text,
Gender,
Company
);
} break;
}
LoadGridUsers();
MessageBox.Show("Data Successfully Submited", "Confirmation Message");
DeleteTextBox();
Errormessage.Text = "";
}
catch (Exception ex)
{
MessageBox.Show("A handled exception occurred" + ex.Message, "Error Message", MessageBoxButton.OK, MessageBoxImage.Error);
}
}
}
Arunava BhattacharjeePosted Sep 25, 2014, 7:53 AM
CREATE PROCEDURE InsertUsers
(
@username varchar(25),
@userpassword varchar(25)
)
AS
IF EXISTS(SELECT 'True' FROM UserTable WHERE username = @username)
BEGIN
--This means it exists, return it to ASP and tell us
SELECT 'This record already exists!'
END
ELSE
BEGIN
--This means the record isn't in there already, let's go ahead and add it
SELECT 'Record Added'
INSERT into MyTable(username, userpassword) VALUES(@username, @userpassword)
END
Now in the code you can call this store procedure and check the count. Also you can get the message. If the count is 0 then data exists otherwise it will add.
You can go through this link : http://www.codeproject.com/Questions/565011/StoredplusProcedureplusC-plus-plus-IFplusEXIST
Always try to reduce the round trip to database. Always think about the performance. Hope this helps you. Please mark it as answered if it satisfied you.
EnosPosted Sep 25, 2014, 3:27 AM
Arunava BhattacharjeePosted Sep 25, 2014, 3:14 AM
One stored procedure should check and then insert data if data do not exist.
Write it in your stored procedure otherwise you will end up routing to DB twice and it will effect your performance. The coding is also simplified :)..Mark the answer as accepted if you agree.