insert, update and delete data

I want to insert, update and delete data in my sql data base through a

Page 1 of 1

2 Replies - 11673 Views - Last Post: 26 February 2008 - 12:42 PM Rate Topic: -----

#1 dushi  Icon User is offline

  • New D.I.C Head

Reputation: 2
  • View blog
  • Posts: 40
  • Joined: 23-February 08

insert, update and delete data

Posted 26 February 2008 - 10:40 AM

' hello, i want to insert, edit and remove data in my database through a form a I made with visual studio, it gives me an syntax error...can someone see what my error is?
thx!


Imports System.Data.Odbc

Public Class Form1
    Inherits System.Windows.Forms.Form
    Private da As OdbcDataAdapter
    Private ds As DataSet
    Private cr As Integer
    Private tot As Integer
    Private conn As OdbcConnection

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        'Load button
        Dim connectionString As String = " Driver={MySQL ODBC 5.1 Driver}; SERVER=localhost; DATABASE=friendsbday; user=root; Password=waanje; "
        Dim conn As New OdbcConnection(connectionString)
        da = New OdbcDataAdapter
        ds = New DataSet
        da.SelectCommand = conn.CreateCommand()
        da.SelectCommand.CommandText = "Select * From FriendsBDay"
        da.Fill(ds, "copyData")

        'Insert datasource
        da.InsertCommand = conn.CreateCommand()
        da.InsertCommand.CommandText = " INSERT INTO FriendsBDay (Name, Adress, Telefoon, BirthDay, IDnummer) Values ( ?b, ?c, ?d, ?e, ?f) "

        'insert
        Dim p1 As New OdbcParameter
        p1.ParameterName = "?b"
        p1.SourceColumn = "Name"
        da.InsertCommand.Parameters.Add(p1)

        Dim p2 As New OdbcParameter
        p2.ParameterName = "?c"
        p2.SourceColumn = "Adress"
        da.InsertCommand.Parameters.Add(p2)

        Dim p3 As New OdbcParameter
        p3.ParameterName = "?d"
        p3.SourceColumn = "Telefoon"
        da.InsertCommand.Parameters.Add(p3)

        Dim p4 As New OdbcParameter
        p4.ParameterName = "?e"
        p4.SourceColumn = "BirthDay"
        da.InsertCommand.Parameters.Add(p4)

        Dim p5 As New OdbcParameter
        p5.ParameterName = "?f"
        p5.SourceColumn = "IDnummer"
        da.InsertCommand.Parameters.Add(p5)

        'update datasource
        da.UpdateCommand = conn.CreateCommand()
        da.UpdateCommand.CommandText = " UPDATE FriendsBDay SET Name = ?b, Adress = ?c, Telefoon = ?d, BirthDay = ?e WHERE IDnummer = ?In "

        'update
        Dim p6 As New OdbcParameter
        p6.ParameterName = "=?b"
        p6.SourceColumn = "Name"
        da.UpdateCommand.Parameters.Add(p6)

        Dim p7 As New OdbcParameter
        p7.ParameterName = "?c"
        p7.SourceColumn = "Adress"
        da.UpdateCommand.Parameters.Add(p7)

        Dim p8 As New OdbcParameter
        p8.ParameterName = "?d"
        p8.SourceColumn = "Telefoon"
        da.UpdateCommand.Parameters.Add(p8)

        Dim p9 As New OdbcParameter
        p9.ParameterName = "?e"
        p9.SourceColumn = "BirthDay"
        da.UpdateCommand.Parameters.Add(p9)

        Dim p10 As New OdbcParameter
        p10.ParameterName = "?f"
        p10.SourceColumn = "IDnummer"
        da.UpdateCommand.Parameters.Add(p10)

        'Delete datasource
        da.DeleteCommand = conn.CreateCommand()
        da.DeleteCommand.CommandText = " DELETE FROM FriendsBDay WHERE IDnummer = ?In "

        'Delete

        Dim p15 As New OdbcParameter
        p15.ParameterName = "?In"
        p15.SourceColumn = "IDnummer"
        da.DeleteCommand.Parameters.Add(p15)
