[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Calling a FireBird stored procedure from VB.NET

Posted on 2006-05-20
7
Medium Priority
?
4,636 Views
Last Modified: 2013-12-09
Hi,

I have a Firebird database with three fields: ID Integer, FIRSTNAME Varchar(50), LASTNAME Varchar(50).

I have created an Insert stored procedure called SP_INS_TABLE1 that reads in EMS SQL Manager 2005:

BEGIN INSERT INTO Table1 (ID, FIRSTNAME, LASTNAME) Values (:inID, :inFIRSTNAME, :inLASTNAME); END

When I read the input parameters in EMS I do see : ID Integer, FIRSTNAME Varchar(50), LASTNAME Varchar(50).

My problem is executing the procedure from my VB.NET app to insert a row.

Here is the code I use:

         Dim conStr As String = "ServerType=1;User=SYSDBA;Password=masterkey;Dialect=3;Database=MYDB.FDB"
   
Dim cnDB As  FirebirdSql.Data.Firebird.FbConnection  
cnDB = New FirebirdSql.Data.Firebird.FbConnection(conStr)

        Dim cmd As FirebirdSql.Data.Firebird.FbCommand
       
       cmd = New FirebirdSql.Data.Firebird.FbCommand("SP_INS_TABLE1", New FirebirdSql.Data.Firebird.FbConnection(conStr))

        cmd.Connection = cnDB
        cmd.CommandType = CommandType.StoredProcedure

        cmd.Parameters.Add("inID", CInt(txtID.Text))
        cmd.Parameters.Add("inFIRSTNAME", txtFirstName.Text)
        cmd.Parameters.Add("inLASTNAME", txtLastName.Text)

        cmd.Connection.Open()

        Try
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox("Failed insert !")
        Finally
            cmd.Connection.Close()
        End Try

This does not work.

any help shall be greatly appreciated.

0
Comment
Question by:Ghanisen
  • 3
  • 2
  • 2
7 Comments
 
LVL 19

Expert Comment

by:NickUpson
ID: 16725424
the command that firebird expects to receive is "execute SP_INS_TABLE1 (value1, value2, value3)"
0
 

Author Comment

by:Ghanisen
ID: 16726194

Hi NickUpson,

Sorry, I tried your solution but it does not work

        Dim conStr As String = "ServerType=1;User=SYSDBA;Password=masterkey;Dialect=3;Database=MYDB.FDB"
       
        Dim cnDB As FirebirdSql.Data.Firebird.FbConnection
        cnDB = New FirebirdSql.Data.Firebird.FbConnection(conStr)

        Dim cmd As FirebirdSql.Data.Firebird.FbCommand
        cmd =  New FirebirdSql.Data.Firebird.FbCommand

        cmd.Connection = cnDB
        cmd.CommandText = "execute SP_INS_TABLE1 (CInt(txtID.Text), txtFirstName.Text, txtLastName.Text"

        If cnDB.State = ConnectionState.Closed Then
            cnDB.Open()
        End If

        Try
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox("Echec de l'insertion !")
        Finally
            If cnDB.State = ConnectionState.Open Then
                cnDB.Close()
            End If
        End Try

I still hope someone shall come with a solution.

0
 
LVL 19

Accepted Solution

by:
NickUpson earned 700 total points
ID: 16727823
see if this helps (about 1/2 way down, example of working code)

http://www.experts-exchange.com/Databases/Interbase/Q_20890464.html?query=VB+firebird&topics=244
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 6

Expert Comment

by:phuocnh
ID: 16727893
I think that your store procedure lack "commit" statement after insert statement.
When you build a store procedure you should test if it works well before by FB/IB client (EMS SQL Manager 2005). After that you use it in your code.
My hint can help you something.
Goodluck
Phuoc H. Nguyen
0
 
LVL 19

Expert Comment

by:NickUpson
ID: 16728877
you can't have commit inside the SP, it has to be in the client application
0
 
LVL 6

Assisted Solution

by:phuocnh
phuocnh earned 300 total points
ID: 16728921
Okie, Transactions cannot be started/committed/rolled back from inside stored procedure or trigger code.
Thanks NickUpson to remind me.
So, in the VB.NET code of Ghanisen must be start an transaction before executing stored procedure and commit it after execting stored procedure.
 To start a transaction you should read here
http://www.ibphoenix.com/main.nfs?a=ibphoenix&page=ibp_60_sqlref#RSf96788
and commit a transaction:
http://www.ibphoenix.com/main.nfs?a=ibphoenix&page=ibp_60_sqlref#RSf70564
0
 

Author Comment

by:Ghanisen
ID: 16729666
Hi,

NickUpson is right: commit cannot be included and no transaction is required.

Actually I have found the correct way to call my insert stored procedure:

 Private Sub Ajouter()

        oCnDB() 'Open database, the connection public name is cnDB

        cmd = New FbCommand("SP_INSERT_TABLE1", cnDB)

        cmd.Parameters.Add("@ID", FbDbType.Integer, 4).Value = CInt(txtID.Text)
        cmd.Parameters.Add("@FIRSTNAME", FbDbType.VarChar, 50).Value = txtFirstName.Text
        cmd.Parameters.Add("@LASTNAME", FbDbType.VarChar, 50).Value = txtLastName.Text

        cmd.CommandType = CommandType.StoredProcedure

        Try
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox("Echec de l'insertion !")
        Finally
            fCnDB() 'close database
        End Try

        End Sub

Thanks to NickUpson and to phuocnh for helping
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

873 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