SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Very strange Stored Proceedure problem

    hello,
    i have a stored proceedure that inserts up to 10 entries into my db. Based on if they are not empty.

    Thereby allowing you to insert between 1- 10 from an ASP page.

    HOWEVER, If the user enters only one name it inserts it into the name and nickname columns respectively.

    However if he inserts all 10, in inserts it into the;
    phone goes into nickname
    and nickname goes into phone

    I spent the whole of yersterday changing it all around, and it keeps doign it wrong.

    Please what is wrong here

    Afrika

  2. #2
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is my script below

    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_NULLS ON
    GO






    ALTER PROCEDURE [dbo].[insert_sms_phonebook_1]
    (@username_1 [varchar](20),
    @Phone_1 [varchar](20),
    @Nickname_1 [varchar](50),
    @Nickname_2 [varchar](50),
    @Phone_2 [varchar](20),
    @Nickname_3 [varchar](50),
    @Phone_3 [varchar](20),
    @Nickname_4 [varchar](50),
    @Phone_4 [varchar](20),
    @Nickname_5 [varchar](50),
    @Phone_5 [varchar](20),
    @Nickname_6 [varchar](50),
    @Phone_6 [varchar](20),
    @Nickname_7 [varchar](50),
    @Phone_7 [varchar](20),
    @Nickname_8 [varchar](50),
    @Phone_8 [varchar](20),
    @Nickname_9 [varchar](50),
    @Phone_9 [varchar](20),
    @Nickname_10 [varchar](50),
    @Phone_10 [varchar](20)
    )



    AS
    If NOT exists (select * from sms_phonebook where phone_number = @phone_1 and username = @username_1)
    INSERT INTO [1day_com_sql].[dbo].[sms_phonebook]
    ([username],
    [Phone_number],
    [Nickname])
    select
    @username_1,
    @Phone_1,
    @Nickname_1

    where @phone_1 <> '23480' and @nickname_1 <> '' and @nickname_1 <> '1'and @phone_1 <> '1'
    union all
    select
    @username_1,
    @phone_2,
    @nickname_2
    where @phone_2 <> '23480' and @nickname_2 <> '' and @nickname_2 <> '1' and @phone_2 <> '1'

    union all
    select
    @username_1,
    @phone_3,
    @nickname_3
    where @phone_3 <> '23480' and @nickname_3 <> '' and @nickname_3 <> '1'and @phone_3 <> '1'


    union all
    select
    @username_1,
    @phone_4,
    @nickname_4
    where @phone_4 <> '23480' and @nickname_4 <> '' and @nickname_4 <> '1'and @phone_4 <> '1'


    union all
    select
    @username_1,
    @phone_5,
    @nickname_5
    where @phone_5 <> '23480' and @nickname_5 <> '' and @nickname_5 <> '1'and @phone_5 <> '1'



    union all
    select
    @username_1,
    @phone_6,
    @nickname_6
    where @phone_6 <> '23480' and @nickname_6 <> '' and @nickname_6 <> '1' and @phone_6 <> '1'


    union all
    select
    @username_1,
    @phone_7,
    @nickname_7
    where @phone_7 <> '23480' and @nickname_7 <> '' and @nickname_7 <> '1' and @phone_7 <> '1'


    union all
    select
    @username_1,
    @phone_8,
    @nickname_8
    where @phone_8 <> '23480' and @nickname_8 <> '' and @nickname_8 <> '1' and @phone_8 <> '1'



    union all
    select
    @username_1,
    @phone_9,
    @nickname_9
    where @phone_9 <> '23480' and @nickname_9 <> '' and @nickname_9 <> '1' and @phone_9 <> '1'

    union all
    select
    @username_1,
    @phone_10,
    @nickname_10
    where @phone_10 <> '23480' and @nickname_10 <> '' and @nickname_10 <> '1' and @phone_10 <> '1'



    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO

  3. #3
    SitePoint Member
    Join Date
    Mar 2005
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Check how the parameters are layed out in your stored procedure, they must be in exactly the same order.

    Example,

    sql stored proc:

    CREATE PROCEDURE update_test (@testID,@testname,@testphone) AS UPDATE TEST SET TestName = @testname, TestPhone = @testphone WHERE TestID = @testID

    ADO Code:

    Dim objCmd as new adodb.command
    set objCmd.activeconnection = objActive

    objCmd.Parameters.append objcmd.createparameter("Test_ID") = lTest
    objCmd.Parameters.append objcmd.createparameter("Test_Phone") = txtPhone
    objCmd.Parameters.append objcmd.createparameter("Test_Name") = txtName

    objCmd.execute

    * This code will cause the Phone No to go into the name field, and the name to go into the Phone field.

  4. #4
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks Jakos,
    i just found out tthat the problem was with the SP above,

    if you noticed the parameter formats were not in order. I just corrected them now and testing

    rgds


Bookmarks

Posting Permissions

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