[meetjoe]

I have been trying to update my database using datagridview control.my code below gets the data from database to datagridview but when i hit the update button ,,i get an oledb exception saying error in insert into statement ..any relevant replies would be appreciated!!

Imports System.Data.OleDb
Public Class Form3
    
    Dim cnxnString As String = ("Provider=Microsoft.JET.OLEDB.4.0;" & _
"Data Source=C:/Users/Space Era/Documents/mod.mdb")
    Dim cnxn As New OleDbConnection(cnxnString)
    Dim sql As New OleDbCommand
    Dim DataAdapter As New OleDbDataAdapter("SELECT * FROM [modify]", cnxn)
    Dim cmdBuilder As New OleDbCommandBuilder(DataAdapter)
    Dim modify As New DataTable

   
       

    Private Sub Form3_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        

        DataAdapter.Fill(modify)
        DataGridView1.DataSource = modify
       
    End Sub

    

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Me.Validate()

        DataAdapter.Update(modify)


    End Sub
End Class

[modi123_1]

It's pretty straight forward - you need to create an insert statement (similar to the SELECT statement) to have the adapter do anything for you.

Basically the adapter is a multi tool that you can set up with specific SQL statements to tell it how to interact with the database from your dataset. Rows with states of 'added' are shunted to the insert sql object, modified to the update, and removed to the deleted. Usually there's a wizard in the dataset designer that asks you to fill in SQL procedures or statements... if you build your own in code you have to supply that or the adapater doesn't have the SQL to function.

Here's some tutorials to get you started.

OleDb Basics in VB.Net Rate Topic

A Really Simple Database Create a Database using Access & VB.net Express 2008

[meetjoe]

if i was working with textboxes then i would have written an insert statement,,,but here i have a datagridview..
i need to finish this module quick..plss give some way..

[modi123_1]

Yes, you need an insert statement even with the datagrid.

[meetjoe]

Firstly,,i dont know an insert statement concerning gridview regarding my project,,And inserting is not the only issue,,i need all the changes,,i.e. if i add,edit or delete cell values in my gridview,the same should be reflected back to my database..The coding i have provided above and the form is attached in this post.I seriously need to finish this quick..

[modi123_1]

Awesome. My two links show you what I mean by Inserting. It sounds like you need a SQL DELETE and a SQL: UPDATE as well.

Did you read those links?

[meetjoe]

modi123_1, on 25 July 2011 - 09:12 AM, said:

Awesome. My two links show you what I mean by Inserting. It sounds like you need a SQL DELETE and a SQL: UPDATE as well.

Did you read those links?

Sorry but i didn't find those links helpful

[modi123_1]

What wasn't helpful? They show how to write the SQL queries you are needing. Are you just looking for someone to give you the right SQL statements and the code to how to use them?

[meetjoe]

I know these links are helpful,,but frankly,,i am not able to use these links with respect to my form..i mean i am totally unaware of sql statements concerning grid view but i slightly know that these involve some row and column number similar to .text in textboxes.in short i am finding it difficult to apply those links to my form according to my needs..

[modi123_1]

Okay... let's see about an over view.

You have your database - it houses your data in tables that contain records (rows) of data for specific columns.

You need to get this data into your grid. To do that you need to create an adapter using a connection string to your database and a sql SELECT statement to tell it what table, columns, and any conditions you want back. When the adapter executes it is preferable to have the adapter fill a dataset. That dataset than can be bound to a grid and the data appears!

Recap - database holds your data, adapter (with the SQL statement of *HOW* to do it) bridges out and grabs your data, a dataset holds the data (locally) in a database-esque fashion, and the grid reveals the data being held locally.

Now when you alter the grid's data (edit, add, delete) those changes should be reflected in your dataset tied to the grid. Remember the dataset is local so it hasn't hit the database at any fashion. What you need to do is create an INSERT, UPDATE, and DELETE sql procedures to tell the database what to do with the data. UPDATEs need to tell what table to affect, what columns to change, what data should fill those columns, and where to find the rows to update. Insert instructs on what table to insert into and how to fill specific columns which what data. Delete specifies what table and how to find the row.

Back in your local dataset the altered rows have a rowstate. A rowstate reflects if it was modified, added, or removed.

When you create your adapter (remember this shuffles data between the local store and the database) you need to provide that connection string again, and that you need to specify the SQL statements to each of the corresponding objects in the adapter. (The adapter has an update, insert, and delete - all use the rowstate to figure out which sql statement to run for each row).

