|
Ye gods, he said Visual Basic 6, and you can't use an OLE DB connection string with the SqlConnection class in the .NET Framework anyway.
Ahem.
Right, a useful answer to the question: when working with ADO, I can't usually be bothered to set up the connection string myself. I use Windows' built-in Universal Data Link tool.
Create a new, empty text document somewhere. Rename it so that the extension is '.udl'. The icon should change to what looks like a computer in front of a database table. Open the file.
You should get a property sheet titled "Data Link Properties". Choose the Provider tab and select "Microsoft Jet 4.0 OLE DB Provider". Click the Connection tab or the Next>> button. Continue making settings as appropriate.
You can now open the file with Notepad, or any other text editor. Inside you'll see the following:
[oledb]
; Everything after this line is an OLE DB initstring
Provider=Microsoft.Jet.OLEDB.4.0;Data Source= (etc) Copy the third line to the clipboard, we'll need it in a moment.
In VB6, use the References box to add a reference to "Microsoft ActiveX Data Objects 2.5 Library". You could also use 2.6, 2.7 or 2.8 if you have a higher version installed, but they don't offer much more functionality for simple data access, and you'd have to ensure the end-user has this version. MDAC 2.5 came with Windows 2000.
You can now access your data, for example:
Dim conn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As ADODB.Recordset
conn.ConnectionString = _
"connection string from UDL file"
conn.Open
cmd.CommandText = "select command"
cmd.CommandType = adCmdText
cmd.ActiveConnection = conn
Set rs = cmd.Execute(, , adOpenForwardOnly Or adLockReadOnly)You could also use rs.Open instead of cmd.Execute.
|
|
|
|
|
Need help....
How do I combine the following two statements? I'm using them in ASP connected to Access DB.
SqlJunk = " SELECT SUM(MSD+Methods+Analysis+Architectures+Models+Studio+Elective1+Elective2+Elective3) As PersonTotals FROM tblpersons "
SqlJunk = SqlJunk & " WHERE User_Id=" & Request.Form("DaInBox")
SqlJunk = "SELECT FirstName, LastName, WeekNo, ProgramName, StudioTeamName FROM tblpersons"
SqlJunk = SqlJunk & " WHERE User_Id=" & Request.Form("DaInBox")
When I run them independantly they work but I don't know how to combine them
Thanx
|
|
|
|
|
Is User_Id your primary key?
Two ways:
1. User_Id is your primary key then:
SELECT (MSD + Methods + Analysis + Architectures + Models +
Studio + Elective1 + Elective2 + Elective3) As PersonTotals,
FirstName, LastName, WeekNo, ProgramName, StudioTeamName
FROM tblpersons
2. User_Id is NOT your primary key
SELECT SUM(MSD + Methods + Analysis + Architectures + Models +
Studio + Elective1 + Elective2 + Elective3) As PersonTotals,
FirstName, LastName, WeekNo, ProgramName, StudioTeamName
FROM tblpersons
GROUP BY FirstName, LastName, WeekNo, ProgramName, StudioTeamName
Without knowing more about your datamodel I can't guarantee they they will produce the expected results.
Where you appeared to be going wrong was that the SUM() function is an aggregator function (It operates on lots of records). If User_Id was your primary key then it was operating on only one record so there was nothing to aggretate. If User_Id was not your primary key then you need to specify the fields to GROUP BY that you want to output but are not part of the input to the aggretate function.
--Colin Mackay--
"In the confrontation between the stream and the rock, the stream always wins - not through strength but perseverance." (H. Jackson Brown)
Enumerators in .NET: See how to customise foreach loops with C#
|
|
|
|
|
Friends,
I am in search of resources from which i can learn about the database concurrency issues related to .NET, Oracle and MS-Access. I've found number of resources related to SQL-Server and .NET. But unable to find resoures related to MS-Access and Oracle. Can you tell me how concurrency issues are handled in Access and Oracle Or give me some good resources.
Thanks
|
|
|
|
|
I want to create some stored procedures, but my server database is read-only. Is there any way that I can create a local database (like MS-Access) and then I can save my stored procedures there? It is important to point out that the application is going to run in computers that do not have Office installed (meaning that ACCESS is not an option to work with, only to create from outside such database with read/write privilegies). Any input is really appreciated. Thanks ..
PS I logged on like 30 times and every time I tried to post my message I got the error that I am not logged on. This is frustrating 
|
|
|
|
|
Anonymous wrote:
PS I logged on like 30 times and every time I tried to post my message I got the error that I am not logged on. This is frustrating
Do you have cookies enabled?
--Colin Mackay--
"In the confrontation between the stream and the rock, the stream always wins - not through strength but perseverance." (H. Jackson Brown)
Enumerators in .NET: See how to customise foreach loops with C#
|
|
|
|
|
Hello, I have a c# code file and within it I have the following code:
private void Page_Load(object sender, System.EventArgs e)
{
DBReader("SELECT PackageNumber FROM Packages WHERE Packages.PackageID = Level1Packages.PackageID");
}
private void DBReader(string SQLQuery)
{
if(!IsPostBack)
{
OleDbCommand myCommand = new OleDbCommand(SQLQuery,oleDbConnection1);
oleDbConnection1.Open();
dataReader = myCommand.ExecuteReader(); *******
while(dataReader.Read())
{
this.Lvl1PackageNumListBox.Items.Add(dataReader.GetString(0));
}
oleDbConnection1.Close();
}
}
The code dies on the line with stars with the error message "no value given for one or more parameters". I think the problem lies in my SQl statement I am passing to the DBReader method. I am not sure if using the . operator is correct when trying to access a column of a table. Thanks for helping out a newbie!
Frank
|
|
|
|
|
Going out on a limb here, but change the SQL to be
SELECT PackageNumber FROM Packages,Level1Packages WHERE Packages.PackageID = Level1Packages.PackageID,
Chris Meech
It's much easier to get rich telling people what they want to hear. Chistopher Duncan
I can't help getting older, but I refuse to grow up. Roger Wright
I've been meaning to change my sig. Thanks! Alvaro Mendez
We're more like a hobbiest in a Home Depot drooling at all the shiny power tools, rather than a craftsman that makes the chair to an exacting level of comfort by measuring the customer's butt. Marc Clifton
|
|
|
|
|
I'm connecting to a database via DSNs. I want to figure out if I'm woring with MSSQL, Oracle or Access. Using ADO or MCF ODBC classes. How?!
|
|
|
|
|
|
I think you need to use SQLGetInfo once you've opened the connection, passing the SQL_DRIVER_NAME value for the InfoType parameter. That's based on my reading of the documentation.
You could also try SQL_DATABASE_NAME or SQL_DBMS_NAME.
When using ADO with a DSN, you're actually using the MSDASQL provider, which is basically a translation layer on top of ODBC. It may be possible to retrieve the underlying ODBC connection handle by looking at the Properties collection of the ADO Connection object.
|
|
|
|
|
Hi!
I am trying to drop a login but it owns a few databases. I know about the sp_changedbowner sproc, but I'd like to know if there's a way to remove the ownership per user (for all dbs)... for example: sp_removeownership 'login'.
I need to do this before dropping a login that owns some databases. Database is MS-SQL.
Thanks!
Carl
|
|
|
|
|
 I am creating an application for a local education company that is basically an off line tutorial application.
