Topic
4 replies Latest Post - ‏2008-08-01T11:19:42Z by SystemAdmin
SystemAdmin
SystemAdmin
2826 Posts
ACCEPTED ANSWER

Pinned topic Call a DB2 store procedure from VB.net

‏2008-07-31T18:26:53Z |
Hi all,
I'm a new DB2. I have some problems with my code
I created a store procedure(name INSERT_BOOK)in DB2 and now I want call it from VB.net
This is my store procedure in workbanch:
*************************************
CREATE PROCEDURE insert_book ( IN NAME VARCHAR(50),
IN TITTLE VARCHAR(5),
IN ROOM VARCHAR(4),
IN CHECKINDATE DATE,
IN CHECKOUTDATE DATE )
DYNAMIC RESULT SETS 1
<hr />
  • SQL Stored Procedure
-- NAME
-- TITTLE
-- ROOM
-- CHECKINDATE
-- CHECKOUTDATE<hr />
P1: BEGIN

INSERT INTO RESERVATION ("NAME(FULL_NAME)", TITLE, ROOM, CHECK_IN_DATE,
CHECK_OUT_DATE)
VALUES (NAME, TITTLE, insert_book2.ROOM, CHECKINDATE, CHECKOUTDATE);
END P1

*******************************

This is my code in VB.net
Public Sub book()
Dim conn As DB2Connection = New DB2Connection("database=QLKS")
Dim cmd As New DB2Command("INSERT_BOOK", conn)
Dim parm As DB2Parameter
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = procName1
parm = cmd.Parameters.Add("@NAME", DB2Type.VarChar)
parm.Direction = ParameterDirection.Input
parm.Value = fullname
parm = cmd.Parameters.Add("@TITTLE", DB2Type.VarChar)
parm.Direction = ParameterDirection.Input
parm.Value = tittle
parm = cmd.Parameters.Add("@ROOM", DB2Type.VarChar)
parm.Direction = ParameterDirection.Input
parm.Value = room
parm = cmd.Parameters.Add("@CHECKINDATE", DB2Type.Date)
parm.Direction = ParameterDirection.Input
parm.Value = checkindate
parm = cmd.Parameters.Add("CHECKOUTDATE", DB2Type.Date)
parm.Direction = ParameterDirection.Input
parm.Value = checkoutdate
End Sub

