Advertisement

05.11.2008 at 12:53PM PDT, ID: 23393018 | Points: 500
[x]
Attachment Details

How to get the OUT value of a MySQL 5 stored procedure with VB.NET

Tags: VB.NET
Hi,

Im using MySQL 5 with VB.NET to create a stored procedure that selects the total of a customers basket. At present I have a stored procedure that takes the userID as IN parameter like so:

create procedure sp_select_basket_total(IN inUID INT)
select sum(price*qty) from basket where uid = inUID  group by uid

How can I get this to give me the value of the select statement as an OUT parameter?

My VB is below:

    Public Shared Function writeBasketTotal(ByVal UID As Integer) As DataSet
        Dim connStr As String = ConfigurationManager.AppSettings("connStr")
        Dim conn As New MySqlConnection(connStr)
        Dim cmd As New MySqlCommand
        Dim dsReturn As New DataSet

        Try
            conn.Open()
            cmd.Connection = conn
            cmd.CommandText = "sp_select_basket_total"
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.Add("?inUID", UID).Direction = ParameterDirection.Input
            Using daloader As New MySqlDataAdapter(cmd)
                daloader.Fill(dsReturn)
            End Using
            Return dsReturn
            conn.Close()
        Catch ex As Exception
            Return dsReturn
        End Try
    End Function

Thanks in advance





Start your free trial to view this solution
Question Stats
Zone: Programming
Question Asked By: bluefezteam
Question Asked On: 05.11.2008
Participating Experts: 1
Points: 500
Views: 0
Translate:
Loading Advertisement...
05.11.2008 at 01:00PM PDT, ID: 21543314

Rank: Genius

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
05.12.2008 at 11:59AM PDT, ID: 21549590

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
05.12.2008 at 12:08PM PDT, ID: 21549654

Rank: Genius

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
05.14.2008 at 12:25PM PDT, ID: 21567786

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
 
Loading Advertisement...
Microsoft
  • Internet Protocols
  • Applications
  • Development
  • OS
  • Hardware
  • Windows Security
Apple
  • Operating Systems
  • Hardware
  • Programming
  • Networking
  • Software
Internet
  • Search Engines
  • File Sharing
  • WebTrends / Stats
  • Spy / Ad Blockers
  • Web Browsers
  • New Net Users
  • Web Development
  • Chat / IM
  • Anti Spam
  • Web Servers
  • Anti-Virus
  • Email Clients
Gamers
  • Tips
  • Online / MMORPG
  • Puzzle
  • Emulators
  • Action / Adventure
  • Role Playing
  • Consoles
  • Game Programming
  • Strategy
  • Sports
  • Misc
  • Computer Games
Digital Living
  • Hardware
  • New Net Users
  • New Users
  • Software
  • Digital Music
  • Gaming World
  • Home Security
  • Apple
  • Networking Hardware
Virus & Spyware
  • Vulnerabilities
  • IDS
  • Encryption
  • Anti-Virus
  • Operating Systems Security
  • Software Firewalls
  • WebApplications
  • Cell Phones
  • Operating Systems
  • Internet
  • Hardware Firewalls
Hardware
  • Handhelds / PDAs
  • Displays / Monitors
  • Components
  • Networking Hardware
  • Peripherals
  • Laptops/Notebooks
  • Storage
  • Servers
  • Desktops
  • New Users
  • Misc
  • Apple
Software
  • System Utilities
  • Industry Specific
  • Network Management
  • Photos / Graphics
  • Page Layout
  • VMWare
  • Misc
  • Web Development
  • OS
  • CYGWIN
  • Voice Recognition
  • Message Queue
  • Quality Assurance
  • Security
  • Firewalls
  • MultiMedia Applications
  • Development
  • Database
  • Office / Productivity
  • Business Management
  • OS/2 Apps
  • Server Software
  • Internet / Email
ITPro
  • OS
  • Storage
  • Encryption
  • Operating Systems Security
  • Apple Hardware
  • Laptops & Notebooks
  • Servers
  • Networking Hardware
  • Peripherals
  • Devices
  • Displays / Monitors
  • WebTrends / Stats
  • Search Engines
  • Firewalls
  • WebApplications
  • IDS
  • Vulnerabilities
  • Email Clients
  • File Sharing
  • Spy / Ad Blockers
  • Web Browsers
  • Web Servers
  • Networking
  • Anti-Virus
  • Chat / IM
  • Anti Spam
Developer
  • Web Servers
  • Web Browsers
  • Game Programming
  • Dev Tools
  • Industry Specific
  • Office / Productivity
  • Database
  • CYGWIN
  • Web Development
  • Search Engines
  • File Sharing
  • WebTrends / Stats
  • Programming
  • Content Management
  • Application Servers
  • Protocols
Storage
  • Removable Backup Media
  • Storage Technology
  • Servers
  • Grid
  • Remote Access
  • Backup / Restore
  • Misc
  • Hard Drives
OS
  • Miscellaneous
  • Security
  • Development
  • Linux
  • VMWare
  • MainFrame OS
  • Unix
  • Apple
  • OS / 2
  • AS / 400
  • BeOS
  • Microsoft
  • VMS / OpenVMS
Database
  • Oracle
  • Miscellaneous
  • MySQL
  • Software
  • Sybase
  • Contact Management
  • PostgreSQL
  • Data Manipulation
  • Clarion
  • InterSystems Cache
  • Siebel
  • MUMPS
  • OLAP
  • SQLBase
  • SAS
  • GIS & GPS
  • 4GL
  • Berkeley DB
  • DB2
  • Informix
  • Interbase / Firebird
  • FoxPro
  • Reporting
  • LDAP
  • Filemaker Pro
  • MS SQL Server
  • dBase
  • MS Access
