SQL Server stored procedure parameter value appended to vb.net value

Question

I am stuck on how to append my SQL Server stored procedure output value with another parameter value in my vb code. I have this stored procedure that has two columns: one column is the suffix values and the other is the document type.

I joined it to another table where the columns are: the document type, document name, and documentid. I am using this procedure to append the suffix to the incoming document names that are inserted everyday to that second table based on document type and the documents with the suffix attached are then added to a third table (that part isn't necessary for this question)

SQL Server stored procedure:

ALTER PROCEDURE [dbo].[up_AddDocumentSuffix]
    (@Docsuffix varchar(50) OUTPUT,
     @DocName varchar(50))
AS
    SELECT @Docsuffix = t2.DocSuffix
    FROM Documents_DocumentDetails t1
    INNER JOIN Documents_DocumentTypes t2 ON t2.DocumentType = t1.DocumentType WHERE (t2.Docname = @DocName)

In VB I have this code (code 1) I am trying to add in, that attaches the suffix from that stored procedure to the parameter of the documentname but I am not sure if I am even going in the right direction. The procedure that adds the documentname is referenced somewhere else in the code (code 2) I am not sure how to incorporate the first code and add the suffix in the second, any ideas?

Edit: forgot to add the modified sql stored procedure I am currently using my apologies, the input parameter @DocName

Vb code 1:

 Dim DocName As String
 DocName = drDocDetailData("DocName").ToString         

 Using DocDetail As New SqlCommand
     With DocDetail
          .Connection = con
          .CommandType = CommandType.StoredProcedure
          .CommandText = ("up_AddDocumentSuffix")
          Suffix = CType(.ExecuteScalar, varchar)
    End With
End Using

Vb code 2:

.Parameters.AddWithValue("@DocName",   Trim(drDocDetailData("DocName").ToString))

thanks to @Morpheus here is the correct code!

Using DocDetail As New SqlCommand
With DocDetail
  .Connection = con
  .CommandType = CommandType.StoredProcedure
  .CommandText = ("up_AddDocumentSuffix")
  .Parameters.AddWithValue("@DocName", con)
  .Parameters.Add("@DocSuffix", SqlDbType.VarChar, 50).Direction =  ParameterDirection.Output
  .ExecuteNonQuery()
  suffix = DocDetail.Parameters("@DocSuffix").Value
  Docname = sb.Append(DocName).Append(suffix).ToString()
End With
End Using

@marc_s thank you for fixing it up! sorry about all the errors — jean55
– jean55, Commented Jun 20, 2016 at 18:22
please let me know if you need more details in my answer or if that helped you resolve the problem. — Morpheus
– Morpheus, Commented Jun 21, 2016 at 14:40

Community · Accepted Answer · 2017-05-23 12:16:16Z

1

You'll need to use an Output parameter on your SqlCommand object to get the value for @Docsuffix and you don't want to use ExecuteScalar in this scenario.

See this answer for an example: https://stackoverflow.com/a/31533252/3854195

The key is ParameterDirection.Output

edited May 23, 2017 at 12:16

CommunityBot

11 silver badge

answered Jun 20, 2016 at 19:46

Morpheus

1,6321 gold badge21 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

jean55 Over a year ago

sorry for the late reply! thank you so much you are correct, I replaced the executescalar with ExecuteNonQuery() and changed the direction to output, I just finished testing and it worked! Thank you! I tried pasting the modified code but it wont let me in this comment any idea how to do that? but thank you again!

Morpheus Over a year ago

Great to hear! I think you would have to add the modified code as an update to your original question.

Collectives™ on Stack Overflow

SQL Server stored procedure parameter value appended to vb.net value

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related