How to check insert.parameters for error and return false?

Post Reply
User avatar
Trebor29
Lieutenant
Lieutenant
Posts: 75
Joined: Thu Apr 29, 2010 12:34 am

How to check insert.parameters for error and return false?

Post by Trebor29 » Fri Mar 30, 2012 1:05 am

Hello again all,

Wasn't sure how to word this one so I hope you can understand what im on about!

I am updating three tables in two sql server databses by passing values into three relevant methods() in my registration class file. I wont go into too much detail as to how ive ended up with this as it is the result of over coming a mountain of obstacles during this development.

Basicly, I send the relevant values to the relevant methods() and commit the data in the very last method by .ExecuteNonQuery(s), to make sure all data is update at the same time... previously data was getting update as and when, and if an error occured half way through, the data would be inconsistant!

At the moment, values are updated to a table and then if all is OK returns true to continue... I have tried to force an error in my code and in my control(s) values but it does not pick up the error, and so always returns true!

If I add the .ExecuteNonQuery just before the return then it picks up the errors and returns false, but I cannot / dont want to execute there, for the above reasons.

Does anyone know of another way (piece of trickery maybe) to check for errors withoutt using the .ExecuteNonQuery here?

Thanks guys! :biggrin:

Code: Select all

public bool RegUser(string fn, string ln, string a1, string a2, string a3, string pCode, string country,
						string mNum, string email)
	{
		SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["UserConnectionString"].ConnectionString);
		con.Open();
		string insCmd = "INSERT INTO UserTable (FirstName, LastName, AddressLine_1, AddressLine_2, AddressLine_3, Postcode, Country, MobileNumber, EmailAddress, Roles, Time, Date) VALUES (@FirstName, @LastName, @AddressLine_1, @AddressLine_2, @AddressLine_3, @Postcode, @Country, @MobileNumber, @EmailAddress, @Roles, @Time, @Date)";
		insertUser = new SqlCommand(insCmd, con);
		insertUser.Parameters.AddWithValue("@FirstName", fn);
		insertUser.Parameters.AddWithValue("@LastName", ln);
		insertUser.Parameters.AddWithValue("@AddressLine_1", a1);
		insertUser.Parameters.AddWithValue("@AddressLine_2", a2);
		insertUser.Parameters.AddWithValue("@AddressLine_3", a3);
		insertUser.Parameters.AddWithValue("@Postcode", pCode);
		insertUser.Parameters.AddWithValue("@Country", country);
		insertUser.Parameters.AddWithValue("@MobileNumber", mNum);
		insertUser.Parameters.AddWithValue("@EmailAddress", email);
		insertUser.Parameters.AddWithValue("@Roles", "LoggedUser");
		insertUser.Parameters.AddWithValue("@Time", DateTime.Now.ToShortTimeString());
		insertUser.Parameters.AddWithValue("@Date", DateTime.Today.Date);

		try // THIS WILL ALWAYS BE TRUE !!!  HOW TO CHECK AND RETURN FALSE IF ERROR OCCURS?
		{
			return true;
		}
		catch
		{
			return false;
		}

	}


Code: Select all

public bool RegAuthDetails(string pw, string PIN)
	{
		SqlConnection con2 = new SqlConnection(ConfigurationManager.ConnectionStrings["UserConnectionString"].ConnectionString);
		con2.Open();
		string insCmd = "INSERT INTO UserAuthDetails (Password, PaymentPIN) VALUES (@Password, @PaymentPIN)";
		insertUserAuth = new SqlCommand(insCmd, con2);
		insertUserAuth.Parameters.AddWithValue("@Password", pw);
		insertUserAuth.Parameters.AddWithValue("@PaymentPIN", PIN);

		try
		{
			return true;
		}
		catch
		{
			return false;
		}
	}

[code]
public void RegUserBankDetails(bool Mobile, string cardNum, string expiryMM, string expiryYYYY, string cardName, string securityCode,
									string email)
	{
		SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["UserBankDatabaseConnectionString"].ConnectionString);
		con.Open();
		string insCmd = "INSERT INTO UserBankTable (CardNumber, ExpiryDateMM, ExpiryDateYYYY, NameOnCard, SecurityCode, EmailAddress, Time, Date) VALUES (@CardNumber, @ExpiryDateMM, @ExpiryDateYYYY, @NameOnCard, @SecurityCode, @EmailAddress, @Time, @Date)";
		insertPayInfo = new SqlCommand(insCmd, con);
		insertPayInfo.Parameters.AddWithValue("@CardNumber", cardNum);
		insertPayInfo.Parameters.AddWithValue("@ExpiryDateMM", expiryMM);
		insertPayInfo.Parameters.AddWithValue("@ExpiryDateYYYY", expiryYYYY);
		insertPayInfo.Parameters.AddWithValue("@NameOnCard", cardName);
		insertPayInfo.Parameters.AddWithValue("@SecurityCode", securityCode);
		insertPayInfo.Parameters.AddWithValue("@EmailAddress", email);
		insertPayInfo.Parameters.AddWithValue("@Time", DateTime.Now.ToShortTimeString());
		insertPayInfo.Parameters.AddWithValue("@Date", DateTime.Today.Date);

		try
		{
			insertUser.ExecuteNonQuery();
			insertUserAuth.ExecuteNonQuery();
			insertPayInfo.ExecuteNonQuery();
			con.Close(); // IS THIS CLOSING ALL THREE CONNECTIONS ????

			if (Mobile == false)
			{
				HttpContext.Current.Response.Redirect("Login.aspx", false); // need to test these bools..!
				return;
			}
			else if (Mobile == true)
			{
				HttpContext.Current.Response.Redirect("MobileLogin.aspx", false); // check this also!!!
				return;
			}
		}
		catch
		{
			if (Mobile == false)
			{
				HttpContext.Current.Response.Redirect("~/ErrorPages/ErrorPage.aspx", false); // should be true i think ???
				return;
			}
			else if (Mobile == true)
			{
				HttpContext.Current.Response.Redirect("MobileErrorPages/ErrorPage.aspx", false); // should be true i think ???
				return;
			}
		   
		}
	}
[/code]
User avatar
Herath
Major
Major
Posts: 417
Joined: Thu Aug 05, 2010 7:09 pm

Re: How to check insert.parameters for error and return false?

Post by Herath » Fri Mar 30, 2012 7:40 am

You will be able to use transactions for this. When employing this method, you could modify the tables, check if there is any error and commit everything if no error has been detected. If you have to used stored procedures, this is going to be easy to catch up with.

http://msdn.microsoft.com/en-us/library/ms174377.aspx

http://www.sqlteam.com/article/introduc ... ansactions

Another quick solution would be to check the number of rows affected from the last query. ExecuteNonQuery returns the number of affected rows. So i think that you will be able to use that to check if the last query executed properly. But the problem with this approach is that you are going to have to manually undo the changes that has been made to the database. With transactions, you could restore the database to the state that it was at the start of the transaction.

Code: Select all

 try // THIS WILL ALWAYS BE TRUE !!!  HOW TO CHECK AND RETURN FALSE IF ERROR OCCURS?
      {
         return true;
      }
      catch
      {
         return false;
      }
If the above code block is the way you have employed try/catch blocks, you have made a little mistake. try/catch blocks are meant for exception handling. For an instance, you could put your database access code inside the try{} block and catch any exception in catch{} block that the code in the try{} block might trigger.
Post Reply

Return to “ASP & ASP.Net”