Excel Oledb - Database Discussion Boards

RChin22-Feb-05 6:14

22-Feb-05 6:14

Does anyone know of a clever way to display the current rowset index within an SQL statement?
For example:

Executing something like:
SELECT Field1, Field2, CURINDEX() FROM tblA
would return:

Field1 | Field2 | Idx |
-------+--------+-----|
Text 1 | Text 2 | 0   |
Text 3 | Text 4 | 1   |
Text 5 | Text 6 | 2   |
...

So from the above example the 'Idx' field would be the current returned order.

I Dream of Absolute Zero

Re: Current Row Index

Michael Potter22-Feb-05 10:40

Michael Potter

22-Feb-05 10:40

Normally you would create a table (temp or memory) with a new identity field. Fill it then return that table.

If you have a unique field(s) in the query then you can also execute a correlated subquery.

SELECT
    t.Field1,
    t.Field2,
    (Select Count(*) FROM tblA WHERE Field1+Field2 < t.Field1+t.Field2) AS Idx
FROM
    tblA t
ORDER BY
    Field1,
    Field2

On a brighter note, I understand that SQL 2005 will give us a function for this.

Re: Current Row Index

RChin22-Feb-05 22:48

RChin

22-Feb-05 22:48

Thanks for the reply.
I understand that each rowset returned from a query are 'stateless' so I by logic I didn't expect there to be an SQL function for this.
I guess a quick VBA macro using ADO will be the best method to achieve what I want.

I Dream of Absolute Zero

MSDE2000A

caoquanghanh22-Feb-05 0:05

caoquanghanh

22-Feb-05 0:05

hi all !
Now I'm using MSDE2000A, I want to backup (restore) databse. How can i do that ? thank alot

I used command is "osql -S 175.3.2.24 -U sa -P mypass -d dbname"
it showed 1>
continue to type: BACKUP DATABASE mydb TO DISK = 'C:\MSDE\Backup\mydb.bak'
after type GO, enter. Have a error appear:
Cannot open backup device 'C:\MSDE\Backup\mydb.bak'. Device error or device off-line. See the SQL Server error log for more details.

please, help me. Thank

hanhquang

about autherization

Allah Kaa Bandaa21-Feb-05 22:10

Allah Kaa Bandaa

21-Feb-05 22:10

hi all,

I am using following autherization in the web config file using following tags

<authorization>
<allow roles="mydomain\mygroup"/>
<deny users="?" roles="BUILTIN\users"/>
</authorization>

<identity impersonate="true"/>
this works fine. But due to this , i am facing following problem...

when i try to connect to a remote SQL server, where login id and pwd of user is given in the connection string , then i am not allow to connect to the SQL server, with exception "either sql server doesn't exist or access is denied". But when i remove the deny tag from above tags then the authentication works fine. Can some body tell me what's the problem there and how can i overcome this problem. The connection string i am using is following
server=myserver;uid=testuser;pwd=12345;database=mytestdatabase;Integrated security=false

it is to mention that, this user "testuser" has administrators rights on the sql server .

strange problem using ado to add record

Dababa21-Feb-05 15:57

Dababa

21-Feb-05 15:57

I'm using ADO to write a ms access database program, it's very wierd to me that when i add a record into database and calling requery immediately, the count property of record set is wrong, but after a while, several seconds, when i requery again, the count is corrent. I think there is a delay in ado to reflect it.
How to resolve this issue. thanks.

I love C++

Strange problem using OLE DB Consumer Templates

lisoft21-Feb-05 13:56

lisoft

21-Feb-05 13:56

I'm writing a program to access the SQL SERVER using OLE DB Consumer templates. There was a strange problem found when I was trying to modify row data using CRowset::SetData method. Here is some key source code:

CDBPropSet ps(DBPROPSET_ROWSET);
ps.AddProperty(DBPROP_IRowsetChange,true);
ps.AddProperty(DBPROP_UPDATABILITY,DBPROPVAL_UP_CHANGE | DBPROPVAL_UP_INSERT | DBPROPVAL_UP_DELETE);
//...
Ctheclass record; //class Ctheclass :
public Command<CAccessor<CtheAccessor> >
record.Open(session,_T("TABLE_NAME"),&ps)
_tcscpy(record.column1,_T("Data to modify"));
record.SetData();

The problem is that sometime these code runs ok and the row data has been modified as expected but sometime the row data did not changed even the SetData() method returned S_OK.

Can any tell me if there is something wrong with my code or something I was missing.

Lisoft

Excel Oledb

Devius21-Feb-05 13:38

Devius

21-Feb-05 13:38

