Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

sql asp

Posted on 2007-12-04
8
Medium Priority
?
228 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 143

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 143

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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 2000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Loops Section Overview
Suggested Courses

877 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