comparing data value

Question

User66371569 posted

Hi I am using below code to  compare my textbox date with database value date if same then show error    but the problem here    empno 1234  who I compare  has more than row and has more than one date   how to edit in my code to bring all his dates save in database and compare  with textbox1

Dim con As New SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("sss").ToString())

Dim cmaa1000 As New SqlCommand("SELECT FORMAT(dfrom,'MMM-yyyy') as dfrom FROM HPDQUALIFICATION WHERE EMPNO='1234' ", con) 

con.Open()

Dim reader1000 As SqlDataReader = cmaa1000.ExecuteReader()
If reader1000.Read() Then
If TextBox1.Text = reader1000("dfrom").ToString() Then
'
msg.Text = "Cannot insert date you insert same before"
Else
msg.Text = ""

con.Close()
End If
End If

Answer 1

User475983607 posted

Add a filter to the SQL WHERE clause rather than filtering in VB.NET  Keep in mind that DateTime types include the time.  If your FromDate column has time then you'll need to adjust the SQL.  Also your con.Close() logic should not be within an IF as you want to make sure the connections is always closed.

Dim con As New SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("sss").ToString())

Dim cmaa1000 As New SqlCommand("SELECT FORMAT(dfrom,'MMM-yyyy') as dfrom FROM HPDQUALIFICATION WHERE EMPNO='1234' AND dfrom <> @fromDate ", con) 

cmaa1000.Parameters.Add("@fromDate", SqlDbType.DateTime);
command.Parameters["@fromDate"].Value = myFromDateVariable;

con.Open()

Dim reader1000 As SqlDataReader = cmaa1000.ExecuteReader()
If reader1000.Read() Then

End If

con.Close()

You can learn basic SQL syntax by reading openly published docs.

https://docs.microsoft.com/en-us/sql/t-sql/language-reference?view=sql-server-2017

Answer 2

User66371569 posted

what I want is comparing textbox  with  data from database

example

if  data from database        jan-2014 ,  feb-2019, sep-2010 

and I write in textbox     feb-2019    I want   code check in database if  textbox =  to value in database  show error  " cannot insert same date you inserted before"

that's what I want exactly 

Answer 3

User61956409 posted

Hi thepast,

To achieve the requirement, you can try to use the following code snippet.

Dim con As New SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("sss").ToString())
Dim cmaa1000 As New SqlCommand("SELECT FORMAT(dfrom,'MMM-yyyy') as dfrom FROM HPDQUALIFICATION WHERE EMPNO='1234' ", con)

con.Open()

Dim reader1000 As SqlDataReader = cmaa1000.ExecuteReader()
While reader1000.Read()
    If TextBox1.Text = reader1000("dfrom").ToString() Then
        '
        msg.Text = "Cannot insert date you insert same before"
        Return
    Else
        msg.Text = ""
    End If
End While
reader1000.Close()
con.Close()

With Reagards,

Fei Han

Answer 4

User66371569 posted

if empno  1234  has more than  one value   how can if  will check      this code if select return one  value

Answer 5

User66371569 posted

thank you