Search results -- dotnet ado.net

Norman Yuan
9/5/2007 5:10:15 PM

With ADO.NET, you could either use SqlCommand object to execute the SP and
have it return a DataReader object; or create a DataAdapter object, have its
SelectCommand to execute the SP, fill the returned data set into a
DataSet/DataTable. Then you could manipulate the
DataReader/dataSet/DataTable on your application side.

[quoted text, click to view]

v-wywang NO[at]SPAM online.microsoft.com
9/6/2007 12:00:00 AM

Hello Manjree,
Thanks for Norman's reply.

Just as what Norman said, we could use SqlCommand to execute both Stored
Procedure and T-SQL query.
What we should do is to set CommandType as
System::Data::CommandType::StoredProcedure.
You may heck the following code snippet.

System::Data::SqlClient::SqlCommand^ scd=gcnew
System::Data::SqlClient::SqlCommand();
scd->Connection=//connection;
scd->CommandText="SP Name";
scd->CommandType=System::Data::CommandType::StoredProcedure;

Then, we can get a DataReader object from SqlCommand to retrieve the result
row by row:
System::Data::SqlClient::SqlDataReader^ sdr=scd->ExecuteReader();

Another option, we can create a DataAdatper and fill the return data into
DataSet or DataTable:
System::Data::SqlClient::SqlDataAdapter^ sda=gcnew
System::Data::SqlClient::SqlDataAdapter(scd);
//Fill the returned data into DataSet
System::Data::DataSet^ ds=gcnew System::Data::DataSet();
sda->Fill(ds);
//Fill the returned data into DataTable
System::Data::DataTable^ dt=gcnew System::Data::DataTable();
sda->Fill(dt);

Hope this helps, please let me know if there is anything unclear. I will
follow up. It's my pleasure to assist you.
Best regards,

Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Manjree Garg
9/6/2007 4:24:02 AM

Hi Wen

Thanks for you reply.
I have the following stored procedure

dbo.SrchResult(
@tabName varChar (50),
@colName varChar (50),
@opr varChar (10),
@val varChar (50)
)
AS
/* SET NOCOUNT ON */
SELECT Supplier.supplierID, sampleID, amount
FROM Supplier
JOIN Sample ON Supplier.supplierID = Sample.supplierID
WHERE (@tabName.@colName @opr @val)

RETURN

Now as you can see the condintion within WHERE() is not working. How can I
give variable condition where it could be any column of any table with any
operator(=, <, > etc.) with any value?

Another problem is that I want the columns in SELECT clause to be variable
as the user will be selecting from the user interface variable no. of columns
of different tables to be displayed.
Shall I pass an array of strings containing column names in the SELECT clause?

Thanks.

Manjree

[quoted text, click to view]

v-wywang NO[at]SPAM online.microsoft.com
9/7/2007 3:21:10 AM

Hello Manjree,

I'm sorry to say what you need is not possible in Stored Procedure.
However, the correct way to achieve that is to create a dynamic T-SQL query
in SQLCommand.

In Stored Procedure, what we can do is pass the value to Stored Procedure.
As far as I know, we cannot pass column name, operator to generate query
and execute on SQL Server.

I suggest you may create a dynamic SQL query (as below) to achieve this.
Such as:
String^ SQLquery=System::String::Format("SELECT {0} FROM Supplier JOIN
Sample ON Supplier.supplierID = Sample.supplierID WHERE ({1})",
"Supplier.supplierID, sampleID, amount","Supplier.supplierID=1");
System::Data::SqlClient::SqlCommand^ scd=gcnew
System::Data::SqlClient::SqlCommand();
scd->CommandText=SQLquery;
scd->CommandType=System::Data::CommandType::Text;
...

Hope this helps. Please let me know if you have any more concern. I'm glad
to assist you.
Have a great weekend!
Best regards,

Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

v-wywang NO[at]SPAM online.microsoft.com
9/11/2007 12:00:00 AM

