General discussion

  • Creator
    Topic
  • #2175374

    Insert using data from form transact sql

    Locked

    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.

All Comments

  • Author
    Replies
    • #3330874

      Reply To: Insert using data from form transact sql

      by jaqui ·

      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

    • #3329004

      Reply To: Insert using data from form transact sql

      by noel ·

      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.

Viewing 1 reply thread