You call your adapter for the dataset and if all goes well your changes are in the database.

Now see - those links above explain this in pretty quick fashion. Clearly they won't be 100% what your problem is, but they will arm you with the knowledge to fashion a solution for your issue. You'll need to suck it up and apply the information from those tutorials and apply it to parts of your solution where they are parallel. I have rewritten your adapter a few times now (assuming I am reading your initial problem right) so I know you can do it!

http://msdn.microsof...v=VS.90%29.aspx
http://msdn.microsof...v=VS.90%29.aspx

[meetjoe]

i followed your links,,and modified my code but still i am getting an exception that the update command need a connection object

Imports System.Data.OleDb
Public Class Form3
    
    Dim cnxnString As String = ("Provider=Microsoft.JET.OLEDB.4.0;" & _
"Data Source=C:/Users/Space Era/Documents/mod.mdb")
    Dim cnxn As New OleDbConnection(cnxnString)
    Dim sql As New OleDbCommand


    Dim modify As New DataTable
    
   
       


    Public Function DataAdaptr() As OleDbDataAdapter

        Dim dataadapter As OleDbDataAdapter = _
            New OleDbDataAdapter("SELECT * FROM [modify]", cnxn)

        DataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
        DataAdapter.InsertCommand = New OleDbCommand( _
           "INSERT INTO modify (Server Name,Backup taken by,Action taken by) " & _
            "VALUES (?, ?,?)")

        DataAdapter.UpdateCommand = New OleDbCommand( _
            "UPDATE modify SET Server Name=?,Backup taken by=?,Action taken by=?" & _
            "WHERE Server Name = ?")

        DataAdapter.DeleteCommand = New OleDbCommand( _
            "DELETE FROM modify WHERE Server Name = ?")
        DataAdapter.InsertCommand.Parameters.Add( _
        "@Server Name", OleDbType.Char, 5, "Server Name")
        DataAdapter.InsertCommand.Parameters.Add( _
            "@Backup taken by", OleDbType.VarChar, 40, "Backup taken by")
        DataAdapter.InsertCommand.Parameters.Add( _
            "@Action taken by", OleDbType.VarChar, 40, "Action taken by")
        DataAdapter.UpdateCommand.Parameters.Add( _
            "@Server Name", OleDbType.Char, 5, "Server Name")
        DataAdapter.UpdateCommand.Parameters.Add( _
            "@Backup taken by", OleDbType.VarChar, 40, "Backup taken by")
        DataAdapter.UpdateCommand.Parameters.Add( _
            "@Action taken by", OleDbType.VarChar, 40, "Action taken by")
        DataAdapter.UpdateCommand.Parameters.Add( _
            "@oldServer Name", OleDbType.Char, 5, "Server Name").SourceVersion = _
            DataRowVersion.Original

        DataAdapter.DeleteCommand.Parameters.Add( _
            "@Server Name", OleDbType.Char, 5, "Server Name").SourceVersion = _
            DataRowVersion.Original
        Return DataAdapter
    End Function
    Private Sub Form3_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        DataAdaptr.Fill(modify)
        DataGridView1.DataSource = modify

    End Sub

    

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Me.Validate()

        DataAdaptr.Update(modify)

    End Sub
End Class

[modi123_1]

Indeed - that exception isn't lying. Your objects need the connection object! When you create your deletecommand, insertcommand, and updatecommand check the other options for the 'new'. You'll find they require the connection object. In this case "cnxn".

[meetjoe]

i didn't get you

[meetjoe]

dataadapter.InsertCommand = New OleDbCommand( _
"INSERT INTO modify (Server Name,Backup taken by,Action taken by) " & _
"VALUES (?, ?,?)", cnxn)

I modified the command specifying the connection but now i am getting a syntax error in update statement.

[modi123_1]

Look at the "new" constructors of the oledbdataadapter. Notice there are four of them. Notice the one you are using for the insert/update/delete is just a string as an input variable. Notice that there is one that has a string and a connection object. If the exception says you need a connection object which one of the four ways of creating an oledbcommand object would you use?

http://msdn.microsof...v=VS.90%29.aspx

You are getting that for the update? Well for Pete's freaking sake perhaps take that knowledge you just gleamed and applying it to parallel issues.

Come on man - a bit of effort! I am not a "debug by proxy" here.