Database

Re: How to get the columns except one

30-Jul-03 5:15

Mike Dimmick wrote:
How can I get the data of all columns except for ID column?

Just want to add that it's a bad practice to use SELECT *. Think what will happen with your code if you rename some of your table columns or reorder them.

Alexandre Kojevnikov
MCAD charter member
Leuven, Belgium

Philip Patrick31-Jul-03 2:38

Philip Patrick

31-Jul-03 2:38

Alexandre Kojevnikov wrote:
Think what will happen with your code if you rename some of your table columns or reorder them.

So what? If you'll specify all columns in SELECT, you will still need to change your code, then what's the matter? Mike actually pointed the right issue about performance of "*"

Philip Patrick
Web-site: www.stpworks.com
"Two beer or not two beer?" Shakesbeer

Re: How to get the columns except one

Alexander Kojevnikov31-Jul-03 4:16

Re: Detecting MDAC version

31-Jul-03 4:16

Philip Patrick wrote:
you will still need to change your code

Exactly. But if you don't use SELECT * you will get errors in your SQL query and will have one more possibility to think about consequences of column renaming and adapt the client code appropriately.

With SELECT * you might forget about adapting the client code. And in many cases you won't even see compile-time errors.

If you are just reordering columns, you won't have to change anything.

Performance is also an important issue of course.

Alexandre Kojevnikov
MCAD charter member
Leuven, Belgium

Detecting MDAC version

Thesisus29-Jul-03 8:04

Thesisus

29-Jul-03 8:04

Does anyone know how to detect what MDAC version a server is running?

Bo Hunter29-Jul-03 10:32

Bo Hunter

29-Jul-03 10:32

RegistryKey key = Registry.LocalMachine.OpenSubKey( @"SOFTWARE\Microsoft\DataAccess" );
string version = key.GetValue( "FullInstallVer", "0.0.0.0" ) as string;
key.Close();

All this kind of stuff is on MSDN.

Bo Hunter

Re: Detecting MDAC version

Mike Dimmick29-Jul-03 13:36

Mike Dimmick

29-Jul-03 13:36

Programmatically, or for diagnostic purposes?

For diagnostics, use the MDAC Component Checker[^].

The snapshots included with the tool can be somewhat misleading - I don't think I've ever had any system installation, including a completely clean install, report no errors with this tool. But it'll make a fairly good identification.

indexof - wishing for

Megan Forbes29-Jul-03 5:07

Megan Forbes

29-Jul-03 5:07

I have a varchar(30) variable which holds data I want, a '$' and then some more data I want. Basically, I'm trying to cheat and return 2 dates from one function, and this seemed one way to do it.

My problem is this - I want to get the two dates in the sp this string is returned to. I was thinking of finding the position of the '$', then the characters before it will be the first date, and the characters between it and the end of the string will be the second date. The problem is, I'm having trouble getting the position of the '$'.

Any ideas? Smile | :)

I hope I'm making sense Unsure | :~

Look at the world about you and trust to your own convictions. - Ansel Adams

Photography[^]

Re: indexof - wishing for

jeff_martin29-Jul-03 5:16

jeff_martin

29-Jul-03 5:16

--first date
SUBSTRING(1, CHARINDEX('$', variablename))

--second date
SUBSTRING(CHARINDEX('$', variablename) + 1, LEN(variablename) - CHARINDEX('$', variablename))

You may have to adjust by adding/subtracting 1 to get proper results

Re: indexof - wishing for

Mike Dimmick29-Jul-03 13:47

Mike Dimmick

29-Jul-03 13:47

Thought of using a table-valued function?

CREATE FUNCTION dbo.fnGetTwoDates
(
   @searchCriteria varchar(30)
)
RETURNS @twoDates TABLE
   (
      FirstDate     datetime,
      SecondDate    datetime
   )
AS
   BEGIN
 
   INSERT INTO @twoDates
   SELECT
      /* Perform selection here */
   
   RETURN

   END
 
Usage:
SELECT FirstDate, SecondDate
FROM dbo.fnGetTwoDates( 'criteria' )

Now, if only we could pass tables INTO stored procedures as parameters - I'm stuck with using temporary tables with fixed names in order to modularise some very large (>1000 line) procedures.

What's wrong with Query String;

Bo Hunter28-Jul-03 18:55

Bo Hunter

28-Jul-03 18:55

