Simple Typed DataSet Primary Key Question

[Steven C.]

Hello:

I'm getting an error, "primary key not defined" when trying to use the
FIND method on the DataTable Rows collection.

I have a typed dataset called 'MortgagesDS' that I created with the
XSL builder tool in .NET. I define the dataset at the beginning the
the main class:

MortgageDS _MortgageDS = new MortgageDS();

And then I do a data pull from an Access database (with primary key
defined) into a table called 'tblMortgages' from within a method
inside the class.

strSql = "SELECT * FROM tblMortgages";
OleDbConnection conn = this.m_GetAccessConnection();
OleDbDataAdapter da = new OleDbDataAdapter(strSql,conn);
da.Fill(_MortgagesDS,"tblMortgages");

Later in the code, I try to locate a record from the tblMortgages
table using the FIND method:

DataRow foundrow;
foundrow =_MortgagesDS._Table.Rows.Find(intMortgagePk);
if(foundrow == null)
Console.WriteLine("No Row Found");
else
{
this.tbLenderName.Text = foundrow[1].ToString();
}

However, I get an error, Primary Key Not Defined when I try to locate
the row. The XSL definition has the primary key defined, and the
Access database has the same field defined as the primary key, so what
am I doing wrong? I'm using Visual Studio 2003.

Many thanks!

Steven

 

[Michael Nemtsev]

Hello Steven C.,

Could you show XSD? Seems that smth wrong with it.
Try to call code below to see if PrimaryKey exist in DataTable

private void GetPrimaryKeys(DataTable table)
{
// Create the array for the columns.
DataColumn[] columns;
columns = table.PrimaryKey;

// Get the number of elements in the array.
Console.WriteLine("Column Count: " + columns.Length);
for(int i = 0; i < columns.Length; i++)
{
Console.WriteLine(columns.ColumnName + columns.DataType);
}
}


S> Hello:
S>
S> I'm getting an error, "primary key not defined" when trying to use
S> the FIND method on the DataTable Rows collection.
S>
S> I have a typed dataset called 'MortgagesDS' that I created with the
S> XSL builder tool in .NET. I define the dataset at the beginning the
S> the main class:
S>
S> MortgageDS _MortgageDS = new MortgageDS();
S>
S> And then I do a data pull from an Access database (with primary key
S> defined) into a table called 'tblMortgages' from within a method
S> inside the class.
S>
S> strSql = "SELECT * FROM tblMortgages";
S> OleDbConnection conn = this.m_GetAccessConnection();
S> OleDbDataAdapter da = new OleDbDataAdapter(strSql,conn);
S> da.Fill(_MortgagesDS,"tblMortgages");
S> Later in the code, I try to locate a record from the tblMortgages
S> table using the FIND method:
S>
S> DataRow foundrow;
S> foundrow =_MortgagesDS._Table.Rows.Find(intMortgagePk);
S> if(foundrow == null)
S> Console.WriteLine("No Row Found");
S> else
S> {
S> this.tbLenderName.Text = foundrow[1].ToString();
S> }
S> However, I get an error, Primary Key Not Defined when I try to locate
S> the row. The XSL definition has the primary key defined, and the
S> Access database has the same field defined as the primary key, so
S> what am I doing wrong? I'm using Visual Studio 2003.
S>
S> Many thanks!
S>
S> Steven
S>
---
WBR,
Michael Nemtsev :: blog: http://spaces.msn.com/laflour

"At times one remains faithful to a cause only because its opponents do not
cease to be insipid." (c) Friedrich Nietzsche

 

[Steven C.]

Hi Michael:

Thanks for replying. Here's the XML. Sorry about the formatting.

I tried the code that you suggested. I'm kind of a newbie in C#, so
forgive me, but, where is that console.writeline output supposed to
go? I'm in the .NET IDE, and I don't see it in any of the panes. Do
I have to explicitly turn this feature on somewhere?

Thanks!
Steven

<?xml version="1.0" encoding="utf-8" ?>
<xs:schema id="MortgagesDS"
targetNamespace="http://tempuri.org/MortgagesDS.xsd"
elementFormDefault="qualified"
attributeFormDefault="qualified"
xmlns="http://tempuri.org/MortgagesDS.xsd"
xmlns:mstns="http://tempuri.org/MortgagesDS.xsd"
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element name="MortgagesDS" msdata:IsDataSet="true">
<xs:complexType>
<xs:choice maxOccurs="unbounded">
<xs:element name="Table">
<xs:complexType>
<xs:sequence>
<xs:element
name="nMortgage_pk" type="xs:int" minOccurs="0"
msdata:AutoIncrement="true" msdata:AutoIncrementSeed="-1"

