I have a problem getting results from SQL database for two dates in datepicker

Question

0.00/5 (No votes)

See more:

VS2013

SQL-Server-2014

I have a problem getting results from SQL database for two dates in DatePicker .. noting that i'm using SQL Server in local machine and the field i'm referring to its data type in SQL Server is "datetime2(0)"

I have tried this still gives me this error "Incorrect syntax near '?'."

Dim ds As DataSet = New DataSet
'da = New SqlDataAdapter(sqlQRY, cnnOLEDB)

Const sqlQRY As String = "Select * From Customers WHERE DateAdded >= ?"
Dim da As New SqlDataAdapter(sqlQRY, cnnOLEDB)
da.SelectCommand.Parameters.AddWithValue("@p0", Today)

What I have tried:

Private Sub CMD_Go_Date_Click(sender As Object, e As EventArgs) Handles CMD_Go_Date.Click
        Try
            Dim sqlQRY As String
            sqlQRY = "Select * From Customers WHERE DateAdded Between #" + Pick_DateFrom.Value + "# And #" + Pick_DateTo.Value + "#"
            Dim da As SqlDataAdapter
            Dim ds As DataSet = New DataSet
            da = New SqlDataAdapter(sqlQRY, cnnOLEDB)
            Dim cb As SqlCommandBuilder = New SqlCommandBuilder(da)
            da.Fill(ds, "Customers")
            DataGridViewCustomer.DataSource = ds
            DataGridViewCustomer.DataMember = "Customers"
            lbl_RowCount.Text = DataGridViewCustomer.RowCount
            Dim row As Integer
            If DataGridViewCustomer.RowCount > 0 Then
                row = DataGridViewCustomer.FirstDisplayedCell.RowIndex
                DataGridViewCustomer.Rows(row).Selected = True
                DataGridViewCustomer.Sort(DataGridViewCustomer.Columns(6), ListSortDirection.Ascending)
                If RowIndex_Cust_TXT.Text = vbNullString Then
                    Exit Sub
                Else
                    Me.DataGridViewCustomer.ClearSelection()
                    Me.DataGridViewCustomer.Rows(RowIndex_Cust_TXT.Text).Selected = True
                    Me.DataGridViewCustomer.FirstDisplayedScrollingRowIndex = RowIndex_Cust_TXT.Text
                End If
            Else
                row = 0
            End If
        Catch ex As SqlException
            MessageBox.Show(ex.Message)
        End Try
    End Sub

Posted 22-Sep-19 4:09am

BassamKassem

Updated 22-Sep-19 5:02am

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Dave Kreskowiak · Accepted Answer · 2019-09-22T05:02:00

Solution 1

You're using an Access query with SQL Server. Obviously, that doesn't work. Also, NEVER use string concatenation to build an SQL query. ALWAYS use parameters.

In Access, parameters are denoted with a ? and no name. In SQL Server, they are named parameters denoted with a @:

sqlQry = "SELECT <fieldList> FROM Customers WHERE DateAdded BETWEEN @StartDate AND @EndDate"
da.SelectCommand.Parameters.Add("@StartDate", Pick_DateFrom.Value);
da.SelectCommand.Parameters.Add("@EndDate", Pick_DateTo.Value);

Of course, this assumes you've checked your DatePicker values to make sure they are valid and in the correct order.

Posted 22-Sep-19 5:02am

Dave Kreskowiak

Updated 22-Sep-19 5:03am

v2

Comments

BassamKassem 22-Sep-19 11:59am

your solution is working and i thank you for your help, but final question or help please. how to make my solution work on network drive to be used by different users , before migrating my database from access to SQL i used to point to the mdb file using below command "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & System.Environment.CurrentDirectory & "\FileName.mdb"

Dave Kreskowiak 22-Sep-19 13:59pm

SQL Server is not a "file-based" database engine. It doesn't use a shared drive. Clients connect to the database, typcially, over TCP/IP.