Updating database through datagridview

[bigzos]

Public Class Form5
    Dim connection As OleDb.OleDbConnection

    Dim mystr As String
    Dim cmd As OleDb.OleDbCommand
    Dim adp As OleDb.OleDbDataAdapter
    Dim ds As New DataSet

    Private Sub Form5_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs)
        connection.Close()
    End Sub
    Private Sub Form5_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        mystr = ("Provider=Microsoft.JET.OLEDB.4.0;" & _
"Data Source=C:/Users/Space Era/Documents/mod.mdb")

        connection = New OleDb.OleDbConnection(mystr)
        ' Fill the data grid viewer
        connection.Open()
        cmd = New OleDb.OleDbCommand("SELECT modify.[Server Name], modify.[Backup taken by], modify.[Action taken by] FROM modify", connection)
        adp = New OleDb.OleDbDataAdapter(cmd)
        adp.Fill(ds, "modify")
        Me.DataGridView1.DataSource = ds
        Me.DataGridView1.DataMember = "modify"



    End Sub
    Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim cmdbuilder As New OleDb.OleDbCommandBuilder(adp)
        Try
            adp.Update(ds, "modify")
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub
End Class

Output - Syntax error in insert into statement......any suggestions..!!

[Pgmer]

Where your making changes to the grid item? After making changes are you getting the changes into dataset which ur using in button1.click event? After making the changes to dataset are you making dataset.acceptchanges? please review ur flow

[bigzos]

thnx but that didn't help!!

[bigzos]

i need to finish this quick....smbdy plss help!!

[pixma]

Private Sub Save_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Save.Click
        Refresh.Enabled = True
        AddNew.Enabled = True
        Save.Enabled = False
        Edit.Enabled = False
        Delete.Enabled = False

        bldgno.Enabled = False
        bldgname.Enabled = False
        bldgcity.Enabled = False

        'null value validation
        If (bldgno.Text = Nothing) Or (bldgname.Text = Nothing) Or (bldgcity.Text = Nothing) Then
            MsgBox("Null Input Unacceptable", MsgBoxStyle.Exclamation)
            bldgname.Focus()
            bldgname.Enabled = True
            bldgcity.Enabled = True
            AddNew.Enabled = False
            Save.Enabled = True

        Else
            If type = True Then
                conn.Open()
                Dim cmd As New OleDbCommand("Insert into Building(bldgno, bldgname, city)values('" & bldgno.Text & "', '" & bldgname.Text & "', '" & bldgcity.Text & "')", conn)
                cmd.CommandType = CommandType.Text

                Dim oleAdapt As New OleDbDataAdapter
                oleAdapt.InsertCommand = cmd
                oleAdapt.InsertCommand.ExecuteNonQuery()
                conn.Close()

            ElseIf type = False Then
                conn.Open()
                Dim cmd3 As New OleDbCommand("Update Building Set bldgname='" & bldgname.Text & "', city='" & bldgcity.Text & "' where bldgno='" & bldgno.Text & "'", conn)
                cmd3.CommandType = CommandType.Text

                Dim oleAdapt3 As New OleDbDataAdapter
                oleAdapt3.UpdateCommand = cmd3
                oleAdapt3.UpdateCommand.ExecuteNonQuery()
                conn.Close()
            End If
        End If


        conn.Open()
        Dim cmd2 As New OleDbCommand("Select * from Building", conn)
        cmd2.CommandType = CommandType.Text

        Dim oleAdapt2 As New OleDbDataAdapter
        oleAdapt2.SelectCommand = cmd2
        oleAdapt2.SelectCommand.ExecuteNonQuery()

        Dim bldgtbl As New DataTable
        oleAdapt2.Fill(bldgtbl)
        DataGridView1.DataSource = bldgtbl
        DataGridView1.SelectionMode = False
        DataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect
        For r As Integer = 0 To DataGridView1.RowCount - 1
            DataGridView1.Rows(r).DefaultCellStyle.BackColor = Color.Empty
        Next

        conn.Close()
    End Sub

this is my sample code..you can base yours in that code

[bigzos]

i need to update the database using just the datagridview,,no textboxes involved....whatever i input in the datagridview at runtime should be reflected back to database..

[adam_k]

You know, you are welcome to contribute to your own program. pixma showed you the way. Try adapting your code, using the example pixma provided and let us know if you are having problems (with your code and the point that you are having difficulties)

[bigzos]

Imports System.Data.OleDb
Public Class Form3
    Dim connection As OleDb.OleDbConnection

    Dim mystr As String



       

    Private Sub Form3_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        mystr = ("Provider=Microsoft.JET.OLEDB.4.0;" & _
"Data Source=C:/Users/Space Era/Documents/mod.mdb")

        connection = New OleDb.OleDbConnection(mystr)

        connection.Open()

        'TODO: This line of code loads data into the 'ModDataSet3.modify' table. You can move, or remove it, as needed.
        Me.ModifyTableAdapter.Fill(Me.ModDataSet3.modify)

    End Sub

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


    End Sub
End Class

Here is the modified code and i am able to get the changes in dataset i.e. i am getting the previously entered values in my datagridview but i am not able to get those changes in the database......any help in this regard would be very much appreciated...

[bigzos]

waiting for a reply.....!!!!

[bigzos]

Here ,,i made a few more changes to my code..I am able to get the database values in the datagridview but when i hit the update button,i get the oledb exception - syntax error in insert into statement...help guys!!

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

[k.prabhu]

i'm also searching all round in google.......