0

As the title says, I'm getting an error from my code below, which basically selects multiple inputs on a ListView, and puts it inside a database. Here's the code:

    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        For Each item As ListViewItem In ListView1.SelectedItems
        Dim StudentID = Integer.Parse(item.SubItems(0).Text)
        Dim FirstName = item.SubItems(1).Text
        Dim LastName = item.SubItems(2).Text
            DBConn()
            SQLSTR = "INSERT INTO '" & TextBox4.Text & "' (StudentID, FirstName, LastName) VALUES ('" & StudentID & "', '" & FirstName & "', '" & LastName & "') "
            alterDB()
            MsgBox("Students succesfully added", msgboxtitle)
        Next
    MsgBox("Students added!", , msgboxtitle)
End Sub

The exact error is:

An unhandled exception of type 'System.InvalidCastException' occurred in Microsoft.VisualBasic.dll

Additional information: Conversion from string "" to type 'Integer' is not valid.

Community
  • 1
  • 1
  • I assume the error is on the line Dim StudentID = Integer.Parse(item.SubItems(0).Text)? Can you step through to see what is the value of item.SubItems(0).Text when the error occurs? Possibly there are some non numerical characters such as spaces – exceptional exception Jan 23 '15 at 15:19
  • 3
    You should not build sql by concatenating string like this. This is prone to sql injection. – Rémi Jan 23 '15 at 15:23
  • @user3479671 When I check the database, the first line in the list view gets in, but the other lines doesn't so I assume the error comes from the looping? If that makes sense –  Jan 23 '15 at 15:25
  • @im_a_noob is there a way to change it? I'm a beginner in coding and this is what I got from asking and formulating around. –  Jan 23 '15 at 15:27
  • regarding the sql injection thing, you'd have to pass the form values to a variable and validate them before including them in a sql string. That aside, when you step through the program, put your mouse over item.SubItems(0).Text) when it errors, a tool tip should pop up to tell you its value – exceptional exception Jan 23 '15 at 15:32
  • @MicoRigunay I see that the question is now tagged vb.net (I think it was VBA when I first commented). You have to use parameterized query. Here a link that should get your started. http://stackoverflow.com/questions/542510/how-do-i-create-a-parameterized-sql-query-why-should-i With this link and google you should figure it out. If not simply another question here on SO – Rémi Jan 23 '15 at 15:35
  • it is usually the job of the DB to assign IDs, so you should not be passing that at all. In cases when you do pass it - eg in a SELECT or UPDATE - pass it as an int, not text. This is best done with SQL Parameters – Ňɏssa Pøngjǣrdenlarp Jan 23 '15 at 15:38
  • @MicoRigunay that explains the integer.parse. I was wondering about that myself – exceptional exception Jan 23 '15 at 15:39

1 Answers1

1

You can use CInt() to convert to Integer. MSDN Reference

I'd recommend Dim variable As Type before you define the variable though.

    Dim item As ListViewItem        
    Dim StudentID as Integer
    Dim FirstName as String
    Dim LastName as String

    For Each item In ListView1.SelectedItems
        StudentID = CInt(item.SubItems(0).Text)
        FirstName = item.SubItems(1).Text
        LastName = item.SubItems(2).Text
Chrismas007
  • 6,085
  • 4
  • 24
  • 47
  • I'm still getting the same error though. It's weird because the first line on my ListView gets in the database, but the next lines do not. –  Jan 23 '15 at 15:31
  • @Chrismas007 the OP would also have to get rid of the ticks around `StudentID` that would pass the Int as text to the db layer – Ňɏssa Pøngjǣrdenlarp Jan 23 '15 at 15:35
  • @MicoRigunay just out of curiosity, does the database require that the StudentID be numeric? It seems odd considering it's name – exceptional exception Jan 23 '15 at 15:36
  • @Chrismas007 never mind, got it to work. I removed the MsgBox on the loop and it got fixed. Thanks! –  Jan 23 '15 at 15:39