VS 2010 Relational Table problem

[Barrabutus]

Hi all

I have 2 tables created through MS Management Studio

Users and Passwords

Now the current code i have is

  ''Insert the new Password
        newConnection.sendCommand("INSERT INTO passwords VALUES ('" + TextBox2.Text + "')")
        'Retrieve the last Insert ID
        Dim lastID As Integer = newConnection.retrieveID("SELECT password_id from passwords WHERE password_id = @@IDENTITY")
       'insert the last ID into the users table, So we know which password is linked to which user by its id.
        newConnection.sendCommand("INSERT INTO users(username,password_id) VALUES ('" + TextBox1.Text + "'," + lastID + ")")

But this is throwing an error message
Conversion from string "INSERT INTO users(username,passw" to type 'Double' is not valid.

When i step through the application LastID does have the last Inserted ID from passwords table, And the table field is set to Integer value, So i cant really see whats going wrong at the moment.

Many Thanks Barrab

[Barrabutus]

Just had a thought could it be because

  Public Function sendCommand(ByVal command As String)

This is expecting a String but lastId is an integer value ?

UPDATE : Insert Working But DataGrids not refreshing.

        newConnection.sendCommand("INSERT INTO users(username,password_id) VALUES ('" + TextBox1.Text + "'," + lastID.ToString + ")")

        Me.UsersDataGridView.Refresh()
        Me.PasswordsDataGridView.Refresh()

The datagrids are filled using dataSetAdapters (Not handcoded as i dont know how to do that yet) lol

The insert now works but the 2 tables displaying the newly entered username and password dont refresh in the form, Do i need to reload the form?

Thanks Barrab

[jmcilhinney]

The problem is that you are using + to concatenate strings instead of &. When everything is a String then + has the same effect as & but, when not everything is a String, it doesn't. Look at this:

"'," + lastID + ")"

'lastID' is an Integer and you are using +, which is the addition operator. You are assuming that the Integer will be converted to a String but that is NOT what happens. It's the opposite, i.e. the String gets converted to a number. That fails of course, hence the exception. ALWAYS use the concatenation operator (&) when concatenating and use the addition operator (+) only when adding.

Also, your code creates a massive security hole. Look at this code:

newConnection.sendCommand("INSERT INTO passwords VALUES ('" + TextBox2.Text + "')")

Now, what happens if the user enters this into TextBox2?

Hello'); DELETE FROM passwords; INSERT INTO passwords VALUES ('Hello

Congratulations! You just deleted the entire contents of your entire 'passwords' table. ALWAYS use parameters to insert variables into SQL code. For more information on the how and why, follow the Blog link in my signature and check out my thread on the subject.

[Barrabutus]

Thanks for the replys i was aware of the SQL security issue,

This is just a personal project im working on at the moment just try and learn visual basic, So with that in mind i didnt think of SQL Security seeing as this is a program just for myself.

Still need to look into a mysql_real_escape_string() function for VB as well.

I will use & for concantenation because yes you are right that + is addition operator
(Doh i should have seen that xD)

Thanks for this Tip : When everything is a String then + has the same effect as & (I didnt know this, Which was why i was puzzled sometimes why i could use & and sometimes +)

How would i refresh the dataSet to show the new changes to the Database within the Datagrids please?

Thanks Barrab

PS Checking your blog now for anymore tips and guides

[jmcilhinney]

Still need to look into a mysql_real_escape_string() function for VB as well.

No you don't. Always use parameters.