datetime INSERT INTO with sp

  • Thread starter Thread starter warway
  • Start date Start date
W

warway

My Access2000 form happily inserts a new record into a SQL2000 table using a
stored procedure apart from an unbound text field containing a formatted
date. I beleive that I have tried every variation, text, short date, long
date etc on the field, in the code behind the form Cstr, CDate, Cvar etc.
The field in the table is datetime, i've tried smalldatetime.

I either get a Type mismatch error or it cannot use the / in the data to
enter into the table.

cn.Execute ("Execute sp_InvoiceCreate " + CStr(Me.ShipmentID) + "," +
CStr(Me.cboInvoice.Column(0)) + "," + CStr(Me.GrossTotal) + "," +
CStr(Me.NetTotal) + "," + CStr(Me.VATTotal) + "," + CVar(Me.txtROE) + "," +
CVar(Me.cboCurrencyActualOut.Column(0)) + "," + CVar(Me.txtInvoiceDate))

Alter Procedure dbo.sp_InvoiceCreate (@Shipment int,@Company
int,@GrossAmount float,@NettAmount float,@Tax float,@ROE float,@Currency
int,@InvDate text)
AS
INSERT INTO tblInvoices
(ShipmentID,CompanyID,GrossAmount,NettAmount,Tax,ROE,CurrencyID,InvDate)
VALUES
(@Shipment,@Company,@GrossAmount,@NettAmount,@Tax,@ROE,@Currency,@Invdate)


Gives type mismatch in execution


cn.Execute ("Execute sp_InvoiceCreate " + CStr(Me.ShipmentID) + "," +
CStr(Me.cboInvoice.Column(0)) + "," + CStr(Me.GrossTotal) + "," +
CStr(Me.NetTotal) + "," + CStr(Me.VATTotal) + "," + CVar(Me.txtROE) + "," +
CVar(Me.cboCurrencyActualOut.Column(0)) + "," + CDate(Me.txtInvoiceDate))

Alter Procedure dbo.sp_InvoiceCreate (@Shipment int,@Company
int,@GrossAmount float,@NettAmount float,@Tax float,@ROE float,@Currency
int,@InvDate datetime)
AS
INSERT INTO tblInvoices
(ShipmentID,CompanyID,GrossAmount,NettAmount,Tax,ROE,CurrencyID,InvDate)
VALUES
(@Shipment,@Company,@GrossAmount,@NettAmount,@Tax,@ROE,@Currency,@Invdate)

Gives type mismatch in execution

Any ideas?

TIA

Andrew
(e-mail address removed)
 
Since @InvDate has been declared as text, you need to have quotes around it.

cn.Execute ("Execute sp_InvoiceCreate " + CStr(Me.ShipmentID) + "," +
CStr(Me.cboInvoice.Column(0)) + "," + CStr(Me.GrossTotal) + "," +
CStr(Me.NetTotal) + "," + CStr(Me.VATTotal) + "," + CVar(Me.txtROE) + "," +
CVar(Me.cboCurrencyActualOut.Column(0)) + ",'" + CVar(Me.txtInvoiceDate) &
"'")

To make it clearer, the last line above is
 
Sorry about that: the clarification I said I was going to include didn't
make it!

CVar(Me.cboCurrencyActualOut.Column(0)) & ",' " & CVar(Me.txtInvoiceDate) &
" ' ")

Alternatively, you could use

CVar(Me.cboCurrencyActualOut.Column(0)) & "," & Chr$(39) +
CVar(Me.txtInvoiceDate) & Chr$(39))

Note that you should be using & as the concatenation symbol in VBA, not +.
 
Back
Top