Security
  • Misc
  • Web Browsers
  • Software Firewalls
  • Operating Systems Security
  • File Sharing
  • Spy / Ad Blockers
  • Vulnerabilities
  • WebApplications
  • IDS
  • Anti-Virus
  • Encryption
  • Anti Spam
  • Email Clients
  • VPN
  • Chat / IM
Programming
  • Editors IDEs
  • Installation
  • Handhelds / PDAs
  • Multimedia Programming
  • System / Kernel
  • Algorithms
  • Game
  • Signal Processing
  • Project Management
  • Open Source
  • Database
  • Misc
  • Languages
  • Processor Platforms
  • Theory
Web Development
  • Scripting
  • Blogs
  • Web Servers
  • Software
  • Search Engines
  • Web Graphics
  • Images
  • Internet Marketing
  • Images and Photos
  • Components
  • Document Imaging
  • Web Languages/Standards
  • Illustration
  • WebApplications
  • Fonts
  • WebTrends / Stats
  • Authoring
  • Digital Camera Software
  • Miscellaneous
Networking
  • Protocols
  • Apple Networking
  • Network Management
  • Message Queue
  • Application Servers
  • Content Management
  • File Servers
  • Email Servers
  • Misc
  • Java Editors & IDEs
  • Wireless
  • Networking Hardware
  • Backup / Restore
  • System Utilities
  • ISPs & Hosting
  • Web Servers
  • Storage Technology
  • Removable Backup Media
  • Servers
  • Broadband
  • Grid
  • OS / 2
  • Novell Netware
  • Unix Networking
  • Windows Networking
  • Security
  • Telecommunications
  • Operating Systems
  • Linux Networking
Other
  • Community Advisor
  • Lounge
  • Community Support
  • New Net Users
  • Philosophy / Religion
  • Math / Science
  • Miscellaneous
  • URLs
  • Expert Lounge
  • Politics
  • Puzzles / Riddles
Community Support
  • Suggestions
  • New to EE
  • New Topics
  • Community Advisor
  • CleanUp
  • Announcements
  • General
  • Feedback
  • Input
  • EE Bugs
 
05.11.2008 at 01:00PM PDT, ID: 21543314

Rank: Genius

here we go:
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
procedure change: 
create procedure sp_select_basket_total(IN inUID INT, OUT q DECIMAL(20,4) )
select sum(price*qty) into q from basket where uid = inUID  group by uid 

.net code: 
Public Shared Function writeBasketTotal(ByVal UID As Integer) As Double
        Dim connStr As String = ConfigurationManager.AppSettings("connStr")
        Dim conn As New MySqlConnection(connStr)
        Dim cmd As New MySqlCommand
        Dim q as MySqlParameter
        Dim dsReturn as Double 
        Try
            conn.Open()
            cmd.Connection = conn
            cmd.CommandText = "sp_select_basket_total"
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.Add("?inUID", UID).Direction = ParameterDirection.Input
            q = cmd.Parameters.Add("?q")
            q.Direction = ParameterDirection.Output
            cmd.ExecuteNonQuery()
            dsReturn = q.Value
            Return dsReturn
        Catch ex As Exception
            Return 0
        Finally
            conn.Close()
        End Try
    End Function
Open in New Window
 
05.12.2008 at 11:59AM PDT, ID: 21549590
Hi angelIII,

I've tried your suggestion but im getting at error at : q = cmd.Parameters.Add("?q")

The error is "Value of type 'Integer' cannot be converted to 'MySQL.Data.MySQLCLient.MySQLParameter"

Any ideas?

Thanks again
 
05.12.2008 at 12:08PM PDT, ID: 21549654

Rank: Genius

no, I don't see how that error could occur on that line ...
 
05.14.2008 at 12:25PM PDT, ID: 21567786
Hi angelIII

I've modified the vb.net code to what is below:

 q = cmd.Parameters.Add("?q", MySqlDbType.Int32)

Which has fixed the error on that line.

The problem is now that when it gets to the line

cmd.ExecuteNonQuery()

I get the error message 'Input string was not in a correct format.'

I've tried changing the function and variable type to decimal as that is what my stored procedure says the output variable is.


Thanks in advance
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
VB
 
    Public Shared Function writeBasketTotal(ByVal UID As Integer) As Decimal
        Dim connStr As String = ConfigurationManager.AppSettings("connStr")
        Dim conn As New MySqlConnection(connStr)
        Dim cmd As New MySqlCommand
        Dim q As MySqlParameter
        Dim dsReturn As Decimal
        Try
            conn.Open()
            cmd.Connection = conn
            cmd.CommandText = "sp_select_basket_total"
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.Add("?inUID", UID).Direction = ParameterDirection.Input
            q = cmd.Parameters.Add("?q", MySqlDbType.Int32)
            q.Direction = ParameterDirection.Output
            cmd.ExecuteScalar()
            dsReturn = q.Value
            Return dsReturn
        Catch ex As Exception
            Return 0
        Finally
            conn.Close()
        End Try
    End Function
 
 
MYSQL SP
select sum(price*qty) into q from basket where uid = inUID  group by uid
IN inUID INT, OUT q DECIMAL(20,4)
Open in New Window
 
 
20080236-EE-VQP-29 / EE_QW_2_20070628