Problem during the process database
This is the error when i execute the code for the association algorithem. "Errors in the high-level relational engine. The data source view does not contain a definition for the 'receiptid' column in the 'receiptdesc' table or view.".
Dim dsAsNew RelationalDataSource("miningas","miningas")ds.ConnectionString =
"Provider=SQLNCLI;Data Source=localhost;" & _"Initial Catalog=miningas;Integrated Security=SSPI"db.DataSources.Add(ds)
' Create connection to datasource to extract schema to datasetDim dsetAsNew DataSet()Dim cnAsNew SqlConnection("Data Source=localhost;" & _"Initial Catalog=supermarket;Integrated Security=True")' Create the receiptdesc data adapterDim daProductAsNew SqlDataAdapter("SELECT * FROM dbo.receipt", cn)daProduct.FillSchema(dset, SchemaType.Mapped,
"receipt")' Create the receipt data adapter
Dim dareceiptAsNew SqlDataAdapter("Select * from dbo.receiptdesc", cn)
dareceipt.FillSchema(dset, SchemaType.Mapped,"receiptdesc")
Dim drreceipt_descAsNew DataRelation("Newreceipt_desc", _dset.Tables(
"receipt").Columns("receiptid"), _dset.Tables(
"receiptdesc").Columns("receiptid"))dset.Relations.Add(drreceipt_desc)
' Create the dsv, add the dataset, and add to the databaseDim dsvAsNew DataSourceView("miningas","miningas")dsv.DataSourceID =
"miningas"dsv.Schema = dset.Clone()
db.DataSourceViews.Add(dsv)
' Update the database to create the objects on the server.db.Update(UpdateOptions.ExpandFull)
EndSub
[4304 byte] By [
Edypion] at [2008-2-20]
It seems like your column is not there after you create dsv. Can you double check? Otherwise, I have the sample code below to create dsv. Please check the difference.
RelationalDataSourceView dsv = new RelationalDataSourceView("SampleDSV", "SampleDSV");
OleDbConnection connection = new OleDbConnection("Provider=SQLOLEDB.1;Initial Catalog=Northwind;Data Source=localhost;Integrated Security=SSPI;Persist Security Info=False");
try
{
connection.Open();
//********** Fill Customers table in schema ************
OleDbDataAdapter adapter1 = new OleDbDataAdapter ("Select * from [dbo].[Customers] where 1=0", connection);
DataTable[] dataTables = adapter1.FillSchema(dsv.Schema, SchemaType.Mapped, "Customers");
if (dataTables.Length > 0)
{
DataTable dataTable = dataTables[0];
dataTable.ExtendedProperties.Add( “TableType”, “Table”);
dataTable.ExtendedProperties.Add( “DbSchemaName”, "dbo");
dataTable.ExtendedProperties.Add( “DbTableName”, "Customers");
dataTable.ExtendedProperties.Add( “FriendlyName”, "Customers");
}
//********** Fill Orders table in schema ****************
OleDbDataAdapter adapter2 = new OleDbDataAdapter ("Select * from [dbo].[Orders] where 1=0", connection);
dataTables = adapter2.FillSchema(dsv.Schema, SchemaType.Mapped, "Orders");
if (dataTables.Length > 0)
{
DataTable dataTable = dataTables[0];
dataTable.ExtendedProperties.Add( “TableType”, “Table”);
dataTable.ExtendedProperties.Add( “DbSchemaName”, "dbo");
dataTable.ExtendedProperties.Add( “DbTableName”, "Orders");
dataTable.ExtendedProperties.Add( “FriendlyName”, "Orders");
}
//********** Create relationship ****************
DataColumn parentColumn = dsv.Schema.Tables["Customers"].Columns["CustomerID"];
DataColumn childColumn = dsv.Schema.Tables["Orders"].Columns["CustomerID"];
dsv.Schema.Relations.Add("FK_Orders_Customers", parentColumn, childColumn, true);
}
finally
{
connection.Close();
}
Couple things I notice in your code. the connection string in datasource is different from the connection string you get schema. Also, you don't need to clone the dataset before setting to the dsv.schema. You can just set it directly.
I used your code on new AS database and I have no problem. However, I used same connection string (at least same initial catalog). I am not sure if that makes difference.
BTW, it is better to use my code because you do need to set some extended properties.
