Re: Updating Related Tables - Database Discussion Boards

Thanks. I'd read the article a few weeks back whilst investigating code generation. I'd been hoping to find an app that already produced the documentation for me. Oh well, looks like I'm going to have to write some code. I'll try and combine it with my code generation ideas to produce a nice integrating solution.

Thanks again,

Michael

'Logic, my dear Zoe, merely enables one to be wrong with authority.' - The Doctor: The Wheel in Space

I would like to change my app from DAO35 to DAO36 or higher, to support ACCESS2000 DBs. How to convert a existing ACCESS97 database programmatically (using DAO)? Is there anything comparable to the RepairDatabase / CompactDatabase calls ?
Thank you.

Couldn't you just open each database in Access 2000 and select "convert" when asked to open or convert? How many databases are you talking about?

the following is the code written to add data into the database!
i get the error saying
COleSafeArray undefined..
i there any need to include any other header files fort this to work.
ADO functions are used in this c++ client!

COleSafeArray vaFieldList;
vaFieldList.CreateOneDim(VT_VARIANT, 4);

// Fill the fields name now
long lArrayIndex[3];
lArrayIndex[0] = 0;
vaFieldList.PutElement( lArrayIndex, &(_variant_t("kEy")) );
lArrayIndex[0] = 1;
vaFieldList.PutElement( lArrayIndex, &(_variant_t("nAme")) );
lArrayIndex[0] = 2;
vaFieldList.PutElement( lArrayIndex, &(_variant_t("tYpe")) );
lArrayIndex[0] = 3;
vaFieldList.PutElement( lArrayIndex, &(_variant_t("vAlue")) );

// Create an array of values to be added
COleSafeArray vaValueArray;
vaValueArray.CreateOneDim(VT_VARIANT, 4);

//Fill the values in each field
lArrayIndex[0] = 0;
vaValueArray.PutElement( lArrayIndex, &(_variant_t("rob")) );
lArrayIndex[0] = 1;
vaValueArray.PutElement( lArrayIndex, &(_variant_t("surya")) );
lArrayIndex[0] = 2;
vaValueArray.PutElement( lArrayIndex, &(_variant_t("kakka")) );
lArrayIndex[0] = 3;
vaValueArray.PutElement( lArrayIndex, &(_variant_t("mac")) );
// call Addnew of recordset now
pRecordset->AddNew( vaFieldList, vaValueArray );
pRecordset->Close();

ranjani

question about BCP process.
The problem is when I transfer the data in date time type field the date '00000000' is not transferable, which is understandable, however it also kicking out some data with appropriate date. Any idea why it occur?

