Proper placement VS C# 2008 Empty DataSet

I am working on this code and being new to VS C# I apparently do not understand proper error checking. If you search for --- in this code you will see where the problem is. The second SQL statement may very well be empty so I thought if I checked for that the Try Catch would not fire. I was wrong. It appears the error actually occurs with this statement: DataDataAdapter.Fill(DataDataSet);. How do I adjust this code so the error message in the Try Catch will not fire.
namespace PPActivityDetail
{
    public partial class frmPPBreakdown : DexUIForm
    {
        // Create a reference to the Cash Receipts Entry window
        static RmCashReceiptsForm CashReceiptsForm = Dynamics.Forms.RmCashReceipts;
        static RmCashReceiptsForm.RmCashReceiptsWindow RmCashReceiptsWindow = CashReceiptsForm.RmCashReceipts;

        // Define connection string
        string ConnectionString = "";

        // Setup SQL Connection Handlers
        private System.Data.SqlClient.SqlConnection DataConnection; 
        private System.Data.DataSet DataDataSet;
        private System.Data.SqlClient.SqlCommand DataCommand;
        private System.Data.SqlClient.SqlDataAdapter DataDataAdapter;

        // Define variables to hold batch number and batch source
        string BatchSource;
        string BatchNumber;

        public frmPPBreakdown()
        {
            InitializeComponent();

            /* Assign values to text box fields */
            txtBatchNumber.Text = RmCashReceiptsWindow.BatchNumber;
            txtDocumentNumber.Text = RmCashReceiptsWindow.DocumentNumber;
            txtCustomerNumber.Text = RmCashReceiptsWindow.CustomerNumber;
            txtDocumentDate.Text = RmCashReceiptsWindow.DocumentDate.Value.ToString("MM/dd/yy");

            /* Assign value for Batch Source and Batch Name */
            BatchSource = RmCashReceiptsWindow.BatchSource;
            BatchNumber = RmCashReceiptsWindow.BatchNumber;

            try
            {
                SqlConnection DataConnection = GetConnection();

                DataDataSet = new System.Data.DataSet();
                DataDataSet.CaseSensitive = false;

                DataCommand = new System.Data.SqlClient.SqlCommand();
                DataCommand.Connection = DataConnection;

                DataCommand.CommandText = "SELECT GLPOSTDT FROM SY00500 WHERE BCHSOURC = '" + BatchSource + "' AND BACHNUMB = '" + BatchNumber + "'";

                DataDataAdapter = new System.Data.SqlClient.SqlDataAdapter();
                DataDataAdapter.SelectCommand = DataCommand;
                DataDataAdapter.TableMappings.Add("Table", "POSTINGDATE");

                DataDataAdapter.Fill(DataDataSet);

                txtPostDate.Text = DataDataSet.Tables["POSTINGDATE"].Rows[0]["GLPOSTDT"].ToString();

                if (txtPostDate.Text != "")
                {
                    txtPostDate.Text = Convert.ToDateTime(txtPostDate.Text).ToString("MM/dd/yy");
                }

                DataCommand.CommandText = "SELECT JOBNUMBR,INVCNMBR,RCPTRCVD,RCPTDISC,PPREQSTD,PPPAID,PPRECOUP,SALSALES,SALFRGHT,SALGRPAID FROM rbsPPACTIVITY WHERE DOCNUMBR = '" + RmCashReceiptsWindow.DocumentNumber + "'";

                DataDataAdapter = new System.Data.SqlClient.SqlDataAdapter();
                DataDataAdapter.SelectCommand = DataCommand;
                DataDataAdapter.TableMappings.Add("Table", "JOBDATA");

                DataDataAdapter.Fill(DataDataSet);
--- I THOUGHT THIS CHECK WOULD TAKE CARE OF THIS ---
                if (DataDataSet.Tables.Count > 0)
                {

                    txtJobNumber.Text = DataDataSet.Tables["JOBDATA"].Rows[0]["JOBNUMBR"].ToString();
                    txtInvoiceNumber.Text = DataDataSet.Tables["JOBDATA"].Rows[0]["INVCNMBR"].ToString();
                    txtReceiptsReceived.Text = DataDataSet.Tables["JOBDATA"].Rows[0]["RCPTRCVD"].ToString();
                    txtReceiptsDiscounts.Text = DataDataSet.Tables["JOBDATA"].Rows[0]["RCPTDISC"].ToString();
                    txtPPRequested.Text = DataDataSet.Tables["JOBDATA"].Rows[0]["PPREQSTD"].ToString();
                    txtPPPaid.Text = DataDataSet.Tables["JOBDATA"].Rows[0]["PPPAID"].ToString();
                    txtPPRecoup.Text = DataDataSet.Tables["JOBDATA"].Rows[0]["PPRECOUP"].ToString();
                    txtSales.Text = DataDataSet.Tables["JOBDATA"].Rows[0]["SALSALES"].ToString();
                    txtSalFreight.Text = DataDataSet.Tables["JOBDATA"].Rows[0]["SALFRGHT"].ToString();
                    txtSalGRPaid.Text = DataDataSet.Tables["JOBDATA"].Rows[0]["SALGRPAID"].ToString();

                    txtReceiptsReceived.Text = Convert.ToDecimal(txtReceiptsReceived.Text).ToString("C");
                    txtReceiptsDiscounts.Text = Convert.ToDecimal(txtReceiptsDiscounts.Text).ToString("C");
                    txtPPRequested.Text = Convert.ToDecimal(txtPPRequested.Text).ToString("C");
                    txtPPPaid.Text = Convert.ToDecimal(txtPPPaid.Text).ToString("C");
                    txtPPRecoup.Text = Convert.ToDecimal(txtPPRecoup.Text).ToString("C");
                    txtSales.Text = Convert.ToDecimal(txtSales.Text).ToString("C");
                    txtSalFreight.Text = Convert.ToDecimal(txtSalFreight.Text).ToString("C");
                    txtSalGRPaid.Text = Convert.ToDecimal(txtSalGRPaid.Text).ToString("C");
                }

            }
            catch (Exception ex)
            {
                MessageBox.Show("ERROR: " + ex.Message);
            }

        }