It's my pleasure to assist you.
Welcome, Manjree. :)

Have a great day,

Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Manjree Garg
9/11/2007 1:26:01 AM

Hi Wen

Thanks for your help. It did work.

Manjree

[quoted text, click to view]

Manjree Garg
9/11/2007 8:08:04 AM

Hi Wen

I am searching the database and storing its results into a DataTable
srchResTable using DataAdapter.

Now I want to search within the search results i.e. in srchResTable. As this
table is not part of the database how can I do it?
Shall I add this result table to the database or is there any other way of
doing so? I also want to save srchResTable as a text file.

I really appreciate your help.

Manjree

[quoted text, click to view]

v-wywang NO[at]SPAM online.microsoft.com
9/12/2007 12:00:00 AM

Hello Manjree
Thanks for your reply.

What's kind of search do you want to do on the srchResTable?

We may count on DataTable:Select() method
[http://msdn2.microsoft.com/en-us/library/b5c0xc84.aspx]

For the rule of filterExpression argument, please check
DataColumn::Expression property.
http://msdn2.microsoft.com/en-us/library/system.data.datacolumn.expression.a
spx
[DataColumn.Expression Property]

Example:
array<System::Data::DataRow^>^ srchResRows=srchResTable->Select("c1>10 and
c2>10");

I think this method is the same as your another post:
http://msdn.microsoft.com/newsgroups/managed/default.aspx?dg=microsoft.publi
c.dotnet.framework.adonet&mid=12a5f6fa-e2ce-460f-b75d-525fe3de7ba0&p=1
[DataRow Array size in dotnet.framework.adonet]

Do you face any further issue on this? Please feel free to let me know. I'm
glad to assist you.

Hope this helps.
Best regards,

Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Manjree Garg
9/12/2007 4:40:01 AM

Thanks Wen. It did work.
Any idea how to store a DataTale as a text file as I want to store the
search results as a text file.

Thanks
Manjree

[quoted text, click to view]

Manjree Garg
9/12/2007 5:12:01 AM

Hi Wen

As I am searching the database on any column of any table selected by user.
The value of the column is entered into an edit box by the user and I access
it by using GetWindowText() of the edit control. Now the problem is in the
columns of type smallDataTime. As the value entered is of type CString it
does not convert it into smallDataTime. How can I compare smallDateTime
columns?

Thanks for any suggestions.

Manjree

[quoted text, click to view]

Manjree Garg
9/13/2007 7:42:00 AM

That's brilliant Wen. Saving the search results did work. It's really great.

Searching the Database on smallDateTime does work the way you have written.
But that is not the problem.

The problem is in datatype conversion. As I wrote earlier:
The user can search on any column(of different type) of any DataTable
selected from the GUI. Now, if the user chooses to search on smallDateTime
type column (say DateExpiry) the user enters the date value in an edit box
m_editVal in Search DialogBox. I access the date value as

CString f1Val;
m_editVal.GetWindowTextA(f1Val);

Now, as f1Val is of CString type when I compare it with the selected column
say dateExpiry (smallDateTime type) value in DataTable the result is not
correct.

Hope it explains the problem.

Another DataType conversion problem is in the fillowing statements:

dlg.m_sampStrgTemp = (float) (row->default[L"strgTemp"]);
dlg.m_sampDtExp = (COleDateTime) row->default[L"dateExpiry"]->ToString();

How to convert from SQL float and date types to CDialog (dlg) float and
date types.

Thanks a lot for you help.

Manjree

[quoted text, click to view]

v-wywang NO[at]SPAM online.microsoft.com
9/13/2007 12:47:49 PM

Hello Manjree,

To render DataTable as Text File, We should have to do that manually.
I wrote a sample code as below for you. Hope this helps.
Let me know if you face any further issue on this. We are glad to assist
you.

System::Data::DataTable^ dt=srchResTable;
array<System::Data::DataRow^>^ srchResRows=dt->Select("c1>1");

System::IO::StreamWriter^ objStreamWriter = gcnew
System::IO::StreamWriter("C:\\Test.txt");
//write column
for(int i=0; i<dt->Columns->Count;i++)
{

objStreamWriter->Write(((System::Data::DataColumn^)dt->Columns[i])->ColumnNa
me);
objStreamWriter->Write("\t");
}
objStreamWriter->WriteLine();
//write rows
for(int rowNum=0;rowNum<dt->Rows->Count;rowNum++)
{
System::Data::DataRow^ dr=dt->Rows->default[rowNum];
for(int colNum=0;colNum<dt->Columns->Count;colNum++)
{
objStreamWriter->Write(dr[colNum]);
objStreamWriter->Write("\t");
}
objStreamWriter->Write(objStreamWriter->NewLine);
}
objStreamWriter->Close();

Best regards,

Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

v-wywang NO[at]SPAM online.microsoft.com
9/13/2007 12:58:23 PM

Sorry, I noticed some mistakes in my code.
Updated:

System::Data::DataTable^ dt=ds->Tables[0];
array<System::Data::DataRow^>^ srchResRows=dt->Select("c1>10");

System::IO::StreamWriter^ objStreamWriter = gcnew
System::IO::StreamWriter("C:\\Test.txt");
//write column
for(int i=0; i<dt->Columns->Count;i++)
{

objStreamWriter->Write(((System::Data::DataColumn^)dt->Columns[i])->ColumnNa
me);
objStreamWriter->Write("\t");
}
objStreamWriter->WriteLine();
//write rows
for(int rowNum=0;rowNum<srchResRows->Length;rowNum++)
{
System::Data::DataRow^ dr=srchResRows[rowNum];
for(int colNum=0;colNum<dt->Columns->Count;colNum++)
{
objStreamWriter->Write(dr[colNum]);
objStreamWriter->Write("\t");
}
objStreamWriter->Write(objStreamWriter->NewLine);
}
objStreamWriter->Close();

Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

v-wywang NO[at]SPAM online.microsoft.com
9/13/2007 1:02:57 PM

Hello Manjree,

To select in DataTable with condition on Small DateTime column, please try:

array<System::Data::DataRow^>^ srchResRows=dt->Select("c4>#1999/01/01#");
or
array<System::Data::DataRow^>^ srchResRows=dt->Select("c4>#1999/01/01
12:00#");

It works on my side. Let me know if you have any further issue.
Have a great day,

Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

v-wywang NO[at]SPAM online.microsoft.com
9/17/2007 12:00:00 AM

Hello Garg,
I'm sorry for delay, due to out of office last Friday.

According to your description, it seems what you need is to convert CString
to SmaillDateTime. Please correct me if I misunderstood anything here.

In ADO.net world, all the types of underling database will be mapped to
Net type.
For example:
The smallDateTime maps to DateTime. Thereby, "DateExpiry" column in
underling database is SmaillDateTime. But after DbAdatper fill it into
DataSet. Its type has been converted to System::DataTime.

Therefore, what you really need is to convert CString to DateTime.
DataTime::Parse method could convert the specified string representation of
a date and time to its DateTime equivalent.
http://msdn2.microsoft.com/en-us/library/1k1skd40.aspx
[DateTime.Parse Method (String)]
For example:
System::DateTime^ a=System::DateTime::Parse(L"2000/01/01");

You may check the following MSDN document for the other DBTypes.
http://msdn2.microsoft.com/en-us/library/system.data.odbc.odbctype.aspx
[OdbcType Enumeration]

Regarding to the other two issues:
[quoted text, click to view]
date types?
I think the data type of row->default[L"strgTemp"] is System::Double,
rather tha SQL Float.
Again, the row->default[L"dateExpiry"] should be System::DatatTime.

Would you please try GetType() method to confirm what is the datatype for
these two columns on your side?

row->default[L"strgTemp"]->GetType()->ToString();
( is it "System::Double"?)

row->default[L"dateExpiry"]->GetType()->ToString();
( is it "System::DatatTime"?)

Please let me know if you have anything unclear or any more concern on
this. I'll follow up. It's my pleasure to assist you.

Have a great day,
Best regards,

Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

v-wywang NO[at]SPAM online.microsoft.com
9/17/2007 12:00:00 AM

Hello Manjree,
I'm sorry for delay, due to out of office last Friday.

According to your description, it seems what you need is to convert CString
to SmaillDateTime. Please correct me if I misunderstood anything here.

In ADO.net world, all the types of underling database will be mapped to
Net type.
For example:
The smallDateTime maps to DateTime. Thereby, "DateExpiry" column in
underling database is SmaillDateTime. But after DbAdatper fill it into
DataSet. Its type has been converted to System::DataTime.

Therefore, what you really need is to convert CString to DateTime.
DataTime::Parse method could convert the specified string representation of
a date and time to its DateTime equivalent.
http://msdn2.microsoft.com/en-us/library/1k1skd40.aspx
[DateTime.Parse Method (String)]
For example:
System::DateTime^ a=System::DateTime::Parse(L"2000/01/01");

You may check the following MSDN document for the other DBTypes.
http://msdn2.microsoft.com/en-us/library/system.data.odbc.odbctype.aspx
[OdbcType Enumeration]

Regarding to the other two issues:
[quoted text, click to view]
date types?
I think the data type of row->default[L"strgTemp"] is System::Double,
rather tha SQL Float.
Again, the row->default[L"dateExpiry"] should be System::DatatTime.

Would you please try GetType() method to confirm what is the datatype for
these two columns on your side?

row->default[L"strgTemp"]->GetType()->ToString();
( is it "System::Double"?)

row->default[L"dateExpiry"]->GetType()->ToString();
( is it "System::DatatTime"?)

Please let me know if you have anything unclear or any more concern on
this. I'll follow up. It's my pleasure to assist you.

Have a great day,
Best regards,

Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Manjree Garg
9/17/2007 5:16:01 AM

Hi Wen

Thanks for your reply.
The DateTime::Parse method does change a String into DateTime. But I am
having problems in comparing the values. I'll email you the source file and
explain it in that.

Now about the other two issues.
How to Convert from SQL float and date types to CDialog (dlg) float and
date types?
As you suggerted the data type of row->default[L"strgTemp"] is
System::Double,
and, the row->default[L"dateExpiry"] is System::DatatTime.
Now if I do

dlg.m_sampStrgTemp = (double)(row->default[L"strgTemp"]);

It throws the exception "Specified cast is not valid."
m_sampStrgTemp is of double type.

Again, the similar conversion problem in the following:

dlg.m_sampDtExp = (COleDateTime)row->default[L"dateExpiry"];

Thanks for your help.

Manjree

[quoted text, click to view]

v-wywang NO[at]SPAM online.microsoft.com
9/18/2007 12:00:00 AM

Hello Manjree,
Thanks for your reply.

I have gotten the email and replied you. If you face any further issue,
please don't hesitate to reply me. I will follow up.

For the second issue:
Just as you have seen, the datatype of row->default["strgTemp"] is
System::Double and row->default["dateExpiry"] is System::DateTime.
Now, the issue has been changed to how to convert System::Double and
System::DateTime to CDialog(dlg) float and date types. This is an issue
about .Net managed datatype and CDialog(dlg) unmanaged data types.

I have consulted my colleague (Jeffrey Tan) who is an expert in C++ field.
To convert System::Double to CDialog(dlg)float, would you please try the
following method?

dlg.m_sampStrgTemp = static_cast<double>(row->default[L"strgTemp"]);

We have done a test on our side and it works fine.
System:ouble db=3.15;
Test(static_cast<double>(db));

#pragma unmanaged
void Test(double db)
{
double test= db;
}
#pragma managed

To convert System::DateTime to ColeDateTime, it seems we cannot cast it
directly.
We have to pass year,month,day and time to create a new COleDateTime
object. As below.

DateTime^ dt=row->default[L"dateExpiry"];
dlg.m_sampDtExp= COleDateTime obj(dt->Year, dt->Month, dt->Day, dt->Hour,
dt->Minute,dt->Second);

Please try the above method. If the issue still persists, would you please
create a simple project which could reproduce the issue? This will help on
the research very much. I think we have to perform further analyze on it.

Have a great day. Please let me know if you have any more concern. I'm glad
to assist you.

Have a great day,
Best regards,

Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Manjree Garg
9/18/2007 4:10:01 AM

Hi Wen

Thanks for your help. It's working the way you suggested though I would
like to clear couple of issues.

(1) If the value in a column is 'NULL' (say row->default["strgTemp"] = NULL)
which is of type float it takes its data type in .NET as System.dbNull. So If
I am modifying that record in VC++ dialogbox I need to change that value to
something else (say 0) in the table first.

(2) For the DateTime I need to do

DateTime^ dt;
dt = (DateTime^)row->default[L"dateExpiry"];

it takes row->default[L"dateExpiry"] as object^ type.

Anyway after these changes my code is working as far as these issues are
concerned.

Now as I am modifying a 'Sample' record which has Parent to Child
relationship from 'Supplier' to 'Sample' on supplierID. The columns in
Supplier are supplierID, supplierRef and notes. When it comes to udate

sqlDb->adapter->Update(sqlDb->samplesTable);

It throws the following exception and does not update the table.

"Missing the DataColumn 'supplierRef' in the DataTable 'samplesTable' for
the SourceColumn 'supplierRef'."

cheers.

Manjree

[quoted text, click to view]

v-wywang NO[at]SPAM online.microsoft.com
9/19/2007 12:00:00 AM

Hello Manjree,
Thanks for your feedback.

Regarding to the issue about "Missing the DataColumn 'supplierRef' in the
DataTable 'samplesTable' for the SourceColumn 'supplierRef'."

It seems like that you use the wrong DBdatapter to update Semples Table.
Would you please let me know what is the update command in sqlDb -> adapter?

Based on my experience, such problem could occur when updating the samples
Table by the wrong SQL command which is used for Supplier table. Therefore,
adapter cannot found "Supplier.supplierRef" column in SamplesTable.

I suggest you can check the sqlDb -> adapter->updateCommand->CommandText
before updating samples by this DBDataAdapter.
I suspect it was "update Supplier .... ".

Hope this helps. Please let me know if you face any futher issue. We are
glad to assist you.
Best regards,

Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Manjree Garg
9/19/2007 3:46:01 AM

Hello Wen

Thanks for your reply.

I realised that in case of update the data set should contain only one
table. My dataset contains all the tables, that's why this problem arised.

Well! Now I am using stored procedure to update the sampleID of Sample table
which is primary key of this table and foreign key of SamplePrep. The
procedure is:

ALTER PROCEDURE dbo.ModifySampID
AS
BEGIN TRANSACTION

UPDATE Sample
SET sampleID = 'ddddd01'
WHERE sampleID = 'bbb01'
IF(@@error <>0)
BEGIN
ROLLBACK TRAN
return 10
END

UPDATE SamplePrep
SET sampleID = 'ddddd01'
WHERE sampleID = 'bbb01'
IF(@@error <>0)
BEGIN
ROLLBACK TRAN
return 11
END

COMMIT TRANSACTION

RETURN

But it is not working either :( throwing the exception:

"Update statement conflicted with the REFERENCE constrained
FK_SamplePrep_Sample. Conflict occured in table SamplePrep column sampleID."

I guess Begin Transaction is not working.

Cheers

Manjree

[quoted text, click to view]

v-wywang NO[at]SPAM online.microsoft.com
9/20/2007 12:00:00 AM

Hello Manjree,
Thanks for your reply.

The error always be thrown SQL Server, even though we put the UPDATE query
in Transaction.
By design, the error message will be thrown after we executes the procedure.

In your case, throwing the exception doesn't mean "Begin Transaction" is
not working. This is by design.

If you step into this procedure, you will notice the exception is throwed
after the line (return 11)

IF(@@error <>0)
BEGIN
ROLLBACK TRAN
return 11 // after this line.

Therefore, I think your stored precedure works fine.

We have to try-catch() the exception in code.

Hope this helps.let me know if there is anything unclear. We are glad to
assist you.
Have a great day,
Best regards,

Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Manjree Garg
9/20/2007 1:48:30 AM

Hi Wen

Thanks for your reply. The procedure is not modifying the value of sampleID.
I tried it in SQL server and getting the following messages:

Msg 547, Level 16, State 0, Line 4
The UPDATE statement conflicted with the REFERENCE constraint
"FK_SamplePrep_Sample". The conflict occurred in database "Protien", table
"dbo.SamplePrep", column 'sampleID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 13
The UPDATE statement conflicted with the FOREIGN KEY constraint
"FK_SamplePrep_Sample". The conflict occurred in database "Protien", table
"dbo.Sample", column 'sampleID'.
The statement has been terminated.
Msg 3903, Level 16, State 1, Line 18
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
Msg 3902, Level 16, State 1, Line 22
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

[quoted text, click to view]

v-wywang NO[at]SPAM online.microsoft.com
9/21/2007 12:00:00 AM

Hello Manjree,
Thanks for your reply.

According to your description, your issue is that you cannot execute the
procedure and receive the error message about the update statement
Conflicted with FK constraint. Please don't hesitate to correct me, if I
misunderstood anything here.

From the error message and your pre-reply, there is a relationship between
Sample and SamplePref by sampleID. The Sample table is the Parent table and
SamplePref is the Child table. Please let me know if I'm wrong.

Regarding to the first update statement:

UPDATE Sample
SET sampleID = 'ddddd01'
WHERE sampleID = 'bbb01'
IF(@@error <>0)

Have you checked if there is any row (which sampleID is 'bbb01' ) in the
SamplePref table?

Due to the FK constraint, each row of Sample has to link to the Parent
Table (SampleRref).
If there is any row whose sampleID is 'bbb01' in SamplePref, we cannot
change the parent row from "bbb01" to 'ddddd01'. Otherwise, the FK
constraint error will be thrown.

You may check the following document.
http://msdn2.microsoft.com/en-us/library/ms175464.aspx
[FOREIGN KEY Constraints]

Although the main purpose of a FOREIGN KEY constraint is to control the
data that can be stored in the foreign key table, it also controls changes
to data in the primary key table. For example, if the row for a salesperson
is deleted from the Sales.SalesPerson table, and the salesperson's ID is
used for sales orders in the Sales.SalesOrderHeader table, the relational
integrity between the two tables is broken; the deleted salesperson's sales
orders are orphaned in the SalesOrderHeader table without a link to the
data in the SalesPerson table.
A FOREIGN KEY constraint prevents this situation.*THE CONSTRAINT ENFORCES
REFERENTIAL INTEGRITY BY GUARANTEEING THAT CHANGES CANNOT BE MADE TO DATA
IN THE PRIMARY KEY TABLE IF THOSE CHANGES INVALIDATE THE LINK TO DATA IN
THE FOREIGN KEY TABLE. IF AN ATTEMPT IS MADE TO DELETE THE ROW IN A PRIMARY
KEY TABLE OR TO CHANGE A PRIMARY KEY VALUE, THE ACTION WILL FAIL WHEN THE
DELETED OR CHANGED PRIMARY KEY VALUE CORRESPONDS TO A VALUE IN THE FOREIGN
KEY CONSTRAINT OF ANOTHER TABLE*. To successfully change or delete a row in
a FOREIGN KEY constraint, you must first either delete the foreign key data
in the foreign key table or change the foreign key data in the foreign key
table, which links the foreign key to different primary key data.

Hope this helps, please let me know if this is the root cause of your
issue. I will follow up. It's my pleasure to assist you.

Have a great day,
Best regards,

Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Manjree Garg
9/24/2007 2:20:02 AM

Hello Wen

Sorry for thr delay as I was away on Friday.

You understood the problem quite well.
Sample is the parent table and SamplePrep is the child table. sampleID is
the key field. Both the tables have records for sampleID = bbb01 and I am
changing it to sampleID = dddd01. To do that I am using Begin Transaction as
you mentioned that it does not alow to change the key field if there is a
record in the child table. The stored procedure code is

BEGIN TRY
BEGIN TRANSACTION

UPDATE Sample
SET sampleID = 'dddd01'
WHERE sampleID = 'bbb01'
IF(@@error <>0)
BEGIN
ROLLBACK TRAN
return 10
END

UPDATE SamplePrep
SET sampleID = 'dddd01'
WHERE sampleID = 'bbb01'
IF(@@error <>0)
BEGIN
ROLLBACK TRAN
return 11
END

COMMIT TRANSACTION

RETURN
END TRY
BEGIN CATCH
SELECT
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage
END CATCH

It is giving the following erroe message:

"Transaction count after EXECUTE indicates that a COMMINT or ROLLBACK
TRANSACTION statement is missing. Previous count = 0 , current count=1."

And it is not changing the sampleID value.

Thanks for yoyr help.

Manjree.

[quoted text, click to view]

v-wywang NO[at]SPAM online.microsoft.com
9/25/2007 12:00:00 AM

Hello Manjree,

Because the Update Query is in TRY-Catch, "RollBack Tran" in (if@@error<>0)
have no chance to be execute. When there is an error in the transaction,
stored procedure will pump to the Catch.

The correct way is that we should have to rollback the transaction in Catch.
The following code snippet will achieve what you need.

alter proc test_proc
as
BEGIN TRY
BEGIN TRANSACTION
Declare @returnMessage varchar(50)

set @returnMessage = '10'
UPDATE Sample
SET sampleID = 'dddd01'
WHERE sampleID = 'bbb01'

set @returnMessage = '11'
UPDATE SamplePrep
SET sampleID = 'dddd01'
WHERE sampleID = 'bbb01'

COMMIT TRANSACTION
Return 1

End Try
BEGIN CATCH
SELECT
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage
If @errormessage = '10'
begin
Rollback tran
return 10
end
else
begin
rollback tran
return 11
end
END CATCH

Hope this helps. Please try the above method and let me know if this method
works for you. I'm glad to assist you.

Have a great day,
Best regards,

Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Manjree Garg
9/25/2007 3:58:03 AM

Hello Wen

Unfortunately, the procedure is not working. When I tried it in SQL server
it's giving the following error message same as I mentioned earlier:

The UPDATE statement conflicted with the REFERENCE constraint
"FK_SamplePrep_Sample". The conflict occurred in database "Protien", table
"dbo.SamplePrep", column 'sampleID'.

I don't get it. If I am using Begin Transaction then why it's conflicting
with the REFERENCE constraint?

Thanks for your help.

Manjree

[quoted text, click to view]

v-wywang NO[at]SPAM online.microsoft.com
9/26/2007 11:34:33 AM

Hello Manjree,

Would you please send me a Screen shooting of the Error and the exact
Stored Procedure which you tried in SQL server?
I'm a little confused. the exception has been caught in procedure. It
should not be thrown when you executed it in SQL Server.
I need more detailed information such as the Screen shooting. Many thanks.

My alias is v-wywang@microsoft.com

If you have any more concern, please feel free to let me know.

Best regards,
Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about dotnet ado.net

dotnet ado.net : Search results