none
Dataset in SQl Statement prob RRS feed

  • Question

  • Hi im having A Dataset csv wich is the Dataset of a Spetial csv file. Now i want to add a Column of this dataset to my databe using this SQL Statement

    Code Snippet

                

    Code Snippet

    DataTable CSVTable = csv.Tables["CSV"];
                  CSVTable.TableName = "CSVTable";
                  
                  string insert = "INSERT INTO TBL_SecurityName ( SecurityName )"
                                   + " SELECT CSVTable.[Security Name]"
                       + " FROM CSVTable"
                       + " LEFT JOIN TBL_SecurityName ON " + "CSVTable.[Security Name]=TBL_SecurityName.SecurityName "
                       + "GROUP BY TBL_SecurityName.SecurityName, " + "CSVTable.[Security Name]"
                       +" HAVING (((TBL_SecurityName.SecurityName) Is Null))";
                    aCmd = new OleDbCommand(insert, Myconnection);
                    aCmd.ExecuteNonQuery();



    But the jet driver does not find CSV !! Thats cleat since it is not a table but my own Dataset.
    Any idea how to solve this for this SQL Statement
    Monday, July 16, 2007 4:21 PM

All replies

  • The database server, which is executing the SQL, can't see tables that aren't in the database.  You need to create a work table in the database and insert the data from CSVTable into the work table.  Then use the work table where you're using CSVTable in the above query.

    There are other possible approaches.  SQL Server has XML features that let it view an XML document as though it were a table; you can then pass a string containing an XML document as a parameter to a stored procedure and execute a query using its contents.  So you could use the output produced by SaveXml() as an input to the SP.  I don't know what database you're using, but it may have similar features.
    Tuesday, July 17, 2007 10:50 AM
  • Hi thnks for the reply. The first idea is obvious.

    For the second:
    I m using a Access database and Ado.net. Is it possible to get the relation of the database to a xsd file. I don t whant to do this manually

    mny thnks
    Thursday, July 19, 2007 9:41 AM