Verifying a Field Using the table instead of a RecordSet 1

[joecdn]

OK, I'm sure someone else has asked a similar question, but what I want to do is verify that a record exists based on what is put into the form. Is there a way I can do this without using a recordset? It must be a syntax thing, because all I want to do is take the field in the table and see if it equals the first value I put in the form.

Any insight would be great.
Thanks

 

[onpnt]

SQL WHERE clause

Dim SQL
SQL = "SELECT * FROM table WHERE column = '" & request.form("field&quot & "'"

execute that and check for EOF

_____________________________________________________________________

[sub]
Hakuna matata!![/sub]

 

[joecdn]

OK, so what's the correct syntax? I have this:

SQLQuery="select * from nl_staff where cnumber = '" & request.form("txtrepnum&quot & "'"

if SQLQuery.eof then
SQLQuery = "INSERT INTO nl_staff (cnumber,sname,sfname,webpass) VALUES ('" & request.form("txtrepnum&quot & "','" & request.form("txtlast&quot & "','" &
request.form("txtfirst&quot & "'," & pv & &quot"
response.write(SQLQuery)
else
SQLQuery = "UPDATE nl_staff SET sname = '" & request.form("txtlast&quot & "', sfname = '" & request.form("txtfirst&quot & "' for cnumber = '" & request.form("txtrepnum&quot & "'"
response.write(SQLQuery)
end if

adoCon.Execute(SQLQuery)

I've never had trouble with syntax as I do with this .. man, is it picky.

Anyway, let me know what I'm doing wrong
Thanks

 

[onpnt]

everything looks fine but that for in there here
SQLQuery = "UPDATE nl_staff SET sname = '" & request.form("txtlast&quot & "', sfname = '" & request.form("txtfirst&quot & "' for cnumber = '" & request.form("txtrepnum&quot & "'"

is this Jet or T-SQL (Access DB or SQL Server

_____________________________________________________________________

[sub]
Hakuna matata!![/sub]

 

[joecdn]

It's actually FoxPro, but it's been acting the same as Access. It's definitely not SQL Server.

The place I'm actually get an error on is the

if SQLQuery.EOF ... is that correct?

The error I'm getting is this...

Object required: 'SQLQuery'

 

[onpnt]

apologies. I assumed those where segments of the code.

if indeed that is the whole thing then you have a few steps missing.
1) the recordset being in your case SQLQuery being that but that won't quite work as the naming of the SQL statement being SQLQuery. not sure how I didn't catch that one.

so, whats missing is something to the order of
Dim RSQuery
Set RSQuery = the connection.Execute(SQLQuery)
then
if RSQuery.eof then
....etc..

you can still utilize the SQLQuery variable and there is no need to declare another as the needs of the last statement are done.

then to execute the INSERT or UPDATE just do a execute
the connection.Execute(SQLQuery)

_____________________________________________________________________

[sub]
Hakuna matata!![/sub]

 

[onpnt]

watch the LockType and CursorType in FoxPro. They get very toushy

_____________________________________________________________________

[sub]
Hakuna matata!![/sub]

 

[joecdn]

Perfect, that works great, thanks for your help ... again.

I do know about the Lock Type and Cursor Type in FoxPro, Don't know if you recall, but I was pulling my hair out about that before.

But, I got it working ... finally.

Thanks again.

 

[onpnt]

I remember ya, lets not go there again

glad to help out again and thanks for the *


_____________________________________________________________________

[sub]
Hakuna matata!![/sub]