none
The Sqlexception Exception was not handling RRS feed

  • Question

  • Hi Everyone,

    I' m turning around , please would someone help me.

    I'm geting the sqlexception at this part of a project:

      Private Sub ShowPonte()
    If Not IsNothing(ds.Tables("MyPonte1")) Then
    ds.Tables("MyPonte1").Clear()
    End If
    con = New SqlConnection(cs)
    con.Open()        
    Dim sql As String = "SELECT * FROM Ponte1 Where CoupleID= " & txtCoupleCode.Text & ""
    cmd = New SqlCommand(sql)
    cmd.Connection = con
    adp = New SqlDataAdapter(cmd)
    ds = New DataSet()
    adp.SelectCommand = cmd
    adp.Fill(ds, "MyPonte1")
    Dgw1.DataSource = ds.Tables("MyPonte1")
    '-- CoupleID
    Dgw1.Columns(0).DefaultCellStyle.Alignment=DataGridViewContentAlignment.MiddleCenter   
    End Sub
    thank you.
    • Edited by Bajtitou Saturday, March 17, 2018 12:57 AM
    Saturday, March 17, 2018 12:55 AM

Answers


  • Hi, 

    Thank you for the quick answer .

    Yes of course I'm using  all parameters in top of the code . 

    Ihe exception is ' The Sqlexception exception was not handling'.

    at this line :.

    adp.Fill(ds, "MyPonte1")

    Thanks verry mutch 

    Best Regards .. Bajtitou


    You are NOT using parameters, the highlighted section is what I asked about, there is no parameter there but instead string concatenation. My guess is this is what is causing the issue. Please get rid of the string concatenation and use a proper parameter as per the link I provided in my first reply.


    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

    • Marked as answer by Bajtitou Saturday, March 17, 2018 10:01 PM
    Saturday, March 17, 2018 8:35 PM
    Moderator
  • I don't see an issue with the code presented even though you hare having issues it's not in the code shown.

    Since you are newing up the DataSet and SqlDataAdapter in the procedure and not using them outside of the method I would suggest using a data class as shown below. LoadData1 is a match up to your code while LoadData2 is what I recommend. Create an instance of the class , call the method, assign the DataTable to your DataGridView.

    Usage 

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim ops as New DataOperations
        Dim dt as DataTable = ops.LoadData2()
        If ops.HasErrors
            MessageBox.Show(ops.ExceptionMessage)
        End If
    End Sub

    Data class

    Imports System.Data.SqlClient 
    Public Class DataOperations
        Private ReadOnly _connectionString As String = 
            "Data Source=KARENS-PC;" & 
            "Initial Catalog=MasterDetailSimple;" & 
            "Integrated Security=True" 
        Public Property HasErrors As Boolean 
        Public Property ExceptionMessage As String 
    
        Public Function LoadData1() As DataTable
            Dim dt As new DataTable
     
            Using cn As New SqlConnection With {.ConnectionString = _connectionString} 
    
                Dim selectStatement as String = 
                        "SELECT id,FirstName,LastName,Address,City,State,ZipCode " & 
                        "FROM Customer"
    
                Dim da As New SqlDataAdapter(selectStatement, cn) 
                Try 
                    Dim ds As New DataSet 
                    da.Fill(ds, "Customer") 
                    dt = ds.Tables("Customer") 
                Catch ex As Exception 
                    HasErrors = True 
                    ExceptionMessage = ex.Message 
                End Try 
            End Using 
    
            return dt
    
        End Function 
         ''' <summary>
         ''' I recommend this version
         ''' </summary>
         ''' <returns></returns>
        Public Function LoadData2() As DataTable
            Dim dt As new DataTable
     
            Using cn As New SqlConnection With {.ConnectionString = _connectionString} 
                Using cmd As new SqlCommand With{.Connection = cn} 
    
                    cmd.CommandText = "SELECT id,FirstName,LastName,Address,City,State,ZipCode " & 
                                      "FROM Customer"
    
                    cn.Open()
                    dt.Load(cmd.ExecuteReader())
                End Using
            End Using 
    
            return dt
    
        End Function 
    End Class
    
    If these fail then you have an issue with the database, not code.


    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

    • Marked as answer by Bajtitou Saturday, March 17, 2018 10:01 PM
    Saturday, March 17, 2018 8:50 PM
    Moderator

