Solved

sql asp

Posted on 2007-12-04
8
221 Views
Last Modified: 2010-03-20
I have the following sql with the parameter value: 16, 17, 19, 20, 25, 30, 32, 33, 34
but I got the error message when I called by asp.
'Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Procedure or Function 'up_TransmitConfirmation' expects parameter '@transactionstring', which was not supplied.'

The code is working when I put parameter value 16 but when I put all of the together, it does not work and show above error message.
Can you help me?



create PROCEDURE dbo.up_TransmitConfirmation
 (
      @transactionstring nvarchar(255)
      )
AS
      /* SET NOCOUNT ON */
      SELECT     tbltransactions.transactionid, tbltransactions.batchnumber, tbltransactions.partno, tbltransactions.transactionprice, tbltransactions.transactionstatusid,
                      tbltransactions.datecreated, tbltransactions.memberid, tbltransactions.remark, tbltransactions.colorid, tbltransactions.quantity,
                      tbltransactions.manufacturerid, tbltransactions.producttypeid, tbltransactions.transactionid AS Expr1, tblcolor.color,
                      tblmanfacturers.manufacturercompany, tblmembers.firstname, tblmembers.lastname, tblmembers.middlename, tblproducttypes.producttypedesc
FROM         tbltransactions INNER JOIN
                      tblcolor ON tbltransactions.colorid = tblcolor.colorid INNER JOIN
                      tblmanfacturers ON tbltransactions.manufacturerid = tblmanfacturers.manufacturerid INNER JOIN
                      tblmembers ON tbltransactions.memberid = tblmembers.memberid INNER JOIN
                      tblproducttypes ON tbltransactions.producttypeid = tblproducttypes.producttypeid
WHERE     (tbltransactions.transactionid IN (@transactionstring))
      
      RETURN
0
Comment
Question by:riskyricky1972
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 25

Expert Comment

by:imitchie
ID: 20409616
Can you please show your exec statement?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20409617
>WHERE     (tbltransactions.transactionid IN (@transactionstring))
with this as parameter
@transactionstring nvarchar(255)

will not return the expected results.
you will need to change the WHERE into this:
>WHERE     (tbltransactions.transactionid IN (SELECT value FROM dbo.ParmsToList(@transactionstring)))

after having added the following function:
CREATE FUNCTION dbo.ParmsToList (@Parameters varchar(500))
returns @result TABLE (Value varchar(30))
AS  
begin
     DECLARE @TempList table
          (
          Value varchar(30)
          ) 
     DECLARE @Value varchar(30), @Pos int 
     SET @Parameters = LTRIM(RTRIM(@Parameters))+ ','
     SET @Pos = CHARINDEX(',', @Parameters, 1) 
     IF REPLACE(@Parameters, ',', '') <> ''
     BEGIN
          WHILE @Pos > 0
          BEGIN
               SET @Value = LTRIM(RTRIM(LEFT(@Parameters, @Pos - 1)))
               IF @Value <> ''
               BEGIN
                    INSERT INTO @TempList (Value) VALUES (@Value) --Use Appropriate conversion
               END
               SET @Parameters = RIGHT(@Parameters, LEN(@Parameters) - @Pos)
               SET @Pos = CHARINDEX(',', @Parameters, 1) 
          END
     END    
     INSERT @result
     SELECT value
        FROM @TempList
     RETURN
END  

Open in new window

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20409621
now, can you show the code that runs this procedure, anyhow? you will need a quote around it:
exec up_TransmitConfirmation '16,17,19,20,25,30,32,33,34'

0
 
LVL 25

Expert Comment

by:imitchie
ID: 20409624
See: http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_21658716.html
Please change your WHERE statement as shown after the function in the code box below
This will work if you're using SQL Server 2000/2005/2008
CREATE   function F_QuickTable_From_List

      ( @StringList varchar(8000),@Delimiter varchar(10)=',' )

RETURNS @table_var TABLE

      (       Position int identity(1,1) ,       ListItem varchar(8000)      )

with schemabinding

AS

BEGIN

      declare @TempString varchar(8000) ; set @TempString = @StringList

      declare @commaposition smallint

      declare @v varchar(8000)

      

      while len(@TempString) > 0

            begin

                  set @commaposition = charindex(@Delimiter,@TempString)

                  if @commaposition > 0

                        begin

                              set @v = ltrim(rtrim(left(@TempString,@commaposition-len(@Delimiter))))

                              set @TempString = substring(@TempString,@commaposition+len(@Delimiter),8000)

                        end

                  else

                        begin

                              set @v = ltrim(rtrim(@TempString))

                              set @TempString = ''

                        end

                  INSERT INTO @table_var (ListItem)values (@v)

            END

      return

