I have date error in SQL server database while using it with my VB.NET application

Question

1.00/5 (1 vote)

See more:

VS2013

SQL-Server-2014

I'm getting this error after migrating my database from Access to SQL Server .

below are that actions I need do but all of them gave me error "SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM."

1- I need to search for all records that are exceeded 24 hours or 1 day
2- I need to search for all records with DateAdded = Pick_DateAdded.Text
3- All records with specific fixed values in two data columns

Thanks in advance

What I have tried:

Private Sub Supplier_Query()
      'Dim sqlQRY As String
      Try
          Dim Yesterday As DateTime
          'call needed query 1st and Update the datatable with the escalatoin date to the supplier
          If cnnOLEDB.State = ConnectionState.Open Then
              cnnOLEDB.Close()
          End If
          cnnOLEDB.ConnectionString = strConnectionString
          cnnOLEDB.Open()
          If SLA_chk.Checked = True Then
              Yesterday = Today.AddDays(-(SLA_TXT.Value))
              sqlQRY = "SELECT * FROM Customers WHERE SupplierName = '" & SuppNameVariable.Text & "' AND Supplier_Feedback = '.....' AND DateAdded <= @Yesterday"

          ElseIf Chk_Specofic_date.Checked = True Then
              sqlQRY = "Select * From Customers WHERE SupplierName = '" & SuppNameVariable.Text & "' AND Supplier_Feedback = '.....' AND DateAdded = @DatePickerValue"
          ElseIf SLA_TXT.Text = "0" Then
              sqlQRY = "Select * From Customers WHERE SupplierName = '" & SuppNameVariable.Text & "' AND Supplier_Feedback = '.....'"
          End If

          Dim da As New SqlDataAdapter(sqlQRY, cnnOLEDB)
          Dim ds As DataSet = New DataSet
          da = New SqlDataAdapter(sqlQRY, cnnOLEDB)
          da.SelectCommand.Parameters.AddWithValue("@Yesterday", Yesterday)
          da.SelectCommand.Parameters.AddWithValue("@DatePickerValue", Pick_DateAdded.Text)

          Dim cb As SqlCommandBuilder = New SqlCommandBuilder(da)
          'Dim d As SqlCommand
          'd = New SqlCommand

          da.Fill(ds, "Customers")
          Alerting_Escalation_Tool.DataGridViewCustomer.DataSource = ds
          Alerting_Escalation_Tool.DataGridViewCustomer.DataMember = "Customers"
          cnnOLEDB.Close()

Posted 22-Sep-19 21:43pm

BassamKassem

Updated 22-Sep-19 21:51pm

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)

OriginalGriff · Accepted Answer · 2019-09-22T21:51:00

Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead.

When you concatenate strings, you cause problems because SQL receives commands like:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'

The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'

Which SQL sees as three separate commands:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'x';

A perfectly valid SELECT

SQL

DROP TABLE MyTable;

A perfectly valid "delete the table" command

SQL

--'

And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.

So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?

Fix that - throughout your whole app, miss one and your DB is at risk - and you will almost certainly find the problem you are working on has gone as well.

Quote:
I'm not very much familiar with parameterised queries that's why am lost

Basics:

VB

Using con As New SqlConnection(strConnect)
	con.Open()
	Using cmd As New SqlCommand("SELECT description FROM myTable WHERE ID = @ID", con)
        cmd.Parameters.AddWithValue("@ID", idToSearchFor)
		Using reader As SqlDataReader = cmd.ExecuteReader()
			While reader.Read()
				Dim desc As String = DirectCast(reader("description"), String)
				Console.WriteLine("ID: {0}" & vbLf & "    {1}", idToSearchFor, desc)
			End While
		End Using
	End Using
End Using

For full details Google "Parameterised queries VB" and start reading!