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

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...

Code:

---

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:

Code:

---

    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

That would be because you're calling Initialize, and therefore setting the parameter values, before the user has entered any data. You have to wait until the user has entered data into the TextBoxes until you can get that data in code and use it to set the parameter values.

Also, you should NOT be using AddWithValue with a DataAdapter. With a DataAdapter the parameter values are supposed to come from the DataTable, not be set explicitly by you. Take a look at this.

hi,

after reading your comments i changed my code to

vb Code:

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.Clear()
            .Parameters.Add("p_user_num", OracleType.Number).Direction = ParameterDirection.Output
            .Parameters.Add(":p_username", OracleType.VarChar, 30, "p_username")
            .Parameters.Add(":p_password", OracleType.VarChar, 30, "p_password")
            .Parameters.Add(":p_title", OracleType.VarChar, 10, "p_title")
            .Parameters.Add(":p_last_name", OracleType.VarChar, 30, "p_last_name")
            .Parameters.Add(":p_first_name", OracleType.VarChar, 30, "p_first_name")
            .Parameters.Add(":p_middle_name", OracleType.VarChar, 30, "p_middle_name")
            .Parameters.Add(":p_email_address", OracleType.VarChar, 100, "p_email_address")
            .Parameters.Add(":p_gender", OracleType.VarChar, 1, "p_gender")
            .Parameters.Add(":p_date_of_birth", OracleType.DateTime, 30, "p_date_of_birth")
            .Parameters.Add(":p_description", OracleType.VarChar, 250, "p_description")
 
        End With
 
        With da
            .SelectCommand = selCmd
            .InsertCommand = insCmd
        End With
 
    End Sub

i also moved the sub Initialize after the opening the connection... not sure if thats what you wanted me to do...

vb Code:

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

i'm getting a different error now "PLS-00306: wrong number or types of arguments in call to 'create_user'".. the error seems to say that i'm not giving enough values..

i think the problem might be the srcColumn i'm passing when adding the parameter... not sure what to put there.

can someone help me?

thanks.

never mind.. i figured out what the problem was.. :D