CODEWITHSUNDEEP
sqlvb.netms-access
user1162628
user1162628

Reputation: 81

Sql error on insert statement

I'm using a gridviews Row Updating to add edited fields into a table for monitoring. Everything seems to work except for the one value, as shown below. 

   For i As Integer = 0 To colnum

        If e.OldValues(i).ToString() <> e.NewValues(i).ToString() Then
            Dim uid As Integer = Convert.ToInt32(strUID)

            Dim UpC As OleDbCommand = New OleDbCommand("INSERT INTO tblAud (TN, FN, tID, OldValue, UserID) VALUES ('" + tN + "', '" + fn + "'," + tID + ", '"+ oldValue + "'," + uid + ")", con)

            UpC.ExecuteNonQuery()

        End If
    Next

If I display the userid to a text box, it shows the id fine, if I type a number in the sql in place of it, it works. Also the sql works if I take out the userid, but with it on I get an error "Input string was not in a correct format." Also everything is added to the database twice, any idea why that is?

Upvotes: 0

Views: 522

Answers (3)

user1162628
user1162628

Reputation: 81

Got it working by adding the id to a textbox and then using Textbox.text on the sql statement.

Upvotes: 0

mellamokb
mellamokb

Reputation: 56769

Input string was not in a correct format.

Is generally associated with conversion. Since you are converting a string to a number, that is the most likely culprit. You can try a method like Int32.TryParse to validate the number and display an error message if it can't be parsed. Note that Convert.ToInt32 on an empty string or null will yield this error message as well.

Everything being added to the database twice could be because you are not actually using e.OldValues(i) in your INSERT query, but some other variable called oldValue. It's hard to say without more code sample, but your best bet is to learn your debugging tools and watch what is happening.

Upvotes: 0

Steve Wellens
Steve Wellens

Reputation: 20620

Create the SQL insert string in a separate variable. Then you can look at it in the watch window and see what is happening.

Something like...

Dim SQl as String = 'INSERT INTO tblAud (TN, FN, tID, OldValue, UserID) VALUES ('" + tN + "', '" + fn + "'," + tID + ", '"+ oldValue + "'," + uid + ")"'

Upvotes: 1

Related Questions