        private SqlConnection GetConnection()
        {
            // Create reader and open file containing connection string
            TextReader tr = new StreamReader(@"C:\Program Files\Microsoft Dynamics\GP\Addins\GSE.INI");

            // Read line containing the connection string
            ConnectionString = tr.ReadLine();

            // Close the stream
            tr.Close();
          
            DataConnection = new System.Data.SqlClient.SqlConnection(ConnectionString);
            DataConnection.Open();

            return DataConnection;
        }

        private void frmPPBreakdown_Load(object sender, EventArgs e)
        {

        }
    }
}

Open in new window

LVL 1
rwheeler23Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

AshokCommented:
I think this should be =====> DataDataAdapter.TableMappings.Add("Table", "SY00500");

private System.Data.SqlClient.SqlConnection myConnection;
private System.Data.DataSet myDataSet;
private System.Data.SqlClient.SqlCommand myCommand;
private System.Data.SqlClient.SqlDataAdapter DataAdapter;

string connectionString = "server=(local)\\NetSDK;" +
    "Trusted_Connection=yes; database=northwind";        
myConnection = new System.Data.Sql.SqlConnection(connectionString);
myConnection.Open( );
myDataSet = new System.Data.DataSet( );
myDataSet.CaseSensitive=true;
myCommand = new System.Data.SqlClient.SqlCommand( )
myCommand.Connection=myConnection;
myCommand.CommandText = "Select * from Customers";    // Table name is Customers
DataAdapter = new System.Data.SqlClient.SqlDataAdapter( );
DataAdapter.SelectCommand= myCommand;
DataAdapter.TableMappings.Add("Table","Customers");          // Table name must be same as before.
DataAdapter.Fill(myDataSet);

