[RESOLVED] [2005] need help in using stored proc for insert command

[adshocker]

hi all,

first of, here's what i'm using.

Visual Basic 2005 Express Edition
Oracle Database 10g Express Edition

i created a procedure stored in the oracle database create_user and accepts a few parameters...

procedure create_user 
( p_user_num        out tbl_users.user_num%type
 ,p_username         in tbl_users.username%type
 ,p_password         in tbl_users.encrypted_password%type
 ,p_title            in tbl_users.title%type
 ,p_last_name        in tbl_users.last_name%type
 ,p_first_name       in tbl_users.first_name%type
 ,p_middle_name      in tbl_users.middle_name%type
 ,p_email_address    in tbl_users.email_address%type
 ,p_gender           in tbl_users.gender%type
 ,p_date_of_birth    in tbl_users.date_of_birth%type
 ,p_description      in tbl_users.description%type);

in my visual basic project, i manually created a dataset and a datatable for the tbl_users table using the dataset designer since express edition doesn't support oracle for its dataset wizard.

anyways, in my form i have a BindingNavigator and a BindingSource..

and the following codes:

    Private da As New OracleDataAdapter
    Private selCmd As New OracleCommand
    Private updCmd As New OracleCommand
    Private delCmd As New OracleCommand
    Private insCmd As New OracleCommand
    Private dbconn As New OracleConnection

    Private Sub Initialize()
        With selCmd
            .CommandText = "select * from tbl_users where 1 = 1"
            .CommandType = CommandType.Text
            .Connection = dbconn
        End With

        With insCmd
            .CommandText = "create_user"
            .CommandType = CommandType.StoredProcedure
            .Connection = dbconn
            .Parameters.Add("p_user_num", OracleType.Number).Direction = ParameterDirection.Output
            .Parameters.AddWithValue("p_username", Me.UsernameTextBox.Text)
            .Parameters.AddWithValue("p_password", Me.PasswordTextBox.Text)
            .Parameters.AddWithValue("p_title", Me.TitleTextBox.Text)
            .Parameters.AddWithValue("p_last_name", Me.Last_nameTextBox.Text)
            .Parameters.AddWithValue("p_first_name", Me.First_nameTextBox.Text)
            .Parameters.AddWithValue("p_middle_name", Me.Middle_nameTextBox.Text)
            .Parameters.AddWithValue("p_email_address", Me.Email_addressTextBox.Text)
            .Parameters.AddWithValue("p_gender", Me.GenderTextBox.Text)
            .Parameters.AddWithValue("p_date_of_birth", Me.Date_of_birthDateTimeTextBox.Text)
            .Parameters.AddWithValue("p_description", Me.DescriptionTextBox.Text)
        End With

        With da
            .SelectCommand = selCmd
            .InsertCommand = insCmd
        End With

    End Sub

    Private Sub frmUser_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Initialize()
        Dim dbstring As New OracleConnectionStringBuilder
        With dbstring
            .DataSource = "xe"
            .UserID = "dev"
            .Password = "password"
        End With
        dbconn.ConnectionString = dbstring.ConnectionString
        dbconn.Open()
    End Sub

    Private Sub SaveToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SaveToolStripButton.Click
        Me.Validate()
        Me.Tbl_usersBindingSource.EndEdit()
        Me.da.Update(Me.DsUser.tbl_users)
    End Sub

so i run the form, click the AddNew button in the BindingNavigator, enter the data in the textboxes and then click Save. i receive no error but the values of the textboxes are not being passed to my stored procedure. it is creating records but all columns are null.

i checked this by using an SQL Editor to query the table back-end.

can anyone help?

thanks