Page 3 of 3 FirstFirst 123
Results 81 to 104 of 104

Thread: Retrieving and Saving Data in Databases

  1. #81
    Lively Member
    Join Date
    Oct 2011
    Posts
    72

    Re: Retrieving and Saving Data in Databases

    If I have a dgv with some rows being new requiring an Insert query and some requiring an update and others requiring deletion

    Is it possible to use insert, update and delete commands all in the same code? How could this be done?

  2. #82

    Thread Starter
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Retrieving and Saving Data in Databases

    Quote Originally Posted by AC1982 View Post
    If I have a dgv with some rows being new requiring an Insert query and some requiring an update and others requiring deletion

    Is it possible to use insert, update and delete commands all in the same code? How could this be done?
    Did you even bother to read my posts in this thread?

  3. #83
    Lively Member
    Join Date
    Oct 2011
    Posts
    72

    Re: Retrieving and Saving Data in Databases

    Quote Originally Posted by jmcilhinney View Post
    Did you even bother to read my posts in this thread?
    I have read the posts and I tried implementing the code but I came up with an error. It was due to a mistake in my Update sql query which I have now fixed. Thanks for creating this thread and writing about ado.net parameters on your blog the information has proven very helpful.
    Last edited by AC1982; Jan 23rd, 2012 at 08:46 PM. Reason: Error in my SQL Statement

  4. #84
    ASP.NET Moderator gep13's Avatar
    Join Date
    Nov 2004
    Location
    The Granite City
    Posts
    21,963

    Re: Retrieving and Saving Data in Databases

    Quote Originally Posted by AC1982 View Post
    I have read the posts and I tried implementing the code but I came up with an error. It was due to a mistake in my Update sql query which I have now fixed. Thanks for creating this thread and writing about ado.net parameters on your blog the information has proven very helpful.
    For future reference, if you are having a particular problem with a piece of code, it would make sense to ask a question about it in the main forum, rather than in the CodeBank, which is intended for code samples. By all means reference a thread like this to say that you are using this code, but posting it in the main forum means that everyone who visits the forum are more likely to see it, and you will get more responses. In addition, if you are getting an error/exception in your code, remember to tell people about that in your post, including the stack trace. This is how people will be able to help you.

    Gary

  5. #85
    Hyperactive Member DavesChillaxin's Avatar
    Join Date
    Mar 2011
    Location
    WNY
    Posts
    451

    Re: Retrieving and Saving Data in Databases

    Quote Originally Posted by venuspcs View Post
    Okay you where so write about the date....I renamed all my fields in the database and in my code to mmddyy and it started working, well sort of....it is writing to the database now but it is not filling the database with the information from the form, instead all my fields say either: "System.Windows.Forms", "System.Win", or "System.Windows.Forms.TextBox,Text:"

    WTH - Here is the complete revised code:

    Code:
        Private Sub Submit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Submit.Click
    
            Dim connection As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='C:\Users\Richard Colbert\Documents\Visual Studio 2008\Projects\WindowsApplication1\WindowsApplication1\shenandoah.accdb'")
            Dim adapter As New OleDb.OleDbDataAdapter("SELECT ID, mmddyy, Driver, Truck, Customer, Rate, Where_From, Where_To, Pit_Ticket, PO_Number, Yards_Tons, Description, QP_Fee, Fuel, Expenses, Advances FROM Shenandoahs", connection)
            Dim insert As New OleDb.OleDbCommand("INSERT INTO Shenandoahs (mmddyy, Driver, Truck, Customer, Rate, Where_From, Where_To, Pit_Ticket, PO_Number, Yards_Tons, Description, QP_Fee, Fuel, Expenses, Advances) VALUES (@mmddyy, @Driver, @Truck, @Customer, @Rate, @Where_From, @Where_To, @Pit_Ticket, @PO_Number, @Yards_Tons, @Description, @QP_Fee, @Fuel, @Expenses, @Advances)", connection)
    
            insert.Parameters.Add("@mmddyy", OleDb.OleDbType.VarChar, 20, "mmddyy")
            insert.Parameters.Add("@Driver", OleDb.OleDbType.VarChar, 20, "Driver")
            insert.Parameters.Add("@Truck", OleDb.OleDbType.VarChar, 10, "Truck")
            insert.Parameters.Add("@Customer", OleDb.OleDbType.VarChar, 10, "Customer")
            insert.Parameters.Add("@Rate", OleDb.OleDbType.VarChar, 10, "Rate")
            insert.Parameters.Add("@Where_From", OleDb.OleDbType.VarChar, 100, "Where_From")
            insert.Parameters.Add("@Where_To", OleDb.OleDbType.VarChar, 100, "Where_To")
            insert.Parameters.Add("@Pit_Ticket", OleDb.OleDbType.VarChar, 20, "Pit_Ticket")
            insert.Parameters.Add("@PO_Number", OleDb.OleDbType.VarChar, 20, "PO_Number")
            insert.Parameters.Add("@Yards_Tons", OleDb.OleDbType.VarChar, 20, "Yards_Tons")
            insert.Parameters.Add("@Description", OleDb.OleDbType.VarChar, 100, "Description")
            insert.Parameters.Add("@QP_Fee", OleDb.OleDbType.VarChar, 20, "QP_Fee")
            insert.Parameters.Add("@Fuel", OleDb.OleDbType.VarChar, 20, "Fuel")
            insert.Parameters.Add("@Expenses", OleDb.OleDbType.VarChar, 20, "Expenses")
            insert.Parameters.Add("@Advances", OleDb.OleDbType.VarChar, 20, "Advances")
    
            adapter.InsertCommand = insert
            adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
    
            Dim table As New DataTable
    
            'Retrieve the data.
            adapter.FillSchema(table, SchemaType.Source)
    
            'Add the new rows to the DataTable, e.g.
            Dim row As DataRow = table.NewRow()
    
            row("mmddyy") = mmddyy
            row("Driver") = Driver
            row("Truck") = Truck
            row("Customer") = Customer
            row("Rate") = Rate
            row("Where_From") = Where_From
            row("Where_To") = Where_To
            row("Pit_Ticket") = Pit_Ticket
            row("PO_Number") = Po_Number
            row("Yards_Tons") = Yards_Tons
            row("Description") = Description
            row("QP_Fee") = QP_Fee
            row("Fuel") = Fuel
            row("Expenses") = Expenses
            row("Advances") = Advances
    
            table.Rows.Add(row)
    
            'Save the changes.
            adapter.Update(table)
    
        End Sub
    You can also just put brackets "[]" around your columns to avoid this. I pretty much do this all the time now.
    Please rate if my post was helpful!
    Per favore e grazie!




    Code Bank:
    Advanced Algebra Class *Update | True Gradient Label Control *Dev | A Smarter TextBox *Update | Register Global HotKey *Update
    Media Library Beta *Dev | Mouse Tracker (Available in VB.net and C#.net) *New | On-Screen Numpad (VB.net) *New

  6. #86
    New Member
    Join Date
    Feb 2012
    Posts
    7

    Re: Retrieving and Saving Data in Databases

    I am trying to use this for what I assumed was a relatively simple insert into query, taking the values from a DataTable, but I am encountering issues. Any advise if you have time would be gratefully received - I assume by now you are sick of the questions and pleas for help

    My Post

  7. #87
    New Member
    Join Date
    Jan 2013
    Posts
    1

    Question Re: Retrieving and Saving Data in Databases

    I have based my code off of the following by jmci which is on page 1 of this thread:
    Retrieving multiple records that will be read and discarded. The ExecuteReader method provides read-only, forward-only access to the entire result set:
    vb.net Code:
    1. Using connection As New SqlConnection("connection string here")
    2.     Using command As New SqlCommand("SELECT Quantity, Unit, Name FROM StockItem", _
    3.                                     connection)
    4.         connection.Open()
    5.  
    6.         Using reader As SqlDataReader = command.ExecuteReader()
    7.             While reader.Read()
    8.                 MessageBox.Show(String.Format("There are {0} {1} of {2} remaining in stock.", _
    9.                                               reader("Quantity"), _
    10.                                               reader("Unit"), _
    11.                                               reader("Name")))
    12.             End While
    13.         End Using
    14.     End Using
    15. End Using

    Here is my code:

    vb.net Code:
    1. Using con As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Documents and Settings\Joey.JPNET.000\Local Settings\Application Data\Temporary Projects\WindowsApplication1\Daycare.accdb;")
    2.             Using cmd As OleDb.OleDbCommand = New OleDb.OleDbCommand( _
    3.                        "SELECT * FROM Daycare.Guardians WHERE GuardianName = '" & _
    4.                        txtUsername.Text & "' AND [AccessCode] = '" & txtPassword.Text & "' ", con)
    5.                 con.Open()
    6.                 Using reader As OleDb.OleDbDataReader = cmd.ExecuteReader()
    7.                     If (reader.Read() = True) Then
    8.                         Form2.Show()
    9.                         Me.Hide()
    10.                     Else
    11.                         MessageBox.Show("Invalid username or password!")
    12.                     End If
    13.                 End Using
    14.             End Using
    15.         End Using


    I currently have a pair of text boxes and a Go/Cancel button on my form. Ultimately it will be a combo box and a "access code" box where parents select their name and type in a code. Once the person is authenticated, they will then be able to check in/check out their child and/or simply unlock the door. I am having a problem with my VB code talking to the MS Access database. I keep getting the error on the line "Using reader As OleDb.OleDbDataReader = cmd.ExecuteReader()" that it "Could not find file 'C:\Documents and Settings\Joey.JPNET.000\Local Settings\Application Data\Temporary Projects\WindowsApplication1\bin\Debug\Daycare.mdb'." This is because I am using the newer .aacdb format database and not the older .mdb format database. How do I format it such that it connects to the correct database which is actually located in that folder?

    Thank you for your help!
    ~Joey

  8. #88
    Fanatic Member
    Join Date
    Nov 2010
    Posts
    965

    Re: Retrieving and Saving Data in Databases

    Very very nice data you've provided.

    Code:
        Using connection As New SqlConnection("connection string here")
            Using adapter As New SqlDataAdapter("SELECT ID, Name, Quantity, Unit FROM StockItem", _
                                                connection)
                Dim insert As New SqlCommand("INSERT INTO StockItem (Name, Quantity, Unit) VALUES (@Name, @Quantity, @Unit)", _
                                             connection)
         
                insert.Parameters.Add("@Name", SqlDbType.VarChar, 100, "Name")
                insert.Parameters.Add("@Quantity", SqlDbType.Float, 8, "Quantity")
                insert.Parameters.Add("@Unit", SqlDbType.VarChar, 10, "Unit")
         
                adapter.InsertCommand = insert
                adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
         
                Dim table As New DataTable
         
                'Retrieve the schema.
                adapter.FillSchema(table, SchemaType.Source)
         
                'Add the new rows to the DataTable, e.g.
                Dim row As DataRow = table.NewRow()
         
                row("Name") = someName
                row("Quantity") = someQuantity
                row("Unit") = someUnit
                table.Rows.Add(row)
         
                'Save the changes.
                adapter.Update(table)
            End Using
        End Using
    Hi John. I don't understand this
    Code:
    adapter.missingschema........
    . Isn't there any way, in which i don't use this line of code and insert multiple values? Please guide me.

  9. #89

    Thread Starter
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Retrieving and Saving Data in Databases

    Quote Originally Posted by ADQUSIT View Post
    Hi John. I don't understand this
    Code:
    adapter.missingschema........
    . Isn't there any way, in which i don't use this line of code and insert multiple values? Please guide me.
    The reason for using the MissingSchemaAction property is to ensure that primary key information is added to the DataTable when you call FillSchema. You can build the DataTable schema yourself and not call FillSchema and, either way, you can set the PrimaryKey of the DataTable yourself. If you're going to call Fill or FillSchema though, you may as well let it set the PrimaryKey for you to, which it will do if you set MissingSchemaAction to AddWithKey.

  10. #90
    Fanatic Member
    Join Date
    Nov 2010
    Posts
    965

    Re: Retrieving and Saving Data in Databases

    The reason for using the MissingSchemaAction property is to ensure that primary key information is added to the DataTable when you call FillSchema
    Do you simply mean that this property automatically adds PK to the DataTable? Am I right?

    And What is Schema John?

  11. #91
    New Member
    Join Date
    May 2013
    Posts
    13

    Re: Retrieving and Saving Data in Databases

    Do you need to use a connection string if you link the DB with the server explorer?

  12. #92

    Thread Starter
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Retrieving and Saving Data in Databases

    Quote Originally Posted by ADQUSIT View Post
    Do you simply mean that this property automatically adds PK to the DataTable? Am I right?

    And What is Schema John?
    If MissingSchemaAction is set to Add then, when calling Fill, any columns that exist in the result set of the query will be created in the DataTable if they don't already exist, but the PrimaryKey property of the DataTable will not be set. Using AddWithKey will also set the PrimaryKey property to the same column(s) as the database if the query involves only one table and includes that table's PK column(s).

    Schema is basically the structure of the database, in this case the number of columns, their names and data types, etc.

  13. #93

    Thread Starter
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Retrieving and Saving Data in Databases

    Quote Originally Posted by awweather View Post
    Do you need to use a connection string if you link the DB with the server explorer?
    The only way to connect to a database is with a connection string. If you use the Server/Database Explorer then that's where the connection string comes from. Select a data connection in the Server/Database Explore and open the Properties window and you'll see the connection string for that connection.

  14. #94
    Lively Member
    Join Date
    Oct 2011
    Posts
    72

    Re: Retrieving and Saving Data in Databases

    How would one save a data from a bound data table to SQL Server CE, using ADO.NET?

    I havent worked with Sql Server Compact much at all, but I noticed there there is no equivalent to SQLDbType, so I am having trouble defining parameters.

  15. #95

    Thread Starter
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Retrieving and Saving Data in Databases

    Quote Originally Posted by AC1982 View Post
    How would one save a data from a bound data table to SQL Server CE, using ADO.NET?

    I havent worked with Sql Server Compact much at all, but I noticed there there is no equivalent to SQLDbType, so I am having trouble defining parameters.
    Using SQL Server CE is pretty much exactly as I've shown above, as it is for pretty much any data source when using ADO.NET. You need to add the SQL Server CE ADO.NET provider, which you do by referencing the System.Data.SqlServerCe.dll assembly. The relevant classes can then be found in the System.Data.SqlServerCe namespace. When working with parameters, you use the exact same System.Data.SqlDbType enumeration to specify the data type as you do for SQL Server.

  16. #96
    Fanatic Member
    Join Date
    Jul 2006
    Location
    Netherlands
    Posts
    817

    Re: Retrieving and Saving Data in Databases

    Dear JMC,

    I am fairly new to updating, inserting and deleting data from a datagridview.
    After reading this topic I have build the following code:

    Code:
    Dim sql As String = String.Format("SELECT	R.Datum,R.Chauffeur_ID,Wagen_ID,R.Beginstand,R.Eindstand,R.Liter,R.Dieselkoeling,R.Stops,R.StopsDachser,R.BrutoUren,R.Maut,R.Eurovignet " & _
                                        "FROM	tblRIT AS R ")
    
    
    Private connection As New OleDbConnection(_Connectionstring)
    Private adapter As New OleDbDataAdapter(sql, connection)
    Private table As New DataTable
    
    Private Sub InitialiseDataAdapter()
        ' tblRIT has a combined key [Datum, Chauffeur_ID, Wagen_ID]
        Dim delete As New OleDbCommand("DELETE FROM tblRIT WHERE Datum = @Datum AND Chauffeur_ID = @Chauffeur_ID AND Wagen_ID = @Wagen_ID", Me.connection)
        Dim insert As New OleDbCommand("INSERT INTO tblRIT (Datum, Chauffeur_ID, Wagen_ID) VALUES (@Datum, @Chauffeur_ID, @Wagen_ID)", Me.connection)
        Dim update As New OleDbCommand("UPDATE tblRIT SET Datum = @Datum, Chauffeur_ID = @Chauffeur_ID, Wagen_ID = @Wagen_ID WHERE Datum = @Datum AND Chauffeur_ID = @Chauffeur_ID AND Wagen_ID = @Wagen_ID", Me.connection)
    
        delete.Parameters.Add("@Datum", OleDbType.Date, "Datum")
        delete.Parameters.Add("@Chauffeur_ID", OleDbType.BigInt, "Chauffeur_ID")
        delete.Parameters.Add("@Wagen_ID", OleDbType.BigInt, "Wagen_ID")
    
        insert.Parameters.Add("@Datum", OleDbType.Date, "Datum")
        insert.Parameters.Add("@Chauffeur_ID", OleDbType.BigInt, "Chauffeur_ID")
        insert.Parameters.Add("@Wagen_ID", OleDbType.BigInt, "Wagen_ID")
    
        update.Parameters.Add("@Datum", OleDbType.Date, "Datum")
        update.Parameters.Add("@Chauffeur_ID", OleDbType.BigInt, "Chauffeur_ID")
        update.Parameters.Add("@Wagen_ID", OleDbType.BigInt, "Wagen_ID")
    
        Me.adapter.DeleteCommand = delete
        Me.adapter.InsertCommand = insert
        Me.adapter.UpdateCommand = update
    
        Me.adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
      End Sub
    
    Private Sub GetData()
        'Retrieve the data.
        Me.adapter.Fill(Me.table)
        dgvRitInfo.DataSource = Me.table
        'The table can be used here to display and edit the data.
        'That will most likely involve data-binding but that is not a data access issue.
      End Sub
    
      Private Sub SaveData()
        'Save the changes.
        Me.adapter.Update(Me.table)
      End Sub
    I call the savedata() and getData() from 2 different buttons.

    When I press the button getData the datagridview gets filled with the correct data.

    But when I update data and I click the saveData button I get the error message: "Update requires a valid UpdateCommand when passed DataRow collection with modified rows."
    But when I insert data and I click the saveData button I get the error message: "Update requires a valid InsertCommand when passed DataRow collection with modified rows."
    But when I delete data and I click the saveData button I get the error message: "Update requires a valid DeleteCommand when passed DataRow collection with deleted rows."

    How should I solve this problem?
    I am a little bit lost.

  17. #97

    Thread Starter
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Retrieving and Saving Data in Databases

    Quote Originally Posted by bodylojohn View Post
    Dear JMC,

    I am fairly new to updating, inserting and deleting data from a datagridview.
    After reading this topic I have build the following code:

    Code:
    Dim sql As String = String.Format("SELECT	R.Datum,R.Chauffeur_ID,Wagen_ID,R.Beginstand,R.Eindstand,R.Liter,R.Dieselkoeling,R.Stops,R.StopsDachser,R.BrutoUren,R.Maut,R.Eurovignet " & _
                                        "FROM	tblRIT AS R ")
    
    
    Private connection As New OleDbConnection(_Connectionstring)
    Private adapter As New OleDbDataAdapter(sql, connection)
    Private table As New DataTable
    
    Private Sub InitialiseDataAdapter()
        ' tblRIT has a combined key [Datum, Chauffeur_ID, Wagen_ID]
        Dim delete As New OleDbCommand("DELETE FROM tblRIT WHERE Datum = @Datum AND Chauffeur_ID = @Chauffeur_ID AND Wagen_ID = @Wagen_ID", Me.connection)
        Dim insert As New OleDbCommand("INSERT INTO tblRIT (Datum, Chauffeur_ID, Wagen_ID) VALUES (@Datum, @Chauffeur_ID, @Wagen_ID)", Me.connection)
        Dim update As New OleDbCommand("UPDATE tblRIT SET Datum = @Datum, Chauffeur_ID = @Chauffeur_ID, Wagen_ID = @Wagen_ID WHERE Datum = @Datum AND Chauffeur_ID = @Chauffeur_ID AND Wagen_ID = @Wagen_ID", Me.connection)
    
        delete.Parameters.Add("@Datum", OleDbType.Date, "Datum")
        delete.Parameters.Add("@Chauffeur_ID", OleDbType.BigInt, "Chauffeur_ID")
        delete.Parameters.Add("@Wagen_ID", OleDbType.BigInt, "Wagen_ID")
    
        insert.Parameters.Add("@Datum", OleDbType.Date, "Datum")
        insert.Parameters.Add("@Chauffeur_ID", OleDbType.BigInt, "Chauffeur_ID")
        insert.Parameters.Add("@Wagen_ID", OleDbType.BigInt, "Wagen_ID")
    
        update.Parameters.Add("@Datum", OleDbType.Date, "Datum")
        update.Parameters.Add("@Chauffeur_ID", OleDbType.BigInt, "Chauffeur_ID")
        update.Parameters.Add("@Wagen_ID", OleDbType.BigInt, "Wagen_ID")
    
        Me.adapter.DeleteCommand = delete
        Me.adapter.InsertCommand = insert
        Me.adapter.UpdateCommand = update
    
        Me.adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
      End Sub
    
    Private Sub GetData()
        'Retrieve the data.
        Me.adapter.Fill(Me.table)
        dgvRitInfo.DataSource = Me.table
        'The table can be used here to display and edit the data.
        'That will most likely involve data-binding but that is not a data access issue.
      End Sub
    
      Private Sub SaveData()
        'Save the changes.
        Me.adapter.Update(Me.table)
      End Sub
    I call the savedata() and getData() from 2 different buttons.

    When I press the button getData the datagridview gets filled with the correct data.

    But when I update data and I click the saveData button I get the error message: "Update requires a valid UpdateCommand when passed DataRow collection with modified rows."
    But when I insert data and I click the saveData button I get the error message: "Update requires a valid InsertCommand when passed DataRow collection with modified rows."
    But when I delete data and I click the saveData button I get the error message: "Update requires a valid DeleteCommand when passed DataRow collection with deleted rows."

    How should I solve this problem?
    I am a little bit lost.
    Those errors mean that the InsertCommand, UpdateCommand and DeleteCommand of your data adapter haven't been set, which means that you must not be calling your InitialiseDataAdapter method.

  18. #98
    Fanatic Member
    Join Date
    Jul 2006
    Location
    Netherlands
    Posts
    817

    Re: Retrieving and Saving Data in Databases

    Should I call the InitialiseDataAdapter method on each butten click event?

  19. #99

    Thread Starter
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Retrieving and Saving Data in Databases

    Quote Originally Posted by bodylojohn View Post
    Should I call the InitialiseDataAdapter method on each butten click event?
    You should call the method when you need to to do what it does. What does it do? It initialises the data adapter. When do you need to initialise the data adapter? That's when you should call it.

  20. #100
    Fanatic Member
    Join Date
    Nov 2016
    Location
    Slovenia
    Posts
    575

    Re: Retrieving and Saving Data in Databases

    jmcilhinney,

    I've read entire article many times and I'm still facing troubles with doing inserts. What I have is a stored procedure in Oracle and doing exactly what you proposed doesn't insert a record. I don't know If I'm declaring parameters wrong or something else, and my error is quite bad - "ORA-00900", which means wrong SQL statement and you can't figue out what is wrong. My debugger shows me nothing so I really can't even tell If my values are passed or not. I only know that procedure executes with no errors from Oracle directly - and It inserts record too.

    this is my ORACLE procedure:

    Code:
    CREATE OR REPLACE PROCEDURE MYSCHEMA.UPDATE_TABLE (
    CHOOSE_SELECT_IN    IN     NUMBER,
    ID_INSTALLATIONS_IN IN MYSCHEMA.INSTALLATIONS.ID_INSTALLATIONS%TYPE,
    ID_TABLE1_FK_IN IN MYSCHEMA.INSTALLATIONS.ID_TABLE1_FK%TYPE,
    ID_TABLE2_FK_IN IN MYSCHEMA.INSTALLATIONS.ID_TABLE2_FK%TYPE,
    DESCRIPTION_IN IN MYSCHEMA.INSTALLATIONS.DESCRIPTION%TYPE)
    
    IS
    BEGIN
    
       CASE  CHOOSE_SELECT_IN
        
       WHEN 1 THEN  
       INSERT INTO MYSCHEMA.INSTALLATIONS (ID_INSTALLATIONS,
                                        ID_TABLE1_FK,
                                        ID_TABLE2_FK,
                                        DESCRIPTION)
            VALUES (ID_INSTALLATIONS_IN,
                    ID_TABLE1_FK_IN,
                    ID_TABLE2_FK_IN,
                    DESCRIPTION_IN);
         
     END CASE;
    
    END UPDATE_TABLE;
    /
    And this is my VB.NET code, starting from your example:

    Code:
    Private Sub InicialiseDataAdapter()
    
          Dim insert As New OracleCommand("MYSCHEMA.UPDATE_TABLE", Myconn)
    
            insert.CommandType = CommandType.StoredProcedure
    
            insert.Parameters.Add(New OracleParameter("CHOOSE_SELECT_IN", OracleDbType.Decimal, 1, ParameterDirection.Input))
            insert.Parameters.Add(New OracleParameter("ID_INSTALLATIONS_IN", OracleDbType.Decimal, 4, "ID_INSTALLATIONS"))
            insert.Parameters.Add(New OracleParameter("ID_TABLE1_FK_IN", OracleDbType.Decimal, 4, "ID_TABLE1_FK"))
            insert.Parameters.Add(New OracleParameter("ID_TABLE2_FK_IN", OracleDbType.Decimal, 4, "ID_TABLE2_FK"))
            insert.Parameters.Add(New OracleParameter("DESCRIPTION_IN", OracleDbType.NVarchar2, 5, "DESCRIPTION"))
        
            Me.adapter.InsertCommand = insert
            Me.adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
    
        End Sub
    
         Private Sub SaveData()
            'Save the changes.
            Me.adapter.Update(table)
        End Sub
    I initialize Datadapter when It get's filled with data. And when I try to insert using SaveData I get error I mentioned. What is wrong ? Looking at Parameters.Add method you have columns like this (parameterName,OracleDBType, Size, srcColumnName). I don't know what size is for, I tried to set It same as my field size are in DB, but no effect. What I would probably need is to send corresponding values of fields from Datatable to stored procedure parameters, but with exception of CHOOSE_SELECT_IN = 1 which tells procedure what Query needs to be executed. Please help If you know how to solve this.
    Last edited by LuckyLuke82; Mar 31st, 2017 at 07:32 AM.

  21. #101

    Thread Starter
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Retrieving and Saving Data in Databases

    Quote Originally Posted by LuckyLuke82 View Post
    And this is my VB.NET code, starting from your example:

    Code:
    Private Sub InicialiseDataAdapter()
    
          Dim insert As New OracleCommand("MYSCHEMA.UPDATE_TABLE", Myconn)
    
            insert.Parameters.Add(New OracleParameter("CHOOSE_SELECT_IN", OracleDbType.Decimal, 1, ParameterDirection.Input))
            insert.Parameters.Add(New OracleParameter("ID_INSTALLATIONS_IN", OracleDbType.Decimal, 4, "ID_INSTALLATIONS"))
            insert.Parameters.Add(New OracleParameter("ID_TABLE1_FK_IN", OracleDbType.Decimal, 4, "ID_TABLE1_FK"))
            insert.Parameters.Add(New OracleParameter("ID_TABLE2_FK_IN", OracleDbType.Decimal, 4, "ID_TABLE2_FK"))
            insert.Parameters.Add(New OracleParameter("DESCRIPTION_IN", OracleDbType.NVarchar2, 5, "DESCRIPTION"))
        
            Me.adapter.InsertCommand = insert
            Me.adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
    
        End Sub
    
         Private Sub SaveData()
            'Save the changes.
            Me.adapter.Update(table)
        End Sub
    I don't use Oracle but I assume that it works the same way in this regard. When you create an ADO.NET command object, the CommandType property is set to Text by default, which means that it expects SQL code in the CommandText property. If you are going to assign the name of the stored procedure to the CommandText property then you need to set the CommandType to StoredProcedure.

  22. #102
    Fanatic Member
    Join Date
    Nov 2016
    Location
    Slovenia
    Posts
    575

    Re: Retrieving and Saving Data in Databases

    I don't use Oracle but I assume that it works the same way in this regard. When you create an ADO.NET command object, the CommandType property is set to Text by default, which means that it expects SQL code in the CommandText property. If you are going to assign the name of the stored procedure to the CommandText property then you need to set the CommandType to StoredProcedure.
    I'm sorry, I forgot to add that in my post, I edited It now. I have that allready, but It doesn't work either. I'm not sure what I do wrong. Only thing that comes in my mind is that I fill DataAdapter from different stored procedure. My steps regarding this are (in Load):

    Code:
    Using cmd As New OracleCommand("MY_ANOTHER_STORED_PROCEDURE", Myconn)
                   
     cmd.CommandType = CommandType.StoredProcedure
    
      'Adding parameters for stored procedure here  
      ...
    
      'Assigning adapter Select command and display data in Datagridview
      adapter.SelectCommand = cmd
      adapter.Fill(table)
      InicialiseDataAdapter()
      My_DGV.DataSource = table
     
    'I accept changes because rows get manually changed by me during binding - I set different values for 1 row
      table.AcceptChanges()
    
    End Using
    EDIT:

    Looks like I fixed It. It works now, but I'm not sure for how long. What I did is that I end edit in Datagridview first (My_DGV.EndEdit) and now It saves into DB. However I have another problem in one different Datagridview- I need to save changes only for rows where certain cell has value and ignore rows other even If It's added row, or modified - how can I do that ? Loop through Datagridview rows or datatable rows ?
    Last edited by LuckyLuke82; Mar 31st, 2017 at 09:05 AM.

  23. #103

    Thread Starter
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Retrieving and Saving Data in Databases

    Quote Originally Posted by LuckyLuke82 View Post
    However I have another problem
    This thread is not intended to be a place that anyone can ask any question related to ADO.NET. You should start a new thread dedicated to this new specific topic.

  24. #104
    Fanatic Member
    Join Date
    Nov 2016
    Location
    Slovenia
    Posts
    575

    Re: Retrieving and Saving Data in Databases

    Ok, no problem.

Page 3 of 3 FirstFirst 123

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