I am modifiying code that sets up an excel connection string as follows:
Dim sExcelConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=NO;IMEX=1"""
The problem is that when I use a data adapter to fill the data set I get a "Not a legal OleAut date" error because sometimes I am trying to import and older version of an excel file. I don't get an error if the file is the latest version of excel. Basically, I need a way to determine the version of the excel file before I execute this statement. I would appreciate any suggestions.

Thank you

Glen

Glen

Please HELP! Data Source Configuration Wizard

Member 169642421-Feb-05 7:13

Member 1696424

21-Feb-05 7:13

hi. i'm using vs 2005 beta and struggling to set up a project data source

when i try to use the data source configuration wizard, i only have the option to choose a "Web Service" or "Object". i need to be able to add a Local Database, but there is no icon available.

any ideas? do i have an installation issue?

thanks

EXCLUDING DATA USING DATE AS PARAMETER

Brendan Vogt20-Feb-05 19:28

Brendan Vogt

20-Feb-05 19:28

Hi,

I have an archives sectionon my website. It must display all news except the news of the current. So if we work on the current year and month, then all news articles mut display except news articles that were added for February 2005. My logic says that they SQL statement must look somthing like:

SELECT NewsID_PK, NewsSubject, NewsDateAdded
FROM tblNews
WHERE (YEAR(NewsDateAdded) <> 2005 AND MONTH(NewsDateAdded) <> 2)
ORDER BY NewsDateAdded DESC;

But this doesn't display correctly. If I replace the AND with anOR thenit works,like this:

SELECT NewsID_PK, NewsSubject, NewsDateAdded
FROM tblNews
WHERE (YEAR(NewsDateAdded) <> 2005 OR MONTH(NewsDateAdded) <> 2)
ORDER BY NewsDateAdded DESC;

I don't understand why. Can some one please explain why. If there is a better way of writing this statement, please let me know.

Any help and suggestions would be appreciated!!

Thanks
Brendan

Re: EXCLUDING DATA USING DATE AS PARAMETER

Colin Angus Mackay20-Feb-05 19:54

Colin Angus Mackay

20-Feb-05 19:54

Try:

WHERE NewsDateAdded < '2005-02-01'

Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums

Re: EXCLUDING DATA USING DATE AS PARAMETER

Member 174480620-Feb-05 21:17

Member 1744806

20-Feb-05 21:17

The first one seems to be correct. The second one isn't because will eliminate all dates containing 2005 as year and 02 as month

SQL Server Replace Text/Characters

Brendan Vogt20-Feb-05 19:27

Brendan Vogt

20-Feb-05 19:27

Hi,

I have the following 2 questions with which I need help with...

QUESTION 1:
I want to add a news article to a SQL Server 2000 database using classic ASP. I need to know what characters I should search for in the text which may/can display incorrectly.

For example this is what I do when there is an apostrophe in the text:

Replace(Request.Form("txtBody"), "'", "''")

For what other characters must I be aware of when adding to the database table?

QUESTION 2:
When displaying database text to a reader (through a web page) that has line breaks in it,is this the best way to do it? arr(2, 0) is a field in my table called NewsBody.

Replace(arr(2, 0), Chr(10), Chr(10) & "
")

With regards to question 1, what else needs to be taken care of when displaying data from a database table.

Any help and suggestions would be appreciated!!

Thanks
Brendan

Re: SQL Server Replace Text/Characters

Colin Angus Mackay20-Feb-05 19:58

Colin Angus Mackay

20-Feb-05 19:58

Brendan Vogt wrote:
Replace(Request.Form("txtBody"), "'", "''")

This looks like you are not using paramterised queries. This could open your code up to the possibility of a SQL Injection attack if you are not careful. See: SQL Injection Attacks and Some Tips on How to Prevent Them[^]

Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums

Creating New Table and Pump data from ActiveX

Mehbub19-Feb-05 3:27

Mehbub

19-Feb-05 3:27

I want to create a new table in SQL Server, which will populate Date, weeks, months and years. I want the dates starting from 1st January 2004 and finishing on 31st December, 2008. Basically i want to use this table as a mapping, so i know if dates are in between this range this is week 1, if dates are in between this range this is week 2, somthing like this.

I know this is possible in a sql query by writing case statement. But this is a lot of coding.

I was just wondering if someone could tell me how could i create a table like this by using DTS (ActiveX VBScript). Some kind of loop....

The problem is i do not know how to create a new table from ActiveX and insert data into it from ActiveX. Just letting you know that you can create/truncate table by using "Excute SQL Task". But it does not let you to create a workflow in between ActiveX and "Excute SQL Task". I think i got to create the connection in the ActiveX but not sure.

Can someone help, please? I am looking for a table like this :

Date_Time Week_No Month Year
3/1/2005, 09:01:44 PM 1 Jan 2005
4/1/2005, 09:01:44 PM 1 Jan 2005
.....
10/1/2005, 09:01:44 PM 2 Jan 2005
11/1/2005, 09:01:44 PM 2 Jan 2005
...

Thanks
Bob

RowNumber in results.

ABBASI_RA18-Feb-05 23:52

ABBASI_RA

18-Feb-05 23:52

HI How i can create a rownumber column in my view results?
like this sample:

select f1 from table1
go

results:

f1
---
www
aaa
vvv
bbb

I WANT THIS FORM:
rowno f1
---- ---
1 www
2 aaa
3 vvv
4 bbb

thanks

Re: RowNumber in results.

Mike Ellison19-Feb-05 19:53

Mike Ellison

19-Feb-05 19:53

Hi there. It depends on the database you are using - which one?

Re: RowNumber in results.

ABBASI_RA20-Feb-05 1:37

ABBASI_RA

20-Feb-05 1:37

hi I use MS SQL2000 Ent.

Re: RowNumber in results.

Mike Ellison22-Feb-05 9:48

Mike Ellison

22-Feb-05 9:48

Okay. Unfortunately, there isn't a simple way (like Oracle's ROWNUM) to do this in SQL Server 2000. SQL Server 2005 will support a function for this, but in 2000 you have basically two directions you can go. One is to use a temporary table with an identity column - probably in a stored procedure so you can reuse it like you would a view. http://www.sqlteam.com/item.asp?ItemID=1491[^]

Another would be to use a specially constructed auto-join query designed to rank the results.
http://support.microsoft.com/default.aspx?scid=KB;EN-US;q186133[^]

I suppose you could also create a stored procedure that loops through a cursor, but the performance would probably be bad.

Re: RowNumber in results.

SimonS20-Feb-05 1:47

SimonS

20-Feb-05 1:47

You could create a temp table with an autonumber field for the "rowno" and do a INSERT SELECT from your current resultset.

Else, have a look at this [^]which might be of help.

Cheers,
Simon

sig ::
"Don't try to be like Jackie. There is only one Jackie.... Study computers instead.", Jackie Chan on career choices.

article :: animation mechanics in SVG blog:: brokenkeyboards
"Most of us are programmers, but a few use VB", Christian Graus

Re: RowNumber in results.

ABBASI_RA20-Feb-05 2:40

ABBASI_RA

20-Feb-05 2:40

hi i saw that page thank u
BUT
i want to use it in views.
i cant create an extra table
tanks again

Back up database

GoldenStar18-Feb-05 22:56

GoldenStar

18-Feb-05 22:56

Hi all

I have been reading through the SQL Server 2000 database book, and I know that there are 3 types of back-up database: full backup, differential backup and transaction log backup. So which one is the best recommendation?

I'd appreciate for anyone whoi can help me out!

Cheers,

Kelly

Re: Back up database

Colin Angus Mackay19-Feb-05 1:10

Colin Angus Mackay

19-Feb-05 1:10

Kelly Giang wrote:
full backup, differential backup and transaction log backup. So which one is the best recommendation?

It depends on what you want to achieve...

A full backup creates a backup of the whole database

A differential backup creates a backup of just the differences between the last backup and now

A transaction log backup creates a backup of just the transaction log since the last backup.

Restoring a full backup means you can recover to the point of the backup. Restoring a differential backup ontop of that means you can recover to the point of the last differential backup, and a transaction log backup can be used to allow you to restore to an exact point in time or to the point of failure.

You should read the SQL Server 2000 Books-online[^] for more information.

Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums

Re: Back up database

GoldenStar20-Feb-05 10:13

GoldenStar

20-Feb-05 10:13

Thanks a lot Colin!

Cheers,

Kelly

Some help with this SQL statement

Tom Wright18-Feb-05 7:05

Tom Wright

18-Feb-05 7:05

Can someone help me with this SQL statement? I'm wanting a bigint with a datetime stamp similar to:20050211102341780

Here's my SQL statement:

<br />
INSERT INTO TSK2 EXECUTE ('SELECT' ' DATEPART([YEAR], CURRENT_TIMESTAMP), DATEPART([MONTH], CURRENT_TIMESTAMP), DATEPART([DAY], CURRENT_TIMESTAMP), <br />
                      DATEPART([HOUR], CURRENT_TIMESTAMP), DATEPART([MINUTE], CURRENT_TIMESTAMP), DATEPART([SECOND], CURRENT_TIMESTAMP) <br />
                      DATEPART(MILLISECOND, CURRENT_TIMESTAMP)'' AS TSK2_DATE_TIME, 0 AS TSK2_PRIORITY, 341 AS TSK2_ACTION')<br />

Thanks

Tom Wright
tawright915@yahoo.com

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.