problem in inserting a record whose values are of date and time format.

hello,

I am trying to insert date and time into my table.

insert into <table_name> values('12/12/2006','12:23:04');

but it displays error at " ; "

can anyone help me to figure out the problem

Thanks a lot in advance.

Regards,

Sweety

What happens if you remove ";"?

|||

hi,

thx for responding..i figured out the problem and i solved it..

bye

Sweety

Problem in creating new record

hi all,
I am getting a problem when creating a record in my database

when i click on new button to create a record it is throwing a error message:

Fatal error: could not get the next record number
(ODBC call-failed.-3146)
7411-37000[microsoft][Odbc sql server driver] [sql server] server 'xxxx' is not configured for Dataaccess. ODBC. recordset
3146-odbc--callfailed DAO-recordset.

please some one help me.

Shashidhar.Where is this button you speak of?

Problem in Creating AFTER INSERT Trigger

I am trying to create a AFTER INSERT Trigger. I created it and it worked for one record. But when I am trying to input multiple inserts it gives me error even on my insert statement. I am sure the syntax is right. Can anyone confirm this syntax. When I created a small table for testing this syntax worked... I dont know why its not working here.

INSERT INTO media (
media_name,
description,
active_flag,
activity_log,
data_entry_status_code,
category_id,
vendor_id,
created_by,
created_date
)
VALUES
('Test Media20', 'Test Description20', 1, 'Test Log20', 1, -1,-1, -1,'1/1/2001 12:00:00 AM'),
('Test Media21', 'Test Description21', 1, 'Test Log21',1, -1,-1,-1,'1/1/2001 12:00:00 AM'),
('Test Media22', 'Test Description22', 1, 'Test Log22',1, -1,-1,-1,'1/1/2001 12:00:00 AM')It is an insert statement.
Show me trigger text, please and what error messages you have if any.|||Also what is table [media] definition?
I mean show me datatypes of your columns.sql

Problem in checking null record

Hello,

I am using sql server 2000 as my backend.

If there is any null record in my table , I am getting following error:

***********

Conversion from type 'DBNull' to type 'String' is not valid.

**************

to overcome this error I want to check if its null then don't do anything else do something.

I have tried like this:

If rs.fields("ph").value="NULL" then

else

.......

....

...

end if

But this does not solve my problem.

Please help me i anyone can.

Thanks.

If rs.fields("ph") Is DbNull.Value Then
'it's null
Else
'it can be converted to a string
End If

Your solution test to see if the field = "NULL". Because you have NULL in quotes, you are actually testing to see if the field holds a string wiht the letters N U L L in it. The other thing about NULL is that nothing can equal null. Null means "unknown value", so it is impossible to compare one unknown value with another, because, well, they are unknown... The test for null is IsNull.

problem getting the most recent record for a range of clients

I have a fairly complex SP with 3 tables the idea being to display a Region, Client and Event
I want to show only the last event for each client

I have replaced my event table with a View which returns the TOP 1 but all I get is the most recent of all records
I want eg:
Region 1, Client 1, Most recent event
Region 1, Client 2, Most recent event
Region 1, Client 3, Most recent event

Not sure of the structure of your tables, but maybe you're looking for something like this?

SELECT RegionName, ClientName, EventName FROM Regions r INNER JOIN Clients c ON r.ClientID=c.ClientID INNER JOIN Events e ON c.ClientID=e.EventID WHERE EventDate=(SELECT MAX(EventDate) FROM Events WHERE ClientID=c.ClientID)

|||

Just the job Many thanks , I was not thinking right, this is a simplified version, unfortunatly I needed two MAX fields one for Date the other for EventID as the last ID may not be the most recent Date

exmple below gets all events with the most recent date then picks the most recent id from the most recent dates,

SELECT dbo.tblClients.FName, dbo.tblClients.SName, dbo.tblOCompEvents.CompEventID, dbo.tblOCompEvents.StartDate,
dbo.tblOCompEvents.IRef, dbo.tblClients.ClientID, dbo.tblOCompEvents.Status
FROM dbo.tblOCompEvents INNER JOIN
dbo.tblClients ON dbo.tblOCompEvents.ClientID = dbo.tblClients.ClientID
WHERE (dbo.tblOCompEvents.StartDate IN
(SELECT MAX(StartDate) AS sd
FROM dbo.tblOCompEvents AS tblOCompEvents_1
WHERE (ClientID = dbo.tblOCompEvents.ClientID))) AND (dbo.tblOCompEvents.CompEventID IN
(SELECT MAX(CompEventID) AS ceid
FROM dbo.tblOCompEvents AS tblOCompEvents_1
WHERE (ClientID = dbo.tblOCompEvents.ClientID))) AND (dbo.tblOCompEvents.Status = 25)
ORDER BY dbo.tblClients.ClientID

Problem Getting COUNT() to return the value Im after

Hello. I have a database with the following pivot table which has one record linking each employee in the company to the offices they work at. I'm using a pivot table instead of a direct reference because some employees work at more than one office.

Table Def: tblOfficePivot
--------
key <- PK ID
officeLink <- Link to office record
employeeLink <- link to employee record.

I want to write a select I can use to return the total number of offices with an emplyee population between X and Y (eg. 1-250, 251-500, 501-1000, etc.)

I can write a query which return one result for each office that falls into the range of employees I define. This query looks like this:

SELECT COUNT(officeLink) AS theTotal
FROM tblOfficePivot
GROUP BY officeLink
HAVING COUNT(*) BETWEEN 1 AND 250

The above query returns 1 record for each office with between 1 and 250 employees - the result being the employee count. So, if 2 offices fell into this category, A & B, having 50 and 123 employees respectively, the result set would look Like this:

theTotal
---
1. 50
2. 123

What I want instead is the total number of offices, in this case 2.

Is there a way to do this without the COMPUTE clause?

Thanks for any suggestions, this one is driving me crazy.I'd use:SELECT Count(*)
FROM (SELECT COUNT(officeLink) AS theTotal
FROM tblOfficePivot
GROUP BY officeLink
HAVING COUNT(*) BETWEEN 1 AND 250
) AS a-PatP|||Thanks Pat, that worked a charm. Hadn't written a query in that format until now. Definitely something that will come in handy in the future.

Regards, Matt|||What a bright boy

USE Northwind
GO

SELECT Count(*)
FROM (SELECT CustomerId, COUNT(CustomerId)TotalPerId, count(*) AS theTotal
FROM Orders
GROUP BY CustomerId
HAVING COUNT(*) BETWEEN 1 AND 10
) AS a