Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Update Data SQL server Using Create Cursor VFP

bharons

bharons

Technical User
Jan 23, 2019
49
ID
Dear Expert...:giggle::giggle:
I use VFP8 for SQL Server data processing,
I have a question, how to code or script to update data in SQL Server using Create Cursor VFP.
For example, below is the script that I use.

Code: 
create cursor skedul1 (norekening N(13), ke N(2), pokok_kr N(12), bunga_kr N(12), pokok_sd N(12))
SET SAFETY OFF
SELECT     skedul1
ZAP
SET SAFETY ON
insert into skedul1 (norekening, ke, pokok_kr, pokok_sd, bunga_kr) values (1312124120052, 1, 0, 26400, 850000)
insert into skedul1 (norekening, ke, pokok_kr, pokok_sd, bunga_kr) values (1312124120052, 2, 0, 26400, 850000)
insert into skedul1 (norekening, ke, pokok_kr, pokok_sd, bunga_kr) values (1312124120052, 3, 0, 26800, 850000)
insert into skedul1 (norekening, ke, pokok_kr, pokok_sd, bunga_kr) values (1312124120052, 4, 0, 26400, 850000)
insert into skedul1 (norekening, ke, pokok_kr, pokok_sd, bunga_kr) values (1312124120052, 5, 0, 25500, 850000)
insert into skedul1 (norekening, ke, pokok_kr, pokok_sd, bunga_kr) values (1312124120052, 6, 850000, 26400, 0)

select skedul1
m.value1 = skedule1.norekening
m.value2 = skedule1.ke
m.value3 = skedule1.pokok_kr
m.value4 = skedule1.pokok_sd
m.value5 = skedule1.bunga_kr

    TEXT TO m.lcSQL TEXTMERGE noshow
    UPDATE dbo.skedul
        SET pokok_kr = ?m.value3,
        pokok_sd = ?m.value4,
        bunga_kr = ?m.value5
        WHERE norekening = ?m.value1 and ke = ?m.value2
    ENDTEXT

when I run this script, why only the first line can change.. :unsure:
i need solution..
also if i use this skrip i got warning that :
Connectivity error: [Miscrosoft][ODBC SQL Server Drive][SQL Server] Invalid Object Name 'skedul1'
below script that i used for update value on my SQL Server
Code: 
Select skedul1
m.value1 = skedul1.norekening
m.value2 = skedul1.ke
m.value3 = skedul1.pokok_kr
m.value4 = skedul1.pokok_sd
m.value5 = skedul1.bunga_kr

    TEXT TO m.lcSQL TEXTMERGE noshow
    UPDATE dbo.skedul
        SET pokok_kr = ?m.value3,
        pokok_sd = ?m.value4,
        bunga_kr = ?m.value5
        FROM [skedul1]
        WHERE rekening = ?m.value1 and ke = ?m.value2
    ENDTEXT       

lnResult = SQLExec(Thisform.nHandle, m.lcSQL)
If m.lnResult < 0
    Aerror(aWhy)
    Messagebox(aWhy[2])
    SQLDisconnect(0)
    Return .F.
Else
    Messagebox("Data Telah Update..!!")
    thisform.PopulateGrid()
    thisform.grid2.RecordSource = ""
Endif

Thank you for the advice and enlightenment from the experts on this forum.. 🙏

Ikon Diverifikasi Komunitas


Ikon Diverifikasi Komunitas
 
Sort by date Sort by votes
bharons said:
when I run this script, why only the first line can change.. :unsure:
Click to expand...
Because this code only copies the current record into variables:
Code: 
select skedul1
m.value1 = skedule1.norekening
m.value2 = skedule1.ke
m.value3 = skedule1.pokok_kr
m.value4 = skedule1.pokok_sd
m.value5 = skedule1.bunga_kr
You need to do this repeatedly in a SCAN...ENDSCAN loop

