Error loading sql parameters in a loop, VB.Net

Question

I am currently working in Microsoft visual studio express 2013 with an sql back end. I am trying to run a loop through 2 comboboxes and a datetimepicker for any instance a checkbox is checked. However, I am running into an error that reads "System.ArgumentException: No Mapping exists from Object type system.windows.forms.datetimepicker to a known managed provider native type." When I run the code I have put a watch on the parameter value and it is not saving the data into the variable before the sql command fires. I think I need to store the variable in a different way to allow access to the variable. Here is my code:

Try

Using conn1 As New SqlConnection(connstring)
    conn1.Open()
    Using comm1 As New SqlCommand("SELECT isnull(max(AuditID) + 1, 1) as 'AuditID' FROM table1", conn1)
        Dim reader1 As SqlDataReader = comm1.ExecuteReader
        reader1.Read()
        Dim AuditID As Integer = reader1("AuditID")
        reader1.Dispose()

        'Loop through all checkboxes and write into sql tables
        Dim Run As Integer
        For Run = 1 To 5
            Dim LineItem = DirectCast(Me.Controls("CB" & Run), CheckBox)
            If LineItem.Checked = True Then
                Dim DateTime = DirectCast(Me.Controls("DTP" & Run), DateTimePicker)
                Dim Frequency = DirectCast(Me.Controls("CBWeek" & Run), ComboBox)
                Dim Repeat = DirectCast(Me.Controls("CBRepeat" & Run), ComboBox)

                'sql statements
                'select ID
                Using conn2 As New SqlConnection(connstring)
                    conn2.Open()
                    Using comm2 As New SqlCommand("SELECT isnull(max(AuditID) + 1, 1) as 'ID' FROM table1", conn1)
                        Dim reader As SqlDataReader = comm2.ExecuteReader
                        reader.Read()
                        Dim ID As Integer = reader("ID")
                        reader.Dispose()

                        'Insert into table audit line
                        Using conn3 As New SqlConnection(connstring)
                            conn3.Open()
                            Using comm3 As New SqlCommand("INSERT INTO table1 (ID, AuditID, DateStart, Freq, repeats) " _
                                                          & "VALUES (@ID, @AuditID, @DateStart, @Freq, @Repeats)", conn3)
                                With comm3.Parameters
                                    .AddWithValue("@ID", ID)
                                    .AddWithValue("@AuditID", AuditID)
                                    .AddWithValue("@DateStart", DateTime)
                                    .AddWithValue("@Freq", Frequency)
                                    .AddWithValue("@Repeats", Repeat)
                                End With
                                comm3.ExecuteNonQuery()
                            End Using
                            conn3.Close()
                        End Using
                    End Using
                    conn2.Close()
                End Using
            End If
        Next
    End Using
    conn1.Close()
End Using

Catch ex As Exception
    MsgBox(ex.ToString)

My try statement stop my code on this line:

 comm3.ExecuteNonQuery()

However, I know that this error is coming from when I add my parameters, specifically these 3 lines:

.AddWithValue("@DateStart", DateTime)
.AddWithValue("@Freq", Frequency)
.AddWithValue("@Repeats", Repeat)

I am trying to get tese variables with a loop statement based on the design name here:

Dim DateTime = DirectCast(Me.Controls("DTP" & Run), DateTimePicker)
Dim Frequency = DirectCast(Me.Controls("CBWeek" & Run), ComboBox)
Dim Repeat = DirectCast(Me.Controls("CBRepeat" & Run), ComboBox)

It does not seem like the program likes using these dimensions above to be inserted into the sql table. Does anyone know a way I can carry these values over to the sql parameters statement?

Thanks for the code edit plutonix, I was just trying to figure that one out. — Cheddar, Feb 05 '15 at 13:55
Project Properties => Compile => Option Strict CBO to ON. or `Option Strict On` at the top of each source file — Ňɏssa Pøngjǣrdenlarp, Feb 05 '15 at 13:58
you have several dubious type conversions: `Dim DateTime = DirectCast(Me.Controls("DTP" & Run), DateTimePicker)` first, DateTime is a type so it is a bad name, but here it will contain a Control after the cast. Later `.AddWithValue("@DateStart", DateTime)` is trying to add the control to the DB rather than the `.Value` of that control — Ňɏssa Pøngjǣrdenlarp, Feb 05 '15 at 14:00
Ahhh, so I need to use variables to convert them to the .value type. — Cheddar, Feb 05 '15 at 14:00
the same with the others - if you fix the DTP it will reveal the same problem with Frequency — Ňɏssa Pøngjǣrdenlarp, Feb 05 '15 at 14:02
you can also specify more than one object per Using block. "Stacking" `comm3` and `conn3` would reduce indentation. Also, you could use the same connection object for both parts. for a "stacking" example: http://stackoverflow.com/a/28322399/1070452 — Ňɏssa Pøngjǣrdenlarp, Feb 05 '15 at 14:19

score 1 · Accepted Answer · answered Feb 05 '15 at 14:11

There are a number of things I would do differently. First and always, use Option Strict, it will catch some of the type conversion you have going on.

I would get the controls from an explicit list rather then fetching from Controls. Just make a few arrays to hold the control refs so you do not need to to find them in the collection:

Private DTPs As DateTimePicker() = {DTP1, DTP2...}

This will avoid the need to cast them, fewer hoops and implied converts like "DTP" & Run:

Dim dt As DateTime           ' vars for the SQL
Dim freq As Integer
For Run As Integer = 0 To 4
    dt = DTPs(Run).Value
    freq = cboFreq(Run).SelectedValue
    ...

score 0 · Answer 2 · answered Feb 05 '15 at 14:05

I have fixed the problem, I needed to put my loop variables into .value type variables. I added this to fix it:

              Dim DateTime = DirectCast(Me.Controls("DTP" & Run), DateTimePicker)
                        Dim Frequency = DirectCast(Me.Controls("CBWeek" & Run), ComboBox)
                        Dim Repeat = DirectCast(Me.Controls("CBRepeat" & Run), ComboBox)



                        Dim Time As Date = DateTime.Value
                        Dim Freq As Integer = Frequency.SelectedValue
                        Dim Again As Integer = Repeat.SelectedValue

Error loading sql parameters in a loop, VB.Net

2 Answers2