VB.NET

Welcome to Dream.In.Code
	Become a VB.NET Expert! Join 150,070 VB.NET Programmers for FREE! Get instant access to thousands of VB.NET experts, tutorials, code snippets, and more! There are 1,806 people online right now. Registration is fast and FREE... Join Now! Chat LIVE With a VB.NET Expert

insert, update and delete data

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

Options

dushi

26 Feb, 2008 - 09:40 AM

Post #1

New D.I.C Head

Joined: 23 Feb, 2008
Posts: 39

Thanked: 1 times
My Contributions

' 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

.

This post has been edited by PsychoCoder: 26 Feb, 2008 - 11:40 AM

This Post Was Helpful!


Register for Free to Make These Ads Go Away!

dushi	RE: Insert, Update And Delete Data 26 Feb, 2008 - 10:16 AM Post #2
New D.I.C Head Joined: 23 Feb, 2008 Posts: 39 Thanked: 1 times My Contributions	i've already fixed the problem...thnx any way
	This Post Was Helpful!

PsychoCoder	RE: Insert, Update And Delete Data 26 Feb, 2008 - 11:42 AM Post #3
using DIC.Core; Joined: 26 Jul, 2007 Posts: 9,483 Thanked: 161 times Dream Kudos: 9075 Expert In: VB, VB.Net, C#, SQL, ASP, ASP.Net, Web Development, HTML, CSS, Win32 API, Javascript, mySQL, J#, Boo.Net My Contributions	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
	This Post Was Helpful!