I need your help to solve it.
Thank you very much!!
Updated on 2008-08-01T11:19:42Z at 2008-08-01T11:19:42Z by SystemAdmin
  • kanchana
    kanchana
    147 Posts
    ACCEPTED ANSWER

    Re: Call a DB2 store procedure from VB.net

    ‏2008-07-31T18:48:16Z  in response to SystemAdmin
    What is the error you are seeing? I can see a number of things in your code that needs rework.
    Depending on what error you are seeing, I will reply.
  • SystemAdmin
    SystemAdmin
    2826 Posts
    ACCEPTED ANSWER

    Re: Call a DB2 store procedure from VB.net

    ‏2008-08-01T04:00:33Z  in response to SystemAdmin
    I'm sorry, it's my mistakes.
    ***********************************************************
    My store procedure
    CREATE PROCEDURE insert_book ( IN NAME VARCHAR(50),
    IN TITTLE VARCHAR(5),
    IN ROOM VARCHAR(4),
    IN CHECKINDATE DATE,
    IN CHECKOUTDATE DATE )
    DYNAMIC RESULT SETS 1
    <hr />

    SQL Stored Procedure
    • NAME
    • TITTLE
    • ROOM
    • CHECKINDATE
    • CHECKOUTDATE<hr />
    P1: BEGIN
    INSERT INTO RESERVATION ("NAME(FULL_NAME)", TITLE, ROOM, CHECK_IN_DATE,
    CHECK_OUT_DATE)
    VALUES (NAME, TITTLE, insert_book2.ROOM, CHECKINDATE, CHECKOUTDATE);
    END P1

    **************************************************

    This is my code,i've just fixed but it have mistakes

    ****************************************************************
    Imports IBM.Data.DB2
    Imports IBM.Data.DB2.DB2Command
    Imports IBM.Data.DB2Types
    Imports Microsoft.VisualBasic
    Imports System.Data.SqlClient
    Imports System.Data
    '-----------------------------------
    Public Class frmbook
    Public fullname As String
    Public tittle As String
    Public room As String
    Public checkindate As Date
    Public checkoutdate As Date
    '--------------------------------------------------------------------------
    Private Sub frmbook_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    'TODO: This line of code loads data into the 'QLKSDataSet.SELECT_ROOM_AVAILABLE1' table. You can move, or remove it, as needed.
    Me.SELECT_ROOM_AVAILABLE1TableAdapter.Fill(Me.QLKSDataSet.SELECT_ROOM_AVAILABLE1)
    End Sub
    '-------------------------------------------------------------------------
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    tittle = cmbtittle.Text
    Name = txtname.Text
    'birthday = txtbirth.Text
    room = SELECT_ROOM_AVAILABLE1ComboBox.Text
    checkindate = txtcin.Text
    checkoutdate = txtcout.Text
    Dim conn As DB2Connection = New DB2Connection("database=QLKS")
    'Dim procName1 As String = "INSERT_BOOK"
    'Dim procName2 As String = "INSERT_BOOK2"
    Dim cmd As New DB2Command
    Dim parm1 As DB2Parameter
    Dim parm2 As DB2Parameter
    Dim parm3 As DB2Parameter
    Dim parm4 As DB2Parameter
    Dim parm5 As DB2Parameter
    conn.Open()
    cmd.CommandType = Data.CommandType.Text
    cmd.CommandText = "call INSERT_BOOK2(@NAME,@TITTLE,@ROOM,@CHECKINDATE,@CHECKOUTDATE)"
    parm1 = New DB2Parameter("@NAME", DB2Type.VarChar, 20, ParameterDirection.Input, True, 0, 0, "NAME", DataRowVersion.Current, "")
    parm2 = New DB2Parameter("@TITTLE", DB2Type.VarChar, 4, ParameterDirection.Input, True, 0, 0, "TITTLE", DataRowVersion.Current, "")
    parm3 = New DB2Parameter("@ROOM", DB2Type.VarChar, 4, ParameterDirection.Input, True, 0, 0, "RO0M", DataRowVersion.Current, "")
    parm4 = New DB2Parameter("@CHECKINDATE", DB2Type.Date, 11, ParameterDirection.Input, True, 0, 0, "CHECKINDATE", DataRowVersion.Current, "")
    parm5 = New DB2Parameter("@CHECKOUTDATE", DB2Type.Date, 11, ParameterDirection.Input, True, 0, 0, "CHECKOUTDATE", DataRowVersion.Current, "")
    cmd.Parameters.Add(parm1)
    cmd.Parameters.Add(parm2)
    cmd.Parameters.Add(parm3)
    cmd.Parameters.Add(parm4)
    cmd.Parameters.Add(parm5)
    cmd.ExecuteNonQuery()
    conn.Close()
    End Sub
    End Class
    **********************************************************
    This is my problem

    IMGhttp://i49.photobucket.com/albums/f266/vanlethanh/DB2.jpg[/IMG]

    Thanks for your reply!!!
    Thanks a lots
  • kanchana
    kanchana
    147 Posts
    ACCEPTED ANSWER

    Re: Call a DB2 store procedure from VB.net

    ‏2008-08-01T04:14:26Z  in response to SystemAdmin
    You need to set cmd.Connection = conn before calling ExecuteNonQuery.
  • SystemAdmin
    SystemAdmin
    2826 Posts
    ACCEPTED ANSWER

    Re: Call a DB2 store procedure from VB.net

    ‏2008-08-01T11:19:42Z  in response to SystemAdmin
    Thankyou for your help!
    But when I set cmd.connection=conn, I have a new problem. I think this is a date type mistake. In DB2 is YYYY/MM/DD, but in VB.net, it has some differences.
    I don't know how to solve it.
    My new problem:
    IMGhttp://i49.photobucket.com/albums/f266/vanlethanh/DB21.jpg[/IMG]

    Thanks for your reading!