Join or sign in Register for your free TechRepublic membership or if you are already a member, sign in using your preferred method below. Use Your Email Use FacebookUse Linkedin

Insert using data from form transact sql

Locked

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.

Topic

Reply To: Insert using data from form transact sql

In reply to Insert using data from form transact sql

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

Reply To: Insert using data from form transact sql

In reply to Insert using data from form transact sql

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.

[Author]

Replies