I have three tables, a main table and two subtables that are related to the main table by the main tables key field. In my application I set up three OleDbDataAdapters to read three seperate tables into three tables of a dataset and then I add to the datasets relation collection to link the tables together. The problem is that while I can update the main table by calling the update method of it's DataAdapter, I can't do the same with the other two tables. When I call their update method, nothing happens. I get no errors and the rows that where marked to be updated are still marked to be updated (i.e. AcceptChanges hasn't been called). Also, I can't change the key field even in the main table. Changes to the key simply get ignored and don't get written back to the database. Does anybody know what is happening here? I suspect it's something to do with the tables being related.

Please show your code which updates the Child Tables.

Stefan

Okay, thanks for your reply. Here's an example of the problem I'm having. I've included some of the code I've added just to try and figure out what's happening. When I iterate throw the rows of the child table I find that all the rows have RowState = Unchanged, even though some of them have an Original and a Proposed value. I believe they are not getting updated because the RowState is Unchanged, but I can't figure out why the RowState is Unchanged when there is clearly a Proposed value available. Surely setting a Proposed value for a row should automatically change the RowState?

<br />
public void UpdateData()<br />
{<br />
	connDB.Open();<br />
        // I tried to intecept the RowUpdating events, but they never actually occur!<br />
	SubApt.RowUpdating += new OleDbRowUpdatingEventHandler(MyRowUpdateHandler);<br />
<br />
        // Try to find out what the status of the rows in the child table actually is.<br />
	string s = "";<br />
	foreach (DataRow thisRow in m_myDS.Tables["tblSub"].Rows)<br />
        {<br />
		s += thisRow["Key"] + " -> " + thisRow["Field1"] + " " + thisRow.RowState.ToString() + "\n";<br />
                // RowState is ALWAYS Unchanged even though some rows have a proposed value!<br />
		if (thisRow.HasVersion(DataRowVersion.Proposed))<br />
		{<br />
                // The proposed value really is the value it's supposed to be changed to, and the original value<br />
                // really is the original value<br />
			s += thisRow["Field1", DataRowVersion.Original] + " " + thisRow["Field1", DataRowVersion.Proposed] + "\n";<br />
		}<br />
	}<br />
	MessageBox.Show(s);<br />
<br />
        // the main table updates just fine<br />
	MainApt.Update(m_myDS.Tables["tblMain"]);<br />
        // but this does nothing!!! It doesn't matter which way around I update the two tables, or even if I leave<br />
        // out the main table altogether<br />
	SubApt.Update(m_myDS.Tables["tblSub"]);<br />
	connDB.Close();<br />
}<br />

1. Did you call AcceptChanges somewhere? Don't call AcceptChanges before Adap.Update().
2. Clear the EventHandler for RowUpdate. This may call AcceptChanges()
3. Try to edit a Row and immediatly afterwards check it's RowState. If the RowState is unchanged I don't know what's wrong. AcceptChanges is a tricky thing: after this call all Rows are marked unchanged! So there is nothing what could be Updated by the Update Method of Apt.

Please inform me about Point 3.

Stefan

STW wrote:
1. Did you call AcceptChanges somewhere? Don't call AcceptChanges before Adap.Update().

No, I have not called AcceptChanges explictly anywhere in my program. I had thought that perhaps calling update on the main table might automatically call AcceptChanges on the child table, but not updating the main table didn't fix the problem. I was also thinking that calling AcceptChanges should move the proposed value into the original value shouldn't it? That's not been happening.

STW wrote:
2. Clear the EventHandler for RowUpdate. This may call AcceptChanges()

I tried it without the EventHandler and it makes no difference. I only put the EventHandler there in the hopes of being able to intercept the call and figure out what was happening. As it turns out, the event never gets fired.

STW wrote:
3. Try to edit a Row and immediatly afterwards check it's RowState. If the RowState is unchanged I don't know what's wrong. AcceptChanges is a tricky thing: after this call all Rows are marked unchanged! So there is nothing what could be Updated by the Update Method of Apt.

Okay, I tried manually changing the value of one field in the child table like this:

<br />
m_myDS.Tables["subtable"].Rows[0]["Field1"] = 101;<br />

And if I then iterate through the rows in the table, I can see that row has been changed to RowState.Modified and it doesn't have a proposed value anymore. Further, when I call the update that row does, in fact, get updated in the database.

Okay, I just figured something out. If, while I'm iterating through the rows, I call EndEdit on each row it will set the RowState to modified (if it has actually been modified). So the problem seem to be that when a datarow is bound to my form, BeginEdit is implicitly called, and when that particular row becomes unbound, it should automatically call EndEdit on that row. But it isn't doing it for my child table. Now, I don't want to have to iterate through all my records and call EndEdit on every one of them before I run my update because I have a lot of records and I expect it to grow rapidly once (if) I finish my app. I'm thinking I could intercept the PositionChanged event of my CurrencyManager, but I think at that point the position has already changed so I won't be able to grab the previous row to call EndEdit on it. I could try and intercept every button or event that results in a change of position and call EndEdit before I change the position, but that's a pain since I'd have to remember to do that in several different places. I'd also have to make sure I call EndEdit on the current row when the Update button is clicked, but that's not a major problem (because it's only one place).
So I think I can work around my problem, but I'd be much happier if I could just figure out why the CurrencyManager isn't calling EndEdit and fix it!

I am getting an error msg:
"The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value."
when I have the following code in where close:
and n.dateentered between (case
When DatePart (dw , getdate())=2 Then (getDate()-3 )
When DatePart (dw , getdate())=4 Then (getDate()-2 )
When DatePart (dw , getdate())=6 Then (getDate()-2 )
Else GetDate() End ) and (getdate()-1)
but when I run with this (I put dates manually) it is fine.
and n.dateentered between '09/20/2003' and '09/29/2003'
Any idea why?