All replies

  • Hello,

    • What is the exception
    • Which line is the exception thrown on

    On top of this you need to be using parameters for your WHERE condition rather than string concatenation. Also why are you adding a empty string to the end of the SELECT statement ???


    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


    Saturday, March 17, 2018 1:01 AM
    Moderator
  • Hello,

    • What is the exception
    • Which line is the exception thrown on

    On top of this you need to be using parameters for your WHERE condition rather than string concatenation. Also why are you adding a empty string to the end of the SELECT statement ???


    Hi, 

    Thank you for the quick answer .

    Yes of course I'm using  all parameters in top of the code . 

    Ihe exception is ' The Sqlexception exception was not handling'.

    at this line :.

    adp.Fill(ds, "MyPonte1")

    Thanks verry mutch 

    Best Regards .. Bajtitou


    • Edited by Bajtitou Saturday, March 17, 2018 8:06 AM
    Saturday, March 17, 2018 8:02 AM
  • No, you are not using parameters. This is what Karen means...

    Dim sql As String = "SELECT * FROM Ponte1 Where CoupleID= @CoupleID;"
    cmd = New SqlCommand(sql)
    cmd.Parameters.Add("@CoupleID", SqlDbType.Int).Value = txtWhatever.Text


    Mary

    Saturday, March 17, 2018 8:30 AM
  • Hi, 

    I m trying to fill datagridview from an sql table .
    I have made some change to the last code ,I get no error but just empty rows .

    here is the new code:

     
     Private Sub ShowPonte()
    con = New SqlConnection(cs)
    con.Open()
     Dim sql As String = "SELECT * FROM Ponte1 "
    Dim Adp As New SqlDataAdapter(sql, con)
     Dim ds As New DataSet()
     Adp.Fill(ds, "ponte1") 
     Dgw1.DataSource = ds.Tables(0)
    Dgw1.Columns(0).DefaultCellStyle.Alignment =
    DataGridViewContentAlignment.MiddleCenter
    con.Close()
    End Sub

    So what's wrong with.

    thanks and best regards .. Bajtitou

    Saturday, March 17, 2018 7:55 PM

  • Hi, 

    Thank you for the quick answer .

    Yes of course I'm using  all parameters in top of the code . 

    Ihe exception is ' The Sqlexception exception was not handling'.

    at this line :.

    adp.Fill(ds, "MyPonte1")

    Thanks verry mutch 

    Best Regards .. Bajtitou


    You are NOT using parameters, the highlighted section is what I asked about, there is no parameter there but instead string concatenation. My guess is this is what is causing the issue. Please get rid of the string concatenation and use a proper parameter as per the link I provided in my first reply.


    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

    • Marked as answer by Bajtitou Saturday, March 17, 2018 10:01 PM
    Saturday, March 17, 2018 8:35 PM
    Moderator
  • I don't see an issue with the code presented even though you hare having issues it's not in the code shown.

    Since you are newing up the DataSet and SqlDataAdapter in the procedure and not using them outside of the method I would suggest using a data class as shown below. LoadData1 is a match up to your code while LoadData2 is what I recommend. Create an instance of the class , call the method, assign the DataTable to your DataGridView.

    Usage 

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim ops as New DataOperations
        Dim dt as DataTable = ops.LoadData2()
        If ops.HasErrors
            MessageBox.Show(ops.ExceptionMessage)
        End If
    End Sub

    Data class

    Imports System.Data.SqlClient 
    Public Class DataOperations
        Private ReadOnly _connectionString As String = 
            "Data Source=KARENS-PC;" & 
            "Initial Catalog=MasterDetailSimple;" & 
            "Integrated Security=True" 
        Public Property HasErrors As Boolean 
        Public Property ExceptionMessage As String 
    
        Public Function LoadData1() As DataTable
            Dim dt As new DataTable
     
            Using cn As New SqlConnection With {.ConnectionString = _connectionString} 
    
                Dim selectStatement as String = 
                        "SELECT id,FirstName,LastName,Address,City,State,ZipCode " & 
                        "FROM Customer"
    
                Dim da As New SqlDataAdapter(selectStatement, cn) 
                Try 
                    Dim ds As New DataSet 
                    da.Fill(ds, "Customer") 
                    dt = ds.Tables("Customer") 
                Catch ex As Exception 
                    HasErrors = True 
                    ExceptionMessage = ex.Message 
                End Try 
            End Using 
    
            return dt
    
        End Function 
         ''' <summary>
         ''' I recommend this version
         ''' </summary>
         ''' <returns></returns>
        Public Function LoadData2() As DataTable
            Dim dt As new DataTable
     
            Using cn As New SqlConnection With {.ConnectionString = _connectionString} 
                Using cmd As new SqlCommand With{.Connection = cn} 
    
                    cmd.CommandText = "SELECT id,FirstName,LastName,Address,City,State,ZipCode " & 
                                      "FROM Customer"
    
                    cn.Open()
                    dt.Load(cmd.ExecuteReader())
                End Using
            End Using 
    
            return dt
    
        End Function 
    End Class
    
    If these fail then you have an issue with the database, not code.


    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

    • Marked as answer by Bajtitou Saturday, March 17, 2018 10:01 PM
    Saturday, March 17, 2018 8:50 PM
    Moderator
  • Hi,

    thank you verry much ,

    I have found the solution ,

    I used an other code to fill the datagridview from sql table and this is my code:

    Private Sub ShowPonte()
    Try
    con = New SqlConnection(cs)
    con.Open()
    cmd = New SqlCommand("Select PID,RTRIM(Name),Age,Sexe,Date,Observation from Ponte1 order by Age", con)
    rdr=cmd.ExecuteReader(CommandBehavior.CloseConnection)
    Dgw1.Rows.Clear()
    While (rdr.Read() = True)
    Dgw1.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3), rdr(4),rdr(5))  
    End While
    con.Close()
    Catch ex As Exception
    MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK,MessageBoxIcon.Error)
    End Try
    End Sub

    Saturday, March 17, 2018 10:14 PM