Link to home
Start Free TrialLog in
Avatar of rwheeler23
rwheeler23Flag for United States of America

asked on

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

ASKER CERTIFIED SOLUTION
Avatar of Ashok
Ashok
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of rwheeler23

ASKER

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