END

GO
 

WHERE     (tbltransactions.transactionid IN ( select ListItem from dbo.F_QuickTable_From_List(@transactionstring, ',' ) ))

Open in new window

0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 25

Expert Comment

by:imitchie
ID: 20409639
looks like angel beat me to it with a similar solution
0
 
LVL 19

Expert Comment

by:Rimvis
ID: 20409675
Hello riskyricky1972,

angelIII is right, you have t ouse quotes.

BUT, if you are expecting to pass comma delimited IDs as string into "IN (...)" statement, you are wrong. There are two options:

1) Build a SQL statement dynamically

EXEC ('SELECT     tbltransactions.transactionid, tbltransactions.batchnumber, tbltransactions.partno, tbltransactions.transactionprice, tbltransactions.transactionstatusid,
                      tbltransactions.datecreated, tbltransactions.memberid, tbltransactions.remark, tbltransactions.colorid, tbltransactions.quantity,
                      tbltransactions.manufacturerid, tbltransactions.producttypeid, tbltransactions.transactionid AS Expr1, tblcolor.color,
                      tblmanfacturers.manufacturercompany, tblmembers.firstname, tblmembers.lastname, tblmembers.middlename, tblproducttypes.producttypedesc
FROM         tbltransactions INNER JOIN
                      tblcolor ON tbltransactions.colorid = tblcolor.colorid INNER JOIN
                      tblmanfacturers ON tbltransactions.manufacturerid = tblmanfacturers.manufacturerid INNER JOIN
                      tblmembers ON tbltransactions.memberid = tblmembers.memberid INNER JOIN
                      tblproducttypes ON tbltransactions.producttypeid = tblproducttypes.producttypeid
WHERE     (tbltransactions.transactionid IN (' + @transactionstring + '))')

2) Create a splitter function, for example like this:
http://www.novicksoftware.com/UDFofWeek/Vol1/T-SQL-UDF-Volume-1-Number-29-udf_Txt_SplitTAB.htm

Then your SELECT wuold look like this:

      SELECT     tbltransactions.transactionid, tbltransactions.batchnumber, tbltransactions.partno, tbltransactions.transactionprice, tbltransactions.transactionstatusid,
                      tbltransactions.datecreated, tbltransactions.memberid, tbltransactions.remark, tbltransactions.colorid, tbltransactions.quantity,
                      tbltransactions.manufacturerid, tbltransactions.producttypeid, tbltransactions.transactionid AS Expr1, tblcolor.color,
                      tblmanfacturers.manufacturercompany, tblmembers.firstname, tblmembers.lastname, tblmembers.middlename, tblproducttypes.producttypedesc
FROM         tbltransactions INNER JOIN
                      tblcolor ON tbltransactions.colorid = tblcolor.colorid INNER JOIN
                      tblmanfacturers ON tbltransactions.manufacturerid = tblmanfacturers.manufacturerid INNER JOIN
                      tblmembers ON tbltransactions.memberid = tblmembers.memberid INNER JOIN
                      tblproducttypes ON tbltransactions.producttypeid = tblproducttypes.producttypeid
WHERE     (tbltransactions.transactionid IN (SELECT dbo.udf_Txt_SplitTAB(@transactionstring)))





Regards,

Rimvis
0
 

Author Comment

by:riskyricky1972
ID: 20428841
I appreciated all of your helps here. And I will review the codes in this weekend for sure.
By the way, I am little confused what you all provided in stored procedure.
Rimvis: can you re-post the revised one you have with completed working code?
Sorry I am new for this sql stuff...
0
 
LVL 19

Accepted Solution

by:
Rimvis earned 500 total points
ID: 20439825

1) Create "split" procedure:

SET QUOTED_IDENTIFIER ON

SET ANSI_NULLS ON

GO
 

CREATE FUNCTION dbo.udf_Txt_SplitTAB (
 

    @sInputList varchar(8000) -- List of delimited items

  , @Delimiter char(1) = ',' -- delimiter that separates items

)   RETURNS @List TABLE (Item varchar(8000))

    WITH SCHEMABINDING

