[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2007-10-11
11
Medium Priority
?
302 Views
Last Modified: 2013-11-26
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();
            }
0
Comment
Question by:amillyard
  • 6
  • 5
11 Comments
 
LVL 21

Expert Comment

by:surajguptha
ID: 20061733
Is TextBox_SearchMasterAccounts.Text the masteraccount id?
0
 

Author Comment

by:amillyard
ID: 20061748
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.
0
 
LVL 21

Expert Comment

by:surajguptha
ID: 20061772
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
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:amillyard
ID: 20061879
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 ]";
        }
    }
0
 
LVL 21

Expert Comment

by:surajguptha
ID: 20061903
Just say string MasterAccountSearch = ""; instead of string MasterAccountSearch;
0
 

Author Comment

by:amillyard
ID: 20061923
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...)
0
 
LVL 21

Expert Comment

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

Author Comment

by:amillyard
ID: 20061955
I think so as follows:

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

Accepted Solution

by:
surajguptha earned 2000 total points
ID: 20061991
That query would always return a record. In my opinion it can never throw a record not found exception

Can u try manually running the query using a sql client?
0
 

Author Comment

by:amillyard
ID: 20062065
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";                
            }
0
 

Author Comment

by:amillyard
ID: 20062068
thank you very much for your time and efforts :-)
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
Hello there! As a developer I have modified and refactored the unit tests which was written by fellow developers in the past. On the course, I have gone through various misconceptions and technical challenges when it comes to implementation. I would…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question