Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL: Saving data from temporary tables to actual database (using web service)

Status
Not open for further replies.

pinkpoppy

Programmer
Jul 6, 2011
75
US
Hello, I have a web application that users must enter values, which is then saved to a temporary database. Once it successfully saves to the temporary database, I need those data to send to the web service to be saved in the actual database. But the issue is, those data are not saving in the actual database. I keep getting an error. This is the error message: "DataSet contains the wrong number of tables"

If anyone can provide some help, I can provide the method from my application and the method from the web service. Can anyone help me with my method. What is the issue?


Here is my method.

Code:
        public DataSet GetDataFromTempTables(string connection)
        {
            
            DataSet dsReportHeader = new DataSet();
            string sql = "";

            //connection to temporary database
            scon = new SqlConnection(connection);

            sql = "select Confirmation_ID,Clerk_ID,Checkbook_ID,County,Month,Year,Create_User,Create_Date,Modify_User,Modify_Date from Report_Header";

            try
            {
                sadp = new SqlDataAdapter(sql, scon);
                sadp.Fill(dsReportHeader);
                dsReportHeader.Tables[0].TableName = "Report_Header";

                if (dsReportHeader.Tables["Report_Header"].Rows.Count > 0)
                {
                    sql = "Select Person_Count,Amount_Collected,Amount_Retained,Amount_Revenue,Create_User,Create_Date,Modify_User,Modify_Date from Report_Accounting_Detail where Confirmation_ID='"
                        + dsReportHeader.Tables["Report_Header"].Rows[0]["Confirmation_ID"].ToString() + "'";

                    sadp = new SqlDataAdapter(sql, scon);
                    sadp.Fill(dsReportHeader);
                    dsReportHeader.Tables[1].TableName = "Report_Accounting_Detail";

                    sql = "Select Judge_ID,Court_Description,Appointed,Appointed_GAL,Waived,Waived_GAL,Create_User,Create_Date,Modify_User,Modify_Date from Report_Judge_Detail where Confirmation_ID='"
                        + dsReportHeader.Tables["Report_Header"].Rows[0]["Confirmation_ID"].ToString() + "'";

                    sadp = new SqlDataAdapter(sql, scon);
                    sadp.Fill(dsReportHeader);
                    dsReportHeader.Tables[2].TableName = "Report_Judge_Detail";
                }
            }
            catch (Exception ex)
            { 
            
            }
            return dsReportHeader;
        }



Here is the web service method: (this is not all the method code) The error is in red.

Code:
    [WebMethod]
    public string SaveRecord(DataSet RTables)
    {
        ////////DataSet RTables = new DataSet();

        //setup connection to pull data
        conn = new SqlConnection(dbConn);
        string strSQL;
        int ScopeIdentity = 0;
        string x = "";  //returned value from 2 of the inserts

 try
        {
            //check data passed from user and abort if data is incorrect/incomplete
            //1) DataSet must have 3 tables 
            //      Report_Header
            //      Report_Accounting_Detail
            //      Report_Judge_Detail
[COLOR=#EF2929]            if (RTables.Tables.Count != 3)
                throw new Exception("DataSet contains the wrong number of tables");[/color]
            if (RTables.Tables[0].TableName != "Report_Header")
                throw new Exception("DataSet Table[0] should be named 'Report_Header'");
            if (RTables.Tables[1].TableName != "Report_Accounting_Detail")
                throw new Exception("DataSet Table[1] should be named 'Report_Accounting_Detail'");
            if (RTables.Tables[2].TableName != "Report_Judge_Detail")
                throw new Exception("DataSet Table[2] should be named 'Report_Judge_Detail'");


            //2) Each table must have columns for all values (named to match database tables
            if (RTables.Tables[0].Columns.Count != 10)
                throw new Exception("Report_Header table has wrong number of columns");
            if ((RTables.Tables["Report_Header"].Columns[0].ColumnName !=    "Confirmation_ID")
                || (RTables.Tables["Report_Header"].Columns[1].ColumnName != "Clerk_ID")
                || (RTables.Tables["Report_Header"].Columns[2].ColumnName != "Checkbook_ID")
                || (RTables.Tables["Report_Header"].Columns[3].ColumnName != "County")
                || (RTables.Tables["Report_Header"].Columns[4].ColumnName != "Month")
                || (RTables.Tables["Report_Header"].Columns[5].ColumnName != "Year")
                || (RTables.Tables["Report_Header"].Columns[6].ColumnName != "Create_User")
                || (RTables.Tables["Report_Header"].Columns[7].ColumnName != "Create_Date")
                || (RTables.Tables["Report_Header"].Columns[8].ColumnName != "Modify_User")
                || (RTables.Tables["Report_Header"].Columns[9].ColumnName != "Modify_Date"))
                throw new Exception("Report_Header table has misnamed/missing column names");

            if (RTables.Tables[1].Columns.Count != 8)
                throw new Exception("Report_Accounting_Detail table has wrong number of columns");
            if ((RTables.Tables["Report_Accounting_Detail"].Columns[0].ColumnName != "Person_Count")
                || (RTables.Tables["Report_Accounting_Detail"].Columns[1].ColumnName != "Amount_Collected")
                || (RTables.Tables["Report_Accounting_Detail"].Columns[2].ColumnName != "Amount_Retained")
                || (RTables.Tables["Report_Accounting_Detail"].Columns[3].ColumnName != "Amount_Revenue")
                || (RTables.Tables["Report_Accounting_Detail"].Columns[4].ColumnName != "Create_User")
                || (RTables.Tables["Report_Accounting_Detail"].Columns[5].ColumnName != "Create_Date")
                || (RTables.Tables["Report_Accounting_Detail"].Columns[6].ColumnName != "Modify_User")
                || (RTables.Tables["Report_Accounting_Detail"].Columns[7].ColumnName != "Modify_Date"))
                throw new Exception("Report_Accounting_Detail table has misnamed/missing column names");

            if (RTables.Tables[2].Columns.Count != 10)
                throw new Exception("Report_Judge_Detail table has wrong number of columns");
            if ((RTables.Tables["Report_Judge_Detail"].Columns[0].ColumnName !=    "Judge_ID")
                || (RTables.Tables["Report_Judge_Detail"].Columns[1].ColumnName != "Court_Description")
                || (RTables.Tables["Report_Judge_Detail"].Columns[2].ColumnName != "Appointed")
                || (RTables.Tables["Report_Judge_Detail"].Columns[3].ColumnName != "Appointed_GAL")
                || (RTables.Tables["Report_Judge_Detail"].Columns[4].ColumnName != "Waived")
                || (RTables.Tables["Report_Judge_Detail"].Columns[5].ColumnName != "Waived_GAL")
                || (RTables.Tables["Report_Judge_Detail"].Columns[6].ColumnName != "Create_User")
                || (RTables.Tables["Report_Judge_Detail"].Columns[7].ColumnName != "Create_Date")
                || (RTables.Tables["Report_Judge_Detail"].Columns[8].ColumnName != "Modify_User")
                || (RTables.Tables["Report_Judge_Detail"].Columns[9].ColumnName != "Modify_Date"))
                throw new Exception("Report_Judge_Detail table has misnamed/missing column names");
 
Why is the application not saving directly to the permanent database, or sending the data to the web service itself? This seems a little overly complex (oh, sorry. 'Enterprise-ey')
 
The SaveRecord method is passed the RTables Dataset. In the routine that calls SaveRecord, can you slip in some test code to print out the Tablenames in RTables prior to the call? If RTables thinks the table count's some value other than 3, perhaps it will help if you see a list of the tables it thinks it has.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top