Results 1 to 7 of 7

Thread: [RESOLVED] Inserting record into Oracle via code

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Apr 2006
    Posts
    16

    Resolved [RESOLVED] Inserting record into Oracle via code

    Hello,
    I am using VB.NET 2005 Express and am able to connect and retrieve data from the table but my insert code does not work.

    I get no error messages but neither does my data appear in the table, any ideas?

    VB Code:
    1. Dim strSQL As String = "SELECT * FROM QA_SCRAP ORDER BY CONTROL_NO"
    2.         Dim connectionString As String
    3.         connectionString = "User Id=*;Password=*;Data Source=*;"
    4.         Dim connection As New OracleConnection(connectionString)
    5.         Dim insert As New OracleCommand(strSQL, connection)
    6.         insert.CommandType = CommandType.Text
    7.  
    8.         Try
    9.             strSQL = "INSERT INTO QA_SCRAP(SCRAP_DATE, SHIFT, PART_NUMBER, PART_DESCRIPTION, DEFECT_CODE, MODEL,"
    10.             strSQL &= "SCRAP_QTY, DETAILS_OF_DEFECT, FOUND_ON_LINE, CAUSED_ON_LINE,STATION_ID, ASSOCIATE_NAME)"
    11.             strSQL &= "VALUES (TO_DATE('" & lblDate.Text & "', 'MM/DD/YYYY:HH24:MI:SS'), '" & cbbShift.Text & "',"
    12.             strSQL &= "'" & cbbPartNum.Text & "', '" & cbbPartName.Text & "', '" & cbbCode.Text
    13.             strSQL &= "','" & cbbModel.Text & "', " & txtQuantity.Text & ",'" & txtDefectDescription.Text
    14.             strSQL &= "', '" & cbbFound.Text & "', '" & cbbCaused.Text & "', '" & cbbStation.Text
    15.             strSQL &= "','" & txtName.Text & "')"
    16.         Catch ex As Exception
    17.             MsgBox("Error Creating String.", MsgBoxStyle.Critical = MsgBoxStyle.OkOnly, "Error Caught, Contact SIG with Error Message.")
    18.         End Try
    19.  
    20.         Try
    21.             connection.Open()
    22.             insert.ExecuteNonQuery()
    23.         Catch
    24.             MsgBox("Error Saving Information.", MsgBoxStyle.Critical = MsgBoxStyle.OkOnly, "Error Caught, Contact SIG with Error Message.")
    25.         Finally
    26.             connection.Close()
    27.             connection.Dispose()
    28.             connection = Nothing
    29.         End Try

    DS

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,501

    Re: Inserting record into Oracle via code

    I don't use .Net , but shouldn't you set up strSQL before passing it to the declaration of insert?

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Apr 2006
    Posts
    16

    Re: Inserting record into Oracle via code

    I tried that and now I get this error:

    "A first chance exception of type 'System.Data.OracleClient.OracleException' occurred in System.Data.OracleClient.dll"

    DS

  4. #4
    Frenzied Member litlewiki's Avatar
    Join Date
    Dec 2005
    Location
    Zeta Reticuli Distro:Ubuntu Fiesty
    Posts
    1,162

    Re: Inserting record into Oracle via code

    http://www.dotnet247.com/247referenc...Exception.aspx

    if this can help it says u need a hotfix
    __________________
    ________________0___
    ___0________(___)____
    __(___)_________) _/_____
    ___\_ (_________(_/______
    ____\_)_________________

  5. #5

    Thread Starter
    Junior Member
    Join Date
    Apr 2006
    Posts
    16

    Re: Inserting record into Oracle via code

    Thanks for the help but I got it working, I am now able to read and write from the Oracle table using a dataset and dataadapter.

    DS

  6. #6
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,501

    Re: [RESOLVED] Inserting record into Oracle via code

    Thanks for marking this as resolved.

    For the benefit of others with the same problem, could you tell us what you did (or what code you used) to fix it?

  7. #7

    Thread Starter
    Junior Member
    Join Date
    Apr 2006
    Posts
    16

    Re: [RESOLVED] Inserting record into Oracle via code

    Sorry it took me so long to put this up but here is the solution:

    This assumes an Oracle Table called TABLE1 with 1 column called First Name trying to insert from a TextBox named txtFirstName.

    VB Code:
    1. Dim connectionString As String = "User Id=*;Password=*;Data Source=*;"
    2. Dim connection As New OracleConnection(connectionString)
    3. Dim strSelect As String = "SELECT * FROM TABLE1"
    4. Dim strInsert As String = ""
    5. Dim strLabel As String = ""
    6. Dim myDataSet As New DataSet '' create the dataset object
    7. Dim myDataAdapter As OracleDataAdapter '' create the DataAdapter
    8. Dim command As OracleCommand = New OracleCommand
    9. Dim myDataRow As DataRow
    10. Dim cmdInsert As New OracleCommand(strInsert, connection)
    11. command.CommandText = strSelect '' set the command text
    12. command.Connection = connection
    13. myDataAdapter = New OracleDataAdapter(command)
    14. myDataAdapter.Fill(myDataSet)
    15. connection.Open()
    16.  
    17. Try
    18.             myDataRow = myDataSet.Tables(0).NewRow()
    19.             myDataRow("FIRST_NAME") = getDate()
    20.             myDataSet.Tables(0).Rows.Add(myDataRow)
    21.  
    22.             strInsert = "INSERT INTO TABLE1 NAME VALUE =" & txtFirstName.text
    23.  
    24.             myDataAdapter.InsertCommand = cmdInsert
    25.             cmdInsert.CommandText() = strInsert
    26.             myDataAdapter.Update(myDataSet)
    27.  
    28. Catch ex As Exception
    29.            
    30. Finally
    31.             connection.Close()
    32.             myDataSet.Dispose()
    33.             myDataAdapter.Dispose()
    34.             myDataSet = Nothing
    35.             myDataAdapter = Nothing
    36. End Try

    Hope this helps.

    DS

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width