CODEWITHSUNDEEP
vb.netvb.net-2010
Dark Horse
Dark Horse

Reputation: 13

How to add Data into the Database from VB.Net

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?

Upvotes: 0

Views: 358

Answers (2)

David
David

Reputation: 218798

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.

Upvotes: 2

Paul Abbott
Paul Abbott

Reputation: 7211

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.

Upvotes: 0

Related Questions