Database Discussion Boards

Re: How do I execute a query through an ASP page to update my fields

leppie27-Dec-02 12:20

leppie

27-Dec-02 12:20

I dont know about ASP as such, but SQL aloows you to make Stored Procedures that in turn aloows you to pass parameters (variables) into the statement. The .Net frameworks allows you to use this functionality.

Smile | :)

WebBoxes - Yet another collapsable control, but it relies on a "graphics server" for dynamic pretty rounded corners, cool arrows and unlimited font support.

How to send data from one application to another

Exceter25-Dec-02 17:37

Exceter

25-Dec-02 17:37

1. I have an application (SDI) which works with database
that is, retrieves data and the data are displayed in Edit Boxes and Statics
I want to send these data to another program, MS Word in my case
how to do this ?

2. Is it possible with ADO?

thank you.

Re: How to send data from one application to another

Alexander Kojevnikov26-Dec-02 6:00

Alexander Kojevnikov

26-Dec-02 6:00

You ought to use WinWord's COM API, e.g. create a new Word document, insert a table, and fill it in with the data from your database.

Alexandre Kojevnikov
MCP (SQL2K)
Leuven, Belgium

Count records / ASP

Sarvesvara (BVKS) Dasa24-Dec-02 21:02

Sarvesvara (BVKS) Dasa

24-Dec-02 21:02

I want to count the number of records.... and display it in my web-page using my ASp page. I know that the "Select count(*) from table" will give the count in the database.

How can I retrive the 'result' from that query and pass it on to my ASP page??

Any help?

I was born intelligent
Education ruined me!.

Re: Count records / ASP

Rein Hillmann24-Dec-02 23:04

Rein Hillmann

24-Dec-02 23:04

Off the top of my head it goes something like this (there might be some mistakes since I haven't tested it):

<script language=vbscript runat=server>
Dim ADOConn
Dim ADORs
Dim RecordCount

set ADOConn = CreateObject("ADODB.Connection")
ADOConn.Open(myConnectionString)
set ADORs = ADOConn.Execute("select count(*) as NumRecords from MyTable")

if not ADORs.BOF and not ADORs.EOF then
    RecordCount = ADORs.Fields("NumRecords").Value
end if

ADORs.Close
ADOConn.Close

Response.Write "# Records: " & RecordCount
</script>

Re: Count records / ASP

Tatham26-Dec-02 7:04

Tatham

26-Dec-02 7:04

The fact that you're using a <script runat="server"> tag says that you're using .NET.

Therefore:
1. Use .NET the proper way with code behinds and a literal control
2. Use .NET the proper way with ADO.NET rather than ADO and late binding

Those are where your problems lie - your using legacy code in a new environment.

If you use ADO.NET, to get your record count just go get the value of myDataset.Tables(0).Rows.Count.

Tatham Oddie (VB.NET/C#/ASP.NET/VB6/ASP/JavaScript)
tatham@e-oddie.com
+61 414 275 989

Re: Count records / ASP

Rein Hillmann26-Dec-02 17:43

Rein Hillmann

26-Dec-02 17:43

Actually, you're wrong there.

The <script runat="server"> tag was there long before .NET was developed. It's how script is run server-side in ASP.

Tatham wrote:
Those are where your problems lie - your using legacy code in a new environment.

Since the grandparent post asked for "ASP" and not "ASP.NET", I answered using VBScript which is the de-facto standard for using server side scripting in ASP.

Not everyone is using .NET yet.

Why my SQL Server is so slow?

Wenrich24-Dec-02 18:12

Wenrich

24-Dec-02 18:12

I just installed SQL Server on my machine, and found that it was
really slow! I even had to wait for quite a while when
create a table or open an empty table in the Enterrpise
Manager. Accessing it from a program is also painfully
slow. I got SQL Server SP2 installed as well, but didn't
solve the problem. Any ideas?

Thanks a lot!

Wenrich

Re: Why my SQL Server is so slow?

Nick Seng26-Dec-02 23:10

Nick Seng

26-Dec-02 23:10

What machine/spec are you running it on? Memory, OS, Clock-Speed, Harddisk ( fragmentation) all plays a part on the overall speed.

Notorious SMC

The difference between the almost-right word & the right word is a really large matter - it's the difference between the lightning bug and the Lightning
Mark Twain

Get your facts first, and then you can distort them as much as you please
Mark Twain

Re: Why my SQL Server is so slow?

leppie27-Dec-02 12:25

leppie

27-Dec-02 12:25

Thats quite true what you are saying. I did a small test the other day. First test I set SQl to use all memoty available (384mb in my case). The avg time for an INSERT plus several updates x 100 were 30ms. The 2nd test I dropped SQL's memory to 128mb. The same test yeilded results of 65ms per "operation". It seems SQL likes memory (like most MS products). In a production enviroment I would bother if the SQL server didnt have at least 512mb RAM.

Smile | :)

WebBoxes - Yet another collapsable control, but it relies on a "graphics server" for dynamic pretty rounded corners, cool arrows and unlimited font support.

Re: Why my SQL Server is so slow?

Thomas Lau30-Dec-02 18:28

Thomas Lau

30-Dec-02 18:28

You can try to reinstall. Smile | :)

