update data table sql server using form vfp 2

[bharons]

hay..
i wanna ask something, are my script is right to execute to update data on table sql server using vfp 8
on load form vfp
public KONEKSI, mskedul1
STORE SQLCONNECT('dana','dana','dana_database') TO KONEKSI
IF KONEKSI > 0

​

SQLEXEC(KONEKSI,'use ksp_arthadana')
endif
SQLTables(m.KONEKSI,'TABLE','tableList')

on init form vfp
thisform.grid1.recordsource = 'tablelist'

on destroy form vfp
STORE SQLCONNECT('dana','dana','dana_database') TO KONEKSI
IF KONEKSI > 0
SQLEXEC(KONEKSI,'use ksp_arthadana')
endif
SQLDisconnect(KONEKSI)

on click event my commandbutton vfp
If m.KONEKSI > 0
LOCAL lnResult

lcValue = Thisform.text4.Value
lnResult = SQLExec(KONEKSI, "select * from dbo.customer where cif = ?m.lcValue", "nasabah1")
IF m.lnResult > 0
lcValue1 = thisform.text6.value or 07/10/2024 02:30:00
lcExeCmd= "INSERT INTO dbo.nasabah (date) VALUES (?m.lcValue1) where cif = ?m.lcValue"
SQLEXEC(KONEKSI, lcExeCmd)
Messagebox('Data Has Been Save..!!')
Else
Aerror(laErrors)
Messagebox('Error: ' + laErrors[2])
Endif
endif

for compare i also add one more command button at my form
on click event my commandbutton1 vfp

If m.KONEKSI > 0
lcValue = thisform.text4.value
lcValue1 = thisform.text6.value or 07/10/2024 02:30:00
TEXT TO lcUpdCmd NOSHOW TEXTMERGE
UPDATE DBO.customer SET date = ?m.lcValue1
where cif = ?m.lcValue
ENDTEXT
SQLEXEC(KONEKSI, lcUpdCmd)
Messagebox('Data Has Been Save..!!')
Else
Aerror(laErrors)
Messagebox('Error: ' + laErrors[2])
endif

property for text4 vfp are numeric same like sql server datatype numeric property text6 are blank or none maybe general datatype
and i browse based on dbo.customer there is no change data, i mean date at my dbo.customer before i update are like this 02/05/2024 02:30:00 AM and i want to update like lcValue1 above. then can someone explain me why and what should i do to make it correct..

 

[Chris Miller]

TEXT TO lcUpdCmd NOSHOW TEXTMERGE
UPDATE DBO.customer SET date = ?m.lcValue1
where cif = ?m.lcValue
ENDTEXT
SQLEXEC(KONEKSI, lcUpdCmd)

That part works, under the following conditions:
1. KONEKSI is a valid and still open connection handle - I think that checks out okay.
2. m.lcValue1 and m.lcValue are of the correct data type. For example if thwe SQL Server table field "date" is a datetime, then m.lcValue1 must also be a VFP datetime. A string of a datetime could also work in some cases, but then it must be composed the right way.

You can of course verify whether the update worked by doing a SELECT * FROM DBO.customer WHERE cif=some value. You did and didn't see the update.

Well, one thing to always check about SQLEXEC is the return value:

TEXT TO lcUpdCmd NOSHOW TEXTMERGE
UPDATE DBO.customer SET date = ?m.lcValue1
where cif = ?m.lcValue
ENDTEXT
lnReturnvalue = SQLEXEC(KONEKSI, lcUpdCmd)
If lnReturnvalue<0
   Aerror(laError)
   suspend
Endif

If the sqlexec didn't work, the return value is negative and aerror will create an array of error information and (within the IDE) SUSPEND then suspends code execution and you can inspect the elements of the laError array. A simple way for that is starting the debugger and looking into the locals window for the laError variable.

The only error handling you have so far is only checking If m.KONEKSI > 0, well, in such a case you're not having a connection, to use AERROR in the ELSE branch only makes sense after SQLCONECT() or SQLSTRINGCONNECT, not before doing an SQLEXEC with the m.KONEKSI handle. On one side you can be pretty sure about a connection to not break, usually, until you use SQLDISCONNECT. On the other side, if a connection is terminated from the side of SQL Server, that'll not change your m.KONEKSI variable, you will only detect a connection closed by SQL Server when trying to use it and then it'll also cause a negative return value of SQLEXEC or similar function calls using the positive, but nevertheless invalid handle that was closed for some reason not by your code, but by the server.

So in short, every SQLEXEC you do deserves a verification and error handling, not only because the SQL you send over could be wrong, not only because you could have a type mismatch in parameterization, but also becasue the m.KONEKSI handle has become invalid and for more reasons I didn't think of.

By the way, if this runs and doesn't suspend, then you could still have the problem that m.lcValue may not be a cif value and so the update is done but has zero rows affected. You could do SELECT @@ROWCOUNT and see if that is the to be expected 1 or is 0.

Chriss

 

[bharons]

dear chriss by your code that give me warning without any message just do suspended..
any idea to fix it..
by debugging my lcValue1 = .F. it may give me that value on my text2.value are not datetime function..

 

[Chris Miller]

If it suspends, you have to look into the array laerror, as I said.

Chriss

 

[bharons]

i havent see the array laerror.. below my image..

also if i change lcValue1 = TTOD(thisform.text5.value).. property on this textbox i change value to {}.. its give me do suspended, but i dont know how to see laerror that Chriss say before.. .. below my image

 

