Database

Re: fill an access table with a dataset

14-Mar-04 18:36

Did you tried to use Table Direct for Fill data set

D!shan

pankajdaga14-Mar-04 21:27

pankajdaga

14-Mar-04 21:27

Hi,

I am sorry I do not understand what you mean. Could you please elaborate on it?

My problem is that the dataset is filled from an SQL server database. Now I need to dump the contents of this dataset in an Access table. So, I would like to determine which is the most efficient way to do this. I can of course call an UPDATE statement for each row in the dataset, but I have a feeling that there might be a better way Big Grin | :-D

Thanks,

Pankaj

Without struggle, there is no progress

Re: fill an access table with a dataset

dishanf14-Mar-04 21:39

14-Mar-04 21:39

Hi You can build a multi sql statement and execute it once!. you dont need to execute singel insert / update statements many times.

Ex. sSQL = "INSERT INTO .....;INSERT INTO ...;INSERT.."
executeQuery( sSQL );

HTH.,
D!shan Cool | :cool:

Count (*)

Peter Mayhew14-Mar-04 1:43

Peter Mayhew

14-Mar-04 1:43

I wish to count the number of records in my table and then display the number on the screen

So i done:

SQLQuery = "SELECT COUNT(*) FROM Log"<br />
Set RS = OBJdbConnection.Execute(SQLQuery)<br />
Response.Write(RS.RecordCount)

But i'm getting into a muddle on how to do it.

Can someone please show me the light.!!

---
Peter M

-Dr_X-14-Mar-04 8:15

14-Mar-04 8:15

First of all, does the Log table have an primary key index, preferable an autoincrementing ID. It would be much faster as the log file table size increases to do a 'select count(ID) from log' than doing a count(*). Executing a count(*) may induce a table scan while count(ID) will only scan the primary key index.
2nd, you should probably execute a OBJdbConnection.ExecuteScalar rather than a execute. The executescalar is designed to return a single value which is what you are looking for.

Dim rowCount as object
SQLQuery = "SELECT COUNT(ID) FROM Log"
value = OBJdbConnection.ExecuteScalar(SQLQuery)
if not value is dbnull.value then 
  'Make sure you didn't receive a Null from the database. 
  'If not it should be an int or int32. Can't remember which one is actually returned.
  Response.Write(value.tostring)
end if

The above is not tested but you should get the idea.

Michael

I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)

Peter Mayhew14-Mar-04 9:30

Peter Mayhew

14-Mar-04 9:30

Yes it does have a primary index, which is just a auto number.
Before I recieved your message, I managed to get things working by doing the following

<br />
SQLQuery = "SELECT * FROM Log" <br />
Set RS = OBJdbConnection.Execute(SQLQuery)<br />
<br />
While (Not RS.EOF)<br />
sTotalpages = sTotalpages + 1<br />
RS.MoveNext<br />
Wend

Maybe its a crude way to count the pages.

I'll give your code a try now

---
Peter M

Peter Mayhew14-Mar-04 9:56

Peter Mayhew

14-Mar-04 9:56

Well I tried your code but I get the error

"ADODB.Connection error '800a0bb9'

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.
/Includes/paging.asp, line 12 "

I couldnt' get the tostring to work, so i took it out as well as the error checking, as they will not be blank fields (we are checking the primary after all)

So the code ended up

<br />
dim sTotalpages<br />
SQLQuery = "SELECT COUNT(ID) FROM Log"<br />
sTotalpages = OBJdbConnection.ExecuteScalar(SQLQuery)   'Line 12 - the error point<br />
Response.Write(sTotalpages)

I'm rather confused to why I am having problems, i mean it looks easy enough, there is obviously something I am doing wrong.

Well I'll go back to my old method for now, and have a break, and come back to it later

---
Peter M

-Dr_X-14-Mar-04 10:17

14-Mar-04 10:17

Here this works:

