CODEWITHSUNDEEP
vb.netstringsql-server-2008smalldatetime
Esteves
Esteves

Reputation: 35

Vb select using dates

This is my code, i want to select data from a database that is between two dates:

          Dim Sql As String = "Select strCodSeccao,strAbrevTpDoc,strCodExercicio,strNumero From Mov_Venda_Cab where dtmdate between " & date1 & " and " & date2 & ";"

The sqlserver database dtmdate field is smalldatetime and here is the code from date1 and date2:

            Dim data1, data2 As DateTime
            data1 = DateTime.Parse(txtDate1.Text)
            data2 = DateTime.Parse(txtDate2.Text)

it says argument exception and doesnt execute the select command

thanks in advance __________________________/____________________________________/_______________

thanks Steve, i used your code and now i can select form a sql database to a another database and now it works 

            Dim x As Integer = 0
            Dim temp1, temp2, temp3, temp4 As String


            Dim Sql As String = "Select strCodSeccao,strAbrevTpDoc,strCodExercicio,strNumero " & _
                 "From Mov_Venda_Cab where dtmdata between @d1 and @d2"


            Dim data1, data2 As DateTime
            data1 = DateTime.Parse(txtData1.Text)
            data2 = DateTime.Parse(txtData2.Text)

            data2 = data2.AddMinutes(0)
            data2 = data2.AddHours(0)
            data2 = data2.AddSeconds(0)

            data1 = data1.AddMinutes(0)
            data1 = data1.AddHours(0)
            data1 = data1.AddSeconds(0)


            Using con = New SqlConnection("Data Source=" & txtserv.Text & ";" & "Initial Catalog=" & txtBD.Text & ";" & "User ID=" & txtuser.Text & ";" & "Password=" & txtPass.Text & "")
                Using cmd = New SqlCommand(Sql, con)
                    con.Open()
                    cmd.Parameters.AddWithValue("@d1", data1)
                    cmd.Parameters.AddWithValue("@d2", data2)
                    Using reader = cmd.ExecuteReader()
                        While reader.Read()
                            Dim strCodSeccao = reader("strCodSeccao").ToString()
                            temp1 = reader.Item(x)
                            temp2 = reader.Item(x + 1)
                            temp3 = reader.Item(x + 2)
                            temp4 = reader.Item(x + 3)


                            Dim Con2 As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Utilizador.Utilizador-PC\Documents\Visual Studio 2013\Projects\WindowsApplication1\WindowsApplication1\Doc_Vendas_Cab.mdb;Persist Security Info=True")
                            Con2.Open()
                            Dim Ole2 As String = "Insert into Mov_Venda_Cab values('" & temp1 & "','" & temp2 & "','" & temp3 & "','" & temp4 & "');"
                            Dim OledbCom2 As New OleDb.OleDbCommand(Ole2, Con2)

                            Try
                                OledbCom2.ExecuteNonQuery()
                            Catch Ex As Exception
                                MsgBox(Ex)
                            End Try
                            Con2.Close()
                        End While
                    End Using
                End Using
            End Using

Upvotes: 1

Views: 162

Answers (1)

Steve
Steve

Reputation: 216253

You should use a parameterized query and pass the datetime values

 Dim Sql As String = "Select strCodSeccao,strAbrevTpDoc,strCodExercicio,strNumero " & _
                     "From Mov_Venda_Cab where dtmdate between @d1 and @d2"


 Dim data1, data2 As DateTime
 data1 = DateTime.Parse(txtDate1.Text)
 data2 = DateTime.Parse(txtDate2.Text)
 data2 = data2.AddMinutes(1439)

 Using con = new SqlConnection("connection string here")
 Using cmd = new SqlCommand(Sql, con)
     con.Open()
     cmd.Parameters.AddWithValue("@d1", data1)
     cmd.Parameters.AddWithValue("@d2", data2)
     Using reader = cmd.ExecuteReader()
         While reader.Read() Then
             Dim strCodSeccao = reader("strCodSeccao").ToString()
             .....
         End While
     End Using
 End Using
 End Using

Notice that I add 1439 minutes to the second date to be inclusive of the last day in case your datetime field includes also values with hours/minutes/seconds set.

Upvotes: 2

Related Questions