|
I have an issue with the use of parameters when attempting to talk to the database.
// This piece of code returns exactly what I expect
OleDbConnection myConnection = new OleDbConnection(ConnectString);
myConnection.Open();
OleDbCommand myCommand = myConnection.CreateCommand();
myCommand.CommandText = "SELECT statement FROM sys_sql " +
"WHERE key = 'clsDBNotesSelect'";
string strResult = (string) myCommand.ExecuteScalar();
myConnection.Close();
// This piece of code results in "An unhandled exception of
// type 'System.Data.OleDb.OleDbException' occurred in system.data.dll"
OleDbConnection myConnection = new OleDbConnection(ConnectString);
myConnection.Open();
OleDbCommand myCommand = myConnection.CreateCommand();
myCommand.CommandText = "SELECT statement FROM sys_sql " +
"WHERE key = @KEY";
myCommand.Parameters.Add("@KEY", OleDbType.VarChar, 50);
myCommand.Parameters["@KEY"].Value = "clsDBNotesSelect";
string strResult = (string) myCommand.ExecuteScalar();
myConnection.Close();
I've tried several variations and methods. None will get me past the error. Any input is appreciated.
Dan
|
|
|
|
|
Hi Dan
The "@Key" syntax for parameters is required by the SQL-Server provider. For the OLEDB provider (that you seem to be using), you need to use question marks in the query text:
OleDbConnection myConnection = new OleDbConnection(ConnectString);<br />
myConnection.Open();<br />
OleDbCommand myCommand = myConnection.CreateCommand();<br />
myCommand.CommandText = "SELECT statement FROM sys_sql " +<br />
"WHERE key = ?";<br />
myCommand.Parameters.Add("@KEY", OleDbType.VarChar, 50);<br />
myCommand.Parameters["@KEY"].Value = "clsDBNotesSelect";<br />
string strResult = (string) myCommand.ExecuteScalar();<br />
myConnection.Close();
Note that I have not tested the above code.
Hope this helps.
Andy Harman
|
|
|
|
|
Thank you very much. All the references I have always talk about the Sql provider and I've had to stumble through several different oddities due to the OleDb provider.
Your solution does work but I've lost the uniqueness of the parameters. I tried to replace the @ with the ? (leaving KEY) but it didn't like that. What is the methodology used if the CommandText changes (which it will!) to something like:
"SELECT statement FROM sys_sql WHERE key = ? AND owner = ?"
Is it going to be based on the order of the parameters added? Therefore, the first one added belongs to "key = ?" and the second to "owner = ?"?
Thanks
Dan
|
|
|
|
|
OleDb does not support named parameters.
Yes you have to add them in order.
The first param will be the first ?
and so on.
Bo Hunter
<marquee behavior="alternate">Bo Hunter
|
|
|
|
|
You could always build the string with String::Format and then pass (or initiate) it to an instance of OleDBCommand object...
OleDBCommand cmd = new OldDBCommand(String::Format("Select * from database where var1 = {0} and var2 = {1}",System::Object, System::Object ) ); //check the overrides for Format for your particular instance...
not 100% sure on the syntax but it's close enough for you to get the idea... It's 11:30 on a sat night and I'm fixing to go to bed...
Chris Blazek
|
|
|
|
|
Hello,
I am fairly new to .NET and wanted to ask recommendations on the proper use of sqlDataAdapters regarding using one sqlDataAdapter on each form created or, what is the best way to centrally setup the sqlDataAdapters that each form can access the same sqlDataAdapter. In Delphi, there is a DataModule (threaded) that allows you to put your non-visual data access components on it and then access the DataModule from each form or function that needs data access.
What might be some articles or references to a question like this?
Thank you,
|
|
|
|
|
A SqlDataAdapter is simply that: an adapter that fits between a DataSet or DataTable and one or more SqlCommands - one to select data, the SelectCommand property, and up to three others to update, insert and delete data (UpdateCommand, InsertCommand and DeleteCommand, respectively).
There are basically two ways to access data in a database with ADO.NET. The simplest, where you just need to fetch a result set from the database and do something with it quickly (e.g. export to a file, display on screen) is to use a SqlDataReader, which you obtain by calling ExecuteReader on the SqlCommand object. If the query you're executing only returns a single value, or you only want the value of the first field in the first row of the result set, consider ExecuteScalar instead. If you don't care about the response at all, or there isn't one, use ExecuteNonQuery.
If you're familiar with classic ADO, a SqlDataReader is similar to a Recordset opened with the adOpenForwardOnly and adLockReadOnly options. You can't reuse the SqlConnection that you executed the command on until you have closed your SqlDataReader or read all the results (when Read returns False).
For more complicated scenarios, where you need to access multiple result sets simultaneously, or you need to execute other queries against the result set, or the user needs to be able to update the data, ADO.NET provides DataSet and DataTable.
DataTable represents a cache of a result set, and offers many of the features from classic ADO, including editing and access to original and modified versions of rows. However, a DataTable is disconnected - any changes made to the DataTable are not reflected in the database until you use the DataAdapter's Update method.
A DataSet is a collection of DataTables and, optionally, relationships (DataRelations) between those tables (for example, parent-child).
Classes listed above which begin with Sql are specific to SQL Server, and live in the System.Data.SqlClient namespace. The other classes (beginning Data) are general and can be used with any provider; they live in the System.Data.
OLE DB equivalents of the Sql classes can be found in the System.Data.OleDb namespace. There are also so-called provider classes for ODBC and Oracle (downloadable from Microsoft for .NET Framework 1.0, and included in 1.1).
|
|
|
|
|
Hi,
Is there a method like "SetFieldNull" (a CDAORecordset method) in ADO programming ?
Thanks.
|
|
|
|
|
I am sorry if this seems off topic here,
but I'm not sure where else I could post this question,
and the people lurking in this forum probably know something of DBs,
and may know something that can help me, so...
...not to mention this problem is **killing** me !
Here goes:
I'm trying to set a dll to trace the ODBC communications.
The machine is running Win 2K 5.00 w/ SP-4.
I downloaded and installed the MDAC 2.8 and MDAC SDK 2.6.
From the MDAC SDK I got the source code for the trace.dll.
So I compiled it in Visual Studio 6.0, and I obtained the
trace.dll.
Now, in the Administrative Tools>Data Sources, in the
Tracing tab, I click on "Select DLL", navigate to the
location of the trace.dll, and click "OK.
When I click "OK" or "Apply", I receive the error message:
"General Error: either C:\WINNT\system32\trace.dll is not
an ODBC tracing dll or it is obsolete."
with an "OK" button.
Well... I'm not Ok with it, but I can only click on it...
After Ok-ing the error message, the DLL in the "Custom
Trace DLL" text box (in the Data Source > Tracing tab)
reverts to "C:\WINNT\system32\odbctrac.dll", which was the
value in it before I tried to change it.
I looked at the dependencies of the two dlls involved, to
check if I was not exporting something.
I noticed that the trace.dll was not listing all of its
routines.
So I added a trace.def file to my project, listed all of
the routines to be exported (I think), and tried again.
Now the trace.dll shows the various routines when I "View
Dependencies" on it, but the rest of the scenario has not
changed.
Last attempt (after long research on the net)
was to modify the signature of the routines to be exported
so that they are preceeded by __declspec(dllexport)
to ensure that they are being exported, but nothing changed...
....Suggestions?
Thanks in advance for your attention,
Frank
|
|
|
|
|
[mode="supplicant"]
..please ?
[/mode]
F.O.R.
|
|
|
|
|
A couple of things spring to mind:
Are the functions declared extern "C" if compiled as C++?
Is the TraceVersion function returning the correct version number?
|
|
|
|
|
Mike Dimmick wrote:
A couple of things spring to mind:
Are the functions declared extern "C" if compiled as C++?
Is the TraceVersion function returning the correct version number?
Mike,
first of all thanks for replying.
The source code is the done that comes with the MDAC SDK.
The various functions are declared as:
RETCODE SQL_API TraceSQLxxx
SQL_API is defined as __stdcall.
The sqltypes.h header file imported in the project
includes the following block:
#ifdef __cplusplus
extern "C" { /* Assume C declarations for C++ */
#endif /* __cplusplus */
How would I check the results of TraceVersion?
Do I need to build my harness to call just that routine and get the result?
BTW, which one would be the correct version?
Looking at the code, the TraceVersion routine returns the TRACE_VERSION constant, defined in
SQLEXT.H as 1000.
Thanks in advance,
F.O.R.
|
|
|
|
|
Well, it's neither of my extremely obvious points, so it looks like you're on your own again. Sorry. I don't have any experience in writing trace DLLs.
|
|
|
|
|
Thanks anyway...
..the funny thing is, it seems *nobody* has experience with this.
I mean, some of the DB-gurus I talked to didn't even know you could specify your own trace DLL,
and after much googling, I haven't found any info..
I guess I'm (once again) on the bleeding edge of technology that has been around for 5+ years but was always overlooked 
Thanks,
F.O.R.
|
|
|
|
|
Frank Olorin Rizzi wrote:
I mean, some of the DB-gurus I talked to didn't even know you could specify your own trace DLL
Count me in on the group. I thought I had done literally every advanced thing you can do with ODBC, but that's one thing I haven't done. When you do find out how to do this, there might be an article in there....
Cheers,
Tom Archer
Inside C#,
Extending MFC Applications with the .NET Framework
It's better to listen to others than to speak, because I already know what I'm going to say anyway. - friend of Jörgen Sigvardsson
|
|
|
|
|
Tom Archer wrote:
When you do find out how to do this, there might be an article in there....
"If"..."If I find out"
I'll be sure to post any solution I find
F.O.R.
|
|
|
|
|
Here's the latest update I can provide:
After checking the list of routines exported by the odbctrac.dll (the one that works), and the list of routines exported by the trace.dll (the one produced by compiling the source code provided by M$ in the samples for ODBC from the MDAC SDK), I found that two routines where missing from the trace.dll:
FireVSDebugEvent
TraceVSControl
So, I added to the .def file for trace.dll.
Compiling, I got an external unreesolved error for FireVSDebugEvent
(indeed, the other one is implemented in one of the files in the project).
The signature for FireVSDebugEvent is provided, but no implementation.
So I made a dummy implementation (FireVSDebugEvent returns void, so I just
did nothing in its body), and tried again.
It *still* doesn't work.
So, my conclusion, at this time, is that there has been some change to the requirements for the ODBC trace DLL since the 2.6 version (I got the source code from MDAC SDK 2.6, but the MDAC itself is up to 2.8).
Apparently, M$ will release the ComponentChecker for MDAC 2.8 ASAP.
I am hoping that, at that time, they will release a new MDAC SDK, or provide a list of changes.
For the time being, I'll put this aside.
Hope this can provide some info to those interested,
even if it is not a solution.
F.O.R.
|
|
|
|
|
For anyone who may stumble upon this...
A colleague of mine actually fixed this to work.
Apparently, he simply added the .def file to export all of the
various TraceSQL* routines.
In addition to that, he had to include the FireVSDebugEvent
and TraceVSControl routines to the .def file.
Looking at his source, and mine,
I guess mine wasn't working because I had __declspec(_stdcall)
(or something like that...I'm not 100% sure yet).
In short:
the source code from MS seems to work once you
add the .def file.
Hope this Helps,
F.O.R.
PS: Props to my colleague, and to Brannon, who sent some helpful hints as well!
|
|
|
|
|
I have a BULK INSERT query that works fine when i call it from query analyzer, but when i call same query from VC++ application via ADO Connection Execute method, it gives me error. I tried to look at the COM error, its gives me error -2147217900
I make sure that i am using same username/password while connecting to Database either from Query Analyzer or from VC++.
Any help or tip will he highly appreciated
|
|
|
|
|
I tried to look into details of COM error, here is what i get
IDispatch error #3092
Please please help
|
|
|
|
|
The error code translates to 0x80040e14 (hex) which, looking up in oledberr.h from the Platform SDK gives DB_E_ERRORSINCOMMAND.
Did you mark your command as adCmdText when calling Execute?
|
|
|
|
|
You can use Transaction concept in your code.When you open the connection you have to start the Transation (i.e. BeginTransaction). Once your all Bulk Insertion is finished you can either commit trasaction or rollback.
Manish Darji
|
|
|
|
|
I know that Datasets are supposed to be slower than datareaders, but exactly how slow is it? I've tried using both to compare and it would seem that the datset is about 7 times slower than the reader. Is it supposed to be this way. In one of my web application which uses dataset to populate a very large table, this seems to take forever. Is the because of the dataset or is it some Windows setting problem?
Thanks for listening.
God, I pity me! - Phoncible P. Bone
If I end up Windows ME someone is going to be hurting. - One of the answers to a question for What OS are you
|
|
|
|
|
Hi,,
it's because of the dataset..
Datasets caches data locally then you can disconnect from the datasource..This means that you'll consume alot of memory , time..
As you just populate a table..Use Datareader brcause it's made for this setuation (Fast-Forward only-Read only).
|
|
|
|
|
Thanks for answering.
I realized that, but I was wondering if is it usual to be 7 times slower?
And for my cases, I need to use the dataset because I need to manipulate the datas returned. I thought that there might be any other method/settings that I can change
God, I pity me! - Phoncible P. Bone
If I end up Windows ME someone is going to be hurting. - One of the answers to a question for What OS are you
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
