Conversion for Time

I can get my DB to accept my date by doing the following: row.Item("RequestDate") =Me.fullDate.Date --I have fulldate dimensioned as date above. However if I try to do the follwing for a Time it gives me an error when it trys to update the DB the column is set to datetime & when I check the value of the row Item in my command window it says

?row.Item("BeginTime")
#6:00:00 AM# {Date}
[Date]: #6:00:00 AM#

row.Item("BeginTime") =CDate(ddlBegin.SelectedValue & beginAMPM)
row.Item("EndTime") =CDate(ddlEnd.SelectedValue & endAMPM)
The SQL Error I get is the following:

SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details:System.Data.SqlTypes.SqlTypeException: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.
Source Error:

Line 335: row.Item("EndTime") = CDate(ddlEnd.SelectedValue & endAMPM)Line 336: DsVacationData1.RequestData.AddRequestDataRow(row)Line 337: SqlDataAdapter2.Update(DsVacationData1)Line 338: DsVacationData1.AcceptChanges()Line 339: End Sub

Thanks for any help.Ok i figured out what I was doing wrong, to a point. I got it to store my time but it also put in today's date along w/the time is there a way to just enter in the time w/no date?|||There's no Time data type. There's only datetime andsmalldatetime. So the answer to your question, only way is toutilize char, var, nchar, nvar or add in a universal date that will beignored.
|||

The cause of the problem is ANSI SQL NULL is an unknown while .NET NULL is an empty string so the difference is causing the overflow. I found two VB code and a C# link with code. Hope this helps.
http://www.inq.net/WebLog/dbalzer/archive/2005/06/16/125145.aspx

(If SomeDate = DateTime.MinValue Then
cmd.Parameters("@.SomeDate").Value = DBNull.Value
Else
cmd.Parameters("@.SomeDate").Value = SomeDate
End If)
First codeblock

(Public Function chkDateParam(ByVal d As Date, ByRef sqlParam As
SqlParameter)
Try
If d = System.DateTime.MinValue Then
sqlParam.Value = DBNull.Value
Else
sqlParam.Value = d
End If

Catch ex As NullReferenceException

'if the field is blank, str will = null. so set the param to
null too!
sqlParam.Value = DBNull.Value

End Try
End Function )
Second codeblock

sqlsql

Consolidating Rows and Summing Totals

I would like to know if there is an easy way to consolidate lines of the same item but differing quantities.

For example:

ITEM DESCRIPT MFR QTY
-- ---- -- --
ABC TABLE OSH 1
ABC TABLE OSH 3
ABC TABLE OSH 2
ABD CHAIR KMT 2
ABD CHAIR KMT 1
ABE PILLOW SOF 1

I would like to display this information as:

ITEM DESCRIPT MFR QTY
-- ---- -- --
ABC TABLE OSH 6
ABD CHAIR KMT 3
ABE PILLOW SOF 1

Summary: I want to consolidate those items which appear in the table more than once by combining their quantities and leaving one row that has the sum of all.

Your help is greatly appreciated.
TechRickselect ITEM, DESCRIPT, MFR
, sum(QTY)
into newtable
from yourtable
group by ITEM, DESCRIPT, MFR

delete from yourtable

insert into yourtable
select * from temptable

rudy|||Thanks Rudy,

I just tried it but I lose my column header when I use the SUM.
Can I select by column number?

This is the error I recieved:
Server: Msg 8155, Level 16, State 1, Line 70
No column was specified for column 4 of 'temptable'

Thanks again,
TechRick|||sorry, i should have anticipated that

select ITEM, DESCRIPT, MFR
, sum(QTY) as QTY
into newtable
from yourtable
group by ITEM, DESCRIPT, MFR|||Originally posted by r937
sorry, i should have anticipated that

select ITEM, DESCRIPT, MFR
, sum(QTY) as QTY
into newtable
from yourtable
group by ITEM, DESCRIPT, MFR

Rudy,

I've tried repeatedly and in various ways to implement your suggestion and for some reason the rows are not being consolidated. The same information is in both tables.

Is is possible that I need to do a loop and then consolidate like items?

TechRick|||pick a couple of items, let's call them 'itm1' and 'itm2', and run a detail report

select ITEM, DESCRIPT, MFR, QTY
from yourtable
where ITEM in ('itm1','itm2')
order by ITEM, DESCRIPT, MFR

post the results (assuming only a few lines, eh)

now run a summary report

select ITEM, DESCRIPT, MFR, sum(QTY) as QTY
from yourtable
where ITEM in ('itm1','itm2')
group by ITEM, DESCRIPT, MFR

and post the results|||Originally posted by r937
pick a couple of items, let's call them 'itm1' and 'itm2', and run a detail report

select ITEM, DESCRIPT, MFR, QTY
from yourtable
where ITEM in ('itm1','itm2')
order by ITEM, DESCRIPT, MFR

post the results (assuming only a few lines, eh)

now run a summary report

select ITEM, DESCRIPT, MFR, sum(QTY) as QTY
from yourtable
where ITEM in ('itm1','itm2')
group by ITEM, DESCRIPT, MFR

and post the results

Here's what I did. It seems to work.

CODE:
select mfr_sku, title, MFR_name, Q_stk
from A1_TEMPTABLE
where mfr_sku = 'st19171wc'
order by mfr_sku, title, MFR_name

select mfr_sku, title, MFR_name, sum(Q_stk) as Q_stk
from A1_TEMPTABLE
where mfr_sku = 'item1'
group by mfr_sku, title, MFR_name

RESULTS:

ITEM1 9GB 3.5 80PIN SCSI SEA 486
ITEM1 9GB 3.5 80PIN SCSI SEA 431

ITEM1 9GB 3.5 80PIN SCSI SEA 941

I think I might know what I was doing wrong but I'll have to check it to make sure...

Thanks again,
Rick|||Originally posted by r937
sorry, i should have anticipated that

select ITEM, DESCRIPT, MFR
, sum(QTY) as QTY
into newtable
from yourtable
group by ITEM, DESCRIPT, MFR

Sure enough, it was as I thought. I was adding the QTY in the 'group by' statement and that seems to have been causing the problem. I thought that it was required but it wasn't. It's all worked out now -- working like a charm.

Thanks Rudy!

Best Regards,
TechRick

Consolidating Like Items and Summing Totals

I need to know if there is an easy way to consolidate lines of the same item but differing quantities.

For example:

ITEM QTY
-- --
ABC 1
ABC 3
ABC 6
ABD 2
ABD 1
ABE 1

I would like to display this information as:

ITEM QTY
-- --
ABC 10
ABD 3
ABE 1

Summary: I want to consolidate those items which appear in the table more than once by combining their quantities and leaving one row that has the sum of all.

Your help is greatly appreciated.
TechRickselecting the totals is easy, right?

select ITEM, SUM(QTY)
from yourtable
group by ITEM

however, consolidating them and "leaving one row" is tricky

i suggest writing the total rows to a temporary table, deleting all rows from the original, then inserting the total rows back

create table temptable
( ITEM char(3)
, QTY integer )

insert into temptable
select ITEM, SUM(QTY)
from yourtable
group by ITEM

delete from your table

insert into yourtable
select * from temptable

drop temptable

rudy
http://rudy.ca/|||Thanks for the help. I was able to get it worked out with your suggestions.

Best Regards,
TechRick