insert stored procedure

Discussion in 'ASP .Net' started by nicholas, Nov 17, 2004.

  1. nicholas

    nicholas Guest

    Could someone tell me how to implement an INSERT on an aspx-page using a
    stored procedure, in VB-code?

    THX,
    Nic

    PS: I have been trying this, but it ain't working:

    Sub insert_new_content (sender As Object, e As EventArgs)

    Dim MyConnectionString as String =
    ConfigurationSettings.AppSettings("ConnectionString")
    Dim myConnection As SQLConnection
    myConnection = New SQLConnection(MyConnectionString)

    Dim myCommand As New SqlCommand("spaddshopscats", myConnection)
    myCommand.CommandType = CommandType.StoredProcedure
    Dim Parameter As SqlParameter

    Parameter = New SqlParameter("@shopID", SqlDbType.Int, 4)
    myCommand.Parameters.Add(Parameter)
    Parameter.Direction = ParameterDirection.Input
    Parameter.Value = ctype(request.querystring("shopID"),integer)

    Parameter = New SqlParameter("@categoryID", SqlDbType.VarChar, 2000)
    myCommand.Parameters.Add(Parameter)
    Parameter.Direction = ParameterDirection.Input
    Parameter.Value = categoryID.text


    myConnection.Open()
    myCommand.ExecuteNonQuery()

    myConnection.Close()

    End Sub
     
    nicholas, Nov 17, 2004
    #1
    1. Advertising

  2. What error are you getting?


    "nicholas" <> wrote in message
    news:...
    > Could someone tell me how to implement an INSERT on an aspx-page using a
    > stored procedure, in VB-code?
    >
    > THX,
    > Nic
    >
    > PS: I have been trying this, but it ain't working:
    >
    > Sub insert_new_content (sender As Object, e As EventArgs)
    >
    > Dim MyConnectionString as String =
    > ConfigurationSettings.AppSettings("ConnectionString")
    > Dim myConnection As SQLConnection
    > myConnection = New SQLConnection(MyConnectionString)
    >
    > Dim myCommand As New SqlCommand("spaddshopscats", myConnection)
    > myCommand.CommandType = CommandType.StoredProcedure
    > Dim Parameter As SqlParameter
    >
    > Parameter = New SqlParameter("@shopID", SqlDbType.Int, 4)
    > myCommand.Parameters.Add(Parameter)
    > Parameter.Direction = ParameterDirection.Input
    > Parameter.Value = ctype(request.querystring("shopID"),integer)
    >
    > Parameter = New SqlParameter("@categoryID", SqlDbType.VarChar, 2000)
    > myCommand.Parameters.Add(Parameter)
    > Parameter.Direction = ParameterDirection.Input
    > Parameter.Value = categoryID.text
    >
    >
    > myConnection.Open()
    > myCommand.ExecuteNonQuery()
    >
    > myConnection.Close()
    >
    > End Sub
    >
    >
     
    Nick Stansbury, Nov 17, 2004
    #2
    1. Advertising

  3. nicholas

    nicholas Guest

    The problem was in my stored procedure: in my second line I had "@categoryID
    varchar (200)" => I removed "(200)" and it works now. But it is still not
    working perfectly:

    This is the stored procedure:
    ---------
    CREATE PROCEDURE spaddshopscats

    @shopID int, @categoryID varchar

    AS

    DECLARE @CatInsert varchar(2000)

    SET @CatInsert = 'INSERT INTO tbl_shopscats (shopID, categoryID) SELECT ' +
    CONVERT(varchar,@shopID) + ', categoryID From tbl_categories Where
    categoryID IN (' + @categoryID + ')'

    exec (@CatInsert)

    Return
    GO
    -----------

    So, in @categoryID there is a string of category ID's separated by comma's
    (ex: 1,25,78)
    The stored procedure should insert for each of these values a new record
    together with the id of the shop.

    So if @shopID = 63

    We should have these records inserted:
    record1: shopID= 63 and categoryID=1
    record2: shopID= 63 and categoryID=25
    record3: shopID= 63 and categoryID=78


    Now, it works for the categoryID = 1 but not for the others.

    THX for your help,
    Nic

    "Nick Stansbury" <> wrote in message
    news:%...
    > What error are you getting?
    >
    >
    > "nicholas" <> wrote in message
    > news:...
    > > Could someone tell me how to implement an INSERT on an aspx-page using a
    > > stored procedure, in VB-code?
    > >
    > > THX,
    > > Nic
    > >
    > > PS: I have been trying this, but it ain't working:
    > >
    > > Sub insert_new_content (sender As Object, e As EventArgs)
    > >
    > > Dim MyConnectionString as String =
    > > ConfigurationSettings.AppSettings("ConnectionString")
    > > Dim myConnection As SQLConnection
    > > myConnection = New SQLConnection(MyConnectionString)
    > >
    > > Dim myCommand As New SqlCommand("spaddshopscats", myConnection)
    > > myCommand.CommandType = CommandType.StoredProcedure
    > > Dim Parameter As SqlParameter
    > >
    > > Parameter = New SqlParameter("@shopID", SqlDbType.Int, 4)
    > > myCommand.Parameters.Add(Parameter)
    > > Parameter.Direction = ParameterDirection.Input
    > > Parameter.Value = ctype(request.querystring("shopID"),integer)
    > >
    > > Parameter = New SqlParameter("@categoryID", SqlDbType.VarChar, 2000)
    > > myCommand.Parameters.Add(Parameter)
    > > Parameter.Direction = ParameterDirection.Input
    > > Parameter.Value = categoryID.text
    > >
    > >
    > > myConnection.Open()
    > > myCommand.ExecuteNonQuery()
    > >
    > > myConnection.Close()
    > >
    > > End Sub
    > >
    > >

    >
    >
     
    nicholas, Nov 17, 2004
    #3
  4. I can't see straight away why your code isn't working.

    However your approach isn't ideal. From a performance perspective you're not
    gaining much by doing this all in one go - from what I've read its opening
    and closing the database connection that has the overhead. I'd adjust like
    this:

    Create Procedure spAddShopsCats
    @ShopId int,
    @CategoryId int
    as

    insert into tbl_shopsCats (ShopID, categoryID) VALUES (@ShopID, @CategoryID)

    Then just exec the procedure once per category rather than try and get the
    stored procedure to parse the string. If you really do want to do it all in
    one go then post DDL (create table script etc.) and I'll have a longer look.



    > CREATE PROCEDURE spaddshopscats
    >
    > @shopID int, @categoryID varchar
    >
    > AS
    >
    > DECLARE @CatInsert varchar(2000)
    >
    > SET @CatInsert = 'INSERT INTO tbl_shopscats (shopID, categoryID) SELECT '

    +
    > CONVERT(varchar,@shopID) + ', categoryID From tbl_categories Where
    > categoryID IN (' + @categoryID + ')'
    >
    > exec (@CatInsert)
    >
    > Return
    > GO
    > -----------
    >
    > So, in @categoryID there is a string of category ID's separated by comma's
    > (ex: 1,25,78)
    > The stored procedure should insert for each of these values a new record
    > together with the id of the shop.
    >
    > So if @shopID = 63
    >
    > We should have these records inserted:
    > record1: shopID= 63 and categoryID=1
    > record2: shopID= 63 and categoryID=25
    > record3: shopID= 63 and categoryID=78
    >
    >
    > Now, it works for the categoryID = 1 but not for the others.
    >
     
    Nick Stansbury, Nov 17, 2004
    #4
  5. nicholas

    nicholas Guest

    Thanks for your help, but I changed my code completely.
    Is better and safer and most important: it works !

    Thanks a lot,
    Nic

    "Nick Stansbury" <> wrote in message
    news:...
    > I can't see straight away why your code isn't working.
    >
    > However your approach isn't ideal. From a performance perspective you're

    not
    > gaining much by doing this all in one go - from what I've read its opening
    > and closing the database connection that has the overhead. I'd adjust like
    > this:
    >
    > Create Procedure spAddShopsCats
    > @ShopId int,
    > @CategoryId int
    > as
    >
    > insert into tbl_shopsCats (ShopID, categoryID) VALUES (@ShopID,

    @CategoryID)
    >
    > Then just exec the procedure once per category rather than try and get the
    > stored procedure to parse the string. If you really do want to do it all

    in
    > one go then post DDL (create table script etc.) and I'll have a longer

    look.
    >
    >
    >
    > > CREATE PROCEDURE spaddshopscats
    > >
    > > @shopID int, @categoryID varchar
    > >
    > > AS
    > >
    > > DECLARE @CatInsert varchar(2000)
    > >
    > > SET @CatInsert = 'INSERT INTO tbl_shopscats (shopID, categoryID) SELECT

    '
    > +
    > > CONVERT(varchar,@shopID) + ', categoryID From tbl_categories Where
    > > categoryID IN (' + @categoryID + ')'
    > >
    > > exec (@CatInsert)
    > >
    > > Return
    > > GO
    > > -----------
    > >
    > > So, in @categoryID there is a string of category ID's separated by

    comma's
    > > (ex: 1,25,78)
    > > The stored procedure should insert for each of these values a new record
    > > together with the id of the shop.
    > >
    > > So if @shopID = 63
    > >
    > > We should have these records inserted:
    > > record1: shopID= 63 and categoryID=1
    > > record2: shopID= 63 and categoryID=25
    > > record3: shopID= 63 and categoryID=78
    > >
    > >
    > > Now, it works for the categoryID = 1 but not for the others.
    > >

    >
    >
     
    nicholas, Nov 17, 2004
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. EmJayEm
    Replies:
    4
    Views:
    4,183
    EmJayEm
    Jan 11, 2005
  2. Mike P
    Replies:
    0
    Views:
    3,374
    Mike P
    Jun 19, 2006
  3. Quinet, Joel
    Replies:
    0
    Views:
    508
    Quinet, Joel
    Aug 8, 2003
  4. Replies:
    3
    Views:
    14,015
    raghav
    Sep 6, 2006
  5. jobs
    Replies:
    0
    Views:
    908
Loading...

Share This Page