Re: Grouping data from SQL in ASP - Database Discussion Boards

Does Adapter.GetData read all data? (noob)

peterchen21-Feb-07 10:23

21-Feb-07 10:23

If I call GetData() on an System.Data.OleDb.OleDbAdapter, does that actually read all data from the database?
Or does it just open the rowset, and the data is read only when I access the actual rows?

Developers, Developers, Developers, Developers, Developers, Developers, Velopers, Develprs, Developers!
We are a big screwed up dysfunctional psychotic happy family - some more screwed up, others more happy, but everybody's psychotic joint venture definition of CP
Linkify!|Fold With Us!

Re: Does Adapter.GetData read all data? (noob)

Mark J. Miller22-Feb-07 3:26

Mark J. Miller

22-Feb-07 3:26

System.Data.OleDb.OleDbAdapter doesn't exist, but System.Data.OleDb.OleDbDataAdapter does. But it doesn't have a GetData() method, it has a fill method which loads a disconnected dataset or datatable will all the results of the query and then disconnects (if the connection was closed before Fill() was called.

Conditional constraints

Brady Kelly21-Feb-07 8:06

Brady Kelly

21-Feb-07 8:06

Is it possible to have a constraint that depends on a column value? For instance, I would like my DocUpdate table to require NewDocStatus is not null when IsStatusUpdate = 1, but allow NewDocStatus to be null in other records.

Re: Conditional constraints

Mark J. Miller22-Feb-07 3:19

Mark J. Miller

22-Feb-07 3:19

Using a check constraint you can do the following:

<br />
CREATE TABLE myChkTable (IsStatusUpdate BIT, NewDocStatus VARCHAR(25))<br />
go<br />
<br />
ALTER TABLE myChkTable ADD CONSTRAINT chkTblConstraint CHECK (<br />
	(IsStatusUpdate = 1 AND NewDocStatus IS NOT NULL) OR IsStatusUpdate = 0<br />
)<br />
go<br />
<br />
--the first three will succeed<br />
INSERT INTO myChkTable VALUES (0, null)<br />
INSERT INTO myChkTable VALUES (1, 'status')<br />
INSERT INTO myChkTable VALUES (0, 'not update')<br />
<br />
--this will fail<br />
INSERT INTO myChkTable VALUES (1, null)<br />
<br />

MRD

Ryno Burger21-Feb-07 6:43

Ryno Burger

21-Feb-07 6:43

I don't mean to sound stupid.

But what does the term "MRD" stands for in SQL Server?

Does anybody know?

Thanks.

R

Grouping data from SQL in ASP

JennGom21-Feb-07 6:16

JennGom

21-Feb-07 6:16

Hi,

For the life of me, I haven't been able to figure the following out and I hoping someone out there can offer some good advice.

I have an ASP page that lists a lot of manuals and their associated new features for that particular release.
For example:
Manual title New Feature
manual 1 new feature1
manual 1 new feature2
manual 1 new feature3
manual 2 new feature1
manual 3 new feature1
manual 3 new feature2

Rather than have the same manual listed consecutively with a different new feature association, I would like to list the results as such:

Manual title New Feature
manual 1 new feature1
new feature2
new feature3
manual 2 new feature1
manual 3 new feature1
new feature2

Is this possible using ASP and SQL?

Here's the stored procedure:
<code>CREATE PROCEDURE [docadmin1].[sp_view_nfprodrel]
@product nvarchar (250)

AS
SELECT distinct top 100 Percent doc_id, title, relnum, substring(dn,3,14) as DN, product, New_Feature FROM nf_prodrel_man WHERE product = @product order by Title
GO</code>

Here's the view being called from the stored procedure:

<code>CREATE VIEW dbo.nf_prodrel_man
AS
SELECT TOP 100 PERCENT dbo.Books.Title + ' ' + dbo.Books.Rel_Num AS title, dbo.PROD_REL.Product, dbo.DOC_RELATIONSHIP.Doc_ID,
dbo.New_Feature.NF_ID, SUBSTRING(dbo.New_Feature.New_Feature, PATINDEX('% %', dbo.New_Feature.New_Feature),
LEN(dbo.New_Feature.New_Feature)) AS New_Feature, dbo.Books.DN, dbo.New_Feature.Description, RIGHT(dbo.PROD_REL.Product, 3)
AS RELNUM
FROM dbo.PROD_REL INNER JOIN
dbo.NF_PROD_REL ON dbo.PROD_REL.Prod_Rel_ID = dbo.NF_PROD_REL.Prod_REL_ID INNER JOIN
dbo.New_Feature ON dbo.NF_PROD_REL.NF_ID = dbo.New_Feature.NF_ID INNER JOIN
dbo.DOC_RELATIONSHIP ON dbo.NF_PROD_REL.REL_ID = dbo.DOC_RELATIONSHIP.REL_ID INNER JOIN
dbo.Books ON dbo.DOC_RELATIONSHIP.Doc_ID = dbo.Books.Doc_ID
ORDER BY dbo.Books.Title + ' ' + dbo.Books.Rel_Num, dbo.PROD_REL.Product</code>

Thanks in advance!

Regards,
Jenn

Re: Grouping data from SQL in ASP

andyharman21-Feb-07 8:15

andyharman

21-Feb-07 8:15

It is possible to do this in SQL, but it is much easier to acheive in the ASP page (especially as it is a presentation thing). The structure would look a bit like:

lngOldBookId = -1
For Each objRec In arrBookList
  Response.Write("<tr><td>")
  If lngOldBookId <> objRec.BookId Then
    Response.Write(objRec.Title)
    lngOldBookId = objRec.BookId
  Else
    Response.Write("&nbsp;")
  End If
  'write other bits of result table.
Next

So you only print a book title when it is different from the previous record.
Hope that helps.
Andy

Re: Grouping data from SQL in ASP

JennGom21-Feb-07 9:27

JennGom

21-Feb-07 9:27

Thanks Andy. I will give a try and let you know how I fare.

Have a great day,
Jenn

Retain Benefits of Non-Normalized Table

perlmunger21-Feb-07 4:54

perlmunger

21-Feb-07 4:54

I normalized a table in a database project I'm working on so that instead of having something like this:

Table: entities
-----------------
ID            NAME           RELATED_ITEM_ID_1        RELATED_ITEM_ID_2 ..... RELATED_ITEM_ID_40
1             Entity 1              1                          24                  67
2             Entity 3              14                         NULL                45

I now have something like these two tables:

Table: entities
-----------------
ID            NAME     
1             Entity 1 
2             Entity 3 

Table: entity_items
-----------------
ENTITY_ID        RELATED_ITEM_ID
1                       1
1                       24
1                       67
2                       14
2                       45

I want to return a table that has a summary of the entities table returning the top three entity_items so that it will come back looking like the original (non-normalized) entities table.

I am using a MySQL database, but any help with the basic concepts and SQL here would be appreciated. I'm sure this is a common problem people deal with, but I didn't even know the terminology to use to find an answer on the web.

Thanks.

-Matt

------------------------------------------
The 3 great virtues of a programmer:
Laziness, Impatience, and Hubris.
--Larry Wall

Re: Retain Benefits of Non-Normalized Table

Mark J. Miller22-Feb-07 3:00

Mark J. Miller

22-Feb-07 3:00

I've never used mySQL so I don't have a solution, but you are probably want to search for 'Pivot Table' or 'Crosstab Query'. Here's one link I pulled up really quick searching for 'mySql Crosstab Query':
http://rpbouman.blogspot.com/2005/10/creating-crosstabs-in-mysql.html[^]

Re: Retain Benefits of Non-Normalized Table

perlmunger22-Feb-07 4:50

perlmunger

22-Feb-07 4:50

Thank you. That was helpful.

-Matt

------------------------------------------
The 3 great virtues of a programmer:
Laziness, Impatience, and Hubris.
--Larry Wall

What is wrong with my SQL string?

JUNEYT21-Feb-07 3:16

JUNEYT

21-Feb-07 3:16

Hello!

I can't execue the following sql string because I am getting an error message. How can I increment the specific field in the table by one?

SQLSTR = "UPDATE setup SET TotalUser=TotalUser + 1 WHERE RecordNumber = 1"

Thanks Sigh | :sigh:

Journey

Re: What is wrong with my SQL string?

Colin Angus Mackay21-Feb-07 3:19

Colin Angus Mackay

21-Feb-07 3:19

B Journey wrote:
I can't execue the following sql string because I am getting an error message.

What is the error message?

Upcoming events:
* Glasgow: Geek Dinner (5th March)
* Edinburgh: Web Security Conference Day for Windows Developers (12th April)

My: Website | Blog | Photos

Re: What is wrong with my SQL string?

JUNEYT21-Feb-07 3:36

JUNEYT

21-Feb-07 3:36

SQLSTR = "UPDATE setup SET TotalUser=TotalUser + 1 WHERE RecordNumber = 1"
Call Initialize_Connection()
Call Execute_Statement(SQLSTR) ' the error occurs in this procedure

the procedure content is here:

Sub Execute_Statement(GetSQL)
Set ObjectRecord = Server.CreateObject ("ADODB.Recordset")

ObjectRecord.CursorLocation = adUseServer
ObjectRecord.CursorType = adOpenkeyset
ObjectRecord.LockType = adLockOptimistic
ObjectRecord.Open GetSQL, ObjectConnection,,, adCmdText 'error points this line

End Sub

Thanks

Re: What is wrong with my SQL string?

Colin Angus Mackay21-Feb-07 3:55

Colin Angus Mackay

21-Feb-07 3:55

Okay - so at least we now know WHERE the error is. You still have not told us WHAT the error is.

What is the error message?

Upcoming events:
* Glasgow: Geek Dinner (5th March)
* Edinburgh: Web Security Conference Day for Windows Developers (12th April)

My: Website | Blog | Photos

Re: What is wrong with my SQL string?

JUNEYT21-Feb-07 4:31

JUNEYT

21-Feb-07 4:31

Sorry! Here is the error message. I have double checked all fields in the table nothing looks wrong. It is working on my local server but when I transfer it to server in Internet, I am getting this error message.

Microsoft JET Database Engine error '80040e10'

No value given for one or more required parameters.

/emlak/include/scripts.inc, line 138

I have included the procedure in my previous message where the error is pointing the line 138.

Thanks

Re: What is wrong with my SQL string?

peterchen21-Feb-07 11:51

peterchen

21-Feb-07 11:51

That's a typical error when you have misspelled one of the column names (e.g. TotalUsers or RecordNumber)

Re: What is wrong with my SQL string?

Pete O'Hanlon21-Feb-07 4:46

Pete O'Hanlon

21-Feb-07 4:46

Try using an ADODB.Command object instead of a recordset. You are performing an update.

the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer

Deja View - the feeling that you've seen this post before.

Re: What is wrong with my SQL string?

kubben21-Feb-07 3:27

kubben

21-Feb-07 3:27

That update should work. I would guess that either you don't have rights to update the setup table. Or you need to better qualify the setup table. Perhaps there is a dbo.setup table and a username.setup table. It is always a good idea to qualify your table names. If it isn't that most likely it is a security thing, the user that is running the update doesn't have update rights on that table. If you provided the error message we might be able to pin point the issue.

Hope that helps.
Ben

Update Advice - Getting most recent value

Brady Kelly21-Feb-07 2:15

Brady Kelly

21-Feb-07 2:15

Please refresh my rusty SQL skills. I have a DocMaster table with Status and StatusTime fields, and I have a DocUpdate table with UpdateTime and DocStatus fields. The DocUpdate table stores a history of all status updates for a document, and the DocStatus and StatusTime fields in the DocMaster table are redundant copies of the UpdateTime and DocStatus fields for the most recent DocUpdate record for a given document. How do I update the DocMaster table to correct the values in these fields?

Re: Update Advice - Getting most recent value

andyharman21-Feb-07 2:25

andyharman

21-Feb-07 2:25

Hi Brad

Normally you would use a trigger to keep the two tables synchronised. If the tables are currently out of sync then the following should fix the master table:

update DocMaster set
   DocStatus = A.DocStatus,
   UpdateTime = A.UpdateTime
  from DocUpdate A
  where A.DocId = DocMaster.DocId
  and A.UpdateTime = (select max(UpdateTime) from DocUpdate
     where DocUpdate.DocId = DocMaster.DocId)

This assumes that the primary key on DocMaster is named "DocId", and that the "UpdateTime" is unique for each document (if you have an identity column on DocUpdate then you should use that instead).

Hope that helps.
Andy

Re: Update Advice - Getting most recent value

Brady Kelly21-Feb-07 2:58

Brady Kelly

21-Feb-07 2:58

That's perfect, thanks Andy. I do have triggers doing the normal sync work, but whenever I create redundant or derived fields like these I always start with an update procedure that makes everything all good again, if something goes wrong.

some question

Imran Khan Pathan21-Feb-07 1:04

Imran Khan Pathan

21-Feb-07 1:04

Hi friends

What is a clustered index? How many records can take clustered index in sql server?
what is a cursor?
what is a sql injuction?

thanks in advance

khan

Re: some question

andyharman21-Feb-07 2:38

andyharman

21-Feb-07 2:38

imrankhanpathan wrote:
What is a clustered index?

You will find the answer in SQL-Server's "Books Online". A table can only have one clustered index defined (unless told otherwise SQL-Server will use a clustered index for primary keys). There is no practical limit on the number of records that can be held in a clustered index.

imrankhanpathan wrote:
what is a cursor?

Again the answer is in "Books Online".

imrankhanpathan wrote:
what is a sql injuction?

You can find out about "SQL Injection" here[^]

Average of all columns

Sam Heller20-Feb-07 11:11

Sam Heller

20-Feb-07 11:11

I have a single query that selects a product id. Everytime is finds a product id it also selects several other queries to build up a row of statistics. Therefore my query retrieves rows as follows:

Product ID | Stat Number One | Stat Number Two | More Stats
125 | 76% | 45% |

What I want is a final overall score at the end of the query in a final column. Built on the fly from the other columns. How can I do this?

Eg
(StatNumberOne + Stat Number Two + Rest of Stat Columns / Amount of Columns) As Overall Score

I don't know how to reference these columns on the fly like this ??

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