Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

sql asp

Posted on 2007-12-04
8
Medium Priority
?
227 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
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

How To Install Bash on Windows 10

Windows’ budding partnership with Canonical has certainly led to some great improvements. One of them being the ability to use Bash on your Windows machine without third party applications! This might be one of the greatest things a cloud engineer in a Windows environment can do!

Question has a verified solution.

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

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

661 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