msdata:AutoIncrementStep="-1" />
<xs:element
name="cLenderName" type="xs:string" minOccurs="0" />
<xs:element
name="cLoanId" type="xs:string" minOccurs="0" />
<xs:element
name="nLoanAmount" type="xs:double" minOccurs="0" />
<xs:element
name="nInterestRate" type="xs:double" minOccurs="0" />
<xs:element
name="nSquareFeet" type="xs:double" />
<xs:element
name="nLoanYears" type="xs:double" minOccurs="0" />
<xs:element
name="nClosingCost" type="xs:double" minOccurs="0" />
<xs:element
name="nSchoolPropTax" type="xs:double" minOccurs="0" />
<xs:element
name="nPrincipleInterest" type="xs:double" minOccurs="0" />
<xs:element
name="nTotalTaxes" type="xs:double" minOccurs="0" />
<xs:element
name="nCostPerSqFoot" type="xs:double" minOccurs="0" />
<xs:element
name="nMonthlyPayment" type="xs:double" minOccurs="0" />
<xs:element
name="cAddress1" type="xs:string" minOccurs="0" />
<xs:element
name="cAddress2" type="xs:string" minOccurs="0" />
<xs:element
name="cCity" type="xs:string" minOccurs="0" />
<xs:element
name="cState" type="xs:string" minOccurs="0" />
<xs:element
name="cZip" type="xs:string" minOccurs="0" />
<xs:element
name="nTotalInterest" type="xs:double" minOccurs="0" />
<xs:element
name="nHouseCost" type="xs:double" minOccurs="0" />
<xs:element
name="nDownPayment" type="xs:double" minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
<xs:key name="nMortgage_pk">
<xs:selector xpath=".//mstns:Table" />
<xs:field xpath="mstns:nMortgage_pk" />
</xs:key>
</xs:element>
</xs:schema>

Hello Steven C.,

Could you show XSD? Seems that smth wrong with it.
Try to call code below to see if PrimaryKey exist in DataTable

private void GetPrimaryKeys(DataTable table)
{
// Create the array for the columns.
DataColumn[] columns;
columns = table.PrimaryKey;

// Get the number of elements in the array.
Console.WriteLine("Column Count: " + columns.Length);
for(int i = 0; i < columns.Length; i++)
{
Console.WriteLine(columns.ColumnName + columns.DataType);
}
}


S> Hello:
S>
S> I'm getting an error, "primary key not defined" when trying to use
S> the FIND method on the DataTable Rows collection.
S>
S> I have a typed dataset called 'MortgagesDS' that I created with the
S> XSL builder tool in .NET. I define the dataset at the beginning the
S> the main class:
S>
S> MortgageDS _MortgageDS = new MortgageDS();
S>
S> And then I do a data pull from an Access database (with primary key
S> defined) into a table called 'tblMortgages' from within a method
S> inside the class.
S>
S> strSql = "SELECT * FROM tblMortgages";
S> OleDbConnection conn = this.m_GetAccessConnection();
S> OleDbDataAdapter da = new OleDbDataAdapter(strSql,conn);
S> da.Fill(_MortgagesDS,"tblMortgages");
S> Later in the code, I try to locate a record from the tblMortgages
S> table using the FIND method:
S>
S> DataRow foundrow;
S> foundrow =_MortgagesDS._Table.Rows.Find(intMortgagePk);
S> if(foundrow == null)
S> Console.WriteLine("No Row Found");
S> else
S> {
S> this.tbLenderName.Text = foundrow[1].ToString();
S> }
S> However, I get an error, Primary Key Not Defined when I try to locate
S> the row. The XSL definition has the primary key defined, and the
S> Access database has the same field defined as the primary key, so
S> what am I doing wrong? I'm using Visual Studio 2003.
S>
S> Many thanks!
S>
S> Steven
S>
---
WBR,
Michael Nemtsev :: blog: http://spaces.msn.com/laflour

"At times one remains faithful to a cause only because its opponents do not
cease to be insipid." (c) Friedrich Nietzsche

 

[Michael Nemtsev]

Hello Steven C.,

Instead of Console.WriteLine use Debug.WriteLine and see in VD IDE output
window whether your datatable has primary key