Count total records

Jassim Rahma23-Dec-02 21:18

Jassim Rahma

23-Dec-02 21:18

what is the easiest way to count the total records in C#? can I have an example please?

Jassim Rahma

Re: Count total records

Mazdak24-Dec-02 1:34

Mazdak

24-Dec-02 1:34

Select count(*) from tablename

This will return one record and its number of records.

Mazy

"And the carpet needs a haircut, and the spotlight looks like a prison break
And the telephone's out of cigarettes, and the balcony is on the make
And the piano has been drinking, the piano has been drinking...not me...not me-Tom Waits

Re: Count total records

dangdinhtien26-Dec-02 2:52

dangdinhtien

26-Dec-02 2:52

Use alias name:
Select Count(*) as total from tablename
Display result:
response.write(rs("total"))

dungnv

Deleting rows in disconnected dataset

Ludwig Stuyck23-Dec-02 7:53

Ludwig Stuyck

23-Dec-02 7:53

Hi all,

I have a disconnected dataset in a remoting smart client application.
If I insert and update rows at client data set, I can send it back to the server, call the Update() command and everything works fine.

However, when I delete a row and send the dataset back to the server, I get a concurrency violation and the database is not modified.

So update and insert work, delete doesn't.

Any ideas why this could be? I lost a whole day looking for a solution but still haven't found it :-/

Kind regards,
Ludwig

Ludwig

Re: Deleting rows in disconnected dataset

Mazdak23-Dec-02 8:08

Mazdak

23-Dec-02 8:08

Use GetChanges() function.

p.s:Do you use Access or SQLServer?

Mazy

"And the carpet needs a haircut, and the spotlight looks like a prison break
And the telephone's out of cigarettes, and the balcony is on the make
And the piano has been drinking, the piano has been drinking...not me...not me-Tom Waits

Re: Deleting rows in disconnected dataset

Ludwig Stuyck23-Dec-02 9:24

Ludwig Stuyck

23-Dec-02 9:24

Hi there,

well, in short, I use the following approach:

- at client side, I have a typed dataset connected to a listbox. If I select an item in the listbox and hit a delete button, that dataset is serialized and sent to the server through the http-channel.

- at server side, it is deserialized and written to the database:

SqlDataAdapter sqlda = new SqlDataAdapter("select * from tbSearch", sqlcon)
SqlCommandBuilder sqlcomb = new SqlCommandBuilder(sqlda);	
sqlda.Update(dsetUserSearches, dsetUserSearches.tbSearch.TableName);
dsetUserSearches.AcceptChanges();

I then send this dataset dsetUserSearches back to the client, where it is merged with the client dataset:

dsetUserSearches.Merge(us, false);

This works with updates and insertions, but it does not when I delete items in the listbox. The strange thing is, that it works the first time, it doesn't the second time, it does the third time, it doesn't the 4th time, etc...

The error I get is 'concurrency violation'.

I'm stuck here....

PS - I'm using sql server

Ludwig

Re: Deleting rows in disconnected dataset

Mazdak23-Dec-02 9:42

Mazdak

23-Dec-02 9:42

Well,I don't know about the way you did it,but I use a function like this to save changes,and it works fine me.May you can make some change to use in server and client side:

private System.Data.DataSet deletedRows;
private System.Data.DataSet updatedRows;
private System.Data.DataSet insertedRows;

private bool save()
{
deletedRows=null;
updatedRows=null;
insertedRows=null;
	
try
{
deletedRows = myDataSet1.GetChanges(System.Data.DataRowState.Deleted);
if(deletedRows != null)
mysqlAdapter.Update(myDataSet1 , "Table1");
updatedRows = myDataSet1.GetChanges(System.Data.DataRowState.Modified);
if(updatedRows !=null)
mysqlAdapter.Update(myDataSet1 , "Table1");
insertedRows = myDataSet1.GetChanges(System.Data.DataRowState.Added);
if(insertedRows !=null)
mysqlAdapter.Update(myDataSet1 , "Table1");   
return true;
}
catch(Exception myE)
{			
MessageBox.Show("Problem!!" + myE.ToString() );
return false;
}		
}