[Chris Miller]

You said

your code that give me warning without any message just do suspended..

Your error now states that the DTOT function can't work with the type of thisform.text5.value. That's before you even do the SQLEXEC and won't have anything to do with the suspend.

The screenshots don't tell which textbox is text 5, nor what its value is.

But DTOT would expect a date and turn it into a datetime with the time portion set to midnight. I don't think that's what you want. So you picked the wrong function DTOT. I can't say what exactly you want, though I know the result of that should be a datetime used in the SQLExec, well, either text5 already is a datetime, otherwise you can try to convert a string to a datetime with CTOT, but not with DTOT.

Chriss

 

[Chris Miller]

By the way, things to know in this context:

VFP's TYPE or VARTYPE functions return data types in the form of single letters, CREATE TABLE and CAST allow to use thes single letters for datatypes, too.
And the three to know in this context are

1. C=Char
2. D=Date
3. T=DateTime

So the function familiy you used wrong here can convert between strings, dates and datetimes:

1. CTOD() - C To D char to date
2. CTOT() - C To T char to datetime
3. DTOT() - D To T date to datetime

It's easy to see which function to use for which conversion once you know. The family does not extend to any type, though, there is no ITOC, for example, that's the job of STR, I'm just the messenger here, at least VFP has a single function that can turn any data type into a string - TRANSFORM. There is a kind of universal counterpart with the CAST function, it also has a special syntax that's derived from SQL, not a usual function parameterization. But what you need I guess is CTOT, and perhaps better a datetime picker control in which you don't get cases where the entered string does not convert to a datetime, but the user picks a date from a calender and a time, so get a valid datetime value that's neither off in things like day 31 in a month with only 30 days or wrong formatting. Setting the value of a textbox to DATETIME(), for example, would mean the value data type is T (datetime) and stays T and users can enter dates and times, their input can be or become incomplete and invalid, though, so a datetime picker is the best alternative to use for input of datetimes.

Chriss

 

[Chris Miller]

And once more - when you use this construct everywhere you do any SQLEXEC...

lnReturnvalue = SQLEXEC(KONEKSI, lcSQL)
If lnReturnvalue<0
   Aerror(laError)
   suspend
Endif

When you get to the suspend line and code execution stops - then you will have an laError array containing the problem about the query or connection, parameters, what not.

And you could also CANCEL, SET STEP ON, call your error handling function, whatever, but for simple and easy enough debugging starting the debugger and looking for laError in the locals window does the job for me, to inform me of the problem by the error message that's captured in the array. And there's no doubt the array exists by AERROR and the fact SQLEXEC returned a negative value. Then there is an error message pending to be captured from VFP by AERROR.

Chriss

 

[bharons]

even i try convert it to CTOT or CTOD its same.. i got error.. below is my image

i got error with number 1526.. what meaning of it...
and try to remove convert on lcValue1..

and try combination convert date to datetime..

still i got error at laerror with number 1526..

 

[Chris Miller]

Don't just look at the laError variable with mouse hovering, you only see the first array element. There's more in tha tarray. What did I wrote twice already? Please read and do as I said: Look into the Local window for laError and then expand what's in the array and you see more.

Regarding the conversion of text5.value. I also already said, that either the textbox value already is a datetime, then you don't need a conversion, or it is a string, then you need CTOT. You have to know what you have, because you set text5.value somewhere.

Just quoting what I alrady told you:

either text5 already is a datetime, otherwise you can try to convert a string to a datetime with CTOT

Check the data type of text5 in the command window, for example:

? vartype(thisform.text5.value)

By the way, in your first posting you have text6 for the date:

lcValue1 = thisform.text6.value && or 07/10/2024 02:30:00

Listen, if you're sloppy you won't ever get this going. A slip of the finger and you have text5 instead of text6, a slip of the finger and you have D instead of C. Typos are what I have in most of my posts, but in code everything has to be precise.

Chriss

 

[bharons]

between text5 or text6 just comparison.. text5 is value triggered by nasabah1.waktu.. where waktu is timestamp on dbo sql server..
text6 i just using value property to {}..
so my mistake then if Chriss confuse..
between those textbox i used in sqlexec() but give me error.. and suspended..

 

[Chris Miller]

but give me error.. and suspended..

And have you now looked into the locals window within the debugger and expanded what's in the items of the laError array?

If you set text6.value to {}, thats the empty DATE, not DATETIME.
If you keeep it empty, then you have a problem also when converting it to DATETIME, because SQL Server doesn't know an empty DATETIME. It has to become a concrete DATETIME value for SQL Server to be able to use it in your update SQL query.

You can't work with EMPTY dates and datetimes, only VFP knows this special value. SQL Server works with NULL for no date, just like for all data types or you use an ultimo datetime like January 1st 19000 for SQL Server, that depends on whether the date field is designed to be nulable or not.

Chriss

 

[Chris Miller]

As you don't seem to get how to look into the array, if you get to the debugger the laError array will show as this.

Click on the plus highlighted in yellow, here.

Chriss

 

[bharons]

its say i got error on column name..

After long i search from this forum, now i understand that my field are not exact with datatype. and more searching from my listbox. , i found it that column name are "waktu" not "date". . so i would to say thanks Chriss for your understanding me about what should i do to deal with this problems. and you are same like mr. Cetin my Teacher at another forums.