?
Solved

Syntax error converting the varchar value '21, 0' to a column of data type int.

Posted on 2006-03-27
4
Medium Priority
?
889 Views
Last Modified: 2007-12-19
I am trying to run a SP that has an IN statement for selecting records based on a table PK but I keep running into this error?  Any ideas?

Here is my SP:
--------------------------------
CREATE PROCEDURE sp_EmailSent
      @AdminId AS INT,
      @Newsletters AS VarChar(50),
      @NewsletterSubject AS VarChar(100),
      @NewsletterSent AS VarChar(100),
      @Out AS INTEGER OUTPUT
AS
SET NOCOUNT ON
      
      DECLARE @NumSent AS INT
      SELECT @NumSent = COUNT(*) FROM dbo.view_subscribers WHERE NewsletterId IN (@Newsletters)
      INSERT INTO NewslettersSent (NewsletterSubject,NewsletterSent,NumberOfNewslettersSent,AdminId) VALUES (@NewsletterSubject,@NewsletterSent,@NumSent,@AdminId)
      SET @Out = @@IDENTITY

      SELECT CustomerId , EmailAddress, @Out AS SentId   -- use cast to force datatype
         FROM  dbo.view_subscribers  WHERE NewsletterId IN (@Newsletters) AND Bounced<3 AND AdminId=@AdminId
      ORDER BY EmailAddress
GO


Here is the .net code calling the SP
---------------------------------------------------------------
Dim objConn As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("AliveContactConnectionString").ConnectionString)
Dim cmd As New SqlCommand("sp_EmailSent", objConn)
cmd.Parameters.Add("@AdminId", SqlDbType.Int).Value = Session("AdminId")
cmd.Parameters.Add("@Newsletters", SqlDbType.VarChar).Value = SelectedLists
cmd.Parameters.Add("@NewsletterSubject", SqlDbType.VarChar).Value = EmailSubject.Text.ToString()
cmd.Parameters.Add("@NewsletterSent", SqlDbType.VarChar).Value = DropDownList1.SelectedValue.ToString()
cmd.Parameters.Add("@Out", SqlDbType.Int).Value = 0
cmd.CommandType = CommandType.StoredProcedure

Dim dataAdapter As SqlDataAdapter = New SqlDataAdapter(cmd)
Dim dataSet As New DataSet

dataSet = New DataSet
dataAdapter.Fill(dataSet)
objConn.Close()

------------------------------------------------------------------------
Incidentially this sp worked fine before when I was using classic asp?


Thanks for any help!
0
Comment
Question by:alivemedia
  • 2
4 Comments
 
LVL 2

Author Comment

by:alivemedia
ID: 16304466
I forgot to mention SelectedLists comes from a mulitle checkbox list:


Dim i As Object
        Dim SelectedLists As String = ""

        If Newsletters.Items.Count > 1 Then
            For i = 0 To Newsletters.Items.Count - 1
                If Newsletters.Items(i).Selected Then
                    SelectedLists = SelectedLists + Newsletters.Items(i).Value + ", "
                End If
            Next i
        Else
            SelectedLists = Newsletters.Items(0).Value
        End If
        SelectedLists = SelectedLists + "0"
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 16304481
create this helper 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    

and use it like this:

CREATE PROCEDURE sp_EmailSent
     @AdminId AS INT,
     @Newsletters AS VarChar(50),
     @NewsletterSubject AS VarChar(100),
     @NewsletterSent AS VarChar(100),
     @Out AS INTEGER OUTPUT
AS
SET NOCOUNT ON
     
     DECLARE @NumSent AS INT
     SELECT @NumSent = COUNT(*) FROM dbo.view_subscribers WHERE NewsletterId IN (@Newsletters)
     INSERT INTO NewslettersSent (NewsletterSubject,NewsletterSent,NumberOfNewslettersSent,AdminId) VALUES (@NewsletterSubject,@NewsletterSent,@NumSent,@AdminId)
     SET @Out = @@IDENTITY

     SELECT CustomerId , EmailAddress, @Out AS SentId   -- use cast to force datatype
        FROM  dbo.view_subscribers  WHERE NewsletterId IN (select value from dbo.ParmsToList (@Newsletters)) AND Bounced<3 AND AdminId=@AdminId
     ORDER BY EmailAddress


http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21627393.html
0
 
LVL 7

Expert Comment

by:LandyJ
ID: 16304536
Check the value of the session variable AdminID.  You're sending '21,0' as the value.
0
 
LVL 7

Expert Comment

by:LandyJ
ID: 16304777
DOH!  Disregard my post.  I totally missed the " IN (@Newsletters)" part.  Can't use a list that way.

BTW, you'll need angel's "(select value from dbo.ParmsToList (@Newsletters)) " on the "SELECT @NumSent = COUNT(*) FROM dbo.view_subscribers WHERE NewsletterId IN (@Newsletters)" line of code also.





0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Question has a verified solution.

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

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

862 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