I call this function when I want to make database updated when I use disconnected dataset.

Mazy

"And the carpet needs a haircut, and the spotlight looks like a prison break
And the telephone's out of cigarettes, and the balcony is on the make
And the piano has been drinking, the piano has been drinking...not me...not me-Tom Waits

Re: Deleting rows in disconnected dataset

Ludwig Stuyck23-Dec-02 10:07

Ludwig Stuyck

23-Dec-02 10:07

Just a thought:

maybe it has something to do with the fact that the client dataset rows are different than the database rows. It only occurs after I delete something. Maybe it has something to do with the auto-increment field in my datatable? If I delete the last row in the client dataset, send it to the server and update the database, what happens to the autonumber? On the database, if the last row was number 61, then 61 is gone and the next number would be 62. But is this also the case on client side? If not, maybe this is what causing the concurrency violation.

The idea would be to make sure that client dataset rows are equal to database rows, only question is, how do I do that?

Ludwig

Re: Deleting rows in disconnected dataset

Nick Seng26-Dec-02 23:17

Nick Seng

26-Dec-02 23:17

From my experience with databases, cuncurrency usually occurs when you violate relationship rules. For example, if Table A is a composite table with relationship to Table B, and if a row in Table B is deleted, concurrency error occurs because the values in Table A just became invalid

Also, from what i know, commandbuilder doen't work with datasets with multiple tables

Does that help?

Notorious SMC

Re: Deleting rows in disconnected dataset

lustuyck27-Dec-02 3:47

lustuyck

27-Dec-02 3:47

Well, I found the reason.

When a new row is inserted in my dataset at client side, the autonumber column for that row receives a number that is equal to the previous rownumber + 1, for example 10.

The dataset is then sent to the server, where it is written back to the database. However, the autonumber of the new row in the database is not always the previous autonumber + 1, for example if you previously deleted the last row in the database. In this example, it will be 11.

At this point, the dataset at client side and the database rows are not consistent anymore.

Solution: after the database has been updated, the updated dataset has to be sent back to the client. But first, the autonumber column of the new row has to be adjusted to the real database autonumber (by using the RowUpdated event handler).

At client side, the new row at the original client dataset is first purged; then it is merged with the new dataset coming from the server, and then AcceptChanges() is called.

After this, the client dataset is equal to the database rows, and concurrency violation won't occur.

Eek! | :eek:

It was a day work to find this one Unsure | :~

Re: Deleting rows in disconnected dataset

Nick Seng27-Dec-02 14:21

Nick Seng

27-Dec-02 14:21

lustuyck wrote:
It was a day work to find this one

Well, you know what they say, better a day than a week Poke tongue | ;-P

Glad you found the problem!

Notorious SMC

Paging in ADO.NET

vasily23-Dec-02 0:31

vasily

23-Dec-02 0:31

Hello, I have extreamly huge table and I want to show it in DataGrid. How can I read this table from DB by parts (oracle database and c# client)? I dont want to select each data part from server (select first N-records then next N-records ...).

Re: Paging in ADO.NET

leppie23-Dec-02 6:08

leppie

23-Dec-02 6:08

There is an overloaded method for DataAdapter.Fill() that allows you to select ranges, else (I have just found this) use a PagedDataSource object.

EG (seeing I just seen it in action)

DataView dv = mydataset.News.DefaultView;
dv.Sort = "Date DESC";

PagedDataSource pds = new PagedDataSource();
pds.DataSource = dv;
pds.PageSize = 4;
pds.AllowPaging = true;
   
DataList1.DataSource = pds;
DataList1.DataBind();

WebBoxes - Yet another collapsable control, but it relies on a "graphics server" for dynamic pretty rounded corners, cool arrows and unlimited font support.

Newbie needs help

Benjamin Ng22-Dec-02 16:58

Benjamin Ng

22-Dec-02 16:58

What is Visual C++ .NET's primary role currently? I wanted to do some ADO database with it but I'm at a lost. It seems that there is no built in support or wizards for ADO.
I checked out Amazon but there the books on databases are mainly for VB only. Please help. Confused | :confused:

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