/* 

* Returns a table of strings that have been split by a delimiter.

* Similar to the Visual Basic (or VBA) SPLIT function. The 

* strings are trimmed before being returned.  Null items are not

* returned so if there are multiple separators between items, 

* only the non-null items are returned.

* Space is not a valid delimiter.

*

* Example:

select * FROM dbo.udf_Txt_SplitTAB('abcd,123, 456, efh,,hi', ',')

*

* Test:

DECLARE @Count int, @Delim char(10), @Input varchar(128)

SELECT @Count = Count(*) 

    FROM dbo.udf_Txt_SplitTAB('abcd,123, 456', ',')

PRINT 'TEST 1 3 lines:' + CASE WHEN @Count=3 

              THEN 'Worked' ELSE 'ERROR' END

SELECT @DELIM=CHAR(10)

     , @INPUT = 'Line 1' + @delim + 'line 2' + @Delim

SELECT @Count = Count(*) 

    FROM dbo.udf_Txt_SplitTAB(@Input, @Delim)

PRINT 'TEST 2  LF    :' + CASE WHEN @Count=2 

              THEN 'Worked' ELSE 'ERROR' END

*

* © Copyright 2003 Andrew Novick http://www.NovickSoftware.com

* You may use this function in any of your SQL Server databases

* including databases that you sell, so long as they contain 

* other unrelated database objects. You may not publish this 

* UDF either in print or electronically.

* Published in T-SQL UDF of the Week Newsletter Vol 1 #29

http://www.NovickSoftware.com/UDFofWeek/UDFofWeek.htm

***************************************************************/

AS BEGIN
 

DECLARE @Item Varchar(8000)

DECLARE @Pos int -- Current Starting Position

      , @NextPos int -- position of next delimiter

      , @LenInput int -- length of input

      , @LenNext int -- length of next item

      , @DelimLen int -- length of the delimiter
 

SELECT @Pos = 1

     , @DelimLen = LEN(@Delimiter) --  usually 1 

     , @LenInput = LEN(@sInputList)

     , @NextPos = CharIndex(@Delimiter, @sInputList, 1) 
 

-- Doesn't work for space as a delimiter

IF @Delimiter = ' ' BEGIN

   INSERT INTO @List 

       SELECT 'ERROR: Blank is not a valid delimiter'

   RETURN

END
 
 

-- loop over the input, until the last delimiter.

While @Pos <= @LenInput and @NextPos > 0 BEGIN
 

    IF @NextPos > @Pos BEGIN -- another delimiter found

       SET @LenNext = @NextPos - @Pos           

       Set @Item = LTrim(RTrim(

                            substring(@sInputList

                                   , @Pos

                                  , @LenNext)

                               )

                         ) 

       IF LEN(@Item) > 0 

           Insert Into @List Select @Item

       -- ENDIF
 

    END -- IF
 

    -- Position over the next item

    SELECT @Pos = @NextPos + @DelimLen

         , @NextPos = CharIndex(@Delimiter

                              , @sInputList

                              , @Pos) 

END
 

-- Now there might be one more item left

SET @Item = LTrim(RTrim(

                      SUBSTRING(@sInputList

                               , @Pos

                               , @LenInput-@Pos + 1)

                       )

                 )
 

IF Len(@Item) > 0 -- Put the last item in, if found

   INSERT INTO @List SELECT @Item
 

RETURN

END

GO
 
 

2) Change your stored procedure:
 

create PROCEDURE dbo.up_TransmitConfirmation

 (

      @transactionstring nvarchar(255)

      )

AS

      /* SET NOCOUNT ON */

 SELECT     tbltransactions.transactionid, tbltransactions.batchnumber, tbltransactions.partno, tbltransactions.transactionprice, tbltransactions.transactionstatusid,

                      tbltransactions.datecreated, tbltransactions.memberid, tbltransactions.remark, tbltransactions.colorid, tbltransactions.quantity,

                      tbltransactions.manufacturerid, tbltransactions.producttypeid, tbltransactions.transactionid AS Expr1, tblcolor.color,

                      tblmanfacturers.manufacturercompany, tblmembers.firstname, tblmembers.lastname, tblmembers.middlename, tblproducttypes.producttypedesc

FROM         tbltransactions INNER JOIN

                      tblcolor ON tbltransactions.colorid = tblcolor.colorid INNER JOIN

                      tblmanfacturers ON tbltransactions.manufacturerid = tblmanfacturers.manufacturerid INNER JOIN

                      tblmembers ON tbltransactions.memberid = tblmembers.memberid INNER JOIN

                      tblproducttypes ON tbltransactions.producttypeid = tblproducttypes.producttypeid

WHERE     (tbltransactions.transactionid IN (SELECT dbo.udf_Txt_SplitTAB(@transactionstring)))

      

      RETURN

Open in new window

0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Delivering innovative fully-managed cloud services for mission-critical applications requires expertise in multiple areas plus vision and commitment. Meet a few of the people behind the quality services of Concerto.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now