CODEWITHSUNDEEP
sqlsql-servervbastored-procedures
Martijn Dekkers
Martijn Dekkers

Reputation: 55

Cannot find the Nvarchar to Int conversion in my code

I am currently making a project for school where I have to make a program where I can create orders in. When I try to create orders I get this error returned to me.

Error converting Data Type Nvarchar to Int.

In advance I'm sorry if this is a really basic question that is easely spotted. Me and my project members are beginning programmers but we simply cannot spot where this goes wrong.

The code we execute in vba is this :

Set rs = DbConn.Execute("EXEC spOrderPlaatsen '" & Me.tbTijd & "', " & Me.klantnr & ", '" & Me.tbOphaaldatum & "', '" & Me.tbAfleverdatum & "', '" & Me.tbOphaaladres & "', " & Me.tbPalletnr & ", " & Me.tbAantalpallets & "")

This code execute's our Stored procedure. The stored procedure looks like this.

CREATE PROCEDURE spOrderPlaatsen
(  
@tijd               time(0),
@klantnr            integer,
@ophaaldatum        date,
@afleverdatum       date,

@palletnr   integer,
@aantal     integer,
@Adres      Varchar(255)

) 
AS 

BEGIN TRANSACTION 

DECLARE @ordernr int
DECLARE @besteldatum date

set @besteldatum = getdate()

SELECT @ordernr = MAX(o.ordernr) + 1 
FROM orders o 

insert into orders values (@ordernr, @besteldatum, @tijd, @klantnr, @ophaaldatum, @afleverdatum, @adres)
insert into orderregel values (@ordernr, @palletnr, @aantal)

IF @@ERROR <> 0 
BEGIN 

ROLLBACK 

RAISERROR ('Error tijdens het plaatsen van order.', 16, 1) 
RETURN 
END 

COMMIT
go

Somehow we get a conversion error that we cannot find.

Could any1 figure this out for us? It would be greatly appreciated

Upvotes: 0

Views: 66

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269843

There are multiple places where this could be going wrong. Here is some advice:

  1. Execute the stored procedure with explicit parameters (see here for an example). Do not ever just dump the parameters into the query string.
  2. Always list the columns for an insert. So, this should be insert into orders(col1, col2, col3, . . .) for whatever columns you are including.
  3. Use identity so ordernr is calculated automatically by the database. You can get the value using an OUTPUT clause in the INSERT.
  4. Similarly, you might want besteldatum to default to the current date -- or not. I'm not sure what this column really is, but a default would be appropriate for a create date column.
  5. Surround the entire body of the stored procedure in its own BEGIN/END block (don't depend on the BEGIN TRANSACTION.

Upvotes: 2

Related Questions