locked
vfp sql insert with condition RRS feed

  • Question

  • hi,
    how should i do it in sql?

    sele table1
    go top
    locate for fieldname=this.text.value
    if found()
        messagebox()
    else
        append blank
        replace
    endif

    any help?
    Monday, December 14, 2009 9:46 AM

Answers

  • Someething along these lines will do it:

    lcFVal = This.Text.Value
    IF NOT EXISTS( SELECT id FROM table1 WHERE fieldname = lcFVal )
       INSERT INTO table1 (Column_list) VALUES (Column_Values)
    ENDIF

    -- Andy Kramek
    • Marked as answer by Young_fox Monday, December 14, 2009 11:48 AM
    Monday, December 14, 2009 11:00 AM
  • Many Thanks to you Andy.

    • Marked as answer by Young_fox Monday, December 14, 2009 11:48 AM
    Monday, December 14, 2009 11:47 AM
  • >>  know what I can use in VFP or in SQL. The question was from Young_fox. I just asked the question. Is this a VFP or SQL code? ;o)

    Oh, I see. You were just trying to be funny in correcting my typo.

    Sorry, I thought you were actually asking how to use EXISTS() in VFP.
    -- Andy Kramek
    • Marked as answer by Young_fox Wednesday, December 16, 2009 3:23 PM
    Wednesday, December 16, 2009 10:53 AM
  • hi zografski and andy,

    thing is,  the code is in vfp that i usually does most of my vfp projects when i was using local vfp table.
    just recently, i am using sql for my data.

    so what i am asking is how to do it in sql if the the condition/situation is the same as the code below

    **i originally using it for my local vfp table
    sele table1
    go top
    locate for fieldname=this.text.value
    if found()
        messagebox()
    else
        append blank
        replace
    endif

    I hope this could clear things to you. I am sorry my english is very poor he he he
    The original answer I gave you is the correct syntax SQL - just ignore the "endif".

    IF NOT EXISTS( SELECT id FROM table1 WHERE fieldname = [field_value] )
       INSERT INTO table1 (Column_list) VALUES (Column_Values)

    The easiest way would be to create a parameterized stored procedure in SQL and simply call that stored procedure from VFP using SQLEXEC().

    -- Andy Kramek
    • Marked as answer by Young_fox Wednesday, December 16, 2009 3:22 PM
    Wednesday, December 16, 2009 10:56 AM

