vfp sql insert with condition

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?

Answer 1

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

Answer 2

Many Thanks to you Andy.

Answer 3

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

Answer 4

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

Answer 5

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

Answer 6

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

Answer 7

>>  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

Answer 8

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

Answer 9

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

Answer 10

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

Answer 11

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.

Answer 12

hi,

i am sorry

Answer 13

8 years old

Answer 14

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

Answer 15

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

Answer 16

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

Answer 17

hi,

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

Answer 18

thank youvery much. good morning.

Answer 19

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.

Answer 20

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

Answer 21

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

Answer 22

Hi Naom,

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

Thanks,
Jeff.

---

MCP - SQL SERVER 2k/ WINDOWS XP , PROGRAMMER ANALYST