Re: Concatting 2 fields in SQL from 2 tables - C# Discussion Boards

19-Jan-08 4:01

Hi,

I am trying to select from two tables and concat two values. Thus, i should get a result of "324324234, gareth111".

SQL is below as well as the error.

SELECT UNet_Engineer.EngineerId + ', ' + UNet_Login.Username FROM UNet_Engineer, UNet_Login WHERE UNet_Engineer.EngineerId=UNet_Login.EngineerId;

DbConnection.ExecuteSelect; Exception: System.Data.OleDb.OleDbException: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.<br />
Data type mismatch in criteria expression.<br />
   at System.Data.OleDb.OleDbDataReader.ProcessResults(OleDbHResult hr)<br />
   at System.Data.OleDb.OleDbDataReader.GetRowDataFromHandle()<br />
   at System.Data.OleDb.OleDbDataReader.GetValueBinding(MetaData info)<br />
   at System.Data.OleDb.OleDbDataReader.GetValues(Object[] values)<br />
   at System.Data.ProviderBase.DataReaderContainer.CommonLanguageSubsetDataReader.GetValues(Object[] values)<br />
   at System.Data.ProviderBase.SchemaMapping.LoadDataRow()<br />
   at System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping)<br />
   at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)<br />
   at System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)<br />
   at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)<br />
   at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)<br />
   at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)<br />
   at UNet.Manager.Database.DbConnection.ExecuteSelect(String sql) in D:\UNet.Manager\Database\DbConnection.cs:line 90

Any ideas?

Regards,
Gareth.

pmarfleet19-Jan-08 4:56

19-Jan-08 4:56

What data type is UNet_Engineer.EngineerId? If it is numeric, you need to cast it to a string data type before attempting the concatenation. CAST(UNet_Engineer.EngineerId AS VARCHAR) should do it.

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

Gareth H19-Jan-08 6:31

19-Jan-08 6:31

Hi,

I changed the SQL as you said, but it produced a new error.

SELECT CAST(UNet_Engineer.EngineerId AS VARCHAR) + ', ' + UNet_Login.Username FROM UNet_Engineer, UNet_Login WHERE UNet_Engineer.EngineerId=UNet_Login.EngineerId;<br />
<br />
DbConnection.ExecuteSelect; Exception: System.Data.OleDb.OleDbException: IErrorInfo.GetDescription failed with E_FAIL(0x80004005).<br />
   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)<br />
   at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)<br />
   at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)<br />
   at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)<br />
   at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)<br />
   at System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)<br />
   at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)<br />
   at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)<br />
   at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)<br />
   at UNet.Manager.Database.DbConnection.ExecuteSelect(String sql) in D:\UNet.Manager\Database\DbConnection.cs:line 90

Regards,
Gareth

Gareth H19-Jan-08 6:38

19-Jan-08 6:38

Just thought i'd add. I am using MS Access as my db.

Regards,
Gareth.

pmarfleet19-Jan-08 9:11

19-Jan-08 9:11

gareth111 wrote:
Just thought i'd add. I am using MS Access as my db.

Ah. I'd assumed you were using SQL Server. The syntax I provided is specific to T-SQL and won't work in Access.

My Access knowledge is somewhat lapsed and I don't have it installed on my PC. Have you tried executing your original query directly through the Access IDE? You might get some more meaningful information on the cause of the error.

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

Gareth H19-Jan-08 9:59

19-Jan-08 9:59

pmarfleet,

I have not tested that. But, is there a way of making it not database dependent? Because this project is aimed at running on a variety of database systems and i dont want to have to change the SQL every time, and yes, i know i could use stored procs to solve this, but is there another way.

Regards,
Gareth.

pmarfleet19-Jan-08 10:08

how to display percentage of compeletion of bacup sql server in my app ?

19-Jan-08 10:08

gareth111 wrote:
But, is there a way of making it not database dependent?

AFAIK, string concatenation is pretty standard across different database providers. However there are likely to be subtle differences. For instance, in T-SQL you are required to cast non-string values to strings before attempting to concatenate that value with another string. Access is more forgiving, and I believe doesn't require explicit casting. If you want database independence, you should limit yourself to pure ANSI SQL in your queries and perform any data manipulation in your middle tier.

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

hdv21219-Jan-08 3:45

hdv212

19-Jan-08 3:45

hi if u run this tsql command :
backup database northwind to disk='c:\nw.bak' with format,stats=10
it display percentage of compeletion of backing up your database in sql server output window, but i want to display it, in my app, how to do that ?
thanks

Re: how to display percentage of compeletion of bacup sql server in my app ?

pmarfleet19-Jan-08 5:00

Re: how to display percentage of compeletion of bacup sql server in my app ?

