?
Solved

How to execute a SQL Server 2005 Stored Procedure and get a return value in VB6?

Posted on 2013-01-16
5
Medium Priority
?
645 Views
Last Modified: 2013-01-17
Hello everyone,
                    I have an application in Visual Basic 6 and I need to execute a stored procedure and get a return value.  How can I do this? what is the code that I can use.

Visual Basic 6 no Visual Basic.NET

Thank you
0
Comment
Question by:iscivanomar
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 13

Expert Comment

by:AielloJ
ID: 38784715
iscivanomar,

It's been awhile, but this should work for you:

CREATE PROCEDURE my_proc
            @param1 varchar(10),
            @param2 int
:
:
:
:
Public Shared Function my_function(ByVal myVB_Param As String) As String

    'create connection
    Using cnSQL As SqlConnection = New SqlConnection("MyConnectionString")
        Using cmdSP As New SqlCommand("my_proc", cnSQL)
            cmdSP.CommandType = System.Data.CommandType.StoredProcedure

            'declare and add  parameter
            cmdSP.Parameters.Add("@param1", System.Data.SqlDbType.NVarChar, 50).Value = "ABC"
            cmdSP.Parameters.Add("@param2", System.Data.SqlDbType.Integer, 0).Value = 0

            Dim dtTable As New DataTable

            'execute command
            Try
                cnSQL.Open()
                dtTable.Load(cmdSP.ExecuteReader())
                cnSQL.Close()
            Catch ex As SqlException
                'do something here
            End Try

            'return string
            Return dtTable.Rows(0)(0)
        End Using
    End Using

End Function

Best regards,

AielloJ
0
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 600 total points
ID: 38784723
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 38784725
Looks like @AielloJ missed the

Visual Basic 6 no Visual Basic.NET
0
 
LVL 9

Assisted Solution

by:shorvath
shorvath earned 400 total points
ID: 38787347
Absolutely the easiest way to work with VB6 and Stored Procedures is the ADO Stored Procedure Add-IN from Microsoft.  This Add-In integrates with the VB6 IDE and all you have to do is select the server and stored procedure and it writes all of the code for you.  

Although you can't get it from Microsoft anymore, I did manage to find it for you at:

http://www.vbrad.com/article.aspx?id=103

Just click the download link and install as per instructions.

After you see how easy this is, you can then get a little bit more advanced.
This link shows you how to customize the add-in for you own purposes.  I remember creating server specific custom add-ins using this method and it saved me hundreds of hours of coding over the years.

http://msdn.microsoft.com/en-us/magazine/cc301907.aspx
0
 

Author Closing Comment

by:iscivanomar
ID: 38788984
Thank you for your help,
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

New Windows 7 Installations take days for Windows-Updates to show up and install. This can easily be fixed. I have finally decided to write an article because this seems to get asked several times a day lately. This Article and the Links apply to…
The Windows functions GetTickCount and timeGetTime retrieve the number of milliseconds since the system was started. However, the value is stored in a DWORD, which means that it wraps around to zero every 49.7 days. This article shows how to solve t…
This Micro Tutorial will give you a basic overview of Windows DVD Burner through its features and interface. This will be demonstrated using Windows 7 operating system.
This Micro Tutorial will give you a basic overview of Windows Live Photo Gallery and show you various editing filters and touches to photos you can apply. This will be demonstrated using Windows Live Photo Gallery on Windows 7 operating system.
Suggested Courses
Course of the Month10 days, 8 hours left to enroll

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question