locked
Empty String Being Passed RRS feed

  • Question

  • I have a stored procedure that sends cdo mail, and the sp has a parameter that I can pass a semi-colon separated list to that specifies the attachments for the e-mail.....The e-mail has always worked for me, and when I pass it the attachment parameter from query analyzer it works fine, and it has always worked when the procedure is called via .NET.  The problem is that recently I have .NET dll that I created that passes a long list of attachments to the sp, I tried the values from query analyzer and they worked but when I pass them from .NET the sp says the parameter value is blank.  There are no errors, it just seems to be not accepting such a long parameter string even though the value is shorter than the parameter length (in both the sp and .NET parameter size setting).  Does anyone have any ideas what is going wrong, I am able to write the value to a text file, so I no the value is right, the SQLCommand object just isn't passing the value (I have tried setting the CommandType to SP and Text (with exec command), and both passed blank values).

    Thanks,

    Adam

    Thursday, August 10, 2006 11:01 PM

Answers

  • I got this working, I don't know why it was passing a blank string, but I realized that I was trying to e-mail word documents that my application had not released yet.  Once I sent the e-mail after the release of the word objects everything worked fine.  That doesn't explain the blank string being passed, but my issue is resolved.  Thanks to everyone who made suggestions.

    ACFalcon2001

    Monday, August 14, 2006 2:12 PM

All replies

  • Without seeing the code it would be impossible to diagnose your problem

     

    Friday, August 11, 2006 12:46 PM
  • Here is the .NET Code (the @Attachment parameter is the problem one).  The sAttach value is correct because during the procedure I wrote it to a text file and it looked fine.

     

    oCMEmail = New SqlCommand("test.dbo.testemail",oCNEmail)

    oCMEmail.CommandType = CommandType.StoredProcedure

    oCMEmail.Parameters.Add("@To",oDT.TableName & "@xxxx.com")

    oCMEmail.Parameters.Add("@From","xxxxx@xxxx.com")

    oCMEmail.Parameters.Add("@CC","xxxxx@xxxx.com")

    oCMEmail.Parameters.Add("@Subject","Test Subject")

    oCMEmail.Parameters.Add("@Body","<font style='font-family:Tahoma;font-size:11px'><b>TEST HTML EMAIL</b></font>")

    oCMEmail.Parameters.Add("@Attachment",sAttach)

    oCMEmail.ExecuteNonQuery()

     

    Here is an example of the sp.

     

    create proc testemail(@To varchar(100),@From varchar(50),@CC varchar(100),@Subject varchar(250),@Body varchar(8000),@Attachment varchar(5000))

    as

      insert into table1 values(@Attachment)

    go

     

     

    The value that is inserted is blank, but if I write the value to a text file and pass it to the sp with the same parameters as the .NET app, it get the value fine.

    Friday, August 11, 2006 1:43 PM
  • Hi,

    Could you try declaring a SqlParameter object to manually set the value of the property? Try this:

    Dim param As SqlParameter = oCMEmail.Parameters.Add()
    param.ParameterName = "@Attachment"
    param.Value = sAttach

     

     

    cheers,

     

    Paul June A. Domag

    Friday, August 11, 2006 4:35 PM
  • I already tried that and it didn't work, the same result occurred.
    Friday, August 11, 2006 4:38 PM
  • Hi,

    Have you also tried manually specifying the size?

    param.Size = 5000

    And also try checking the order of creating your paramters. Just to be sure, check if its also in the order on your Stored Procedure parameter. Just trying to help...

     

    cheers,

    Paul June A. Domag

    Friday, August 11, 2006 4:43 PM
  • I got this working, I don't know why it was passing a blank string, but I realized that I was trying to e-mail word documents that my application had not released yet.  Once I sent the e-mail after the release of the word objects everything worked fine.  That doesn't explain the blank string being passed, but my issue is resolved.  Thanks to everyone who made suggestions.

    ACFalcon2001

    Monday, August 14, 2006 2:12 PM