SQLCe Database in VB.NET Throws Exception while inserting data in table

Question

I am playing with databases and today in my attempt to work with SQLCompactEdition database, I've created a local database named UserDB and have a table in it named, User.

I've logged in into admin using my app(which is another database) and through admin, I am creating a user.

Code I used for the purpose is :

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim connStr As New SqlCeConnection("Data Source=c:\users\babi\documents\visual studio 2012\Projects\ShopManagement\ShopManagement\" & "User\UserDB.sdf; Password =")
        Dim name, pass, repass As String
        name = TextBox1.Text
        If (name.Length = 0) Then
            MessageBox.Show("Enter user-name!!")
            Exit Sub
        End If
        pass = TextBox2.Text
        If (pass.Length = 0) Then
            MessageBox.Show("Enter password!!")
            Exit Sub
        End If
        repass = TextBox3.Text
        If (repass.Length = 0) Then
            MessageBox.Show("Re-enter password!!")
            Exit Sub
        End If
        If (getMD5Hash(pass) = getMD5Hash(repass)) Then
            Dim cmd As New SqlCeCommand("INSERT INTO User(uname, upass)VALUES(" & name & "," & getMD5Hash(pass) & ");", connStr)
            connStr.Open()
            cmd.ExecuteNonQuery()
            connStr.Close()
            MessageBox.Show("User Created!")
            Exit Sub
        Else
            MessageBox.Show("Passwords donot match!!")
            Exit Sub
        End If
    End Sub

When I debug, I find the querystring to be :

"INSERT INTO User(uname, upass)VALUES(abcd,827ccb0eea8a706c4c34a16891f84e7b);"

The Exception that occurs is :

An unhandled exception of type 'System.Data.SqlServerCe.SqlCeException' occurred in System.Data.SqlServerCe.dll

I cannot understand what is causing this error. If any more information is required, please ask.

EDIT

Fixed Code :

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim connStr As New SqlCeConnection("Data Source=c:\users\babi\documents\visual studio 2012\Projects\ShopManager\ShopManager\" & "ManagementDB.sdf; Password =")
        Dim name, pass, repass As String
        name = TextBox1.Text
        If (name.Length = 0) Then
            MessageBox.Show("Enter user-name!!")
            Exit Sub
        End If
        pass = TextBox2.Text
        If (pass.Length = 0) Then
            MessageBox.Show("Enter password!!")
            Exit Sub
        End If
        repass = TextBox3.Text
        If (repass.Length = 0) Then
            MessageBox.Show("Re-enter password!!")
            Exit Sub
        End If
        If (getMD5Hash(pass) = getMD5Hash(repass)) Then
            Dim cmd As New SqlCeCommand("INSERT INTO [User](uname, upass) VALUES('" & name & "','" & getMD5Hash(pass) & "');", connStr)
            connStr.Open()
            cmd.ExecuteNonQuery()
            connStr.Close()
            MessageBox.Show("User Created!")
            Exit Sub
        Else
            MessageBox.Show("Passwords donot match!!")
            Exit Sub
        End If
    End Sub

The solution to the problem is given by the combination of the two solutions! So thank you to both of you :)

Regards Priyabrata

Steve · Accepted Answer · 2013-10-15T15:35:25.240

2

Big problems in your query

First of all USER is a reserved keyword. If you want to use it you need to encapsulate it with square brackets. Then you have problems when you try to concatenate strings to build an Sql command. Instead you should use a parameterized query. Finally, open the connection enclosed in a Using statement. In this way you are sure that the connection will be closed and DISPOSED also in case of exceptions (that's good also for the SqlCeCommand).

So, I would write instead

Dim pwd = getMD5Hash(pass)
Dim cmdText = "INSERT INTO [User] (uname, upass) VALUES (@name, @pwd)"
Using connStr As New SqlCeConnection(......)
Using cmd As New SqlCeCommand(cmdText, connStr)
    cmd.Parameters.AddWithValue("@name", name)
    cmd.Parameters.AddWithValue("@pwd", pwd)
    connStr.Open()
    cmd.ExecuteNonQuery()
End Using
End Using

edited Oct 15 '13 at 15:35

answered Oct 15 '13 at 15:30

Steve

213,761
22
232
286

1

@Priyabrata I see you have choosen a different answer, no problem, but rememeber that string concatenation is the road to hell (Sql Injection). What happen if I write in the username field `x'; DELETE FROM [user]--` ? (Please do not try it, [look at this](http://stackoverflow.com/questions/332365/how-does-the-sql-injection-from-the-bobby-tables-xkcd-comic-work) – Steve Oct 15 '13 at 15:41
I see!! Thanks for pointing out. This is my first outing with DB, so I didn't know that. Thanks a lot Steve :) – Priyabrata Oct 15 '13 at 15:44

Andrea · Answer 2 · 2013-12-19T09:44:26.777

1

If uname, upass columns are string/varchar try to add add quote char (') around values during insert operation:

Dim cmd As New SqlCeCommand("INSERT INTO User(uname, upass) VALUES('" & name & "','" & getMD5Hash(pass) & "');", connStr)

so your query should become:

INSERT INTO User(uname, upass) VALUES('abcd','827ccb0eea8a706c4c34a16891f84e7b');

edited Dec 19 '13 at 09:44

answered Oct 15 '13 at 15:23

Andrea

11,801
17
65
72

SQLCe Database in VB.NET Throws Exception while inserting data in table

2 Answers2