All replies

  • Someething along these lines will do it:

    lcFVal = This.Text.Value
    IF NOT EXISTS( SELECT id FROM table1 WHERE fieldname = lcFVal )
       INSERT INTO table1 (Column_list) VALUES (Column_Values)
    ENDIF

    -- Andy Kramek
    • Marked as answer by Young_fox Monday, December 14, 2009 11:48 AM
    Monday, December 14, 2009 11:00 AM
  • Many Thanks to you Andy.

    • Marked as answer by Young_fox Monday, December 14, 2009 11:48 AM
    Monday, December 14, 2009 11:47 AM
  • Someething along these lines will do it:

    lcFVal = This.Text.Value
    IF NOT EXISTS( SELECT id FROM table1 WHERE fieldname = lcFVal )
       INSERT INTO table1 (Column_list) VALUES (Column_Values)
    ENDIF

    -- Andy Kramek
    Hi Andy.
    Is this a VFP or SQL code?
    EXISTS() function in VFP?
    or
    ENDIF in SQL?

    Systems Analyst
    • Proposed as answer by Naomi N Tuesday, December 15, 2009 6:10 PM
    Monday, December 14, 2009 5:45 PM
  • Good question! I was forgetting you can only use EXISTS in a sub-query in VFP so it should have been:

    SELECT .T. FROM junk WHERE EXISTS ( SELECT fval FROM junk WHERE fval = 'EST' ) TO SCREEN NOCONSOLE
    IF _TALLY > 0
      *** Update Here
      ? "Update"
    ELSE
      *** Insert Here
      ? "Insert"
    ENDIF

     

     

     


    -- Andy Kramek
    Monday, December 14, 2009 11:12 PM
  • Good question! I was forgetting you can only use EXISTS in a sub-query in VFP so it should have been:

     

     


    -- Andy Kramek
    I know what I can use in VFP or in SQL. The question was from Young_fox. I just asked the question. Is this a VFP or SQL code? ;o)
    Systems Analyst
    Tuesday, December 15, 2009 5:54 PM
  • hi zografski and andy,

    thing is,  the code is in vfp that i usually does most of my vfp projects when i was using local vfp table.
    just recently, i am using sql for my data.

    so what i am asking is how to do it in sql if the the condition/situation is the same as the code below

    **i originally using it for my local vfp table
    sele table1
    go top
    locate for fieldname=this.text.value
    if found()
        messagebox()
    else
        append blank
        replace
    endif

    I hope this could clear things to you. I am sorry my english is very poor he he he
    Wednesday, December 16, 2009 2:45 AM
  • >>  know what I can use in VFP or in SQL. The question was from Young_fox. I just asked the question. Is this a VFP or SQL code? ;o)

    Oh, I see. You were just trying to be funny in correcting my typo.

    Sorry, I thought you were actually asking how to use EXISTS() in VFP.
    -- Andy Kramek
    • Marked as answer by Young_fox Wednesday, December 16, 2009 3:23 PM
    Wednesday, December 16, 2009 10:53 AM
  • hi zografski and andy,

    thing is,  the code is in vfp that i usually does most of my vfp projects when i was using local vfp table.
    just recently, i am using sql for my data.

    so what i am asking is how to do it in sql if the the condition/situation is the same as the code below

    **i originally using it for my local vfp table
    sele table1
    go top
    locate for fieldname=this.text.value
    if found()
        messagebox()
    else
        append blank
        replace
    endif

    I hope this could clear things to you. I am sorry my english is very poor he he he
    The original answer I gave you is the correct syntax SQL - just ignore the "endif".

    IF NOT EXISTS( SELECT id FROM table1 WHERE fieldname = [field_value] )
       INSERT INTO table1 (Column_list) VALUES (Column_Values)

    The easiest way would be to create a parameterized stored procedure in SQL and simply call that stored procedure from VFP using SQLEXEC().

    -- Andy Kramek
    • Marked as answer by Young_fox Wednesday, December 16, 2009 3:22 PM
    Wednesday, December 16, 2009 10:56 AM
  • hi andy,

    thanks to you again. i was able to do things directly to sql. can you recommend some site for stored proceedure using vfp or its just as simple as like this;

    **sp name
       erika

    ** calling sp erika
        sqlexec(erika)

    i dont know if this is right. but i will try to explore things as you have said
    merry christmas!

    young fox
    Wednesday, December 16, 2009 3:27 PM
  • Yes, you can jus

    lcParam1 = ThisForm.TextBox1.Value
    lcSql = "EXEC Check_Data'" + lcParam1 + "'"
    lnResult = SQLEXEC( nConHandle, lcSql, 'cur_result' )
    IF lnResult < 1
      AERROR( laErr )
      MESSAGEBOX( laErr[2], 16, 'SQL Server Error' )
    ELSE
    IF UPPER( cur_result.Check_Result ) == 'FOUND'
    *** Do whatever you need to do when the record exists
    ELSE
    *** Do whatever you need to do when the record doesn't exist
    ENDIF

    ENDIF To create the stored procedure you would execute this in SQL Server (i.e. Management Studio or Querty Analyzer: IF OBJECT_ID( 'dbo.Check_Data' ) IS NOT NULL DROP PROCEDURE dbo.Check_Data GO CREATE PROCEDURE dbo.Check_Data @InVal VARCHAR(120) AS BEGIN IF NOT EXISTS( SELECT id FROM table WHERE check_field = @InVal ) SELECT "Not Found" AS Check_Result ELSE SELECT "Found" AS Check_Result END
    GO

    t use SQLEXEC() but the correct syntax is to use "EXEC procedure_name" like this:

     

     


    -- Andy Kramek
    Wednesday, December 16, 2009 9:43 PM
  • thanks andy,
    so now i am learning he he he.  i had another question but maybe later
    its almost the same but in different situation. if its ok with you, i am goin to post
    my question here.
    • Edited by Young_fox Wednesday, December 16, 2009 11:05 PM grammatical error
    Wednesday, December 16, 2009 11:03 PM
  • hi,

    i am sorry
    Friday, December 18, 2009 6:17 AM
  • 8 years old
    Monday, December 21, 2009 1:45 AM
  • i'm a girl, my father is also a programmer but he is not good as i think he is. and he refers me to this site for me to get satisfied about my question.
    do you have a son? he is young fox also? is it ok here to keep asking things about programming. my father sometimes use my account for asking.

     

    young

    Monday, December 21, 2009 6:43 AM
  • hi,

    i have made this invoicing system for my aunt business (it takes 1 year) where can i find cretics to have it creticize so i can make correction.

     

    young

    Monday, December 21, 2009 6:48 AM
  • hi its 11pm here.

    i use grid and i do this code

    **interactive change
    thisform.grid1.column3.text1.value=thisform.grid1.column1.text1.value*thisform.grid1.column2.text1.value

    it works fine on the firs line
    but when i go to the next line the answer is the same. it does not recognise the next row

    i used

    goto recno()
    thisform.grid1.column3.text1.value=thisform.grid1.column1.text1.value*thisform.grid1.column2.text1.value
    it works fine for now, i dont if this is correct coz i just read it from help of vfp.

    what is the name of your yuong fox
    my real name is Eira Angela Barrios
    Monday, December 21, 2009 2:53 PM
  • hi,

    why you have many medal? how to get a medal every body i think has a medal. are you first honor?

    Monday, December 21, 2009 2:58 PM
  • thank youvery much. good morning.
    Tuesday, December 22, 2009 3:29 AM
  • Soon. I can be of help to others also. Currently, i dont think i can do much input for someone needing help like me hehehehehehehe. anyways, i will do it if ithink i could share best and correct.

    Tuesday, December 22, 2009 9:01 AM
  • Hi Andy,

    But when i try to execute the procedure i hit errors :

    "

    Msg 207, Level 16, State 1, Procedure Check_Data, Line 3

    Invalid column name 'Not Found'."

    May i know what is the problems ?

    Thanks,
    Jeff.


    MCP - SQL SERVER 2k/ WINDOWS XP , PROGRAMMER ANALYST
    Thursday, January 21, 2010 12:52 AM
  • Try using single quotes around the text instead of double quotes (it was a typo).
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Proposed as answer by yanyee Thursday, January 21, 2010 5:52 AM
    Thursday, January 21, 2010 3:12 AM
  • Hi Naom,

    Thanks for  pointed out. Now managed to compile SP succesfully.

    Thanks,
    Jeff.
    MCP - SQL SERVER 2k/ WINDOWS XP , PROGRAMMER ANALYST
    Thursday, January 21, 2010 5:52 AM