none
SQL Exception - File Name RRS feed

  • Question

  • Hi All

    I'm handling SqlException when trying to fill a DataSet from SQL DataBase

    If I try to read a Table  that doesn't exist in the DB  I get a SqlException.Message.

    I would like to send to user the name of Table that is missing.

    How Can I get the Table Name ?

    Friday, January 4, 2019 7:29 PM

Answers

  • Is this using DataSet defined by data wizards in visual Studio which are implemented via TableAdapter?

    EDIT

    No matter what method was used for SQL-Server database you can create a SqlClient connection and command, populate a DataTable or a List(Of String) with table names using where TODO is your database name.

    SELECT TABLE_NAME
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG='TODO'

    Otherise 

    Using MS-Access? Get table names

    Dim oDataTable As DataTable = yourConnection.GetOleDbSchemaTable(OleDb.OleDbSchemaGuid.Tables, Nothing)

    using the get table names check for the table in question before attempting to read data and abort if the table does not exists.


    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


    Friday, January 4, 2019 8:47 PM
    Moderator
  • If this software is made by you and you know all of the expected tables, then you can make a query that checks the existence of the tables. For example, the next one check three names and returns the ones that do not exist:

       SELECT t.n AS MissingName

       FROM (VALUES ('Users'), ('Customers'), ('Products')) AS t(n)

       WHERE OBJECT_ID(t.n) IS NULL

     

    You can do this on some initialization stage of your software using SqlCommand.ExecuteReader.

    There are more kinds of such queries.

    • Edited by Viorel_MVP Friday, January 4, 2019 8:59 PM
    • Marked as answer by Claudio111 Monday, January 14, 2019 11:52 AM
    Friday, January 4, 2019 8:56 PM
  • Hi,

    You can set the table name to a variable, or return a table name if an error is reported when filling a Dataset.

    code:

    Imports System.Data.SqlClient
    Public Class Form1
        Dim constr As String = "Data Source = (localdb)\MSSQLLocalDB; Integrated Security = True ;AttachDbFileName= C:\Users\alexl2\Desktop\DataBase\Alex\alex.mdf"
        Dim conn As SqlConnection
        Dim sda As SqlDataAdapter
        Dim ds As New DataSet
    
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Dim tablename As String = "tablename"
            Using conn = New SqlConnection(constr)
                conn.Open()
    
                sda = New SqlDataAdapter("Select * From " & tablename, conn)
                Try
                    sda.Fill(ds)
                Catch ex As Exception
                    MsgBox(tablename & " table is not exist")
                End Try
            End Using
        End Sub
    End Class

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Claudio111 Monday, January 14, 2019 11:52 AM
    Monday, January 7, 2019 2:19 AM

All replies

  • Is this using DataSet defined by data wizards in visual Studio which are implemented via TableAdapter?

    EDIT

    No matter what method was used for SQL-Server database you can create a SqlClient connection and command, populate a DataTable or a List(Of String) with table names using where TODO is your database name.

    SELECT TABLE_NAME
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG='TODO'

    Otherise 

    Using MS-Access? Get table names

    Dim oDataTable As DataTable = yourConnection.GetOleDbSchemaTable(OleDb.OleDbSchemaGuid.Tables, Nothing)

    using the get table names check for the table in question before attempting to read data and abort if the table does not exists.


    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


    Friday, January 4, 2019 8:47 PM
    Moderator
  • If this software is made by you and you know all of the expected tables, then you can make a query that checks the existence of the tables. For example, the next one check three names and returns the ones that do not exist:

       SELECT t.n AS MissingName

       FROM (VALUES ('Users'), ('Customers'), ('Products')) AS t(n)

       WHERE OBJECT_ID(t.n) IS NULL

     

    You can do this on some initialization stage of your software using SqlCommand.ExecuteReader.

    There are more kinds of such queries.

    • Edited by Viorel_MVP Friday, January 4, 2019 8:59 PM
    • Marked as answer by Claudio111 Monday, January 14, 2019 11:52 AM
    Friday, January 4, 2019 8:56 PM
  • Hi,

    You can set the table name to a variable, or return a table name if an error is reported when filling a Dataset.

    code:

    Imports System.Data.SqlClient
    Public Class Form1
        Dim constr As String = "Data Source = (localdb)\MSSQLLocalDB; Integrated Security = True ;AttachDbFileName= C:\Users\alexl2\Desktop\DataBase\Alex\alex.mdf"
        Dim conn As SqlConnection
        Dim sda As SqlDataAdapter
        Dim ds As New DataSet
    
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Dim tablename As String = "tablename"
            Using conn = New SqlConnection(constr)
                conn.Open()
    
                sda = New SqlDataAdapter("Select * From " & tablename, conn)
                Try
                    sda.Fill(ds)
                Catch ex As Exception
                    MsgBox(tablename & " table is not exist")
                End Try
            End Using
        End Sub
    End Class

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Claudio111 Monday, January 14, 2019 11:52 AM
    Monday, January 7, 2019 2:19 AM