locked
SQL Statement > SELECT date

    Question

  •  

    Hello to all first of all and please escuse me for my bad english.

     

    My problem starts here:

     

    I have for that i want to make some checking stuff on the database (Access database).

    On the source code i have:

     

    Code Block

    Dim actual_date As String

    actual_date = Date.Now.ToString("dd/MM/yyyy")

     

     

    On the database i have a column named "expiration_date" and a "status" column.

     

    All i want to do it's to chech ALL the (text) fields from the "expiration_date" column that are smaller than the current date given by "actual_date" string.

     

    If it is smaller than the current date to write on the "status" fileds "Inactive".

     

     

    I cannot handle this... i am not sure if this is a good start or not!

    Code Block

     

    Dim actual_date As String

    actual_date = Date.Now.ToString("dd/MM/yyyy")

    Dim cn As New OleDbConnection(Conexiune)

    cn.Open()

    Dim sql As String = "SELECT expiration_date FROM list WHERE expiration_date = '" & Trim(Replace(actual_date, "'", "k")) & "'"

    Dim cmd As New OleDbCommand(sql, cn)

     

     

    Thanks and i hope someone can help me!
    Saturday, January 19, 2008 9:06 PM

Answers

  •  

    Here is your problem. In terms of the way you are setting this up.

     

    31/10/1900   is larger (later)  than 1/10/2007.

     

    the way you are going about this, you are paying attention to the way humans look at dates as compared to how computers do.

    Saturday, January 19, 2008 11:42 PM
  • You have several problems and you must think of how the computer organize data first and how you will display it sensibly to users second. If you don't do that, you'll never receive a useable result for any human to even see.

     

    You have several problems. I'm talking from the experience of having made the mistake of having stored strings. You really need to store dates and times/not as string but in date time format. They are much easier to manipulate using .net methods. Other wise you will constantly be doing string manipulations.

    Sunday, January 20, 2008 12:15 AM
  • hi alex,

    From my understanding of your problem, you just want to check expiration date as against current date. If this is true, this may help.........

     

    Dim actual_date As Date =  Datetime.Today

    Dim strStatus As String

    Dim dr As DataReader  ' you have to check the syntax as i do not use access

    Dim cn As New OleDbConnection(Conexiune)

    Dim sql As String = SELECT expiration_date FROM Table UPDATE Table SET Status = @status

    Dim cmd As New OleDbCommand(sql, cn)

     

    If cn.State = ConnectionState.Closed Then

    cn.Open()

    End If

     

    With cmd.Parameters

    .Add(New SqlClient.SqlParameter("@status", SqlDbType.VarChar, 100)).Value = _

    strStatus

    End With

     

    dr = cmd.ExecuteReader()

    Try

    If dr.HasRows Then

    While dr.Read

    If expiration_date < actual_date Than

    strStatus = "Inactive"

    Else :

    strStatus = "Active"

    End While

    End If

     

    Catch ex As Exception

    MsgBox(ex.Message.ToString)

    Finally

    dr.Close()

    cn.Close()

    End Try

     

    Note: As Renee has said, you will first have to get the expiration date as date rather than string. I personally think that you will be able to achieve the task with string also, but that will be a waste of your time and resources.

     

    regards

    rajeev

    Sunday, January 20, 2008 8:53 AM
  • SELECT valabilitate_permis_sfarsit

     

    This selects a column in a results set. The results set is would be read by the data reader, but you have a larger problem, you have only asked for valabilitate_permis_sfarsit and not the whole record.

     

    I'd do it a little differently since you want complete data from multiple records.

     

     

    Public Function GetTable(ByVal tableName As String, ByVal Cmd As String) As _

                             DataTable

            Dim table As New DataTable(tableName)

            Using Adapter = New OledbDataAdapter(Cmd, Con)

                Try

                    Con.Open()

                    Adapter.Fill(table)

                    table.TableName = tableName

                Catch e As Exception

                    Con.Close()

                    System.Windows.Forms.MessageBox.Show(e.Message.ToString(), _
                    "GetTable")

                    Return Nothing

                Finally

                    Con.Close()

                End Try

            End Using

            Return table

        End Function

     

    Note:

    1.)This method returns a table

    2.)This method is a member of a class which has con which is a connection

    3.)Let’s call the instance of the class IOEngine

    But I would call it like this:

    Dim Table as datatable = IOEngine.GetTable( “Table”, _
    “Select * from
    lista_abonati where valabilitate_permis_sfarsit < ‘” + Expdate.Tostring + “’;”)

    4.)    Expdate is a date datatype.

     

    Monday, January 21, 2008 12:53 AM

