|
Hi,
our website is presently using Access as it's database. We are planning to redesign our website and we'd like to improve our database system. So I have a few questions:
1) What version should we use? 97, 2000 or 2002? Why?
2) Should we use Jet drivers or Access drivers (DRIVER={Microsoft Access Driver (*.mdb)}; )? I guess that questions 1 and 2 are related...
3) Is there a proper way to open and close a connection? For example, should I open a connection at the beginning of a page, let the recordsets found in the code use the same connection, and close that connection at the end of the page, when I'm done with it? Or should I use may different connections (like 1 connection for each recordset)?
Any other info/links are welcomed!
Thanks! 
---------------
Tired of Spam? InboxShield 2.0.2 for Microsoft® Outlook® 2K/2K2/2K3
http://www.inboxshield.com
|
|
|
|
|
LukeV wrote:
What version should we use? 97, 2000 or 2002? Why?
Any version will do !
LukeV wrote:
Should we use Jet drivers or Access drivers (DRIVER={Microsoft Access Driver (*.mdb)}; )? I guess that questions 1 and 2 are related...
I've had the best results with the OLEDB (Jet) Driver
LukeV wrote:
Is there a proper way to open and close a connection? For example, should I open a connection at the beginning of a page, let the recordsets found in the code use the same connection, and close that connection at the end of the page, when I'm done with it? Or should I use may different connections (like 1 connection for each recordset)?
Use the same ADODB Connection, and just close it at the end of code execution
HTH
Tony
|
|
|
|
|
LukeV wrote:
What version should we use? 97, 2000 or 2002? Why?
2000 is good .. 97 is good too !!!
LukeV wrote:
Should we use Jet drivers or Access drivers (DRIVER={Microsoft Access Driver (*.mdb)}; )? I guess that questions 1 and 2 are related...
never use that one I had troubles with it
use jet 4.0
|
|
|
|
|
Have you considered migrating ot MSDE? Essentially the server engine from SQLServer 2K - should have it available on either the Office CD, or Visual Studio stuff - if you have that available, then even get the wazzy front end tools....think it's called the MSDE For Visual Studio o something similar
"Now I guess I'll sit back and watch people misinterpret what I just said......"
Christian Graus At The Soapbox
|
|
|
|
|
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?
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
