none
How to fix this error SqlException was unhandled (System.Data.SqlClient.SqlException) RRS feed

  • Question

  • good morning 

    I have this problem :

    This is the exception  thrown at dr = com.ExecuteReader(); 

     private void pneu_SelectedIndexChanged(object sender, EventArgs e)
            {
             SqlCommand com = new SqlCommand();
            SqlConnection con = new SqlConnection("Data Source=CHAIMA;Initial Catalog=OptiMaint;Integrated Security=True");
                SqlDataReader dr;
                com.CommandText = " select  ORG_DESIGNATION , ORG_FAMILLE , ORG_GROUPE ,ORG_PRIX_ACHAT , ORG_IMAGE from dbo.ORGANES where dbo.ORGANES ='" + pneu.Text + "'";
                com.Connection = con;
                con.Open();
                dr = com.ExecuteReader(); // 
                while (dr.Read())
                {
                    textBox1.Text = dr["ORG_DESIGNATION"].ToString();
                    textBox2.Text = dr["ORG_FAMILLE"].ToString();
                    textBox3.Text = dr["ORG_GROUPE"].ToString();
                    textBox4.Text = dr["ORG_PRIX_ACHAT"].ToString();
                    photo.ImageLocation = dr["ORG_IMAGE"].ToString();
                }
                con.Close();
            }

    Kindly Help!
    Thank you!
    Sunday, April 15, 2018 8:07 AM

