Solved

sql asp

Posted on 2007-12-04
8
226 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
AWS Certified Solutions Architect - Associate

This course has been developed to provide you with the requisite knowledge to not only pass the AWS CSA certification exam but also gain the hands-on experience required to become a qualified AWS Solutions architect working in a real-world environment.

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

624 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