S> Hi Michael:
S>
S> Thanks for replying. Here's the XML. Sorry about the formatting.
S>
S> I tried the code that you suggested. I'm kind of a newbie in C#, so
S> forgive me, but, where is that console.writeline output supposed to
S> go? I'm in the .NET IDE, and I don't see it in any of the panes. Do
S> I have to explicitly turn this feature on somewhere?
S>
S> Thanks!
S> Steven
S> <?xml version="1.0" encoding="utf-8" ?>
S> <xs:schema id="MortgagesDS"
S> targetNamespace="http://tempuri.org/MortgagesDS.xsd"
S> elementFormDefault="qualified"
S> attributeFormDefault="qualified"
S> xmlns="http://tempuri.org/MortgagesDS.xsd"
S> xmlns:mstns="http://tempuri.org/MortgagesDS.xsd"
S> xmlns:xs="http://www.w3.org/2001/XMLSchema"
S> xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
S> <xs:element name="MortgagesDS" msdata:IsDataSet="true">
S> <xs:complexType>
S> <xs:choice maxOccurs="unbounded">
S> <xs:element name="Table">
S> <xs:complexType>
S> <xs:sequence>
S> <xs:element
S> name="nMortgage_pk" type="xs:int" minOccurs="0"
S> msdata:AutoIncrement="true" msdata:AutoIncrementSeed="-1"
S>
S> msdata:AutoIncrementStep="-1" />
S> <xs:element
S> name="cLenderName" type="xs:string" minOccurs="0" />
S> <xs:element
S> name="cLoanId" type="xs:string" minOccurs="0" />
S> <xs:element
S> name="nLoanAmount" type="xs:double" minOccurs="0" />
S> <xs:element
S> name="nInterestRate" type="xs:double" minOccurs="0" />
S> <xs:element
S> name="nSquareFeet" type="xs:double" />
S> <xs:element
S> name="nLoanYears" type="xs:double" minOccurs="0" />
S> <xs:element
S> name="nClosingCost" type="xs:double" minOccurs="0" />
S> <xs:element
S> name="nSchoolPropTax" type="xs:double" minOccurs="0" />
S> <xs:element
S> name="nPrincipleInterest" type="xs:double" minOccurs="0" />
S> <xs:element
S> name="nTotalTaxes" type="xs:double" minOccurs="0" />
S> <xs:element
S> name="nCostPerSqFoot" type="xs:double" minOccurs="0" />
S> <xs:element
S> name="nMonthlyPayment" type="xs:double" minOccurs="0" />
S> <xs:element
S> name="cAddress1" type="xs:string" minOccurs="0" />
S> <xs:element
S> name="cAddress2" type="xs:string" minOccurs="0" />
S> <xs:element
S> name="cCity" type="xs:string" minOccurs="0" />
S> <xs:element
S> name="cState" type="xs:string" minOccurs="0" />
S> <xs:element
S> name="cZip" type="xs:string" minOccurs="0" />
S> <xs:element
S> name="nTotalInterest" type="xs:double" minOccurs="0" />
S> <xs:element
S> name="nHouseCost" type="xs:double" minOccurs="0" />
S> <xs:element
S> name="nDownPayment" type="xs:double" minOccurs="0" />
S> </xs:sequence>
S> </xs:complexType>
S> </xs:element>
S> </xs:choice>
S> </xs:complexType>
S> <xs:key name="nMortgage_pk">
S> <xs:selector xpath=".//mstns:Table" />
S> <xs:field xpath="mstns:nMortgage_pk" />
S> </xs:key>
S> </xs:element>
S> </xs:schema>
S> On Sat, 4 Mar 2006 20:19:26 +0000 (UTC), Michael Nemtsev

Hello Steven C.,

Could you show XSD? Seems that smth wrong with it. Try to call code
below to see if PrimaryKey exist in DataTable

