Monday, March 26, 2012
If date is in range then
here is the code I started:
if {ar_invoices.arih_invoice_date} >= Date(01/01/2005) and {ar_invoices.arih_invoice_date} <= date(01/31/2005)
then
"01/31/2005"
else
if {ar_invoices.arih_invoice_date} >= date(02/01/2005) and {ar_invoices.arih_invoice_date} <= date(01/28/2005)
then
"02/28/2005"
else
if {ar_invoices.arih_invoice_date} >= date(03/01/2005) and {ar_invoices.arih_invoice_date} <= date(03/31/2005)
then
"03/30/2005"
else
if {ar_invoices.arih_invoice_date} >= date(04/01/2005)
then
"Has not been invoiced yet"
I will eventually continue the code, but want to make sure it works first
Let me know if you need more clarification!
Thank you so much!Looks like the date format was incorrect.
for those who may be stuck on the same thing here's the code:
if {ar_invoices.arih_invoice_date} >= Date(2005,1,1) and {ar_invoices.arih_invoice_date} <= date(2005,1,31)
then
"01/31/2005"
else
if {ar_invoices.arih_invoice_date} >= date(2005,2,1) and {ar_invoices.arih_invoice_date} <= date(2005,2,28)
then
"02/28/2005"
else
if {ar_invoices.arih_invoice_date} >= date(2005,3,1) and {ar_invoices.arih_invoice_date} <= date(2005,3,30)
then
"03/30/2005"
else
"Has not been invoiced yet"
Wednesday, March 21, 2012
IDENTITY_INSERT Problem
2.8) in that I cannot insert a specific value to an identity field. (lines
below with >>> are code lines. I am using Python, but the syntax should be
about the same as VBScript)
First, I create an ADO Connection and create my table.
>>> c = win32com.client.Dispatch('ADODB.Connection')
>>> dsn = 'DRIVER=SQL
Server;UID=myID;Trusted_Connection=Yes;Network=DBM SSOCN;APP=Microsoft Data
Access Components;SERVER=SERVER\INSTANCE;"'
>>> c.Open(dsn)
>>> sql = 'CREATE TABLE Table_Name ('
>>> sql += 'ID_Field INTEGER PRIMARY KEY IDENTITY(1,1), '
>>> sql += 'Field_2 nchar(50) NOT NULL, '
>>> sql += 'Field_3 FLOAT DEFAULT 0.0)'
>>> c.Execute(sql)
This works fine. Then, I attempt to allow insertion into the ID_Field.
>>> c.Execute("SET IDENTITY_INSERT Table_Name ON")
This seems to work in that it does not throw an error and gives a return
of -1. Then I open a Recordset
>>> r = win32com.client.Dispatch('ADODB.Recordset')
>>> r.Open('Table_Name', c, 2, 4)
Last, I am attempt to add a record to the recordset with an explicit ID,
>>> r.AddNew()
>>> r.Fields.Item('ID_Field').Value = 45
but this fails with the error of
"Multiple-step OLE DB operation generated errors. Check each OLE DB
status value, if available. No work was done."
Even worse, if I now try to set the identity field to allow inserts again,
Updating() causes an error that I must use an explicit value for ID_Field,
but if I try to give it one, it fails with the above error. I have to
destroy the recordset object at this point to get any further.
I am told that SET IDENTITY_INSERT only remains active for one statement and
thus must be combined with the insert, but I do not know how to do this.
There is a similar sounding bug w/ SQL 7
(http://support.microsoft.com/defaul...b;EN-US;253157), but there
is no indication that it affects newer versions of the DB. Does anyone have
any suggestions or ideas?
Thanks for any help,
-d"drs" <dsavitsk@.remove-and respell-to-send-mail-YAH-HEW.com> wrote in message news:<10gas90gh9n8j44@.corp.supernews.com>...
> Hi, I am having a problem with IDENTITY_INSERT command with MSDE 2000 (ADO
> 2.8) in that I cannot insert a specific value to an identity field. (lines
> below with >>> are code lines. I am using Python, but the syntax should be
> about the same as VBScript)
<snip
I haven't done much ADO programming, so I can't really say much about
the specific error, except to note that this KB article suggests
reviewing the connection string:
http://support.microsoft.com/defaul...kb;EN-US;269495
You might want to try connecting like this instead, to see if it makes
a difference:
c.Provider = 'sqloledb'
dsn = 'Server=MyServer;Database=MyDB;Trusted_Connection= Yes'
c.Open(dsn)
Apart from that, SET IDENTITY_INSERT remains on for your session until
you turn it off, and it can only be on for one table at a time. So I'm
not sure what you mean by putting it together with the INSERT.
One option to consider is to encapsulate your INSERT in a stored
procedure, then call the stored procedure rather than updating the
recordset directly. I don't know how well this fits with what you're
trying to do, but using stored procedures is good practice anyway:
create proc dbo.MyProc
@.ID_Field int,
@.Field_2 nchar(50),
@.Field_3 float
as
set nocount on
begin
set identity_insert dbo.Table_Name on
insert into dbo.Table_Name
(ID_Field, Field_2, Field_3)
values (@.ID_Field, @.Field_2, @.Field_3)
set identity_insert dbo.Table_Name off
end
Simon