none
how to get a result from sql server code in vb.net RRS feed

  • Question

  • Hi members

    i have a procedure in sql server i want get the result using vb.net like this 

    declare @lres int
    
    execute CB_IsRecordLock 'F_ARTICLE',6565445, @lres output
    print @lres
    
    

    please help

    Monday, January 20, 2020 2:47 PM

Answers

  • Dim cmd As New SqlCommand("declare @lres int execute CB_IsRecordLock 'F_ARTICLE','6499437', @lres output select @lres", cn)
            Dim ad As SqlDataReader = cmd.ExecuteReader
            ad.Read()

    i found it

    thank you all for help

    Thursday, January 30, 2020 11:36 AM

All replies

  • Here is a basic pattern where the SP name is dbo.uspContactByContactType which has a single parameter passed in by the caller to this function. Note for the CommandType its setup as a stored procedure.

    Public Function GetAllRecordsByContactTitle(contactTypeIdentifier As Integer) As DataTable
    
    	Dim dt = New DataTable
    
    	Try
    		Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
    			Using cmd As New SqlCommand With {.Connection = cn, .CommandType = CommandType.StoredProcedure}
    
    				cmd.CommandText = "dbo.usp_ContactByType"
    
    				cmd.Parameters.Add(New SqlParameter With
    									  {
    										  .ParameterName = "@ContactTitleTypeIdentifier",
    										  .SqlDbType = SqlDbType.Int
    									  })
    
    				cmd.Parameters("@ContactTitleTypeIdentifier").Value = contactTypeIdentifier
    
    				cn.Open()
    
    				dt.Load(cmd.ExecuteReader)
    
    			End Using
    		End Using
    
    	Catch ex As Exception
    		' handle exception here
    	End Try
    
    	Return dt
    
    End Function
    


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Monday, January 20, 2020 3:20 PM
    Moderator
  • thanks for help

    i have CB_IsRecordLock  is a procedure stored in sql server

    and it have parametre

    CB_IsRecordLock 'database',Number of line, @lresult

    i want use it in vb

    so i tried with 

     Dim cmd As New SqlCommand(" declare @lres int execute CB_IsRecordLock 'F_ARTICLE',6565445, @lres output print @lres ",cn)

    Dim ab = cmd.ExecuteScalar

    messageBox.Show(ab)

    Nothing return

    but in sql server when i execute it return a value

    please help


    Monday, January 20, 2020 3:53 PM
  • thanks for help

    i have CB_IsRecordLock  is a procedure stored in sql server

    and it have parametre

    CB_IsRecordLock 'database',Number of line, @lresult

    i want use it in vb

    so i tried with 

     Dim cmd As New SqlCommand(" declare @lres int execute CB_IsRecordLock 'F_ARTICLE',6565445, @lres output print @lres ",cn)

    Dim ab = cmd.ExecuteScalar

    messageBox.Show(ab)

    Nothing return

    but in sql server when i execute it return a value

    please help


    You need to use the syntax I showed. What you are attempting will never work!!!

    You can see my Microsoft TechNet article and source code with scripts to create and populate a database with stored procedures ready to run for doing stored procedures with SQL-Server and VB.NET.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Monday, January 20, 2020 4:43 PM
    Moderator
  • Hi,

    Have you solved this problem now?

    I think the above reply can provide you with a solution, have you tried it?

    If so, hope you can close this thread by marking the reply as answer as this will help others looking for the same or similar issues down the road.

    Best Regards,

    Julie


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, January 22, 2020 8:54 AM
    Moderator
  • not resolved yet,

    i have a procedure when i executet it return a value so i can't get it

    how i can do it the procedure is CB_IsRecordLock

    and it have 3 parametre the 3rd is the result

    CB_IsRecordLock 'F_ARTICLE',6565445, @lres

    i can declare a variable with vb.net  like 

    dim lock As Integer
    
    Dim cmd2222 As New SqlCommand("
    execute CB_LockRecord F_ARTICLE',3,'" & lock & "'", cn)
    
         cmd2222.ExecuteNonQuery()

    how i can get lock result

    please help at least understand why i can't do it

    Saturday, January 25, 2020 8:24 AM
  • not resolved yet,

    i have a procedure when i executet it return a value so i can't get it

    how i can do it the procedure is CB_IsRecordLock

    and it have 3 parametre the 3rd is the result

    CB_IsRecordLock 'F_ARTICLE',6565445, @lres

    i can declare a variable with vb.net  like 

    dim lock As Integer
    
    Dim cmd2222 As New SqlCommand("
    execute CB_LockRecord F_ARTICLE',3,'" & lock & "'", cn)
    
         cmd2222.ExecuteNonQuery()

    how i can get lock result

    please help at least understand why i can't do it

    The problem is you are treating this operation as if running from SQL-Server Management Studio which is not possible, you need to follow what I've provided which is to execute a Stored Procedure using .NET methods.
    Saturday, January 25, 2020 12:50 PM
  • Let's try this again.

    The stored procedure

    CREATE PROCEDURE dbo.uspCustomersByGender
        (
            @GenderType AS INT
        )
    AS
        BEGIN
            SELECT C.Identifier ,
                   C.CompanyName ,
                   C.ContactName ,
                   C.ContactTypeIdentifier ,
                   CT.ContactType ,
                   C.GenderIdentifier ,
                   G.GenderType
            FROM   dbo.Customer AS C
                   INNER JOIN dbo.ContactTypes AS CT ON C.ContactTypeIdentifier = CT.Identifier
                   INNER JOIN dbo.Genders AS G ON C.GenderIdentifier = G.id
            WHERE  C.GenderIdentifier = @GenderType;
        END;
    

    Run from SSMS

    DECLARE	@return_value int
    
    EXEC	@return_value = dbo.uspCustomersByGender
    		@GenderType = 1
    
    SELECT	'Return Value' = @return_value
    

    Run from a VB.NET project

    Class to hold results

    Namespace Classes
        Public Class Customer
            Public Property Identifier() As Integer
            Public Property CompanyName() As String
            Public Property ContactName() As String
            Public Property ContactTypeIdentifier() As Integer
            Public Property ContactType() As Integer
            Public Property GenderIdentifier() As Integer
            Public Property GenderType() As String
    
            Public Overrides Function ToString() As String
                Return CompanyName
            End Function
        End Class
    End Namespace

    Code to execute the SP

    Imports System.Data.SqlClient
    '
    ' BaseConnectionLibrary is in my signature
    '
    Imports BaseConnectionLibrary.ConnectionClasses
    
    Namespace Classes
        Public Class DataOperations
            Inherits SqlServerConnection
    
            Public Sub New()
                DefaultCatalog = "CustomerDatabase"
                DatabaseServer = "KARENS-PC"
            End Sub
            ''' <summary>
            ''' Get customers by gender
            ''' </summary>
            ''' <param name="genderType">1 Female, 2 Male, 3 Other</param>
            ''' <returns>List of customer by gender</returns>
            Public Async Function CustomersByGenderStoredProcedureAsync(genderType As Integer) As Task(Of List(Of Customer))
                Dim customersList As New List(Of Customer)
    
                Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
                    Using cmd As New SqlCommand With {.Connection = cn}
                        cmd.CommandType = CommandType.StoredProcedure
                        cmd.CommandText = "uspCustomersByGender"
                        cmd.Parameters.AddWithValue("@GenderType", genderType)
    
                        Await cn.OpenAsync()
    
                        Dim reader = Await cmd.ExecuteReaderAsync()
    
    
                        While reader.Read()
                            customersList.Add(New Customer() With {
                                                 .Identifier = reader.GetInt32(0),
                                                 .CompanyName = reader.GetString(1),
                                                 .ContactName = reader.GetString(2)})
                        End While
    
                    End Using
                End Using
    
                Return customersList
    
            End Function
        End Class
    End Namespace

    Form code

    Imports Example2.Classes
    
    Public Class Form1
        Private BindingSource As BindingSource = New BindingSource()
        Private Async Sub Form1_Shown(sender As Object, e As EventArgs) Handles Me.Shown
    
            Dim operations = New DataOperations
            Dim customerList = Await operations.CustomersByGenderStoredProcedureAsync(1)
    
            BindingSource.DataSource = customerList
    
            DataGridView1.AutoGenerateColumns = False
            DataGridView1.DataSource = BindingSource
    
        End Sub
    
        Private Sub ExitButton_Click(sender As Object, e As EventArgs) Handles ExitButton.Click
            Close()
        End Sub
    End Class
    

    Show three fields rather than all fields. DataGridView has predefined columns with DataPropertyName set.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Saturday, January 25, 2020 1:31 PM
    Moderator
  • Dim cmd As New SqlCommand("declare @lres int execute CB_IsRecordLock 'F_ARTICLE','6499437', @lres output select @lres", cn)
            Dim ad As SqlDataReader = cmd.ExecuteReader
            ad.Read()

    i found it

    thank you all for help

    Thursday, January 30, 2020 11:36 AM