Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2003
    Location
    Romania
    Posts
    70

    Unanswered: Stored Procedure + Table data type

    I am a beginner and I try this:
    I try to use a stored Procedure to allow the user to input new data, after that the data will be added into a table. I don’t want to allow the user to add new data directly in the table.

    This is stored procedure:

    Alter Procedure "SP_Test"
    as
    declare @MyTable table (Field_1 int PRIMARY KEY, Field_2 varchar(200))
    select * from @MyTable


    In vb6 one form + ADODC control + DataGridControl

    ADODC1.ConectionString="Provider=SQLOLEDB.1....... ........."
    ADODC1.ComandType=adCmdStoredProc
    ADODC.RecordSet="SP_Test;1"

    If I try to add new record(in the form) : ERROR " Invalid Object Name 'MyDataBaseName .....@MyTable'"

    Sorry for my poor English.
    Thanks

  2. #2
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    A table variable is only accessible withing the SP and will be dropped when the SP exits.

    To add a record to a table call an insert SP with parameters and do the insert within the SP.

  3. #3
    Join Date
    Nov 2003
    Location
    Romania
    Posts
    70
    I want to use a form (in vb6) where the user inputs 20 - 30 records (I want to use Datagrid). I think to use table data type or temporary table (local)

    After that these records (20-30) will be added in a master table (this table have 200.000 - 300.000 records)

    Can I use a table data type + stored procedure + ADO ?
    I am a beginner, an example would be usefull for me.

    Thanks

  4. #4
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    No.
    You would loop through the grid rows and call the insert SP for each row - possibly within a transaction depending on the requirement.

    That's the simplest and most flexible way of doing it - you could also create a disconnected recordset - populate it then connect. Or create a text file and use bulk insert to add the recs to the table.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •