UPDATE - SQL with TextBox Values

[Neil Toulouse]

The heat is definately getting to me this week!

I want to populate some fields in a table with values from textboxes on a form, using UPDATE - SQL, but...

WITH THISFORM

UPDATE Customer SET Surname = ALLTRIM(.txtSurname.Value) WHERE CustID = 1

ENDWITH

...give me an "Alias not found" error pertaining to the ALLTRIM(.txtSurname.Value).

Help?

Neil

I like work. It fascinates me. I can sit and look at it for hours...

 

[Mike Lewis]

Neil,

The heat is definately getting to me this week

Please send some of it up here to the northern extremeties. We could do with it.

"Alias not found" error

Looks like it thinks that txtSurname is an alias and Value is a field. At risk of stating the obvious, are you sure that txtSurname is the correct name of the textbox, and that it is in the form itself rather than a container?

Apart from that, I can't see any other reason why it shouldn't work.

Have you tried:

- placing THISFORM. in front of txtSurname?

- Copying the contents of txtSurname to a variable, and using that in the UPDATE command?

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[Neil Toulouse]

Hi Mike!

You are welcome to some of this heat! - unfortunately we are stuck in an airless office whilst they are refurbishing our 7th floor space, so no air con! Moving back in on Monday though, yippee!!

I have checked all the stuff that you suggested, and tried all permuations I can think of to no avail.

WITH THISFORM

UPDATE Customer SET Surname = .txtSurname.Value WHERE ... 

ENDWITH

..."Alias not found"

WITH THISFORM

UPDATE Customer SET Surname = thisform.txtSurname.Value WHERE ... 

ENDWITH

..."Alias not found".

However...

LOCAL lcSurname

WITH THISFORM

lcSurname = ALLTRIM(.txtSurname.Value)

UPDATE Customer SET Surname = lcSurname WHERE ... 

ENDWITH

...works!

I don't fancy having to assign all the TextBox values to variables to get it to work though :-(

Neil


I like work. It fascinates me. I can sit and look at it for hours...

 

[Neil Toulouse]

ah well, I have just replaced them all with variables! didn't take that long afterall

I like work. It fascinates me. I can sit and look at it for hours...

 

[Mike Lewis]

Neil,

Isn't it annoying when you solve a problem but you don't know why it happened in the first place. (Not as annoying as not solving it at all, of course.)

As a consellation, I would think that assigning the text box values to variables would make the update slightly faster ... but that's only a guess.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[Neil Toulouse]

I think I came across this issue before a while back, but I was only dealing with one field at the time so just worked around it!

It does seem to be a bit of a limitation?

I like work. It fascinates me. I can sit and look at it for hours...

 

[lordhawkins]

When you issue an update command using values from objects in the form the easisest way is to create a string and then use it as a macrosustitution. In your case

with thisform

expresion = "UPDATE Customer SET Surname = '" +ALLTRIM(.txtSurname.Value)+ "' WHERE CustID = 1 "

&expresion

endwith

Note the apostrophe that are being used at the beggining and end of the string that comes from .txtSurname.value

 

[Mike Lewis]

LordHawkins,

Interesting -- although I can't see why it would work via macro substitution and not by direct execution.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[Neil Toulouse]

LordHawkins

Thanks for the input! I do as you say all the time for various things - I was just trying to avoid the 'long handed' way of doing a simple update!

As Mike says, why doesn't it work with direct execution? seems very odd!

I like work. It fascinates me. I can sit and look at it for hours...

 

[StewartUK]

Neil,

I agree it's odd, and inconsistent.

I've known occasions when it hasn't been a problem to use a form/object value in a SQL SELECT expression, and occasions when it has.

Any one else seen this sort of behaviour?

Stewart

 

[Mike Lewis]

I assume we are talking about SQL against local tables here. In other words, SELECTs, UPDATEs, etc. that are run directly within VFP. If the query is being sent to a remote back end, then of course all references to VFP variables and controls would be disallowed. But I assume that's not the case here.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[StewartUK]

That's certainly true for me.

Stewart

 

[Neil Toulouse]

I am trying to run updates to local vfp (free) tables as well!


I like work. It fascinates me. I can sit and look at it for hours...

 

[thatguy]

Anyone have any updates on this topic? As to why inserting the field directly to the SQL statement won't work, but assigning to var works fine?

Just curious as I'm dealing with this right now..

Thanks
--frank~

 

[Neil Toulouse]

thatguy

Unfortunately I don't think there is a solution to this! You will have to do it the long way!

Neil

I like work. It fascinates me. I can sit and look at it for hours...

 

[Olaf Doschke]

I wonder if a REPLACE vs UPDATE would work. I seems that SQL doesn't like references especially shortened with WITH..ENDWITH, but you also tested the full expression. Hmm. Also outside of WITH..ENDWITH?

Create Cursor Customer (iCustID I, cName C(10))
Insert into Customer values (1,"you")
Insert into Customer values (2,"he")
Insert into Customer values (3,"they")
oCustomer = CreateObject("Custom")
oCustomer.AddProperty("cName","u2")

* works:
Update customer set cName = oCustomer.cName where iCustID=1

* works too
With oCustomer
   ? .cName 
EndWith 

* doesn't work
With oCustomer
   Update customer set cName = .cName where iCustID=1
EndWith

So WITH..ENDWITH seems to cause the problems.

Bye, Olaf.

 

[Neil Toulouse]

I have had other instances where WITH...ENDWITH has caused problems, but can't specifically remember when now!

It just seems a shame you have to work around it, but at least it is doable in one form or another!

Neil

I like work. It fascinates me. I can sit and look at it for hours...

 

[StewartUK]

Having just installed VFP9 I thought I would experiment and found that UPDATE worked within a WITH...ENDWITH.

Here's the code I ran:

SELECT temp
WITH THISFORM
    UPDATE temp SET contname = [*]+contname WHERE Pic = .Pik
ENDWITH

This is in the Click event of a button on the form. It succesfully updated the ContName field.

I know that MS did some work on WITH...ENDWITH, so maybe they've fixed this anomaly?

Stewart

 

[Neil Toulouse]

That's interesting Stewart, maybe they have indeed fixed it!

We are still working with v6 and v8 so will have to wait for the fix

Neil

I like work. It fascinates me. I can sit and look at it for hours...