http://home.comcast.net/~jbondor/CSharp/snode111.htm

HTH
Ashok
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rwheeler23Author Commented:
I am using two separate datasets and in this case the second dataset may be empty and I need to check for an empty dataset prior to issung the fill command. I inserted a simple count query and then check if the count >0. This seems like an aweful lot of code for such a simple task. Are there more efficient ways to issue all three of these queries?
namespace PPActivityDetail
{
    public partial class frmPPBreakdown : DexUIForm
    {
        // Create a reference to the Cash Receipts Entry window
        static RmCashReceiptsForm CashReceiptsForm = Dynamics.Forms.RmCashReceipts;
        static RmCashReceiptsForm.RmCashReceiptsWindow RmCashReceiptsWindow = CashReceiptsForm.RmCashReceipts;

        // Define connection string
        string ConnectionString = "";

        // Setup SQL Connection Handlers
        private System.Data.SqlClient.SqlConnection DataConnection; 
        private System.Data.DataSet DataDataSet;
        private System.Data.SqlClient.SqlCommand DataCommand;
        private System.Data.SqlClient.SqlDataAdapter DataDataAdapter;

        /* Define variable to hold record exists flag */
        Boolean RecordExists = false;
        int RecordCount;
        
        // Define variables to hold batch number and batch source
        string BatchSource;
        string BatchNumber;

