Web Development

General discussion

Gravatar
0 Votes
Locked

Insert using data from form transact sql

By noel ·
I am trying to write a simple query (or so I thought) that takes data from an Access form and inserts it into the database. as far as I can tell my syntax is right but it doen't seem to be aware of the form. The code is

ALTER PROCEDURE dbo.Add_Product
(
@Product_Code char = [Forms]![frm_main]![Product_Code_Text],
@Product_Desc char = [Forms]![frm_main]![Product_Desc_Text],
@Product_Supplier char = [Forms]![frm_main]![Product_Supplier_Combo],
@Product_manufacturer char = [Froms]![frm_main]![Product_Manufacturer_combo],
@Product_Serial char = [Forms]![frm_main]![Product_Serial_Text],
@Product_Cost money = CONVERT(money, [Forms]![frm_main]![Product_Cost_Text])
@Product_Id bigint = 1 + SELECT MAX(Product_ID) FROM dbo.products
)

AS INSERT INTO dbo.Products
(Product_Code, Product_Description, Product_Supplier, Product_Manufacturer, Product_Serial, Product_Cost_Ex_GST, Product_Id)
VALUES (@Product_Code, @Product_Desc, @Product_Supplier, @Product_manufacturer, @Product_Serial , @Product_Cost , @Product_Id)

The error I get when trying to save the proceedure is "ADO error: Line 3: Incorrect syntax near '!'. Line 10: Incorrect syntax near ')'. Must declare the variable 'Product_Code'

Any pointers in the right direction would be great. I am starting to get the feeling I am going to have to do this in VB.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

gravatar
Collapse -

by Jaqui In reply to Insert using data from fo ...

sql syntax
$Product_Code
not
@Product_Code

with sql, the symbol to denote a variable is $ not @

that could be what is causing the problem.

though I'm not up on small business / home user tools from m$, as I only use linux

gravatar
Collapse -

by noel In reply to Insert using data from fo ...

No The syntax is correct for the variables though I have put them in as input parameters not variables. I have tried to DECLARE them as local variables and give them a default value of the value that is input on the corresponding form but it won't compile like that. It would seem that I need to leave them as input parameters and call the Stored proceedure from within VB instead of using a macro and trying to get the Stored proceedure to read directly from the form. I would have preferred to get the proceedure to read directly off the form. Does anyone know if this can be done? Therew are examples in MS Access help that show this but whenever I try it I get compilation errors.

Back to Web Development Forum

Start or search

Related Discussions

Related Forums