none
table doesn't have primary key (although it has) RRS feed

  • Question

  • Hi,

    Based on the primary key of a table, I would like to get data from a certain row.

    The code to determine the index of this row is;

    index = Form1.ds.Tables("CoreDB").Rows.IndexOf(Form1.ds.Tables("CoreDB").Rows.Find(KeyValue))

    But this gives an error "Table doesn't have a primary key"

    If I look at the datasources, the correct row has this "yellow key". ( the .xsd file in the tree browser)


    Maybe it has something to do with the way I declare the dataset;


    Dim ds As New DataSet
    Dim dbProvider As String
    Dim dbSource As String
    Dim dbConn As New OleDb.OleDbConnection
    dbProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"
    dbSource = "Data Source = C:\Documents and Settings\..."
    dbConn.ConnectionString = dbProvider & dbSource
    dbConn.Open()
    Dim da As OleDb.OleDbDataAdapter
    Dim sql As String
    sql = "select * from BaseCoreDB"
    da = New OleDb.OleDbDataAdapter(sql, dbConn)
    da.Fill(ds, "CoreDB")

    thanks in advance,


    Wednesday, May 2, 2012 4:50 PM

Answers

  • thanks everyone for the help, it gave same good directions to look for an answer,

    in the end I solved it by adding some extra code before the "fill" statement;

    Dim ds As New DataSet
    Dim dbProvider As String
    Dim dbSource As String
    Dim dbConn As New OleDb.OleDbConnection
    dbProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"
    dbSource = "Data Source = C:\Documents and Settings\..."
    dbConn.ConnectionString = dbProvider & dbSource
    dbConn.Open()
    Dim da As OleDb.OleDbDataAdapter
    Dim sql As String
    sql = "select * from BaseCoreDB"
    da = New OleDb.OleDbDataAdapter(sql, dbConn)
    ''''''new:
    da.FillSchema(ds, SchemaType.Source, "CoreDB")
    ''''''
    da.Fill(ds, "CoreDB")

    As a side note; the .xsd file I refer in the question is not relevant. sorry for the confusion.

    • Marked as answer by MTHulli Thursday, May 3, 2012 4:36 PM
    Thursday, May 3, 2012 4:36 PM

All replies

  • In the From section of your SQL statement try putting the full path name of the file and table. also include the ` character (copy the character from this posting) since you have a space in the file name

    "SELECT * FROM `C:\Documents and Settings\..\filename`.BaseCoreDB"


    jdweng

    Wednesday, May 2, 2012 7:31 PM
  • Note the filename in the SQL should NOT have an extension.  also with excel files th esheet names are considered system files and need a dollar sign at the end of the sheet name like this Sheet1$.  I don't think the dollar sign is necessary with XSD files but thought I should mention it here.

    jdweng

    Wednesday, May 2, 2012 9:29 PM
  • Hi Mthulli,

    Welcome to the MSDN Forum.

    Based on my understanding, the error message means your data table doesn't have primary key rather than your database table.

    You can also check this by this property: http://msdn.microsoft.com/en-us/library/system.data.datatable.constraints.aspx 

    Is there a primary key in it?

    If not, please add set a primary key to this data table.

    If so, please follow up this thread to let me know.

    Best regards,


    Mike Feng
    MSDN Community Support | Feedback to us
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, May 3, 2012 11:38 AM
    Moderator
  • Mike: I have seen this problem before.  When the SQL statement in the FROM clause doesn't contain both a filename and table name an empty recordset is used which is generating the error.

    jdweng

    Thursday, May 3, 2012 11:47 AM
  • Hi,

    as Mike says, there is no primary key in the table.

    You use a generic, empty dataset:

    Dim ds As New DataSet

    and the datatable gets created the moment the 'Fill' happens.

    You write about a xsd file with a yellow key, which leads me to believe that you designed a custom dataset.

    Such a 'typed' dataset is nothing else than a subclass of  a generic dataset, but with more (custom) functionalty added to it.

    The class name of such a typed dataset is generally the name that you see before the '.xsd' extension. If you didn't name it, it would be 'Dataset1' by default.

    If i'm right, you should only replace the word 'Dataset' in your code with your typed dataset's name.


    Regards, Nico




    • Edited by Nico Boey Thursday, May 3, 2012 12:00 PM
    Thursday, May 3, 2012 11:59 AM
  • Nico : You may be right that MTHulli is using a custom dataset, but then why in thec onnection statement specifying a source file?  I've seen this problem before in both Excel VBA and Access VBA with the Microsoft Jet Engine and the recordset object.  The recordset object cannot get the full path name from the Jet Engine.  The recordset objegt can't communicate with the Jet Engine so yo have to specifically give the recordset the full path name of the file the first time you use the recordset object.  After you use the recordset object once, you do not need to use the pathname again.  In access VBA the recordset object is set to the defaults database so when you are using the default dattabase you also don't have to include that filename.  In this case you do need to use the file name in the SQL statement.

    dbSource = "Data Source = C:\Documents and Settings\..."


    jdweng

    Thursday, May 3, 2012 12:09 PM
  • thanks everyone for the help, it gave same good directions to look for an answer,

    in the end I solved it by adding some extra code before the "fill" statement;

    Dim ds As New DataSet
    Dim dbProvider As String
    Dim dbSource As String
    Dim dbConn As New OleDb.OleDbConnection
    dbProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"
    dbSource = "Data Source = C:\Documents and Settings\..."
    dbConn.ConnectionString = dbProvider & dbSource
    dbConn.Open()
    Dim da As OleDb.OleDbDataAdapter
    Dim sql As String
    sql = "select * from BaseCoreDB"
    da = New OleDb.OleDbDataAdapter(sql, dbConn)
    ''''''new:
    da.FillSchema(ds, SchemaType.Source, "CoreDB")
    ''''''
    da.Fill(ds, "CoreDB")

    As a side note; the .xsd file I refer in the question is not relevant. sorry for the confusion.

    • Marked as answer by MTHulli Thursday, May 3, 2012 4:36 PM
    Thursday, May 3, 2012 4:36 PM