asked on

return a record found or not found in sql database / asp.net / c#

Development Platform: c#, asp.net 2.x, Visual Studio Pro utilising Web Developer, IIS 6, SQL Server 2005

Want to be able to enter a case/account number (via a form text field), press the submit button (to search/find account).

-- return back a message indicating 'record not found' OR 'record found' -- no need to display the actual entry -- just need to confirm if that entry is in the database somewhere.

Below I have a script that worked with finding the first successful response in database -- as we are searching for unique account numbers within database, assuming this is still the better way forward for getting some of the account details.

Is this the right scripting to do this -- and if so, what do I need to add for checking for a record find or not variable?

Thank you in advance for your time and efforts with this enquiry.

SqlDataSource FORTUNESQLDataSource = new SqlDataSource();
FORTUNESQLDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["FORTUNEConnectionString"].ToString();

string MasterAccountSearch_ID = "";

using (SqlConnection FORTUNEConnection1 = new SqlConnection(FORTUNESQLDataSource.ConnectionString))
{
FORTUNEConnection1.Open();

SqlCommand scCommand = new SqlCommand((String.Format("SELECT [MasterAccount_ID] FROM [MasterAccounts] WHERE [MasterAccount_ID] = '{0}'", TextBox_SearchMasterAccounts.Text)), FORTUNEConnection1);
MasterAccountSearch_ID = (string)scCommand.ExecuteScalar();
FORTUNEConnection1.Close();
}

surajguptha

Is TextBox_SearchMasterAccounts.Text the masteraccount id?

amillyard

ASKER

yes ... but let me clarify please as follows:

TextBox_SearchMasterAccounts.Text is what is entered on the asp form -- i.e. type in the account number (which is the same as the primary key field in the database table)

MasterAccount_ID -- is the database table

both variables are representing the same value item.

surajguptha

Instead of SELECT [MasterAccount_ID] FROM [MasterAccounts] WHERE [MasterAccount_ID] = '{0}
try SELECT count(*) FROM [MasterAccounts] WHERE [MasterAccount_ID] = '{0}

This will definitely return a value. If it is 0 the records exists and if it is 1 the masterstyle id is valid

amillyard

ASKER

for some reason, I am now getting an error when trying to compile

-- Use of unassigned local variable 'MasterAccountSearch' --

protected void Button_ClientCase_Click(object sender, EventArgs e)
{
SqlDataSource FORTUNESQLDataSource = new SqlDataSource();
FORTUNESQLDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["FORTUNEConnectionString"].ToString();

string MasterAccountSearch;

using (SqlConnection FORTUNEConnection1 = new SqlConnection(FORTUNESQLDataSource.ConnectionString))
{
try
{
FORTUNEConnection1.Open();

SqlCommand scCommand = new SqlCommand((String.Format("SELECT count(*) FROM [MasterAccounts] WHERE [MasterAccount_ID] = '{0}'", TextBox_SearchMasterAccounts.Text)), FORTUNEConnection1);

MasterAccountSearch = (string)scCommand.ExecuteScalar();
}
catch (Exception ex)
{
SQL_FailureHandler(ex);
}
finally
{
FORTUNEConnection1.Close();
}

switch (MasterAccountSearch)
{
case "0" : Label_MasterAccountSearchStatus.Text = "Record Found";
Session["fortuneMasterAccount"] = TextBox_SearchMasterAccounts.Text;
Response.Redirect("default.aspx");
break;
default : Label_MasterAccountSearchStatus.Text = "Record NOT Found";
break;
}
}
}

private void SQL_FailureHandler(Exception ex)
{
// TODO SQL Failure Handler needs to be scripted

Label_MasterAccountSearchStatus.Text = "[ Database Search FAILURE ]";
}
}

surajguptha

Just say string MasterAccountSearch = ""; instead of string MasterAccountSearch;

amillyard

ASKER

I see, interesting -- worked first time ! (the "" advise)
Compiling ok now -- when running the script -- not getting any success yet with checking record -- keeps indicating that no record was found.

yet, I know I am entering the correct data-entry figure matching the record itself (i.e. 1, 2, 3 etc...)

surajguptha

Did you do this????
Instead of SELECT [MasterAccount_ID] FROM [MasterAccounts] WHERE [MasterAccount_ID] = '{0}
try SELECT count(*) FROM [MasterAccounts] WHERE [MasterAccount_ID] = '{0}

amillyard

ASKER

I think so as follows:

SqlCommand scCommand = new SqlCommand((String.Format("SELECT count(*) FROM [MasterAccounts] WHERE [MasterAccount_ID] = '{0}'", TextBox_SearchMasterAccounts.Text)), FORTUNEConnection1);

ASKER CERTIFIED SOLUTION

surajguptha

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

amillyard

ASKER

ok -- got this scripting to work.

I needed to also update one of the string variables to integers as follows:

SqlDataSource FORTUNESQLDataSource = new SqlDataSource();
FORTUNESQLDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["FORTUNEConnectionString"].ToString();

int MasterAccountSearch = -1;

using (SqlConnection FORTUNEConnection1 = new SqlConnection(FORTUNESQLDataSource.ConnectionString))
{
FORTUNEConnection1.Open();
SqlCommand scCommand = new SqlCommand((String.Format("SELECT count(*) FROM [MasterAccounts] WHERE [MasterAccount_ID] = '{0}'", TextBox_SearchMasterAccounts.Text)), FORTUNEConnection1);
MasterAccountSearch = (int)scCommand.ExecuteScalar();
FORTUNEConnection1.Close();

if (MasterAccountSearch == 1)
{
Label_MasterAccountSearchStatus.Text = "Record Found";
Session["fortuneMasterAccount"] = TextBox_SearchMasterAccounts.Text;
Response.Redirect("default.aspx");
}
else
Label_MasterAccountSearchStatus.Text = "Record NOT Found";
}

amillyard

ASKER

thank you very much for your time and efforts :-)