End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        'Next button
        If cr = tot - 1 Then
            cr = 0
        Else
            cr += 1
        End If
        'display next records
        tot = ds.Tables("copyData").Rows.Count()
        TextBox1.Text = ds.Tables("copyData").Rows(cr).Item("Name")
        TextBox2.Text = ds.Tables("copyData").Rows(cr).Item("Adress")
        TextBox3.Text = ds.Tables("copyData").Rows(cr).Item("Telefoon")
        TextBox4.Text = ds.Tables("copyData").Rows(cr).Item("BirthDay")
        TextBox5.Text = ds.Tables("copyData").Rows(cr).Item("IDnummer")

    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        'Prev button
        If cr = 0 Then
            cr = tot - 1
        Else
            cr -= 1
        End If
        'display next records
        tot = ds.Tables("copyData").Rows.Count()
        TextBox1.Text = ds.Tables("copyData").Rows(cr).Item("Name")
        TextBox2.Text = ds.Tables("copyData").Rows(cr).Item("Adress")
        TextBox3.Text = ds.Tables("copyData").Rows(cr).Item("Telefoon")
        TextBox4.Text = ds.Tables("copyData").Rows(cr).Item("BirthDay")
        TextBox5.Text = ds.Tables("copyData").Rows(cr).Item("IDnummer")
    End Sub

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        'new button
        Dim f2 As New Form2

        Dim dr As DialogResult = f2.ShowDialog()
        If dr = DialogResult.OK Then
            
	 'save was pressed
            Dim row As DataRow = ds.Tables("copyData").NewRow
            row.BeginEdit()
            row.Item(0) = f2.TextBox1.Text
            row.Item(1) = f2.TextBox2.Text
            row.Item(2) = f2.TextBox3.Text
            row.Item(3) = f2.TextBox4.Text
            row.Item(4) = f2.TextBox5.Text
            row.EndEdit()
            ds.Tables("copyData").Rows().Add(row)

            tot += 1

            'update database
            da.Update(ds, "copyData")
            ds.AcceptChanges()

            'display current record
            tot = ds.Tables("copyData").Rows.Count
            cr = tot - 1
            TextBox1.Text = ds.Tables("copyData").Rows(cr).Item(0).ToString()
            TextBox2.Text = ds.Tables("copyData").Rows(cr).Item(1).ToString()
            TextBox3.Text = ds.Tables("copyData").Rows(cr).Item(2).ToString()
            TextBox4.Text = ds.Tables("copyData").Rows(cr).Item(3).ToString()
            TextBox5.Text = ds.Tables("copyData").Rows(cr).Item(4).ToString()

        End If
       
    End Sub

    Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click
        'Edit button
        Dim f2 As New Form2
        f2.TextBox1.Text = ds.Tables("copyData").Rows(cr).Item("Name")
        f2.TextBox2.Text = ds.Tables("copyData").Rows(cr).Item("Adress")
        f2.TextBox3.Text = ds.Tables("copyData").Rows(cr).Item("Telefoon")
        f2.TextBox4.Text = ds.Tables("copyData").Rows(cr).Item("BirthDay")
        f2.TextBox5.Text = ds.Tables("copyData").Rows(cr).Item("IDnummer")

        Dim dr As DialogResult = f2.ShowDialog()

        'edit
        If dr = DialogResult.OK Then
            ds.Tables("copyData").Rows(cr).BeginEdit()
            ds.Tables("copyData").Rows(cr).Item("Name") = f2.TextBox1.Text
            ds.Tables("copyData").Rows(cr).Item("Adress") = f2.TextBox2.Text
            ds.Tables("copyData").Rows(cr).Item("Telefoon") = f2.TextBox3.Text
            ds.Tables("copyData").Rows(cr).Item("BirthDay") = f2.TextBox4.Text
            ds.Tables("copyData").Rows(cr).Item("IDnummer") = f2.TextBox5.Text
            ds.Tables("copyData").Rows(cr).EndEdit()

            'update
            da.Update(ds, "copyData")
            ds.AcceptChanges()

            'display current record
            tot = ds.Tables("copyData").Rows.Count
            cr = tot - 1
            TextBox1.Text = ds.Tables("copyData").Rows(cr).Item(0).ToString()
            TextBox2.Text = ds.Tables("copyData").Rows(cr).Item(1).ToString()
            TextBox3.Text = ds.Tables("copyData").Rows(cr).Item(2).ToString()
            TextBox4.Text = ds.Tables("copyData").Rows(cr).Item(3).ToString()
            TextBox5.Text = ds.Tables("copyData").Rows(cr).Item(4).ToString()

            'insert
            tot = ds.Tables("copyData").Rows.Count
            cr = tot - 1
            TextBox1.Text = ds.Tables("copyData").Rows(cr).Item(0).ToString()
            TextBox2.Text = ds.Tables("copyData").Rows(cr).Item(1).ToString()
            TextBox3.Text = ds.Tables("copyData").Rows(cr).Item(2).ToString()
            TextBox4.Text = ds.Tables("copyData").Rows(cr).Item(3).ToString()
            TextBox5.Text = ds.Tables("copyData").Rows(cr).Item(4).ToString()
        End If

    End Sub

    Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
        'Remove button
        Dim f2 As New Form2
        f2.TextBox1.Text = ds.Tables("copyData").Rows(cr).Item("Name")
        f2.TextBox2.Text = ds.Tables("copyData").Rows(cr).Item("Adress")
        f2.TextBox3.Text = ds.Tables("copyData").Rows(cr).Item("Telefoon")
        f2.TextBox4.Text = ds.Tables("copyData").Rows(cr).Item("BirthDay")
        f2.TextBox5.Text = ds.Tables("copyData").Rows(cr).Item("IDnummer")

        Dim dr As DialogResult = f2.ShowDialog()

        'edit
        If dr = DialogResult.OK Then
            ds.Tables("copyData").Rows(cr).BeginEdit()
            
            ds.Tables("copyData").Rows(cr).Delete()

            'update
            da.Update(ds, "copyData")
            ds.AcceptChanges()

            'display current record
            tot = ds.Tables("copyData").Rows.Count
            cr = tot - 1
            TextBox1.Text = ds.Tables("copyData").Rows(cr).Item(0).ToString()
            TextBox2.Text = ds.Tables("copyData").Rows(cr).Item(1).ToString()
            TextBox3.Text = ds.Tables("copyData").Rows(cr).Item(2).ToString()
            TextBox4.Text = ds.Tables("copyData").Rows(cr).Item(3).ToString()
            TextBox5.Text = ds.Tables("copyData").Rows(cr).Item(4).ToString()
        End If
    End Sub
End Class



EDIT: Please, when posting code, put it between code blocks :code: .

This post has been edited by PsychoCoder: 26 February 2008 - 12:40 PM


Is This A Good Question/Topic? 0
  • +

Replies To: insert, update and delete data

#2 dushi  Icon User is offline

  • New D.I.C Head

Reputation: 2
  • View blog
  • Posts: 40
  • Joined: 23-February 08

Re: insert, update and delete data

Posted 26 February 2008 - 11:16 AM

i've already fixed the problem...thnx any way
Was This Post Helpful? 0
  • +
  • -

#3 PsychoCoder  Icon User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1642
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: insert, update and delete data

Posted 26 February 2008 - 12:42 PM

We would have not been much help since you didn't say what error you were getting, but I'm glad you were able to resolve your issue :)

When posting a question for help, please include:

  • The code that is causing the problem
  • The error message you are receiving (if you are receiving an error)
  • If not receiving an error then provide a detailed explanation of what you are trying to do, what the code is doing, and what problem you are having

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1