Updating/editing/deleting records on VB 2010 with MySql as back end

Question

0.00/5 (No votes)

See more:

VS2010

I'm doing this exercise on VB2010, it's a log-in and the other is an add/edit/delete project.
I've coded the connection and been successful at it somehow, now, the thing is I can't update the database even if I get the confirmation that the record has been added.

I have no idea where the error is.
All I have for now is this block of code for the registration (add/edit/delete) so far:

Imports MySql.Data.MySqlClient
Public Class register
    Private Sub register_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        firstnametb.Focus()
        Try
            If conn.State = ConnectionState.Closed Then
                Dim connection As New MySqlConnection("Server = localhost; User Id = root; Password = root; Database = add_edit_delete")
                connection.Open()
            End If
        Catch ex As Exception
            MsgBox("Database Error!", MsgBoxStyle.Information, "System Message")
            Application.Exit()
        End Try
    End Sub
 
    Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles clrbtn.Click
        lastnametb.Text = ""
        firstnametb.Text = ""
        middletb.Text = ""
        agetb.Text = ""
        loctb.Text = ""
        unametb.Text = ""
        pwordtb.Text = ""
        cpwordtb.Text = ""
    End Sub
    Private Sub addbtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles addbtn.Click
        If lastnametb.Text = "" Or firstnametb.Text = "" Or agetb.Text = "" Or loctb.Text = "" Or unametb.Text = "" Or pwordtb.Text = "" Or cpwordtb.Text = "" Then
            MsgBox("Please complete the fields!", MsgBoxStyle.Exclamation, "System Message")
        Else
            If conn.State = ConnectionState.Closed Then
                Dim connection As New MySqlConnection("Server = localhost; User Id = root; Password = root; Database = add_edit_delete")
                connection.Open()
            End If
            Try
                strSql = "INSERT INTO register(LastName, FirstName, MiddleInitial, Age, Location, Uname, Pword) VALUES('" & lastnametb.Text & "','" & firstnametb.Text & "','" & middletb.Text & "','" & agetb.Text & "','" & loctb.Text & "','" & unametb.Text & "','" & pwordtb.Text & "')"
                cmd.CommandText = strSql
                cmd.Connection = conn
                cmd.ExecuteNonQuery()
                MsgBox("Record has been saved!", MsgBoxStyle.Information, "System Message")
                conn.Close()
                Me.Dispose()
            Catch ex As Exception
                MsgBox("Unable to save record!", MsgBoxStyle.Critical, "System Message")
                conn.Close()
            End Try
        End If
    End Sub
    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Me.Dispose()
    End Sub
End Class

Hope someone could help me. :) THANKS! God bless!

Posted 4-Jul-11 20:10pm

Buena Velasco

Updated 4-Jul-11 20:47pm

Dalek Dave

v2

Add a Solution

Comments

[no name] 5-Jul-11 2:15am

debug the code once. and why you are defining sql connection everytime.

Dalek Dave 5-Jul-11 2:48am

Edited for Grammar and Readability.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Christian Graus · Accepted Answer · 2011-07-04T20:13:00

Solution 1

This code is a mess. I know you're just learning, but how will you learn if you do things badly ? No centralisation of SQL code, a connection string in each methods, open to SQL injection attacks...

The first step is to debug and work out what your code is creating for a string of SQL. Then run that against your DB manually and see what errors the DB reports.

Posted 4-Jul-11 20:13pm

Christian Graus

Comments

Buena Velasco 5-Jul-11 3:42am

Thanks Mr. Graus, I'm able to solve it, took out the other connection string and then added cmd.ExecuteNonQuery. :)
<pre>
<pre lang="vb">Private Sub addbtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles addbtn.Click
If lastnametb.Text = "" Or firstnametb.Text = "" Or agetb.Text = "" Or loctb.Text = "" Or unametb.Text = "" Or pwordtb.Text = "" Or cpwordtb.Text = "" Then
MsgBox("Please complete the fields!", MsgBoxStyle.Exclamation, "System Message")
Else

Dim connection As New MySqlConnection("Server = localhost; User Id = root; Password = root; Database = add_edit_delete")
connection.Open()
Try

strSql = "INSERT INTO register(LastName, FirstName, MiddleInitial, Age, Location, Uname, Pword) VALUES('" & lastnametb.Text & "','" & firstnametb.Text & "','" & middletb.Text & "','" & agetb.Text & "','" & loctb.Text & "','" & unametb.Text & "','" & pwordtb.Text & "')"
cmd.CommandText = strSql
cmd.Connection = connection
cmd.ExecuteNonQuery()

MsgBox("Record has been saved!", MsgBoxStyle.Information, "System Message")
connection.Close()
Me.Dispose()
Catch ex As Exception
MsgBox("Unable to save record!", MsgBoxStyle.Critical, "System Message")
connection.Close()

End Try
End If
End Sub</pre>

</pre>