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 ds As New 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 dataset
Dim dset As New DataSet()
Dim cn As New SqlConnection("Data Source=localhost;" & _
"Initial Catalog=supermarket;Integrated Security=True")
' Create the receiptdesc data adapter
Dim daProduct As New SqlDataAdapter("SELECT * FROM dbo.receipt", cn)
daProduct.FillSchema(dset, SchemaType.Mapped, "receipt")
' Create the receipt data adapter
Dim dareceipt As New SqlDataAdapter("Select * from dbo.receiptdesc", cn)
dareceipt.FillSchema(dset, SchemaType.Mapped, "receiptdesc")
Dim drreceipt_desc As New 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 database
Dim dsv As New 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)
End Sub
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();
}
|||Sorry, but the code i still cannot solve the problem. Can u able to give more support on this section. I very urgent need it. Thx|||
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.
