locked
Write a query with count function for same project number and any date in the current year?? RRS feed

  • Question

  • User-1797368610 posted

    Hi,

    i am trying to check a table which has two columns(one is project_num and another one is INSPECT_DATE ).  write a query to prevent insert the values if i have same project number and any INSPECT_DATE in the current year into oracle table. all i want to use count function, so that query can count for the same project and any date exist for the current year. from the below query what i need to do to get the column existing date from oracle database.

    query = " select count(project_num) count from PHY_INSP_SCHEDULE where project_num='" & Ddl_proj.SelectedItem.Value & _

    "' and INSPECT_DATE = dt.ToString(""yyyy-MM-dd"") "
    If SavedAlready(query) > 0 Then
    alert = "<script language='javascript'>" & _
    "alert ('This Project is already saved. you cannot make different schedule within year with same project')"
    End If

    Monday, January 22, 2018 9:45 PM

All replies

  • User475983607 posted

    What probem are you truing to solve?

    Perhaps you should try a parameter query as that should solve the date issue and protect your code against SQL injection. 

    https://www.codeproject.com/Tips/1076851/Oracle-Parameterized-Queries-for-the-NET-Developer

    Try using the Visual Studio debugger to find issues in the code.  Also try running the SQL script using SQL developer or SQL Prompt to make sure the script is correct.

    Monday, January 22, 2018 11:42 PM
  • User-1797368610 posted

    Thanks for your reply. all i am looking for to check in the database whether same project number already exist or not and any date for the current year. lets say i have project no "001923" and Date "02/12/2018" already in the database so when user will try to insert the same project no and date could be different date in the current year. User can create the schedule once within a year for the same project no. i would like to restrict the user so that user wouldn't be able to create another schedule with in same project no. if you look at my below query, its trying to count the project no but problem with the INSPECT_DATE. how can i check for different date in the database because when i am passing parameter values, project no will be same but date could be different.

    query = " select count(project_num) count from PHY_INSP_SCHEDULE where project_num='" & Ddl_proj.SelectedItem.Value & _"' and INSPECT_DATE = PHYSICAL_INSPECT_DATE = '" & txtuidate.Text & "'"

    If SavedAlready(query) > 0 Then
    alert = "<script language='javascript'>" & _
    "alert ('This Project is already saved. you cannot make different schedule within year with same project')"
    End If

    Tuesday, January 23, 2018 3:36 PM
  • User475983607 posted

    Rakib1

    Thanks for your reply. all i am looking for to check in the database whether same project number already exist or not and any date for the current year. lets say i have project no "001923" and Date "02/12/2018" already in the database so when user will try to insert the same project no and date could be different date in the current year. All i am trying to restrict  the user to create schedule one time for the current year.

    Information is missing from your post. Keep in mind that we cannot see you database schema or data and can only guess that the query is incorrect.  Since you have not reported exceptions, either the query syntax is correct or you are hiding exception.   If the query is correct then the parameters might not exist in the table.  You need to verify the parameters exist in the table as we cannot see your table.  If you are hiding exception in a try...catch or not telling us about the exception, well, we can't see that either.

    I would craft a parameter query as shown in the previous link.  This will help solve any casting issues you might have with the current string built SQL.  Other than that, you need to debug your code or provide an example that reproduces the issue.

    Tuesday, January 23, 2018 3:53 PM
  • User269602965 posted

    MERGE COMMAND (available in Oracle, MSSQL and perhaps other databases)

    Merge example the database table PROJECTS against a temporary table with the new information matched by the KEY project identifier.

    If not matched, you INSERT new data into the projects table.

    If found, you can IGNORE and nothing happens to the data,

    or use the option to UPDATE other data like date project.

    If MERGE command does not exist, you can write database procedure to do the same analysis of matching against a key value and deciding what to do next.

    Optionally you can do the check of existing project ID using .NET connection to the database returning true or false before you insert new data or if desired update the existing row.

    More than one way to accomplish the task!

    Tuesday, January 23, 2018 5:16 PM