jean55 jean55 - 5 months ago 17
Vb.net Question

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

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

Answer

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: http://stackoverflow.com/a/31533252/3854195

The key is ParameterDirection.Output