Solved

sql asp

Posted on 2007-12-04
8
220 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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

707 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

16 Experts available now in Live!

Get 1:1 Help Now