How to add Data into the Database from VB.Net

Question

I'm new to VB. It's been few weeks since I started learning VB.My question is I'm having difficulty in adding Data in to the Database (I'm using MS Access) from VB. So far I got this code but it isn't running well:

Imports System.Data.OleDb
Public Class CraeteAccount
Dim connString As String
Dim myConnection As OleDbConnection = New OleDbConnection
Dim cmd As New OleDbCommand
Dim dr As OleDbDataReader


Public Sub btnCreate_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreate.Click
    connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & IO.Path.Combine(My.Application.Info.DirectoryPath, "LogIn1.accdb")

    Dim cmd As New OleDbCommand
    Dim cnn As OleDbConnection = New OleDbConnection(connString)
    Dim str As String
    Dim UserName As String
    Dim Password As String



    If txtPassword.Text = txtRetype.Text Then
        cnn.Open()
        Try
            UserName = txtUserName.Text
            Password = txtPassword.Text

            str = "UPDATE Users SET UserName= '" & UserName & "', Password= '" & Password
            cmd = New OleDbCommand(str, myConnection)
            cmd.Parameters.AddWithValue("@UserName", UserName)
            cmd.Parameters.AddWithValue("@Password", Password)
            cmd.ExecuteNonQuery()
            MsgBox("New User has been Created!")
            cnn.Close()
            Me.Hide()


        Catch ex As Exception
            MsgBox("Error Occured!")
            cnn.Close()

        End Try
        Me.Close()

    Else
        MsgBox("Check your Password!")
        cnn.Close()
        txtPassword.Focus()
    End If

End Sub

When the code runs It donot add data and quickly goes to catch to show the Message Box which reads "Error Occured". So Can anyone Please Help me?

What's the actual exception? Exceptions contain very useful information about runtime problems. Ignoring them makes debugging difficult. — David
– David, Commented Oct 16, 2015 at 17:16
For the time being, comment out the Try, Catch and End Try lines so that you get to see the actual error message and you can let us know exactly what the error message says - if that doesn't help you solve it. — Andrew Morton
– Andrew Morton, Commented Oct 16, 2015 at 17:16
You SQL syntax for the OleDBCommand is incorrect. Should be something like ... Username= ?, Password = ? ... I think - don't use params myself. Might be other problems. — rheitzman
– rheitzman, Commented Oct 16, 2015 at 17:24

David · Accepted Answer · 2015-10-16 17:44:23Z

2

At a quick glance, the SQL query is broken in several ways:

str = "UPDATE Users SET UserName= '" & UserName & "', Password= '" & Password

The first thing to notice is that you're not closing the quotes after the password. However, even that isn't what you really want to do. What you want to do is this:

str = "UPDATE Users SET UserName=@UserName, Password=@Password"

This creates query parameters, which your next two lines are looking to populate with values:

cmd.Parameters.AddWithValue("@UserName", UserName)
cmd.Parameters.AddWithValue("@Password", Password)

Putting user values directly into the query is called a SQL injection vulnerability. It allows users to execute arbitrary code on your database, which is clearly a bad thing. So you're definitely going to want to stick with using these parameters instead.

The second problem here is that this query is going to update every record int he table. So it's basically going to overwrite all Users records with multiple copies of this one record.

If this really should be an UPDATE statement when you're going to want to add a WHERE clause to it which would identify the specific record you want to update.

However, I suspect based on the context that this should instead be an INSERT statement, since it's creating a new record:

str = "INSERT INTO Users (UserName, Password) VALUES (@UserName, @Password)"

Additionally, and this is important, you are storing user passwords in plain text. This is grossly irresponsible to your users. You should be obscuring user passwords with a 1-way hash so that they can never be retrieved in their original form. Not even by you as the system administrator.

(The language and emphasis used here may be a bit harsh for a beginner. Especially if you're working on a purely academic project with no actual users. But it's seriously that important. And there's no time like the present to learn about it.)

Another issue here is that you're assuming success of the query:

cmd.ExecuteNonQuery()
MsgBox("New User has been Created!")

At the very least you should be checking the return value to make sure a record was actually affected:

Dim rowsAffected As Int32 = cmd.ExecuteNonQuery()
If rowsAffected > 0 Then
    MsgBox("New User has been Created!")
Else
    'no record was inserted, handle error condition
End If

Another issue that you're facing, which isn't directly related to your problem but is making it much more difficult for you to debug your problem, is that you're ignoring error information:

Catch ex As Exception
    MsgBox("Error Occured!")
    cnn.Close()

In this code block the ex variable contains all of the information that the .NET Framework can give you about the error that took place. What you're basically doing is replacing all of that diagnostic information (error message, stack trace, etc.) with a single custom error message that contains no information.

Best not to do that.

Note that, given these issues, there may very well be other problems with the code. But this should at least get you going for a bit.

edited Oct 16, 2015 at 17:44

answered Oct 16, 2015 at 17:23

David

221k42 gold badges245 silver badges338 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Luminous Over a year ago

Considering OP is a fresh beginner in VB this answer is quite considerate and informative in the basics of database updates, user password storage, SQL injection, and exception handling.

Dark Horse Over a year ago

Hi, well actually it isn't saving into the data base. When the above steps are followed Database If statement doesn't run and the Database remains unchanged.

David Over a year ago

@DarkHorse: When you step through the code in a debugger, where/how specifically does it fail?

Lews Therin Over a year ago

Upvote for addressing the security issues. Far too many developers ignore these critical concepts.

David Over a year ago

@Sylverac: The funny part is that the OP was already adding the parameters, just didn't have the placeholders for them in the query. I've never seen that happen before :)

|

Paul Abbott · Accepted Answer · 2015-10-16 17:22:37Z

0

You're simultaneously trying to concatenate an update statement with user input (bad) and using parameterized values (good). Try

str = "UPDATE Users SET UserName=@UserName, Password=@Password"
cmd = New OleDbCommand(str, myConnection)
cmd.Parameters.AddWithValue("@UserName", UserName)
cmd.Parameters.AddWithValue("@Password", Password)

But this still won't work because this update statement will update all the records in the database with these values. Are you trying to update an existing record or create a new one? If you're updating an existing one, you need a WHERE clause; if you're trying to create a new one, you need to use INSERT instead.

answered Oct 16, 2015 at 17:22

Paul Abbott

7,2213 gold badges30 silver badges47 bronze badges

1 Comment

Dark Horse Over a year ago

The program ran well but the data is not saved in the database.

Collectives™ on Stack Overflow

How to add Data into the Database from VB.Net

2 Answers 2

7 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related