19-Jan-08 5:00

I doubt that this information is accessible. Instead, why not consider showing some sort of animated graphic? Something like the progress bar that Windows XP displays upon loading?

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

hdv21219-Jan-08 5:22

hdv212

19-Jan-08 5:22

thanks Paul Marfleet
but i think a way that show it in omy app.
however can u more explain of your suggestion ?
thanks

Re: how to display percentage of compeletion of bacup sql server in my app ?

pmarfleet19-Jan-08 5:53

Re: how to display percentage of compeletion of bacup sql server in my app ?

19-Jan-08 5:53

hdv212 wrote:
but i think a way that show it in omy app.

I don't understand what this means.

hdv212 wrote:
however can u more explain of your suggestion ?

Set the Style property of the ProgressBar to ProgressBarStyle.Marquee. Adjust the MarqueeAnimationSpeed property as required.

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

Tamer Oz19-Jan-08 19:04

Tamer Oz

19-Jan-08 19:04

You can achieve this goal by using sqldmo, another option is reading output stream from commandwindow while executing sqlcmd commands.

Anyway I hope this link helps
http://www.sqldev.net/sqldmo/SQL-DMO.NETEventsCS.htm

CSV file Import to SQL Server Database and Validation From C#.Net

Nisha_Aadhi19-Jan-08 1:11

Nisha_Aadhi

19-Jan-08 1:11

Hi
I want to import the data available in .CSV file to a table(s)in sqlserver db from C#.net.

User has to choose the .csv file from the frontend and the data from that particular chosen file should be imported to database after the client side validation.

I hope for this , we need to read the specified file from the client machine and write it into the server machine,... i dont know how to handle permission issues and all.

Pls help me out how to do this scenario ASAP...

Re: CSV file Import to SQL Server Database and Validation From C#.Net

pmarfleet19-Jan-08 4:58

message box in windows service

19-Jan-08 4:58

This is a common question and a quick Google[^] search provides you with plenty of information.

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

Ahmad Adnan19-Jan-08 1:05

Ahmad Adnan

19-Jan-08 1:05

im workingin windows service and i have to notify the user of specific messgae.showing message box is invalid in windows service.any solution.

regards

Re: message box in windows service

jhaga19-Jan-08 4:50

two RegEx questions: subpatterns, performance

19-Jan-08 4:50

Use .NET remoting and have a normal application listening to your service and then pop up a message. NOT good to have a service showing messages!

Recommended books:

Troelsen for Visual Studio 2008

Vavilala's AJAX for ASP.NET

peterchen19-Jan-08 0:29

peterchen

19-Jan-08 0:29

Two queations about regular expressions:

(1) Is it possible to define "sub-patterns"?
I have a larger regex containing the same sub-pattern multiple times, e.g.
(?<idA>[a-zA-Z_][a-zA-Z_0-9]*)=(?<idB>[a-zA-Z_][a-zA-Z_0-9]*)

Is it possible to assign a sub-pattern to something (maybe \ö), so that I can use
(?<idA>\ö)=(?<idB>\ö)

?

(2) performance
Are there any general performance guidelines for regular expressions?
I would prefer to break down complex regexes into a few simpler ones sor readability.

We are a big screwed up dysfunctional psychotic happy family - some more screwed up, others more happy, but everybody's psychotic joint venture definition of CP
blog: TDD - the Aha! | Linkify!| FoldWithUs! | sighist

jhaga19-Jan-08 4:34

19-Jan-08 4:34

I have sometimes used MatchEvaluator delegate. A MatchEvaluator as an event handler that fires when an "OnMatch" event occures. Gives a little stucture to your regex.

And don't try that assignment with a subpattern Wink | ;)

Recommended books:

Troelsen for Visual Studio 2008

Vavilala's AJAX for ASP.NET

peterchen19-Jan-08 7:35

peterchen

19-Jan-08 7:35

jhaga wrote:
And don't try that assignment with a subpattern

hm?

jhaga19-Jan-08 7:51

19-Jan-08 7:51

A subpattern is name on something that has matched. Changing what is matching just don't make any sense. Assign it to a c# variable if you need to do something with it outside the regular expression match.

jhaga

Recommended books:

Troelsen for Visual Studio 2008

Vavilala's AJAX for ASP.NET

peterchen19-Jan-08 9:43

peterchen

19-Jan-08 9:43

Oh my, I should have made that clearer Smile | :)

I guess I am using the term "subexpression" wrong.
What I would like to see is basically an advanced defiition:

something := [a-zA-Z_][a-zA-Z_0-9]
so that I can use something instead of the sequence on the rhs. (They should match different strings, and I am not looking for a replace - just a match and split into components)

jhaga19-Jan-08 11:14