Search results

[Guest]

Hi

I am using SqlServer 2005 database in VC++ (via ADO.NET). I am using
stored procedure to search the database as

SELECT supplierID, sampleID
FROM Supplier
JOIN Sample ON Supplier.supplierID = Sample.supplierID

Now the problem is how to store these search results so that these can be
used in VC++ interface using ADO. NET.
Does it return DataRowCollection object?

Thanks
Manjree

 

[Norman Yuan]

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.

 

[WenYuan Wang [MSFT]]

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:ata::CommandType::StoredProcedure.
You may heck the following code snippet.

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

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

Another option, we can create a DataAdatper and fill the return data into
DataSet or DataTable:
System:ata::SqlClient::SqlDataAdapter^ sda=gcnew
System:ata::SqlClient::SqlDataAdapter(scd);
//Fill the returned data into DataSet
System:ata:ataSet^ ds=gcnew System:ata:ataSet();
sda->Fill(ds);
//Fill the returned data into DataTable
System:ata:ataTable^ dt=gcnew System:ata:ataTable();
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.

 

[Guest]

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

 

[WenYuan Wang [MSFT]]

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:ata::SqlClient::SqlCommand^ scd=gcnew
System:ata::SqlClient::SqlCommand();
scd->CommandText=SQLquery;
scd->CommandType=System:ata::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.

 

[Guest]

Hi Wen

Thanks for your help. It did work.

Manjree

 

[WenYuan Wang [MSFT]]

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.

 

[Guest]

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

 

[WenYuan Wang [MSFT]]

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:ata:ataRow^>^ 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.

 

[Guest]

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

 

[Guest]

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

 

[WenYuan Wang [MSFT]]

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:ata:ataTable^ dt=srchResTable;
array<System:ata:ataRow^>^ 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:ata:ataColumn^)dt->Columns)->ColumnNa
me);
objStreamWriter->Write("\t");
}
objStreamWriter->WriteLine();
//write rows
for(int rowNum=0;rowNum<dt->Rows->Count;rowNum++)
{
System:ata:ataRow^ 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.

 

[WenYuan Wang [MSFT]]

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

System:ata:ataTable^ dt=ds->Tables[0];
array<System:ata:ataRow^>^ 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:ata:ataColumn^)dt->Columns)->ColumnNa
me);
objStreamWriter->Write("\t");
}
objStreamWriter->WriteLine();
//write rows
for(int rowNum=0;rowNum<srchResRows->Length;rowNum++)
{
System:ata:ataRow^ 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.

 

[WenYuan Wang [MSFT]]

Hello Manjree,

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

array<System:ata:ataRow^>^ srchResRows=dt->Select("c4>#1999/01/01#");
or
array<System:ata:ataRow^>^ 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.

 

[Guest]

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

 

[WenYuan Wang [MSFT]]

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:ataTime.

Therefore, what you really need is to convert CString to DateTime.
DataTime:arse 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:ateTime^ a=System:ateTime:arse(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:

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

date types?
I think the data type of row->default[L"strgTemp"] is System:ouble,
rather tha SQL Float.
Again, the row->default[L"dateExpiry"] should be System:atatTime.

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:ouble"?)

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

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.

 

[WenYuan Wang [MSFT]]

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:ataTime.

Therefore, what you really need is to convert CString to DateTime.
DataTime:arse 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:ateTime^ a=System:ateTime:arse(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:

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

date types?
I think the data type of row->default[L"strgTemp"] is System:ouble,
rather tha SQL Float.
Again, the row->default[L"dateExpiry"] should be System:atatTime.

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:ouble"?)

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

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.

 

[Guest]

Hi Wen

Thanks for your reply.
The DateTime:arse 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:ouble,
and, the row->default[L"dateExpiry"] is System:atatTime.
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

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:ataTime.

Therefore, what you really need is to convert CString to DateTime.
DataTime:arse 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:ateTime^ a=System:ateTime:arse(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:

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

date types?
I think the data type of row->default[L"strgTemp"] is System:ouble,
rather tha SQL Float.
Again, the row->default[L"dateExpiry"] should be System:atatTime.

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:ouble"?)

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

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.

 

[WenYuan Wang [MSFT]]

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:ouble and row->default["dateExpiry"] is System:ateTime.
Now, the issue has been changed to how to convert System:ouble and
System:ateTime 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:ouble 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.
Systemuble db=3.15;
Test(static_cast<double>(db));

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


To convert System:ateTime 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.

 

[Guest]

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

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:ouble and row->default["dateExpiry"] is System:ateTime.
Now, the issue has been changed to how to convert System:ouble and
System:ateTime 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:ouble 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.
Systemuble db=3.15;
Test(static_cast<double>(db));

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


To convert System:ateTime 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.