Return a value of a SQL Server SP working with DATASET in VB

Question

Return a value of a SQL Server SP working with DATASET in VB

Navigation

#1 by (1 votes)

0

Good morning, I have the following case, I am working with dataset in visual basic, having an SP in SLQ server which returns a query, but add a parameter to know the number of records to return, to make a validation in SQL Server works well for me, but with the data set I have not found how to recover the variable with the number of records.

These are the parameters in the SP:

PROCEDURE [dbo].registrosaprocesarpormes
    @pnIDECIA int;
    @pdfechacorte date;
    @pnrowcount int OUTPUT

    select * from dbo.movimientos where fecha = fechacorte

    --- actualizando el valor devuelto en el sp
    set @pnrowcount = @@ROWCOUNT;

This variable is what I need to recover or the value of it in the form of the process, I hope your help and I appreciate your explanation.

sql vb.net

asked by alfredo.avmb 28.08.2017 в 22:29

source

1 answer

Dynamic array in C Use onkeyup event to update input?

score 1 · Accepted Answer

For your particular case you do not need to work with a DataSet to just return a value, with a simple SqlCommand you can do this work, you can see an example:

public sub GetCount(idecia as Int32, pdfechacorte as DateTime,con as SqlConnection) as Int32
  Dim cmd1 As SqlCommand = New SqlCommand("registrosaprocesarpormes", con)
  cmd1.CommandType = CommandType.StoredProcedure
  md1.Parameters.Add("@pnIDECIA", SqlDbType.Int).Value = idecia
  md1.Parameters.Add("@pdfechacorte", SqlDbType.Date).Value = pdfechacorte
  md1.Parameters.Add("@pnrowcount", SqlDbType.Int)
  cmd1.Parameters("@pnrowcount").Direction = ParameterDirection.Output
  cmd1.ExecuteNonQuery()
  Dim result = cmd1.Parameters("@pnrowcount").Value
  cmd1.Close();
  return Convert.ToInt32(result)
End Sub