locked
Help in managing multiple databases & tables to perform transactions RRS feed

  • Question

  • User1076910807 posted

    Hi all,

    I am new and have been presented with a complex (well to me anyway) problem.  I'll run through what this is and what i have tried.  There are 2 databases that I access.  One stores personnel information (names, id's etc) while the other stores business information (authorities). 

    I have tried in vain for a while and really need someone to point me in the right direction or tell me as if I was 2 yrs old whaot I am doing wrong.  What I want to do is:

    1. Query the personnel information table (on Database 1) and use a dropdown list to populate information to select a person (person's name).  <<This is fine, can do this no problem.  The dropdown list displays email addresses but the SQL Query returns additional information such as name & ID number. This returns a single row of data.

    2.  Take the information retieved from 1st query, add a button to submit this to a second database (authorities). 

    This is probably easy stuff for most of you but for the life of me I can't combine it all.  Individually I can get a dropdown use its selected value to run the SQL Query, but can't do step #2 above. 

    I won't display  the code I have written because it is all over the place after trolling through these forums trying to find a similar post.  Do I do most of this in the code-behind or in the aspx page ?? Do I use SQL Data Reader.  I guess the sleep deprevation is kicking in.

    Here is a pseudo Algorithm:

    1. Query Personnel Database and retrieve all personnel records to populate the DropDown List with their email addresses. << Can do this. Query 1 returns firstname, lastname, emailAddress, address, phone, id.

    2. Make Selection From Dropdown list.

    3.  Use selected value from dropdown list to select the row of data its from from the Personnel Database (i.e the other data obtained from Query 1).  Take from Query 1 the ID, emailAddress, firstName & LastName and insert these values into Database 2 in an Authorities Table. 

     

    I have no error messages or anything to show because I am so far away from having anything that works.  Let me know what you guys need to help me out and I will provide where I can.  I appreciate your help.

    Thanks.

     

     

    Saturday, August 13, 2011 12:49 PM

Answers

  • User1076910807 posted

    codeasp,

    I have had a quicklook at the link and I think that wht you have provided will answer alot of the questions I have.  I guess I am a little confused as there are many ways of doing things in asp.net.  Do I link all this operations to the buttonclick to commit the items into the database?  After I perform the CRUD operations in the code-behind, can I add a variable to display the status (i.e. a messagebox) or similar to provide the feedback back to the user on the success or failure of the operation.  I tend to add a few more things in while I am debugging anyway, but its been a while since I have done any serious coding and even then it was in JAVA.  

    Although the link above uses the ADO DataReader, this shouldn't be too different for the SQLDataReader. Also, for clarification on my part, in code like that below (from the link above) - this takes the complete row of the retrieved data from the select statement.  And "FirstName" is the associated Field Name (or table column) from the Person Table ("SELECT * FROM Person"). I know this is stated in the article but for my own piece of mind I guess I need some validation.

    while( dr.Read() )
        {
            System.Console.WriteLine( dr["FirstName"] );
        }  
    I will do some work tonight and post some code later that will help.
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, August 14, 2011 6:04 AM
  • User3866881 posted

    After I perform the CRUD operations in the code-behind, can I add a variable to display the status (i.e. a messagebox) or similar to provide the feedback back to the user on the success or failure of the operation.  

    Of course you can!:)

    Just use something like this:

     using (SqlCommand cmd = new SqlCommand("Your CRUD here",new SqlConnection("Your connstr here")))
                {
                    if (cmd.ExecuteNonQuery() > 0)   //This means you are successfully doing CRUD
                    {
                        ClientScript.RegisterStartupScript(GetType(),"msg","alert('OK!');",true);
                    }
                }
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, August 14, 2011 10:37 PM

All replies

  • User849259894 posted

    On the outset your query seems very simple.  You are able to query the personnel database to get necessary information.  Next part is you need to insert the records in the business database.  If you have all the information then it is a simple insert/update to the business database.  Following article will help you in using datareader to do CRUD operations on database table

    http://www.codeproject.com/KB/database/simpledbreadwrite.aspx

    If I have misunderstood your problem, then please rephrase your scenario.

    Sunday, August 14, 2011 2:57 AM
  • User1076910807 posted

    codeasp,

    I have had a quicklook at the link and I think that wht you have provided will answer alot of the questions I have.  I guess I am a little confused as there are many ways of doing things in asp.net.  Do I link all this operations to the buttonclick to commit the items into the database?  After I perform the CRUD operations in the code-behind, can I add a variable to display the status (i.e. a messagebox) or similar to provide the feedback back to the user on the success or failure of the operation.  I tend to add a few more things in while I am debugging anyway, but its been a while since I have done any serious coding and even then it was in JAVA.  

    Although the link above uses the ADO DataReader, this shouldn't be too different for the SQLDataReader. Also, for clarification on my part, in code like that below (from the link above) - this takes the complete row of the retrieved data from the select statement.  And "FirstName" is the associated Field Name (or table column) from the Person Table ("SELECT * FROM Person"). I know this is stated in the article but for my own piece of mind I guess I need some validation.

    while( dr.Read() )
        {
            System.Console.WriteLine( dr["FirstName"] );
        }  
    I will do some work tonight and post some code later that will help.
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, August 14, 2011 6:04 AM
  • User3866881 posted

    After I perform the CRUD operations in the code-behind, can I add a variable to display the status (i.e. a messagebox) or similar to provide the feedback back to the user on the success or failure of the operation.  

    Of course you can!:)

    Just use something like this:

     using (SqlCommand cmd = new SqlCommand("Your CRUD here",new SqlConnection("Your connstr here")))
                {
                    if (cmd.ExecuteNonQuery() > 0)   //This means you are successfully doing CRUD
                    {
                        ClientScript.RegisterStartupScript(GetType(),"msg","alert('OK!');",true);
                    }
                }
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, August 14, 2011 10:37 PM
  • User1076910807 posted

    Thanks Decker Dong.  I wish I had gotten onto the forums sooner rather than the days spend looking for something that now seems so obvious.  

    Monday, August 15, 2011 9:07 AM
  • User3866881 posted

    My pleasure, and glad to hear that your success.

    Monday, August 15, 2011 8:59 PM