They eventually wish to download the application and start up files then use explorer to pull more files in as the students progress, but for the time being a simple distributed application on their network locally.
I have created a simple MFC single doc application, and the view class is based on HTMLview, this allow me to load HTML files into the app easily.
I have decided to create the pages to be loaded by using a script language (haven't decided which one yet) within HTML doc's.
My question is can i use SQL to store and retrieve HTML files, My idea was to load the files into an SQL database, then use a simple CDatabse class or maybe ADO to load and unload the files, probably ADo since it is more flexible. if i can, then this is a great advantage since the target network has SQL.
Am i trying to be too ambitious, is there an easier way to manage these files from a central location using windows, the pages need to be loaded in sequence.
Any responses are greatly appreciated, i do not wish to code from scratch
Thanks to all whom respond.
Joe.A
|
|
|
|
|
You can use ado to store the files in a LongText (Memo for Access) or LongBinary field, but the retrieve performance is likely to be poor. A better idea would be to put all the files in a known directory structure, then store the file path in SQL. You could fetch the sequential list of paths quite quickly, then open the appropriate file. This would also simply file maintenace, as modifications to a document would be reflected 'instantly' in the app with no SQL update needed. Changing the sequence would require just modifying a sequence field in the record.
for example: the table could be doc_id int (identity,1,1) , Doc_seq int,
Doc_Name Varchar(255), Doc_Path Varchar(255). I would initially make the doc_seq values increment by 10 or so (leave space for insertions into the sequence. Retrieve with "order by doc_seq" ....
Genius may have its limitations, but stupidity is not thus handicapped. - Elbert Hubbard
|
|
|
|
|
Hi
How we can create a new database for example access database that does not exist before by ADO.NET?
the code must create files that needed for database (in this example .mdb file)
and I want to know how create a new table in database and update database.
Regards' Amir Jalaly
|
|
|
|
|
The easiest way for Access is to simply copy an empty access database (or a "template" with your data structures but no data) using filecopy. ADO.Net doesn't support "create Database" for Acess (actually it is the underlying oledb provider that doesnt support this), and the ADOX "create catolog" stuff isn't there either. To create a new tqable, juse use and execute query (OledbCommand.Execute or OledbConnection.Execute) like "Create Table TableName
(Field1 Int Null, field2 Varchar(255), field3 Double)" If you need spaces (or keywords) in table/field names, then enclose them in sqare brackets [Field and Space] int,...
Genius may have its limitations, but stupidity is not thus handicapped. - Elbert Hubbard
|
|
|
|
|
When I run this query I get 2 row back with different dates:
SELECT Sdate1,EDate1,SDate2,Edate2
FROM norm,elig
WHERE norm.Number=elig.Number
and norm.Gnumber=elig.gnumber and (norm.Number = '15')
I would like to get only one row back with Sdate1,Edate2.
How do I do this?
|
|
|
|
|
Without knowing anything about your data the following is the best I can manage:
SELECT TOP 1 Sdate1,Edate2
FROM norm,elig
WHERE norm.Number=elig.Number
and norm.Gnumber=elig.gnumber and (norm.Number = '15')
--Colin Mackay--
"In the confrontation between the stream and the rock, the stream always wins - not through strength but perseverance." (H. Jackson Brown)
|
|
|
|
|
Hi,
I need your help.....I want to add numbers stored in the DB and display them on the ASP page.
My Access DB looks like this:
WeekNo Course1 Course2 Course3 Course4
1 2 4 5 1
2 4 6 7 9
I want users to get thier totals for differnt weeks and get their results displayed on the screen via an ASP page.
What I have at the moment is: Users are able to Enter their data and View it then are able to update it. I need code that can get me started.
Thanks in advance.
Petunia
|
|
|
|
|
I do not know how many people out there need something like this, but I recently had use for it with my new dedicate server across the country. They have an MSDE installed database on there but it was not configured for remote connection. To make matters worse, they block port the SQL port to protect from viruses.
Well, I had to reconfigure it so that I could connect to it with Enterprise Manager (since I am a spoiled brat). I found this freeware tool that makes it an easy matter to configure your server along with other features. It is not Enterprise Manager, but it is a lifesaver:
http://www.asql.biz/DbaMgr.shtm[^]
It also has the ability to generate an Insert script for your data in a table. That can be handy when you want to move a table of data quickly or genrate a script to prepopulate a new database.
Rocky <><
www.GotTheAnswerToSpam.com
|
|
|
|
|
I am trying to find a method to return two results sets from a single query that can be handing by one call to the SqlDataAdapter. I am using paged data and I need to return a table with the current page of rows along with the total count of rows that would match (not limited to the page being returned).
The stored procedure I am using has selection for the current page of rows and then does another select for the record count. I have not been able to get the data adapter to populate another table in the dataset with the record count.
Any idea?
Rocky <><
www.GotTheAnswerToSpam.com
|
|
|
|
|
Using multiple SELECTs with a DataAdapter yields multiple DataTables in a DataSet that you pass to DataAdapter.Fill. If you want any of the DataTables named, you must use a DataTableMapping which can be assigned to the DataAdapter.
When you use a SqlCommand, you can get multiple result sets by calling SqlCommand.ExecuteReader with either no parameters or CommandBehavior.Default, so long as CommandBehavior.SingleResult is not passed. Then you loop over SqlDataReader.NextResult() and inside loop over SqlDataReader.Read().
-----BEGIN GEEK CODE BLOCK-----
Version: 3.21
GCS/G/MU d- s: a- C++++ UL@ P++(+++) L+(--) E--- W+++ N++ o+ K? w++++ O- M(+) V? PS-- PE Y++ PGP++ t++@ 5 X+++ R+@ tv+ b(-)>b++ DI++++ D+ G e++>+++ h---* r+++ y+++
-----END GEEK CODE BLOCK-----
|
|
|
|
|
I must be doing something wrong. It does not seem to pick up the second result set. The stored procedure returns two result sets as verified in query analyser but when I feed to to the sda which has been generated in the visual designer and supplied with a typed dataset, it only has one table in it.
Rocky <><
www.GotTheAnswerToSpam.com
|
|
|
|
|
If you're using a strongly-typed DataSet, then you have named DataTables and must use a DataTableMapping on your SqlDataAdapter like I mentioned previously. Both tables must be mapped accordingly.
-----BEGIN GEEK CODE BLOCK-----
Version: 3.21
GCS/G/MU d- s: a- C++++ UL@ P++(+++) L+(--) E--- W+++ N++ o+ K? w++++ O- M(+) V? PS-- PE Y++ PGP++ t++@ 5 X+++ R+@ tv+ b(-)>b++ DI++++ D+ G e++>+++ h---* r+++ y+++
-----END GEEK CODE BLOCK-----
|
|
|
|
|
Heath Stewart wrote:
must use a DataTableMapping on your SqlDataAdapter
Actually, after looking at the results, the dataset does not get two tables, only one. Not sure what could be causing that. I can run it in QA and the sp returns two result sets. Will have to dig further.
Rocky <><
www.GotTheAnswerToSpam.com
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
