[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

[jmcilhinney]

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.

[adshocker]

hi,

after reading your comments i changed my code to

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

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.

[adshocker]

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