bharons said:
also if i use this skrip i got warning that :
Connectivity error: [Miscrosoft][ODBC SQL Server Drive][SQL Server] Invalid Object Name 'skedul1'
below script that i used for update value on my SQL Server
Click to expand...
When you use [skedul1] within the SQL that's executed by the server and it's not finding anything. SQL Server or MySQL Server has no access to the VFP process, or it's cursors. It's totally hopeless to get this going.
 
Upvote 0 Downvote
Dear Chriss... :giggle:
what should i do.. :unsure: if i use first script i just can update first row.
What I want is? that all data in the create cursor is saved or copied in the DBO.Schedule table?? any sugesstion..
 

Attachments

  • 1734068572174.png
    1734068572174.png
    312 bytes · Views: 2
  • 1734068588838.png
    1734068588838.png
    312 bytes · Views: 2
Upvote 0 Downvote
Chris Miller said:
Because this code only copies the current record into variables:
Code: 
select skedul1
m.value1 = skedule1.norekening
m.value2 = skedule1.ke
m.value3 = skedule1.pokok_kr
m.value4 = skedule1.pokok_sd
m.value5 = skedule1.bunga_kr
You need to do this repeatedly in a SCAN...ENDSCAN loop


When you use [skedul1] within the SQL that's executed by the server and it's not finding anything. SQL Server or MySQL Server has no access to the VFP process, or it's cursors. It's totally hopeless to get this going.
Click to expand...
i try this script... but its seem i just only last row has changing...😞
Code: 
select skedul1
scan
m.value1 = skedul1.norekening
m.value2 = skedul1.ke
m.value3 = skedul1.pokok_kr
m.value4 = skedul1.pokok_sd
m.value5 = skedul1.bunga_kr

    TEXT TO m.lcSQL TEXTMERGE noshow
    UPDATE dbo.skedul
        SET pokok_kr = ?m.value3,
        pokok_sd = ?m.value4,
        bunga_kr = ?m.value5
        WHERE norekening = ?m.value1 and ke = ?m.value2
    ENDTEXT       
endscan

lnResult = SQLExec(Thisform.nHandle, m.lcSQL)
If m.lnResult < 0
    Aerror(aWhy)
    Messagebox(aWhy[2])
    SQLDisconnect(0)
    Return .F.
Else
    Messagebox("Data Telah Update..!!")
    thisform.PopulateGrid()
    thisform.grid2.RecordSource = ""
Endif
 
Upvote 0 Downvote
Well, think about where to put the ENDSCAN to execute the update for each record of the cursor. Not where you have it.
 
Upvote 0 Downvote
Chris Miller said:
Well, think about where to put the ENDSCAN to execute the update for each record of the cursor. Not where you have it.
Click to expand...
i use this script.. still have first row change.. 😞
Code: 
select skedul1
m.value1 = skedul1.norekening
m.value2 = skedul1.ke
m.value3 = skedul1.pokok_kr
m.value4 = skedul1.pokok_sd
m.value5 = skedul1.bunga_kr

lcSQL = 'update dbo.skedul SET pokok_kr = ?m.value3, pokok_sd = ?m.value4, bunga_kr = ?m.value5 WHERE norekening = ?m.value1 and ke = ?m.value2'
Select skedul1
Scan
   SQLExec(Thisform.nHandle, m.lcSQL)
Endscan

lnResult = SQLExec(Thisform.nHandle, m.lcSQL)
If m.lnResult < 0
    Aerror(aWhy)
    Messagebox(aWhy[2])
    SQLDisconnect(0)
    Return .F.
Else
    Messagebox("Data Telah Update..!!")
    thisform.PopulateGrid()
    thisform.grid2.RecordSource = ""
Endif
 
Upvote 0 Downvote
The scan was already correct, I only asked you to move the endscan.

Take it slowly, don't guess.
I said "You need to do this repeatedly in a SCAN...ENDSCAN loop":
Code: 
m.value1 = skedule1.norekening
m.value2 = skedule1.ke
m.value3 = skedule1.pokok_kr
m.value4 = skedule1.pokok_sd
m.value5 = skedule1.bunga_kr
When is code within a loop? When the start of the loop is before it, isn't it?
 
