Insert issues, will someone look and help? 1

[Stegmite]

This is my code to insert a record into an Oracle 10gDB, can someone point me on the right track of what to do?

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
OracleDataAdapter1.Fill(dsUsers)
OracleConnection1.Open()
End Sub

Private Sub cmdSubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSubmit.Click
Dim vUserID As Integer
Dim vUserName As String
Dim vUserPass As String
Dim myInsertQuery As String = "INSERT INTO Users (User_ID, Username, Password) VALUES ('vUserID', '" & vUserName & "', '" & vUserPass & "')"
Dim myOracleCommand As New OracleClient.OracleCommand(myInsertQuery, OracleConnection1)

vUserID = dsUsers.USERS.Rows.Count()
vUserID = vUserID + 1
vUserName = txtUserName.Text
vUserPass = txtPass1.Text

myOracleCommand.ExecuteNonQuery()
OracleConnection1.Close()

'Response.Redirect("pc_admin.aspx")

End Sub

 

[andrea96]

I think you should set the values for your variables (vUserID, vUserName, and vUserPass) before you build your query string.

Andrea

 

[Turpis]

Try

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        [b]OracleConnection1.Open()[/b]
        OracleDataAdapter1.Fill(dsUsers)
        [b]OracleConnection1.Close()[/b]
End Sub

Private Sub cmdSubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSubmit.Click
        Dim vUserID As Integer
        Dim vUserName As String
        Dim vUserPass As String
        Dim myInsertQuery As String = "INSERT INTO Users (User_ID, Username, Password) VALUES ('vUserID', '" & vUserName & "', '" & vUserPass & "')"
        Dim myOracleCommand As New OracleClient.OracleCommand(myInsertQuery, OracleConnection1)

        vUserID = dsUsers.USERS.Rows.Count()
        vUserID = vUserID + 1
        vUserName = txtUserName.Text
        vUserPass = txtPass1.Text

        [b]OracleConnection1.Open()[/b]
        myOracleCommand.ExecuteNonQuery()
        OracleConnection1.Close()

        'Response.Redirect("pc_admin.aspx")

End Sub

Charles
Quality Assurance/Developer

 

[Stegmite]

Like this?:
Dim vUserID As Integer
Dim vUserName As String
Dim vUserPass As String
Dim myInsertQuery As String
Dim myOracleCommand As New OracleClient.OracleCommand(myInsertQuery, OracleConnection1)

vUserID = dsUsers.USERS.Rows.Count()
vUserID = vUserID + 1
vUserName = txtUserName.Text
vUserPass = txtPass1.Text

myInsertQuery = "INSERT INTO Users (User_ID, Username, Password) VALUES ('vUserID', '" & vUserName & "', '" & vUserPass & "')"
myOracleCommand.ExecuteNonQuery()

 

[Stegmite]

Now its saying "ORA-00942 - table or view does not exist"

That table exists, could my SQL statement be the problem?

 

[andrea96]

Try this.

Dim vUserID As Integer
        Dim vUserName As String
        Dim vUserPass As String
        Dim myInsertQuery As String
        [b]Dim myOracleCommand As New OracleClient.OracleCommand()[/b]

        vUserID = dsUsers.USERS.Rows.Count()
        vUserID = vUserID + 1
        vUserName = txtUserName.Text
        vUserPass = txtPass1.Text

        myInsertQuery = "INSERT INTO Users (User_ID, Username, Password) VALUES ('vUserID', '" & vUserName & "', '" & vUserPass & "')"
        [b]myOracleCommand As New OracleClient.OracleCommand(myInsertQuery, OracleConnection1)[/b]

        myOracleCommand.ExecuteNonQuery()

Also, does the SQL statement created run outside of the program? Or does it get the same error?

 

[Stegmite]

There's an error at:
myOracleCommand As New....

It says "Expression is not a method

 

[andrea96]

I'm sorry. It should have been.

Dim myOracleCommand As OracleClient.OracleCommand
...
myOracleCommand = New OracleClient.OracleCommand(myInsertQuery, OracleConnection1)