All replies

  • Well, you need to post more than what you have posted about the exception, like the inner. Exception.message, if there. 

    https://msdn.microsoft.com/en-us/library/system.exception.innerexception(v=vs.110).aspx

    https://www.c-sharpcorner.com/uploadfile/puranindia/innerexception-in-C-Sharp/

    You can also get the inner.Exception if you run the program in VS debug mode, let it blowup that will produce the exception popup screen , and you can click on "View Details".

    Sunday, April 15, 2018 8:43 AM
  • Looking at the code there are several issues.

    • First, any time there is a WHERE condition you need to use a command parameter.
    • Creating a connection and command for each time the selected index changes is a bad idea. Instead consider some form of data binding so your not hitting the database each time the selected index changes.
    • You don't need to do while dt.read (but as mentioned in the above best not to be hitting the database) when one record is being returned.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Sunday, April 15, 2018 9:21 AM
    Moderator
  • the Exception.message:

    https://msdn.microsoft.com/query/dev14.query?appId=Dev14IDEF1&l=EN-US&k=k(EHSql);k(TargetFrameworkMoniker-.NETFramework,Version%3Dv4.5.2);k(DevLang-csharp)&rd=true

    Sunday, April 15, 2018 9:22 AM
  • That does us no good. The following is how I would perform what it appears you are after. The ListBox is loaded with product, each time the selected index changes I get supplier details.

    The data structure in SQL-Server

    Data class for reading data

    using System.Data;
    using System.Data.SqlClient;
    
    namespace WindowsFormsApp1
    {
        public class DataOperations
        {
    
            /// <summary>
            /// Replace with your SQL Server name
            /// </summary>
            private string Server = "KARENS-PC";
            /// <summary>
            /// Database in which data resides, see SQL_Script.sql
            /// </summary>
            private string Catalog = "NorthWindAzure1";
            /// <summary>
            /// Connection string for connecting to the database
            /// </summary>
            private string ConnectionString;
            public DataOperations()
            {
                ConnectionString = 
                    $"Data Source={Server};Initial Catalog={Catalog};Integrated Security=True";
            }
    
            public DataTable Products()
            {
                var dt = new DataTable();
                using (var cn = new SqlConnection { ConnectionString = ConnectionString })
                {
                    using (var cmd = new SqlCommand { Connection = cn })
                    {
                        cmd.CommandText = "SELECT ProductID,ProductName,SupplierID FROM dbo.Products";
    
                        cn.Open();
                        dt.Load(cmd.ExecuteReader());
                    }
                }
    
                return dt;
    
            }
            public DataTable Suppliers()
            {
                var dt = new DataTable();
                using (var cn = new SqlConnection { ConnectionString = ConnectionString })
                {
                    using (var cmd = new SqlCommand { Connection = cn })
                    {
                        cmd.CommandText = "SELECT SupplierID,CompanyName,ContactName,ContactTitle " +
                                          "FROM dbo.Suppliers";
    
                        cn.Open();
                        dt.Load(cmd.ExecuteReader());
                    }
                }
    
                return dt;
            }
        }
    }
    

    Form code

    using System;
    using System.Data;
    using System.Windows.Forms;
    
    namespace WindowsFormsApp1
    {
        public partial class Form1 : Form
        {
            private readonly BindingSource _bsSuppliers = new BindingSource();
            public Form1()
            {
                InitializeComponent();
                Shown += Form1_Shown;
            }
    
            private void Form1_Shown(object sender, EventArgs e)
            {
                var ops = new DataOperations();
    
                listBox1.DataSource = ops.Products();
                listBox1.DisplayMember = "ProductName";
                listBox1.ValueMember = "SupplierID";
                listBox1.SelectedIndexChanged += ListBox1_SelectedIndexChanged;
    
                _bsSuppliers.DataSource = ops.Suppliers();
    
                PostionRow();
            }
            private void ListBox1_SelectedIndexChanged(object sender, EventArgs e)
            {
                PostionRow();           
            }
    
            private void PostionRow()
            {
                txtSupplierName.Text = "";
                if (listBox1.SelectedItem != null)
                {
                    _bsSuppliers.Filter = $"SupplierID = {(int)listBox1.SelectedValue}";
                    if (_bsSuppliers.Count > 0)
                    {
                        var row = ((DataRowView)_bsSuppliers.Current).Row;
                        txtSupplierName.Text = row.Field<string>("CompanyName");
                        txtContactName.Text = row.Field<string>("ContactName");
                        txtContactTitle.Text = row.Field<string>("ContactTitle");
                    }
                }
            }
        }
    }
    

    IMPORTANT

    The rule for each product is there is a supplier so there is no chance of issues finding one but even so I have assertion in case something goes wrong when using the filter for the BindingSource, if a supplier is not located the TextBox controls will be left empty.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Sunday, April 15, 2018 10:06 AM
    Moderator
  • the Exception.message:

    https://msdn.microsoft.com/query/dev14.query?appId=Dev14IDEF1&l=EN-US&k=k(EHSql);k(TargetFrameworkMoniker-.NETFramework,Version%3Dv4.5.2);k(DevLang-csharp)&rd=true


    What kind of nonsense is this? You were asked to post the inner.exception.message. You where given links on how to do it. And you post this nonsense? You should learn how to do exception handling and how to determine what the exception is really about, not the generic one you are posting about.   
    Sunday, April 15, 2018 10:24 AM
  • At the very least, you should wrap that whole thing in a try/catch. You should also use parameters and not actually do all this in the SelectedIndexChanged event handler (as Karen indicated) . With a try/catch, you'd at least know the reason for your exception (which is why @DA924x asked for the InnerException).

    private void pneu_SelectedIndexChanged(object sender, EventArgs e)
    {
        SqlConnection con = new SqlConnection("Data Source=CHAIMA;Initial Catalog=OptiMaint;Integrated Security=True");
        try
        {
            SqlCommand com = new SqlCommand();
            // rest of your code
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
        if (con.State != ConnectionState.Closed)
            con.Close();
    }
    


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Sunday, April 15, 2018 5:05 PM
    Moderator
  • @Karen

    Do you know what the forums need and I do what you do too in posting code examples? There needs to be a way for a reader of the read in navigating a thread is the ability to collapse a post in the thread that has long code examples in the thread.

    Sunday, April 15, 2018 6:12 PM
  • @Karen

    Do you know what the forums need and I do what you do too in posting code examples? There needs to be a way for a reader of the read in navigating a thread is the ability to collapse a post in the thread that has long code examples in the thread.

    @DA924x

    That would be nice, do you have any sites that are doing this? Have not seen it myself but a good idea.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Sunday, April 15, 2018 10:33 PM
    Moderator
  • @Karen

    Do you know what the forums need and I do what you do too in posting code examples? There needs to be a way for a reader of the read in navigating a thread is the ability to collapse a post in the thread that has long code examples in the thread.

    @DA924x

    That would be nice, do you have any sites that are doing this? Have not seen it myself but a good idea.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    No I have not seen this, but I don't think it would be that hard to implement seeing that yon can expand and collapse a entire thread in the forum.

    Monday, April 16, 2018 3:29 AM