Insert sequence

Question

User475570419 posted

Hello,

I need to add a sequence statement to the following:

      Dim sql = "Insert into case_notes (CASE_NOTES_ID, CASE_NOTES_TEXT, CASE_NOTES_TYPE_CODE, CONTACT_DATE, ENTRY_DATE, JMS_NUMBER, LAST_UPDATE, POD_CKEY, UOA_CKEY, USER_ID) " & " VALUES " & " ('" & Case_Notes_IDTxt.Text & "','" & CaseNoteTypeCodeTxt.Text & "','" & CaseNotesTextTxt.Text & "','" & ContactDateTxt.Text & "','" & EntryDateTxt.Text & "','" & Docket_NumberTxt.Text & "','" & PODCKeyTxt.Text & "','" & UOACkeyTxt.Text & "','" & UserIdTxt.Text & "')"
        Dim Conn As New OleDbConnection(StrConn)
        'Dim objdr As OleDbDataReader
        Dim cmd As New OleDbCommand(sql, Conn)
        Conn.ConnectionString = StrConn
        Conn.Open()
        With cmd
            .Connection = Conn
            .CommandText = Sql
            .CommandType = CommandType.Text
        End With

        Try
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            ErrorLbl.Text = ex.Message
        End Try

I am not having any luck.  Here is the sequence statemnt:

insert case_notes_id_seq.nextval from dual;    

Any help would as always, be appreciated.

 

Bob

Answer 1

User269602965 posted

What database is this going into??

For the VALUES() section of INSERT statement.

Use case_notes_id_seq.nextval for the inserted key value,

and use BIND variables and database PARAMETERS collection to insert the Form Data.

Database will not know what UOACkeyTxt.Text is.  The parameter statement will also allow you to define to the database the BIND variable name, the datatype of the bind variable, and as required the direction (IN or OUT) when using SQL within a PL/SQL procedure.

/* This example is for Oracle, so if another database,               */
/* the punction before the BIND VARIABLE may be different            */
/* and defining the datatype must be modified for the other database */

Imports System.Xml.Linq.XElement

Try
  Dim SQL =
  <SQL>
    INSERT INTO case_notes 
      (CASE_NOTES_ID, CASE_NOTES_TEXT)
    VALUES
      (Case_Notes_Id_Seq.NEXTVAL, :CaseNoteTypeCodeTxt) 
  </SQL>
  Using conn As New OracleConnection(connectionString)
    Using cmd As New OracleCommand(SQL.Value, conn)
      cmd.Parameters.Clear()
      cmd.Parameters.Add("CaseNoteTypeCodeTxt", OracleDbType.Varchar2, CaseNoteTypeCodeTxt.Text, ParameterDirection.Input)
      conn.Open()
      cmd.ExecuteNonQuery()
    End Using
  End Using
Catch ex As Exception
  ErrorLbl.Text = ex.Message
End Try

Answer 2

User475570419 posted

It is going into an Oracle 11g.  I have very little Oracle skills versus MS SQL.  I hope that answered your question. This is a ASP >net page not a form.  Maybe that changes what I need to do. 

Answer 3

User475570419 posted

The UOA_CKEY I am putting a static value in.

Answer 4

User475570419 posted