        public frmPPBreakdown()
        {
            InitializeComponent();

            /* Assign values to text box fields */
            txtBatchNumber.Text = RmCashReceiptsWindow.BatchNumber;
            txtDocumentNumber.Text = RmCashReceiptsWindow.DocumentNumber;
            txtCustomerNumber.Text = RmCashReceiptsWindow.CustomerNumber;
            txtDocumentDate.Text = RmCashReceiptsWindow.DocumentDate.Value.ToString("MM/dd/yy");

            /* Assign value for Batch Source and Batch Name */
            BatchSource = RmCashReceiptsWindow.BatchSource;
            BatchNumber = RmCashReceiptsWindow.BatchNumber;

            try
            {
                SqlConnection DataConnection = GetConnection();

                DataDataSet = new System.Data.DataSet();
                DataDataSet.CaseSensitive = false;

                DataCommand = new System.Data.SqlClient.SqlCommand();
                DataCommand.Connection = DataConnection;

                DataCommand.CommandText = "SELECT GLPOSTDT FROM SY00500 WHERE BCHSOURC = '" + BatchSource + "' AND BACHNUMB = '" + BatchNumber + "'";

                DataDataAdapter = new System.Data.SqlClient.SqlDataAdapter();
                DataDataAdapter.SelectCommand = DataCommand;
                DataDataAdapter.TableMappings.Add("Table", "POSTINGDATE");

                DataDataAdapter.Fill(DataDataSet);

                txtPostDate.Text = DataDataSet.Tables["POSTINGDATE"].Rows[0]["GLPOSTDT"].ToString();

                if (txtPostDate.Text != "")
                {
                    txtPostDate.Text = Convert.ToDateTime(txtPostDate.Text).ToString("MM/dd/yy");
                }

                DataCommand.CommandText = "SELECT COUNT(*) as RECORDCOUNT FROM rbsPPACTIVITY WHERE DOCNUMBR = '" + RmCashReceiptsWindow.DocumentNumber + "'";
                DataDataAdapter = new System.Data.SqlClient.SqlDataAdapter();
                DataDataAdapter.SelectCommand = DataCommand;
                DataDataAdapter.TableMappings.Add("Table", "RECORDCOUNT");

                DataDataAdapter.Fill(DataDataSet);
                RecordCount = Convert.ToInt16(DataDataSet.Tables["RECORDCOUNT"].Rows[0]["RECORDCOUNT"].ToString());

                if (RecordCount > 0)
                {

                    DataCommand.CommandText = "SELECT JOBNUMBR,INVCNMBR,RCPTRCVD,RCPTDISC,PPREQSTD,PPPAID,PPRECOUP,SALSALES,SALFRGHT,SALGRPAID FROM rbsPPACTIVITY WHERE DOCNUMBR = '" + RmCashReceiptsWindow.DocumentNumber + "'";

                    DataDataAdapter = new System.Data.SqlClient.SqlDataAdapter();
                    DataDataAdapter.SelectCommand = DataCommand;
                    DataDataAdapter.TableMappings.Add("Table", "JOBDATA");

                    DataDataAdapter.Fill(DataDataSet);
                    
                    txtJobNumber.Text = DataDataSet.Tables["JOBDATA"].Rows[0]["JOBNUMBR"].ToString();
                    txtInvoiceNumber.Text = DataDataSet.Tables["JOBDATA"].Rows[0]["INVCNMBR"].ToString();
                    txtReceiptsReceived.Text = DataDataSet.Tables["JOBDATA"].Rows[0]["RCPTRCVD"].ToString();
                    txtReceiptsDiscounts.Text = DataDataSet.Tables["JOBDATA"].Rows[0]["RCPTDISC"].ToString();
                    txtPPRequested.Text = DataDataSet.Tables["JOBDATA"].Rows[0]["PPREQSTD"].ToString();
                    txtPPPaid.Text = DataDataSet.Tables["JOBDATA"].Rows[0]["PPPAID"].ToString();
                    txtPPRecoup.Text = DataDataSet.Tables["JOBDATA"].Rows[0]["PPRECOUP"].ToString();
                    txtSales.Text = DataDataSet.Tables["JOBDATA"].Rows[0]["SALSALES"].ToString();
                    txtSalFreight.Text = DataDataSet.Tables["JOBDATA"].Rows[0]["SALFRGHT"].ToString();
                    txtSalGRPaid.Text = DataDataSet.Tables["JOBDATA"].Rows[0]["SALGRPAID"].ToString();

                    txtReceiptsReceived.Text = Convert.ToDecimal(txtReceiptsReceived.Text).ToString("C");
                    txtReceiptsDiscounts.Text = Convert.ToDecimal(txtReceiptsDiscounts.Text).ToString("C");
                    txtPPRequested.Text = Convert.ToDecimal(txtPPRequested.Text).ToString("C");
                    txtPPPaid.Text = Convert.ToDecimal(txtPPPaid.Text).ToString("C");
                    txtPPRecoup.Text = Convert.ToDecimal(txtPPRecoup.Text).ToString("C");
                    txtSales.Text = Convert.ToDecimal(txtSales.Text).ToString("C");
                    txtSalFreight.Text = Convert.ToDecimal(txtSalFreight.Text).ToString("C");
                    txtSalGRPaid.Text = Convert.ToDecimal(txtSalGRPaid.Text).ToString("C");

                    RecordExists = true;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("ERROR: " + ex.Message);
            }
          
        }

        private SqlConnection GetConnection()
        {
            // Create reader and open file containing connection string
            TextReader tr = new StreamReader(@"C:\Program Files\Microsoft Dynamics\GP\Addins\GSE.INI");

            // Read line containing the connection string
            ConnectionString = tr.ReadLine();

            // Close the stream
            tr.Close();
          
            DataConnection = new System.Data.SqlClient.SqlConnection(ConnectionString);
            DataConnection.Open();

            return DataConnection;
        }

        private void frmPPBreakdown_Load(object sender, EventArgs e)
        {

        }

        private void btnSave_Click(object sender, EventArgs e)
        {
            try
            {
                SqlConnection con = GetConnection();
                SqlCommand cmd = new SqlCommand();

                cmd.CommandType = CommandType.StoredProcedure;

                if (txtReceiptsReceived.Text.StartsWith("$"))
                {
                    txtReceiptsReceived.Text = txtReceiptsReceived.Text.Substring(1,txtReceiptsReceived.Text.Length-1);
                }
                if (txtReceiptsDiscounts.Text.StartsWith("$"))
                {
                    txtReceiptsDiscounts.Text = txtReceiptsDiscounts.Text.Substring(1, txtReceiptsDiscounts.Text.Length - 1);
                }
                if (txtPPRequested.Text.StartsWith("$"))
                {
                    txtPPRequested.Text = txtPPRequested.Text.Substring(1, txtPPRequested.Text.Length - 1);
                }
                if (txtPPPaid.Text.StartsWith("$"))
                {
                    txtPPPaid.Text = txtPPPaid.Text.Substring(1, txtPPPaid.Text.Length - 1);
                }
                if (txtPPRecoup.Text.StartsWith("$"))
                {
                    txtPPRecoup.Text = txtPPRecoup.Text.Substring(1, txtPPRecoup.Text.Length - 1);
                }
                if (txtSales.Text.StartsWith("$"))
                {
                    txtSales.Text = txtSales.Text.Substring(1, txtSales.Text.Length - 1);
                }
                if (txtSalFreight.Text.StartsWith("$"))
                {
                    txtSalFreight.Text = txtSalFreight.Text.Substring(1, txtSalFreight.Text.Length - 1);
                }
                if (txtSalGRPaid.Text.StartsWith("$"))
                {
                    txtSalGRPaid.Text = txtSalGRPaid.Text.Substring(1, txtSalGRPaid.Text.Length - 1);
                }

                cmd.Parameters.Add(new SqlParameter("@BACHNUMB", txtBatchNumber.Text));
                cmd.Parameters.Add(new SqlParameter("@DOCNUMBR", txtDocumentNumber.Text));
                cmd.Parameters.Add(new SqlParameter("@CUSTNMBR", txtCustomerNumber.Text));
                cmd.Parameters.Add(new SqlParameter("@DOCDATE", Convert.ToDateTime(txtDocumentDate.Text).ToString("MM/dd/yy")));
                cmd.Parameters.Add(new SqlParameter("@POSTDATE", Convert.ToDateTime(txtPostDate.Text).ToString("MM/dd/yy")));
                cmd.Parameters.Add(new SqlParameter("@JOBNUMBR", txtJobNumber.Text));
                cmd.Parameters.Add(new SqlParameter("@INVCNMBR", txtInvoiceNumber.Text));
                cmd.Parameters.Add(new SqlParameter("@RCPTRCVD", Convert.ToDecimal(txtReceiptsReceived.Text)));
                cmd.Parameters.Add(new SqlParameter("@RCPTDISC", Convert.ToDecimal(txtReceiptsDiscounts.Text)));
                cmd.Parameters.Add(new SqlParameter("@PPREQSTD", Convert.ToDecimal(txtPPRequested.Text)));
                cmd.Parameters.Add(new SqlParameter("@PPPAID", Convert.ToDecimal(txtPPPaid.Text)));
                cmd.Parameters.Add(new SqlParameter("@PPRECOUP", Convert.ToDecimal(txtPPRecoup.Text)));
                cmd.Parameters.Add(new SqlParameter("@SALSALES", Convert.ToDecimal(txtSales.Text)));
                cmd.Parameters.Add(new SqlParameter("@SALFRGHT", Convert.ToDecimal(txtSalFreight.Text)));
                cmd.Parameters.Add(new SqlParameter("@SALGRPAID", Convert.ToDecimal(txtSalGRPaid.Text)));
               
                if (RecordExists)
                {
                    cmd.CommandText = "rbsUPDATE_PPACTIVITY";
                }
                else
                {
                    cmd.CommandText = "rbsINSERT_PPACTIVITY";
                }

                cmd.Connection = con;

                cmd.ExecuteNonQuery();

                con.Close();

                this.Hide();
                this.Dispose();
            }
            catch (Exception ex)
            {
                MessageBox.Show("ERROR::" + ex.Message);
                this.Close();

            }
        }
    }
}

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.