All replies

  •  

    Date.Now.ToString("dd/MM/yyyy")

     

    This needs to go from most signficant to least significant

     

    Date.Now.ToString("yyyy/MM/dd")

     

    You will also need to check the format/datatype if the database to insure that it works the same.

    Saturday, January 19, 2008 9:51 PM
  •  

    Date.Now.ToString("dd/MM/yyyy") is an Romanian date format.

     

    The database fields are declared as TEXT fields.

    Saturday, January 19, 2008 10:07 PM
  •  

    Here is your problem. In terms of the way you are setting this up.

     

    31/10/1900   is larger (later)  than 1/10/2007.

     

    the way you are going about this, you are paying attention to the way humans look at dates as compared to how computers do.

    Saturday, January 19, 2008 11:42 PM
  • Thanks ReneeC

     

    Yes ReneeC, i am paying attention to the way humans look at dates because we are making (you, me and others) the software more simple for the users. I dont wana make a software to be know and used only by me. I make all this for the users. That's why i am here... that's why WE are here... i want to solve my problem not to simplify it in context of computers!

    Sunday, January 20, 2008 12:06 AM
  • You have several problems and you must think of how the computer organize data first and how you will display it sensibly to users second. If you don't do that, you'll never receive a useable result for any human to even see.

     

    You have several problems. I'm talking from the experience of having made the mistake of having stored strings. You really need to store dates and times/not as string but in date time format. They are much easier to manipulate using .net methods. Other wise you will constantly be doing string manipulations.

    Sunday, January 20, 2008 12:15 AM
  • OK! I have understood... and now from where shold i begin ?!

    Sunday, January 20, 2008 12:19 AM
  •  

    If I were you, I would back up the datbase and change the datatypes of the coluns having strings instead of date/times.
    Sunday, January 20, 2008 8:11 AM
  • hi alex,

    From my understanding of your problem, you just want to check expiration date as against current date. If this is true, this may help.........

     

    Dim actual_date As Date =  Datetime.Today

    Dim strStatus As String

    Dim dr As DataReader  ' you have to check the syntax as i do not use access

    Dim cn As New OleDbConnection(Conexiune)

    Dim sql As String = SELECT expiration_date FROM Table UPDATE Table SET Status = @status

    Dim cmd As New OleDbCommand(sql, cn)

     

    If cn.State = ConnectionState.Closed Then

    cn.Open()

    End If

     

    With cmd.Parameters

    .Add(New SqlClient.SqlParameter("@status", SqlDbType.VarChar, 100)).Value = _

    strStatus

    End With

     

    dr = cmd.ExecuteReader()

    Try

    If dr.HasRows Then

    While dr.Read

    If expiration_date < actual_date Than

    strStatus = "Inactive"

    Else :

    strStatus = "Active"

    End While

    End If

     

    Catch ex As Exception

    MsgBox(ex.Message.ToString)

    Finally

    dr.Close()

    cn.Close()

    End Try

     

    Note: As Renee has said, you will first have to get the expiration date as date rather than string. I personally think that you will be able to achieve the task with string also, but that will be a waste of your time and resources.

     

    regards

    rajeev

    Sunday, January 20, 2008 8:53 AM
  • Thanks alot rajeev_learning_vb2005.

    I have tried to modify your code but is not working... "stare_permis" is the same in the database.

    In the database i have modified the "valabilitate_permis_sfarsit" as Date/Time format as ReneeC said with a Short Date and format as ##/##/####.

    What's wrong ?

    Code Block

    Private Sub verificare_date()

     

    Dim actual_date As Date = DateTime.Today

    Dim stare_permis As String

    Dim dr As OleDbDataReader ' you have to check the syntax as i do not use access

    Dim cn As New OleDbConnection(Conexiune)

    Dim sql As String = "SELECT valabilitate_permis_sfarsit FROM lista_abonati UPDATE lista_abonati SET stare_permis = @stare_permis"

    Dim cmd As New OleDbCommand(sql, cn)

     

    If cn.State = ConnectionState.Closed Then

    cn.Open()

    End If

     

    cmd.Parameters.Add(New OleDbParameter("@stare_permis", OleDbType.VarChar, 100)).Value = stare_permis

     

    dr = cmd.ExecuteReader()

     

    Try

    If dr.HasRows Then

    While dr.Read

    If "valabilitate_permis_sfarsit" < actual_date Then

    stare_permis = "Expirat"

    Else

    stare_permis = "Activ"

    End If

    End While

    End If

     

    Catch ex As Exception

    MsgBox(ex.Message.ToString)

    Finally

     

    dr.Close()

    cn.Close()

     

    End Try

    End Sub

     

     

    With this code it gives me the error "Syntax error in FROM clause."
    Sunday, January 20, 2008 8:01 PM
  •  

    Are you using latin column names?

     

    I don't understand this code. It looks like a blend of updating code and comparision code.

     

     

    If "valabilitate_permis_sfarsit" < actual_date Then stare_permis = "Expirat"

     

    How does this work? You are comparing a fixed string?  At no time do I see you using the reader????

    Sunday, January 20, 2008 10:15 PM
  • If i use it like this i receive "Name 'valabilitate_permis_sfarsit' is not declared". And i think it has the right answer but i dont know where to declare it!

    Code Block

    If valabilitate_permis_sfarsit < actual_date Then

    str_stare_permis = "Expirat"

    Else

    str_stare_permis = "Activ"

    End If

     

     

     

    Against

     

    Code Block
    dr = cmd.ExecuteReader()

     

     

     

    i have used

     

    Code Block

    dr = cmd.ExecuteScalar()

     

     

    It's OK ?

     

     

    Are you using latin column names?

     

    It's Romanian column names... yes it's latin column name but i dont use local diacritics.

    Sunday, January 20, 2008 10:51 PM
  • SELECT valabilitate_permis_sfarsit

     

    This selects a column in a results set. The results set is would be read by the data reader, but you have a larger problem, you have only asked for valabilitate_permis_sfarsit and not the whole record.

     

    I'd do it a little differently since you want complete data from multiple records.

     

     

    Public Function GetTable(ByVal tableName As String, ByVal Cmd As String) As _

                             DataTable

            Dim table As New DataTable(tableName)

            Using Adapter = New OledbDataAdapter(Cmd, Con)

                Try

                    Con.Open()

                    Adapter.Fill(table)

                    table.TableName = tableName

                Catch e As Exception

                    Con.Close()

                    System.Windows.Forms.MessageBox.Show(e.Message.ToString(), _
                    "GetTable")

                    Return Nothing

                Finally

                    Con.Close()

                End Try

            End Using

            Return table

        End Function

     

    Note:

    1.)This method returns a table

    2.)This method is a member of a class which has con which is a connection

    3.)Let’s call the instance of the class IOEngine

    But I would call it like this:

    Dim Table as datatable = IOEngine.GetTable( “Table”, _
    “Select * from
    lista_abonati where valabilitate_permis_sfarsit < ‘” + Expdate.Tostring + “’;”)

    4.)    Expdate is a date datatype.

     

    Monday, January 21, 2008 12:53 AM