I have tested the SQL query using SQL Server Enterprise Manager and it's working fine for me.
My guess is that n.dateentered is of type "smalldatetime" and GetDate actually returns "datetime" instead.

You might want to try one of the following:
1. Use CAST(x as y) e.g. CAST( (case when...end) AS smalldatetime) ->I've tested this with the code and it works
2. Use DateAdd() function instead of getDate()-3
3. Use DatePart to format your date ranges to yyyymmdd format

code for adding a new record,deleting a record that already exists and modifying an existing record needed in C++

ranjani

Is there a way to get the actual SQL commands that the OleDbDataAdapter.Update method is passing? I'm having a problem due to the fact that every time I try and run my Update command, I get an exception thrown by the db (access) claiming that there is a syntax error in my SQL command. Trouble is I can't seem to figure out how to get the actual text of the command (after the adapter has filled in the parameters). I'm pretty confident that the command that I set as the Update command is okay, so I want to see if the problem is with how the adapter has decided to fill in the parameters?
I tried hooking the RowUpdating event, but I still only get the command text without the parameters inserted?

The Update Command is an OleDBCommand. You can get the property CommandText from it...;)

Free your mind...

But the CommandText only has the command before the parameters have been filled in. I need the actual command that is sent to the database when I call the Update method of my OleDbDataAdapter. Is there a way to get this?

Okay, I tracked down the problem by systematically removing fields from my Update statement. The problem is caused by a date field. I have something like this (but with a lot more fields):

<br />
SQLUpdate = "UPDATE myTable SET Field1 = @Field1, Date = @Date, WHERE Key = @Key";<br />

Then I have this:

<br />
OleDbCommand myCmd = new OleDbCommand(SQLUpdate,myConnection);<br />
myCmd.Parameters.Add("@Field1", OleDbType.VarWChar, 20, "Field1");<br />
myCmd.Parameters.Add("@Date", OleDbType.Date, 0, "Date");<br />
<br />
myApt.UpdateCommand = myCmd;<br />

What's the right way to handle the date field in an Access database. Even the command builder seems to fail at this!! BTW, the Date field is bound to a DatePicker control on my form, perhaps something is getting lost in the translation?

I've written a program that uses MS DataGrid Control, but i've got problems with running it on computers without MS Visual Studio. I have registered msdatgrd.ocx, but when i try to run my app i get the error message "cannot initialise data bindings". What else should i install?

You need to make sure the target machine has COMCAT.DLL, MSSTDFMT.DLL and MSDATGRD.OCX, as well as any necessary database components.

Steve S

Does anyone knows how to extract the value of a query using FOR XML ??

All I want to do is assign the result value into a variable.

Thanks Confused | :confused:

Free your mind...

It basically comes out as a set of string fields.

If using classic ADO, use a Recordset to capture it. If using ADO.NET, use a SqlDataReader.

I know it works fine in the middle tier code. I just want to leave the result in the data tier (SQL Server). What I'm trying to do is a kind of audit trail, so when users update a record, I can take a snapshot of it (FOR XML RAW) and write it to another table.

Smile | :)

Free your mind...

Hello all,
A quick question, I’m working on designing a SQL (7.0) database and for 37 of the tables I have 2 fields that are common between them and I’m using these 2 fields as the primary key for the 37 tables and also as the Foreign key to define the relations between these tables.

For example: <br />
Project_ID1: part of the Primary Key<br />
Project_ID2: part of the Primary key<br />

Both of them together form the primary key for the 37 tables because they are the only unique fields within each table. And when I create a relation between 2 of the tables I use these same 2 fields as the foreign keys

So, I would just like to get your opinion on if that is a correct design or not, I had some people telling me that this is a poor design?

Thanks in advance

I didn't really understand right.
Questions:
1. These 37 Tables do they all have the same Fields or what is different between them.
2. Do you store different Projects in different Tables?
3. Just show me the first 4 Fields of two Tables that I can understand.
4. What is the relation between the Tables?

Database Design is more difficult than many people think. A good structure may help the developers.

Please tell me more.

Stefan