Stored Proc

Results 1 to 5 of 5

Thread: Stored Proc

  1. #1
    Dane Krovich Guest

    Default Stored Proc

    Hello<BR><BR>When inserting data into a SQL table with a stored proc with information from a table:<BR><BR>Three of the tables elements are optional and the rest are compulsory.<BR><BR>When a client fills in the form and leaves the optional elements out I get an error:<BR><BR>ADODB.Parameters error &#039;800a0e7c&#039; <BR><BR>Parameter object is improperly defined. Inconsistent or incomplete information was provided. <BR><BR>Is this becuase the stored proc needs a value for those three regardless ? If so how do I get around this without creating a second SP. I tried a If..Then..Else statement on each of the optional parameters, and tried to insert a NULL or "NULL" when those elements where empty but it did not work and had same error...<BR><BR>Any hints on this are appreciated.<BR><BR>TIA<BR>DK

  2. #2
    Join Date
    Dec 1969

    Default Each parameter in the SP

    must be specifically accounted for in ADO. If you post the relevant code, perhaps I can help.<BR>

  3. #3
    Dane Krovich Guest

    Default RE: Each parameter in the SP

    Many thanks<BR><BR>-------------Stored proc code--------<BR>CREATE PROCEDURE sp_lg_add_new_account<BR> @sp_squad_name varchar(50),<BR> @sp_squad_http varchar(250),<BR> @sp_squad_email_1 varchar(100),<BR> @sp_squad_email_2 varchar(100),<BR> @sp_squad_icq_1 varchar(12),<BR> @sp_squad_icq_2 varchar(12),<BR> @sp_squad_contact_1 varchar(50),<BR> @sp_squad_contact_2 varchar(50),<BR> @sp_squad_account_password varchar(25),<BR> @sp_squad_account_login varchar(25),<BR> @sp_squad_status int<BR>AS<BR>BEGIN<BR> INSERT INTO lg_squad_accounts<BR> (<BR> squad_name,<BR> squad_http,<BR> squad_email_1,<BR> squad_email_2,<BR> squad_icq_1,<BR> squad_icq_2,<BR> squad_contact_1,<BR> squad_contact_2,<BR> squad_account_password,<BR> squad_account_login,<BR> squad_status<BR> )<BR> VALUES<BR> (<BR> @sp_squad_name,<BR> @sp_squad_http,<BR> @sp_squad_email_1,<BR> @sp_squad_email_2,<BR> @sp_squad_icq_1,<BR> @sp_squad_icq_2,<BR> @sp_squad_contact_1,<BR> @sp_squad_contact_2,<BR> @sp_squad_account_password,<BR> @sp_squad_account_login,<BR> @sp_squad_status<BR> )<BR>END<BR>----------code end-------------<BR><BR>----------sql table code-------------<BR>CREATE TABLE lg_squad_accounts<BR>(<BR> id_squad INT PRIMARY KEY IDENTITY,<BR> squad_name VARCHAR(50) NULL,<BR> squad_http VARCHAR(250) NULL,<BR> squad_email_1 VARCHAR(100) NULL,<BR> squad_email_2 VARCHAR(100) NULL,<BR> squad_icq_1 INT NULL,<BR> squad_icq_2 INT NULL,<BR> squad_contact_1 VARCHAR(50) NULL,<BR> squad_contact_2 VARCHAR(50) NULL,<BR> squad_account_password VARCHAR(25) NULL,<BR> squad_account_login VARCHAR(25) NULL,<BR> squad_status INT NULL<BR>)<BR>----------code end-------------<BR><BR>----------asp code-------------<BR>..skipped asp code..<BR>Set objCmd = Server.CreateObject("ADODB.Command")<BR> objCmd.ActiveConnection = objConn<BR> objCmd.CommandText = "sp_lg_add_new_account"<BR> objCmd.CommandType = adCmdStoredProc <BR><BR>&#039;--//-- Using stored proc, store form values, these values must be entered in the same <BR>&#039;--//-- order as specified in the stored proc&#039;s code<BR> With objCmd<BR> &#039;.Parameters.Append .CreateParameter("@sp_id_squad", adInteger, adParamInput, 1, 1)<BR> .Parameters.Append .CreateParameter("@sp_squad_name", adVarChar, adParamInput, Len(squad_name), squad_name)<BR> .Parameters.Append .CreateParameter("@sp_squad_http", adVarChar, adParamInput, Len(squad_page), squad_page)<BR> .Parameters.Append .CreateParameter("@sp_squad_email_1", adVarChar, adParamInput, Len(squad_email_1), squad_email_1)<BR> If NOT((squad_email_2 = "") OR (squad_email_2 = " ")) Then<BR> .Parameters.Append .CreateParameter("@sp_squad_email_2", adVarChar, adParamInput, Len(squad_email_2), squad_email_2)<BR> Else<BR> .Parameters.Append .CreateParameter("@sp_squad_email_2", adVarChar, adParamInput, Len(squad_email_2),"NULL")<BR> End If<BR> .Parameters.Append .CreateParameter("@sp_squad_icq_1", adVarChar, adParamInput, Len(squad_icq_1), squad_icq_1)<BR> If NOT((squad_icq_2 = "") OR (squad_icq_2 = " ")) Then<BR> .Parameters.Append .CreateParameter("@sp_squad_icq_2", adVarChar, adParamInput, Len(squad_icq_2), squad_icq_2)<BR> Else<BR> .Parameters.Append .CreateParameter("@sp_squad_icq_2", adVarChar, adParamInput, Len(squad_icq_2),"NULL")<BR> End If<BR> .Parameters.Append .CreateParameter("@sp_squad_contact_1", adVarChar, adParamInput, Len(squad_contact_1), squad_contact_1)<BR> If NOT((squad_contact_2 = "") OR (squad_contact_2 = " ")) Then<BR> .Parameters.Append .CreateParameter("@sp_squad_contact_2", adVarChar, adParamInput, Len(squad_contact_2), squad_contact_2)<BR> Else<BR> .Parameters.Append .CreateParameter("@sp_squad_contact_2", adVarChar, adParamInput, Len(squad_contact_2),"NULL")<BR> End If<BR> .Parameters.Append .CreateParameter("@sp_squad_account_password", adVarChar, adParamInput, Len(squad_account_password), squad_account_password) <BR> .Parameters.Append .CreateParameter("@sp_squad_account_login", adVarChar, adParamInput, Len(squad_account_login), squad_account_login) <BR>&#039;--//-- Becuase it is a new account and has not yet been activated by the user we set this<BR>&#039;--//-- parameter to "false" <BR> .Parameters.Append .CreateParameter("@sp_squad_status", adInteger, adParamInput, 4, squad_status)<BR> End With<BR> <BR> objCmd.Execute<BR>..skipped some more code..<BR>----------code end-------------<BR>

  4. #4
    Dane Krovich Guest

    Default Line error

    The line in the asp code that falls over is:<BR><BR>.Parameters.Append .CreateParameter("@sp_squad_email_2", adVarChar, adParamInput, Len(squad_email_2),"NULL")<BR><BR>located in the param section

  5. #5
    Dane Krovich Guest

    Default RE: Each parameter in the SP

    Sorry for the overload of messages but I only thought now to tell you what I am trying to do with the code given:<BR><BR>I am quite happy to insert a NULL into the optional elements corresponding table cell when those elements are not provided/filled in. The code attempts to do this but does like it for some reason.<BR><BR>Is it becuase NULL is not considered a value ?

Posting Permissions

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