This is my code follwoing your example Lannie:

 Try
            Dim sql = "INSERT INTO case_notes(Case_Notes_ID, JMS_NUMBER, CASE_NOTES_TYPE_CODE, UOA_CKEY, POD_CKEY, USER_ID, CONTACT_DATE, ENTRY_DATE, CASE_NOTES_TEXT)  VALUES (CASE_NOTES_ID_SEQ.NEXTVAL, :JMS_NUMBER, :CASE_NOTES_TYPE_CODE, :UOA_CKEY, :POD_CKEY, :USER_ID, :CONTACT_DATE, :ENTRY_DATE, :CASE_NOTES_TEXT)"
            Using conn As New OleDbConnection("Provider=MSDAORA.1;user id=****;password=****;data source=***")
                Using cmd As New OleDbCommand(sql, conn)
                    cmd.Parameters.Clear()
                    cmd.Parameters.Add("Docket_NumberTxt", SqlDbType.NChar, Docket_NumberTxt.Text, ParameterDirection.Input)
                    cmd.Parameters.Add("Case_Notes_Type_CodeTxt", OleDbType.Variant, CaseNoteTypeCodeTxt.Text, ParameterDirection.Input)
                    cmd.Parameters.Add("UOA_CkeyTxt", SqlDbType.VarChar, UOACkeyTxt.Text, ParameterDirection.Input)
                    cmd.Parameters.Add("POD_CKeyTxt", SqlDbType.NChar, PODCKeyTxt.Text, ParameterDirection.Input)
                    cmd.Parameters.Add("User_Id", SqlDbType.VarChar, UserIdTxt.Text, ParameterDirection.Input)
                    cmd.Parameters.Add("Contact_Date", SqlDbType.DateTime, ContactDateTxt.Text, ParameterDirection.Input)
                    cmd.Parameters.Add("Entry_Date", SqlDbType.DateTime, EntryDateTxt.Text, ParameterDirection.Input)
                    cmd.Parameters.Add("Case_Notes_Text", SqlDbType.VarChar, CaseNotesTextTxt.Text, ParameterDirection.Input)
                    conn.Open()
                    cmd.ExecuteNonQuery()
                End Using
            End Using
        Catch ex As Exception
            ErrorLbl.Text = ex.Message
        End Try

I am having issues with the DB type to the Oracle. I am going to list the Oracle side and can you see what I did wrong please?

Case_notes_ID is Number(10)

JMS_Number Number(10)

Case_notes_type_Code Varchar2 (5 Byte)

UOA_CKEY Number (10)

POD_CKEY Number (10)

User_id VarChar2(15 Byte)

Contact_Date Date

Entry_Date Date

Case_Notes_Text Varchar2(4000 Byte)

I have a feeling it is the SQLDBTYPE is wrong for my code on the VB Side. If I comment one line at a time for the add parameter, I get basically cannot convert string to integer.

Answer 5

User269602965 posted

You need to use OracleDBType, not SQLDBType

 
.NET     ODP.NET                 Oracle database
 
String     OracleDbType.Varchar2   VARCHAR2 
DateTime OracleDbType.Date       DATE
Int64     OracleDbType.Int64      NUMBER(19)
Int32    OracleDbType.Int32      NUMBER(10)
Int16    OracleDbType.Int16      NUMBER(5)
Decimal  OracleDbType.Decimal    NUMBER(x,y)
Float    OracleDbType.Single     NUMBER(x,y)
Double   OracleDbType.Double     NUMBER(x,y)
Byte     OracleDbType.Blob       BLOB
 
 

Answer 6

User269602965 posted

Also,

I have found that whether you are sending interger or decimal/double to Oracle,

I just use Oracle.DBType.Decimal.

 

Answer 7

User475570419 posted

Lannie,

I cannot choose the Oracledbtype or any Oracle referrence you referred to.  I get choose OleDb, SqlDbtype,.....

Answer 8

User269602965 posted

Use OracleDataAccessClient ODAC, ODP.NET, instead of OLEDB.

ODP.NET with ODAC driver is faster than ODBC or OLEDB (older technologies).

ODP>NET works with ASP.NET40/AJAX and WPF Forms.

My examples are ODP.NET code.

Get the latest ODP.NET installer, and when prompted select CLIENT INSTALL, not DATABASE INSTALL.

