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.
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.
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>
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:
<code>lngOldBookId = -1
For Each objRec In arrBookList
If lngOldBookId <> objRec.BookId Then
lngOldBookId = objRec.BookId
'write other bits of result table.
So you only print a book title when it is different from the previous record. Hope that helps. Andy
I normalized a table in a database project I'm working on so that instead of having something like this:
ID NAME RELATED_ITEM_ID_1 RELATED_ITEM_ID_2 ..... RELATED_ITEM_ID_40
1 Entity 1124672 Entity 314 NULL 45
I now have something like these two tables:
1 Entity 12 Entity 3
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.
------------------------------------------ The 3 great virtues of a programmer: Laziness, Impatience, and Hubris. --Larry Wall
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.
That's a typical error when you have misspelled one of the column names (e.g. TotalUsers or RecordNumber)
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!
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.
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?
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:
<code>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)</code>
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).
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.
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.