Last edited:
Upvote 0 Downvote
Chris Miller said:
The scan was already correct, I only asked you to move the endscan.

Take it slowly, don't guess.
I said "You need to do this repeatedly in a SCAN...ENDSCAN loop":
Code: 
m.value1 = skedule1.norekening
m.value2 = skedule1.ke
m.value3 = skedule1.pokok_kr
m.value4 = skedule1.pokok_sd
m.value5 = skedule1.bunga_kr
When is code within a loop? When the start of the loop is before it, isn't it?
Click to expand...
Yupz... but its seem i can't get it.. where and when i must start scan also when i must ended with endscan.. 🙏
 
Upvote 0 Downvote
The loop code has to do two things
1. set the value1 to value5 variables
2. do the SQLExec.

If you do 1 before the loop and only do 2 within the loop, you repeat the same update with the first record values multiple times, if you only do 1 and finally 2, you change the variables multiple times, until they have the values of the last records and only do the update with last record values.

So you have done 2 of 3 possible things, but you haven't done the right thing.
 
Last edited:
Upvote 0 Downvote
One more thing about (a part of) your second code sample:
Code: 
lnResult = SQLExec(Thisform.nHandle, m.lcSQL)
If m.lnResult < 0
    Aerror(aWhy)
    Messagebox(aWhy[2])
    SQLDisconnect(0)
    Return .F.
Else
    Messagebox("Data Telah Update..!!")
    thisform.PopulateGrid()
    thisform.grid2.RecordSource = ""
Endif
That code is fine, in itself. But it has one requirement to be reusable for all your different SQL use cases: It depends on having one SQL string doing everything a job has to do. If doing that sql fails (that means if SQLEXEC returns a negative value) you display the error. If not, that means the SQL succeeds, the else branch tells the users it worked.

In your current case you have an update sql only working on a single record, so you have to do this 4 times. And you only know if all 4 worked, after all 4 updates returned no errors. So that's not working for this case.
 
Last edited:
Upvote 0 Downvote
ohhh... i must reply those script until 4 time.. with condition no error on sql connect.. :unsure: :unsure:
 
Upvote 0 Downvote
No, you don't need to connect 4 times.

I think the language barrier - that of your natural language - makes it impossible to teach you in English. You should look for a Forum in your country.

You essentially know enough to get this going. It won't teach you to get a solution served, you don't show a learning progress and I'm therefore not putting time and effort in continuing. Others might have mercy and more patiente, but I'm off here, sorry.
 
Last edited:
Upvote 0 Downvote
Chris Miller said:
No, you don't need to connect 4 times.

I think the language barrier - that of your natural language - makes it impossible to teach you in English. You should look for a Forum in your country.

You essentially know enough to get this going. It won't teach you to get a solution served, you don't show a learning progress and I'm therefore not putting time and effort in continuing. Others might have mercy and more patiente, but I'm off here, sorry.
Click to expand...
 
Upvote 0 Downvote
Thank you for your attention... maybe next time you can provide other direction regarding the coding mistakes I made...

Ikon Diverifikasi Komunitas
 
Upvote 0 Downvote
You must log in or register to reply here.

Similar threads

bharons
  • Locked
  • Question
update data table sql server using form vfp 2
Replies
13
Views
428
bharons
bharons
SitesMasstec
  • Locked
  • Question
Update data in a Cursor 2
Replies
19
Views
2K
SitesMasstec
SitesMasstec
WedHouse
  • Locked
  • Question
update value dbo sql using vfp 8
Replies
5
Views
291
Chris Miller
Chris Miller
Premal Vala
  • Locked
  • Question
VFP with MYSQL - mySQL server gone away
Replies
8
Views
657
Chris Miller
Chris Miller
cfsjohn
  • Question
Need help with a concept for sharing data between VFP apps
Replies
5
Views
391
Chris Miller
Chris Miller

Part and Inventory Search

Sponsor

Back
Top