none
SQL Constraint - transfer to DataTable RRS feed

  • Question

  • Hi all

    I have a Sql DataBase Table that has a primary key and a Column with an Index of Unique constraint 

    I Use Table Adapter and Designer to create the DataSet in my winform application

    Then I fill the dataset with the Fill Query

    If I loop thru columns of the DataTable in DataSet to check  the value of each Datacolumn.unique property I get 'True' for the primary key but 'false' for the column that has Unique constraint in Database table.

    What I miss ? I do not understand 

    Please help me. Thanks

    Sunday, December 16, 2018 3:45 PM

Answers

  • Wow,  ok ,

    I can set it manually. I just thought I made a mistake in my application.

    Just one thing. Do you Know if this problem is solved if I use DataAdapter instead of Table Adapter, using somethig like FillWithSchema ?

    Doesn't matter if you use a TableAdapter or DataAdapter or FillWithSchema, same as I indicted in my prior reply, it's not possible in 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 Claudio111 Tuesday, December 18, 2018 9:28 AM
    Monday, December 17, 2018 8:42 PM
    Moderator

All replies

  • Then go into the xsd designer, select the field and set Unique to true if it's currently set to false. The data wizards are not by any means perfect.

    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, December 16, 2018 6:01 PM
    Moderator
  • You are saying that there is not way to set runtime a data column unique property getting it from sql data column index constraint ?
    Sunday, December 16, 2018 6:33 PM
  • You are saying that there is not way to set runtime a data column unique property getting it from sql data column index constraint ?

    A index is done in the database table e.g.the following is done in SSMS.


    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, December 16, 2018 6:58 PM
    Moderator
  • As I said in my question I set the index Unique in SSMS for the column but I dont find this property set to 'true' in the DataTable of Dataset.

    Why?

    Sunday, December 16, 2018 7:36 PM
  • Been out which is why I'm replying now.

    A unique index to turn off you must alter the table in the database. At project level you use SomeDataSet.EnforceContraints = False or SomeDataSet.SomeTable.SomeField.Unique = false which allows a duplicate but only in the project. When you go and save this SQL-Server and rightfully so will throw an exception. TableAdapter or DataAdapter or using a managed data provider are all bound to how the table and fields in the table are defined. Now if you using the full version of SQL-Server you could alter the table via SMO (SqlServer Management Objects) which are an underlying API for SQL-Server but does not work for SQLEXPRESS or LocalDb so with that you are left with working with T-SQL to perform an ALTER TABLE....

    In the end you get an exception no matter what you do with settings in 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

    Monday, December 17, 2018 2:50 AM
    Moderator
  • Hi Karen

    maybe i was not clear. I want the unique index in database imported in the Dataset

    This is How I set index in DB

    The following is the DAL to access Database

    Public Class DALTabRegioni
        Inherits DALExceptionsHandler
    
        Public LastRegioneID As Integer
        Private DAL_DSET As DSET
        Private TA_Regioni As New DSETTableAdapters.TabRegioniTableAdapter
        Private TA_Nazioni As New DSETTableAdapters.TabNazioniTableAdapter
        Private DAL_DTRegioni As DSET.TabRegioniDataTable
        ' ricordarsi di cambiare query standard per INSERT 
        ' metodi 
    #Region "FILL DATASET"
        Public Function TabRegioniFillDataset() As DSET
            mHasException = False
            mHasSqlException = False
            mHasCurrException = False
            DAL_DSET = New DSET
            Try
                TA_Regioni.Fill(DAL_DSET.TabRegioni)
                TA_Nazioni.Fill(DAL_DSET.TabNazioni)
                ' TODO controlla se ci sono naioni
            Catch sqlex As SqlException
                mHasSqlException = True
                mLastSqlException = sqlex
            Catch ex As Exception
                mHasException = True
                mLastException = ex
            End Try
            Return DAL_DSET
        End Function
    
    #End Region
    

    And this is the FillQuery of TabRegion TableAdapter

    In DataSet I did not add anything by designer.

    But the column RegioneDes in not set to Unique in DataTable

    I'm using sql express 

    What is the problem ?

    Monday, December 17, 2018 12:54 PM
  • I completely understand what you are talking about and gave you a solution in my first reply. And that data wizards are not perfect.

    So with "But the column RegioneDes in not set to Unique in DataTable", you need to set it yourself, repeating myself, data wizards are not perfect hence the reason why you need to do this yourself.

    You may also ask, can this be fixed? doubtful. If you want to ask Microsoft then use the "Send Feedback" button in the IDE.


    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

    Monday, December 17, 2018 1:27 PM
    Moderator
  • Wow,  ok ,

    I can set it manually. I just thought I made a mistake in my application.

    Just one thing. Do you Know if this problem is solved if I use DataAdapter instead of Table Adapter, using somethig like FillWithSchema ?

    Monday, December 17, 2018 8:09 PM
  • Wow,  ok ,

    I can set it manually. I just thought I made a mistake in my application.

    Just one thing. Do you Know if this problem is solved if I use DataAdapter instead of Table Adapter, using somethig like FillWithSchema ?

    Doesn't matter if you use a TableAdapter or DataAdapter or FillWithSchema, same as I indicted in my prior reply, it's not possible in 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 Claudio111 Tuesday, December 18, 2018 9:28 AM
    Monday, December 17, 2018 8:42 PM
    Moderator