private void GetPrimaryKeys(DataTable table)
{
// Create the array for the columns.
DataColumn[] columns;
columns = table.PrimaryKey;
// Get the number of elements in the array.
Console.WriteLine("Column Count: " + columns.Length);
for(int i = 0; i < columns.Length; i++)
{
Console.WriteLine(columns.ColumnName + columns.DataType);
}
}
S> Hello:
S>
S> I'm getting an error, "primary key not defined" when trying to use
S> the FIND method on the DataTable Rows collection.
S>
S> I have a typed dataset called 'MortgagesDS' that I created with
the
S> XSL builder tool in .NET. I define the dataset at the beginning
the
S> the main class:
S>
S> MortgageDS _MortgageDS = new MortgageDS();
S>
S> And then I do a data pull from an Access database (with primary
key
S> defined) into a table called 'tblMortgages' from within a method
S> inside the class.
S>
S> strSql = "SELECT * FROM tblMortgages";
S> OleDbConnection conn = this.m_GetAccessConnection();
S> OleDbDataAdapter da = new OleDbDataAdapter(strSql,conn);
S> da.Fill(_MortgagesDS,"tblMortgages");
S> Later in the code, I try to locate a record from the tblMortgages
S> table using the FIND method:
S>
S> DataRow foundrow;
S> foundrow =_MortgagesDS._Table.Rows.Find(intMortgagePk);
S> if(foundrow == null)
S> Console.WriteLine("No Row Found");
S> else
S> {
S> this.tbLenderName.Text = foundrow[1].ToString();
S> }
S> However, I get an error, Primary Key Not Defined when I try to
locate
S> the row. The XSL definition has the primary key defined, and the
S> Access database has the same field defined as the primary key, so
S> what am I doing wrong? I'm using Visual Studio 2003.
S>
S> Many thanks!
S>
S> Steven
S>
---
WBR,
Michael Nemtsev :: blog: http://spaces.msn.com/laflour
"At times one remains faithful to a cause only because its opponents
do not cease to be insipid." (c) Friedrich Nietzsche

---
WBR,
Michael Nemtsev :: blog: http://spaces.msn.com/laflour

"At times one remains faithful to a cause only because its opponents do not
cease to be insipid." (c) Friedrich Nietzsche

 

[Steven C.]

After doing so, not only does the datatable not have a primary key,
other fields for which I defined type as Double are showing up as
INT32's. I'm pulling from an Access (Access2000) database, which only
has "Number" as a type (no Int vs. Double, etc).

I thought that the XSL file should define the type of the resultant
data field. What's going on here?

Thanks!
Steven

 

[Michael Nemtsev]

Hello Steven C.,

Strange, I see nothing wrong. How do u compile your schema? smth like "xsd
testSchema.xsd /d /l:CS" ?
I've tested your schema and everything works ok.

I reckon smth wrong with Access PK
I filled standard DataSet from SQLServer, and then call DataSet.WriteSchema
to save schema to the disk.
After that, I opened schema in VS, added PK and generated class for schema
with xsd

and Rows.Find was working fine.

try to start new test project and to experiment a bit

S> After doing so, not only does the datatable not have a primary key,
S> other fields for which I defined type as Double are showing up as
S> INT32's. I'm pulling from an Access (Access2000) database, which
S> only has "Number" as a type (no Int vs. Double, etc).
S>
S> I thought that the XSL file should define the type of the resultant
S> data field. What's going on here?
S>
S> Thanks!
S> Steven
---
WBR,
Michael Nemtsev :: blog: http://spaces.msn.com/laflour

"At times one remains faithful to a cause only because its opponents do not
cease to be insipid." (c) Friedrich Nietzsche

 

[Steven C.]

I'll do that. Michael, you've been a great help here. I have some
ideas now with which to troubleshoot this. I really appreciate your
time. I'll try SQL Server and see what that gets me.

Thanks again!

Steven

 

[Truong Hong Thi]

Steve, add "msdatarimaryKey="true"" into your xs:key element. It
should look like this:
<xs:key name="nMortgage_pk" msdatarimaryKey="true">
<xs:selector xpath=".//mstns:Table" />
<xs:field xpath="mstns:nMortgage_pk" />
</xs:key>

In design view, you can also edit this key by clicking the the "Edit
Key" button on "Xml Schema" toolbar and check the "DataSet Primary Key"
checkbox. You can also add another key (like unique key) to your
dataset by right-click, Add/Key, or drag it from the toolbox.

I think you should change the table name from to something like
"Mortgage", because that make the generated class better named.

After that, if I remember correctly, the generated dataset should
contains a type-safe replacement of Find:
MortgagesDS.MortgageDataTable.FindBynMortgage_pk.

You may also rename the PK to make the name of the method better, or
course

Hope it helps,
Thi

 

[Steven C]

Awesome. Thanks Thi. Good things to check. I'm not sure I compiled
my XSD, per the prior response. I'll try that first.

Thanks again!
Steven

 

[Truong Hong Thi]

Steven,

Regarding compiling the XSD, you often don't need to compile yourself
unless your are using batch or build script. VS.NET builds the XSD
every time you compile, or you can compile it at anytime by right
clicking the XSD file in Solution Explorer and choose "Run Custom
Tool". You can also change the namespace of the generated class in the
XSD's Properties (F4) window. The XSD.EXE tool does provide some
additional options (like specifying the output folder), but I've never
need such.

To view the code of the generated class, click the "Show All Files"
toolbar button of Solution Explorer.

Thi