ODP.NET is already installed on your 11g database. (look for the Oracle HOME/ODP.NET directory.

If the database and client install are on the same workstation/server,

then install the CLIENT in a new Oracle HOME.

http://download.oracle.com/docs/cd/E11882_01/win.112/e18754.pdf

 

 

Answer 9

User475570419 posted

Lannie,

I have the ODP.Net installed. I am getting the uderscore squiggly line under OracleDBConnction.  It suggests OleDbConnection, Odbc.Odbc.Connection or OleDb.OleDBConnection.  I did put the Import that you suggested.  I am lost.

        Try
 
            Dim Sql = "Insert INTO CASE_NOTES (Case_Notes_Id, JMS_Number, Case_Notes_Type_code, UOA_CKey, POD_Ckey, User_Id, Contact_Date, Entry_Date, Last_Update, Case_Notes_Text) Values (Case_Notes_ID_Seq.NextVal, Docket_NumberTxt, CaseNoteTypeCodeTxt,UOA_CkeyTxt, POD_CkeyTxt, User_IDTxt, ContactDateTxt, EntryDateTxt, LastUpdateTxt, CaseNotesTextTxt)"
            Using Conn As New OracleConnection("data source=xxx;user id=xxxx;password=xxx;")
                Using Cmd As New OracleCOmmand(Sql.Value, Conn)
                    Cmd.Parameters.Clear()
                    Cmd.Parameters.Add("Docket_NumberTxt", OracleDBType.Int32, Docket_NumberTxt.Text, ParameterDirection.Input)
                    Cmd.Parameters.Add("CaseNoteTypeCodeTxt", OracleDBType.VarChar2, CaseNoteTypeCodeTxt.Text, ParameterDirection.Input)
                    Cmd.Parameters.Add("UOA_CkeyTxt", OracleDBType.Int32, UOA_CkeyTxt.Text, ParameterDirection.Input)
                    Cmd.Parameters.Add("POD_CkeyTxt", OracleDBType.Int32, POD_CkeyTxt.Text, ParameterDirection.Input)
                    Cmd.Parameters.Add("User_Id", OracleDBType.Varchar2, User_IdTxt.Text, ParameterDirection.Input)
                    Cmd.Parameters.Add("Contact_Date", OracleDBType.Date, ContactDateTxt.Text, ParameterDirection.Input)
                    Cmd.Parameters.Add("Entry_Date", OracleDBtype.Date, EntryDateTxt.Text, ParameterDirection.Input)
                    Cmd.Parameters.Add("Last_Update", OracleDBType.Date, LastUpDateTxt.Text, ParameterDirection.Input)
                    Cmd.Parameters.Add("Case_Notes_Text", OracleDBType.Varchar2, CaseNotesTextTxt.Text, ParameterDirection.Input)
                    Conn.Open()
                    Cmd.ExecuteNonQuery()
                End Using
            End Using
        Catch ex As Exception
            ErrorLbl.Text = ex.Message
        End Try
 
    End Sub

Thank you,

Bob

Answer 10

User269602965 posted

Show your connection string from web.config or your application config, and any other Oracle configurations you have made in the config file.

That way the connection string is stored in ONE place and you do not need to expose your datasource, etc. in code.

Then before the TRY you can call the web.cofig with

    Dim OraConnStr As String = ConfigurationManager.ConnectionStrings("{YourOraConnStrName}").ConnectionString

      Try
 
            Dim Sql = "Insert INTO CASE_NOTES (Case_Notes_Id, JMS_Number, Case_Notes_Type_code, UOA_CKey, POD_Ckey, User_Id, Contact_Date, Entry_Date, Last_Update, Case_Notes_Text) Values (Case_Notes_ID_Seq.NextVal, Docket_NumberTxt, CaseNoteTypeCodeTxt,UOA_CkeyTxt, POD_CkeyTxt, User_IDTxt, ContactDateTxt, EntryDateTxt, LastUpdateTxt, CaseNotesTextTxt)"
            Using Conn As New OracleConnection(OraConnStr)

Answer 11

User269602965 posted

  Imports System.Xml.Linq.XElement
  
    Public Shared Sub updateUnitsActiveFlag(ByVal decQuantity As Decimal)
      ' Insert Quantity into new row Units table'
      Dim OraConnStr As String = ConfigurationManager.ConnectionStrings("{YourOraConnStrName}").ConnectionString
      Try
        Dim SQL =
        <SQL>
        INSERT INTO {YOURSCHEMANAME}.UNITS
          (UNITS_SEQ, QUANTITY) 
        VALUES 
          (UNIT_SEQ.NextVal, :BindVarQuantity)
        </SQL>
        Using conn As New OracleConnection(OraConnStr)
          Using cmd As New OracleCommand(SQL.Value, conn)
            cmd.Parameters.Clear()
            cmd.Parameters.Add("BindVarQuantity", OracleDbType.Decimal, decQuantity, ParameterDirection.Input)
            conn.Open()
            cmd.ExecuteNonQuery()
          End Using
        End Using
      Catch ex As Exception
      End Try

Answer 12

User475570419 posted

I was able to get your code to somewhat work.  I had to add the Oracle ODP .Net in the Start Options > References.  Once I did that, I was able to use the Oracle commands you suggested. 

Answer 13

User475570419 posted

Lannie,

I want to thank you so much for your help.  I got this to work.  It is nice to see people who still are nice enough to help us novices out.   Have a great day and thank you so much again for your help.  I have learned a lot.

 

Bob