Dim cn As New System.Data.SqlClient.SqlConnection(_connectString)
    Dim value As Object
    Dim cmd As New System.Data.SqlClient.SqlCommand("Select count(SampleID) from tblSamplesSample")
    cmd.Connection = cn
    cn.Open()
    value = cmd.ExecuteScalar
    Response.Write(value)

Change the select statement and add your connect string.

Michael

I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)

-Dr_X-14-Mar-04 10:06

14-Mar-04 10:06

Definitely give it a try. It probably has bugs since it was written on-the-fly but looping through the recordset is definitely an insufficient way of doing it. ExecuteScalar with the count being on the primary key is probably the most efficient way of obtaining the count.

Michael

I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)

Peter Mayhew15-Mar-04 4:48

Peter Mayhew

15-Mar-04 4:48

Tried again, but could not get it to work, I had a feeling that the code you submitted was in ASP.NET, which my friend David Wulff confirmed for me.

My host (oneandone) does not allow me to use .NET, which explains things, i've managed to get things to work now with the following code, incase anyone else reads this in the future

set rs = OBJdbConnection.execute("SELECT count(ID) as total from Log")<br />
sTotalpages = rs("total")

Thanks for your time, its appeciated!

---
Peter M

dishanf14-Mar-04 18:39

14-Mar-04 18:39

SQLQuery = "SELECT COUNT(*) AS 'NoOfRows' FROM Log" and use executeQuery

D!shan

Peter Mayhew15-Mar-04 4:52

Peter Mayhew

15-Mar-04 4:52

Could not get this to work with executeQuery, as mentioned with Dr X, this might be because the code is ADO.NET?

Your all using space age technology compared to me, i'm not on the .NET train yet Eek! | :eek:

Thanks for your time otherwise

---
Peter M

Peter Mayhew15-Mar-04 10:12

Peter Mayhew

15-Mar-04 10:12

OK, now i have working code to count all the records thanks for all your suggestions, that’s all fine now.

The other thing that I wanted to clear up was this, and i'll do my best at explaining!

I wanted to select all the data in a table, then sort in descending order (the primary being a Autonumber called ID), and then select a range of records for paging.

Now i can do the paging fine, the the first record is shown first, and wanted everything to be in reverse order. So my first bit of code works, in ascending order. (ignore the maths bit its used to select ie records 1-5 for page 1 and 6-10 for page 2 ...)

SQLQuery = "SELECT * FROM Log WHERE Log.ID BETWEEN " & (((sCurrentpage-1)*5)+1) & " AND " & (sCurrentpage*5)

This works fine, but I wanted it to have all the records in reverse order, so I thought ah ha, i'll just put in order by:

SQLQuery = "SELECT * FROM Log ORDER BY ID DESC WHERE Log.ID BETWEEN " & (((sCurrentpage-1)*5)+1) & " AND " & (sCurrentpage*5)

But this doesn't work.
If I were to put ORDER BY ID DESC at the end of the SQL statement it will work, but it will only reverse order of the selected records.
But i wanted to reverse order of everything and then select..understand the problem??

Suggestions on a post card.. Please note, I am not able to use anything to do with .NET, so i need .NETless code Blush | :O

..cheers!

---
Peter M

-Dr_X-15-Mar-04 11:49

15-Mar-04 11:49

Order by goes at the end of the statement.

SQLQuery = "SELECT * FROM Log WHERE Log.ID BETWEEN " & (((sCurrentpage-1)*5)+1) & " AND " & (sCurrentpage*5) & " ORDER BY ID DESC"

Michael

I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)

sql statements

pnpfriend12-Mar-04 9:02

pnpfriend

12-Mar-04 9:02

what is the sql statement to get the name of all tables exiting in a database

thank you

Re: sql statements

Henrik Stuart12-Mar-04 9:15

Henrik Stuart

12-Mar-04 9:15

USE db;
GO

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE';

For instance. Presuming, of course, you're talking about MSSQL Server.

--
Henrik Stuart (http://www.unprompted.com/hstuart/)

Re: sql statements

dishanf14-Mar-04 18:42