SELECT [ID], Title, NodeType, ParentID, [Language], Description FROM SourceCode WHERE Title LIKE '%Title%' OR Code LIKE '%Title%' AND Language = 1 OR Language = 2 OR Language = 3 OR Language = 4 OR Language = 5 AND NodeType = 1 OR NodeType = 2 OR NodeType = 3 OR NodeType = 4 OR NodeType = 5 OR NodeType = 6 AND ParentID = 4 OR ParentID = 6

Do the numbers have to be quoted?
Any Suggestions?

Thank You
Bo Hunter

Alexander Kojevnikov28-Jul-03 23:20

28-Jul-03 23:20

AND operator has a higher priority than OR. You might want to use parentheses:

SELECT [ID], Title, NodeType, ParentID, [Language], Description 
FROM SourceCode 
WHERE (Title LIKE '%Title%' OR Code LIKE '%Title%') 
AND (Language = 1 OR Language = 2 OR Language = 3 OR Language = 4 OR Language = 5)
AND (NodeType = 1 OR NodeType = 2 OR NodeType = 3 OR NodeType = 4 
OR NodeType = 5 OR NodeType = 6)
AND (ParentID = 4 OR ParentID = 6)

HTH,

Alexandre Kojevnikov
MCAD charter member
Leuven, Belgium

Jon Hulatt29-Jul-03 1:43

Jon Hulatt

29-Jul-03 1:43

Furthermore, you really ought to use "IN":-

SELECT [ID], Title, NodeType, ParentID, [Language], Description 
FROM SourceCode (nolock)
WHERE (Title LIKE '%Title%' OR Code LIKE '%Title%') 
AND Language IN (1,2,3,4,5)
AND NodeType IN (1,2,3,4,5,6)
AND ParentID IN (4,6)

#include <beer.h>

Bo Hunter29-Jul-03 10:21

Bo Hunter

29-Jul-03 10:21

Will the IN operator work in Access?
I forgot to mention that.
And also some of the idetifiers like Language
has to be exscaped. Does that also have to be
exscaped in OleDbParameter like so,
OleDbParameter param = new OleDbParameter( "[Language]", bla bla );
Is this correct?

Thank You
Bo Hunter

Alexander Kojevnikov29-Jul-03 22:07

Read-only Access DB Problem

29-Jul-03 22:07

Bo Hunter wrote:
Will the IN operator work in Access?

Yep, it will.

Bo Hunter wrote:
Does that also have to be
exscaped in OleDbParameter

Probably it has to be escaped. This won't hurt anyway Wink | ;)

Alexandre Kojevnikov
MCAD charter member
Leuven, Belgium

-- NA --28-Jul-03 2:41

28-Jul-03 2:41

Hi all,

I'm having the following problem: my site can access the database perfectly, using an ODBC-less connection, but when I try and insert or edit records I get the error "Cannot update. Database or object is read-only." I have changed all permissions in IIS, and in Windows Explorer but I can't seem to get it working. Suggestions please? Big Grin | :-D

The connection string I'm using:

(driver: Microsoft.Jet.OLEDB.4.0)

var sConnectionString = "Provider=" + sDBDriver + "; ";<br />
sConnectionString += "Data Source=" + Server.MapPath("../../bin/db/db.mdb");

Paul

Tiny problem with said member has been corrected.
Chris Maunder

modified 18-Jul-18 11:59am.

-- NA --28-Jul-03 5:56

28-Jul-03 5:56

Fixed

Paul Wink | ;)

Tiny problem with said member has been corrected.
Chris Maunder

modified 18-Jul-18 11:59am.

Roger Wright28-Jul-03 7:26

Roger Wright

28-Jul-03 7:26

How?

"Welcome to Arizona!
Drive Nice - We're Armed..."
- Proposed Sign at CA/AZ Border

-- NA --28-Jul-03 7:27

28-Jul-03 7:27

I had to give IUSR_<computername> read and write access to the directory containing the Access DB.

HTH Big Grin | :-D

Paul

Tiny problem with said member has been corrected.
Chris Maunder

modified 18-Jul-18 11:59am.

Roger Wright28-Jul-03 7:48

Roger Wright

28-Jul-03 7:48

Ah, of course... I've run into similar fun and games simply hosting files on IIS for public access. I assumed that the Everyone group included IUSR_... but never bothered to look. I still haven't, in fact, but found empirically that adding IUSR access to hosted files suddenly made them accessible from the outside world.

"Welcome to Arizona!
Drive Nice - We're Armed..."
- Proposed Sign at CA/AZ Border

-- NA --28-Jul-03 7:51