1. hatem moalla
  2. PowerBuilder
  3. Sunday, 25 February 2024 13:18 PM UTC

hello everyone here

in my powerbuilder 19 project, it's about article management. so when i insert a new article it works fine and when I update an article it works fine also but when I insert another article (after the update), i get the sqlstate error 23000 duplicated primary key reference = "the reference of the article that i inserted first" even though that the reference does not exist. 

note : reference column is primary key; not auto increment and is not null

here is the script of "new" button

char reference
dwc_article.scrolltorow(dwc_article.insertrow(0))

// Scroll to the newly inserted row
long ll_new_row
ll_new_row = dwc_article.InsertRow(0)
dwc_article.ScrollToRow(ll_new_row)

// Get the current date
date ld_date_creation
ld_date_creation = today()

string ls_formatted_date
ls_formatted_date = String(ld_date_creation, "yyyy-mm-dd")

// Set the current date to the "date_de_creation" column
dwc_article.SetItem(ll_new_row, "date_de_creation", ls_formatted_date)




dwc_article.modify("reference.Protect='0'") // This removes input capability from all columns
	   dwc_article.modify("name.Protect='0'")
	   dwc_article.modify("prix.Protect='0'")
		 dwc_article.modify("date_de_creation.Protect='1'")

and here is the script of "save new article" button 

dwc_article.accepttext()
dwc_article.update()
int li_sqlcode
commit using sqlca;

// Check for SQL errors
li_sqlcode = sqlca.sqlcode
if li_sqlcode = 0 then
    MessageBox("Success", "The article was successfully added.")
else
    MessageBox("Error", "Failed to add the article. SQLCode: ")
end if

// Refresh the report
dwc_report.Retrieve()
Attachments (1)
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Sunday, 25 February 2024 14:49 PM UTC
  2. PowerBuilder
  3. # 1

Hi Hatem;

  What you need to check is that the value of the Primary key is unique in the DWO or if the PK is "Auto increment" that your DWO is not trying to update it as the DBMS would then want to do that instead.

Regards ... Chris 

Comment
There are no comments made yet.
John Fauss Accepted Answer Pending Moderation
  1. Sunday, 25 February 2024 15:40 PM UTC
  2. PowerBuilder
  3. # 2

Hi, Hatem -

Do you perform a dw_article.ResetUpdate() after a successful dw_article.Update() call to reset the DataWindow's row and column status flags?

Do you have any triggers on the gest_article table that could be affecting this behavior?

You may wish to trace and analyze the commands issued to the database by temporarily connecting to the database via SQLCA.DBMS = "TRACE ODB" instead of the normal SQLCA.DBMS = "ODB", to see if that gives you some additional insight as to what is happening.

Best regards, John

Comment
  1. hatem moalla
  2. Sunday, 25 February 2024 16:03 PM UTC
THANK YOU SO MUCH YOU SAVED MY LIFE AND I WILL GET HIRED



this script "dw_article.ResetUpdate() " solved the issue



thank you so much



can i ask for one more last help ?? i want to get a messagebox directly if i enter a reference(PK) that already exists before submitting the form and after i clicked the "new" button



here is the script of "new" button

"char reference

dwc_article.scrolltorow(dwc_article.insertrow(0))



// Scroll to the newly inserted row

long ll_new_row

ll_new_row = dwc_article.InsertRow(0)

dwc_article.ScrollToRow(ll_new_row)





// Get the current date

date ld_date_creation

ld_date_creation = today()



string ls_formatted_date

ls_formatted_date = String(ld_date_creation, "yyyy-mm-dd")



// Set the current date to the "date_de_creation" column

dwc_article.SetItem(ll_new_row, "date_de_creation", ls_formatted_date)









dwc_article.modify("reference.Protect='0'") // This removes input capability from all columns

dwc_article.modify("name.Protect='0'")

dwc_article.modify("prix.Protect='0'")

dwc_article.modify("date_de_creation.Protect='1'")"

  1. Helpful
There are no comments made yet.
hatem moalla Accepted Answer Pending Moderation
  1. Sunday, 25 February 2024 15:45 PM UTC
  2. PowerBuilder
  3. # 3

how to check if the PK is unique or not cause when i created the table in DB it didnt ask me and no it's no auto increment

i think my error in "update" or "save update" buttons. here is the script of both of them. please help i need this to get hired

this is the script of "update" button

long ll_row
ll_row = dwc_report.GetSelectedRow(0) // Get the selected row index in dwc_report


if ll_row >= 0 then // If a row is selected
    // Clear existing data in dwc_article
    dwc_article.Reset()
	 
	 long ll_nextRow
    ll_nextRow = dwc_article.RowCount() + 1

    // Copy data from dwc_report to dwc_article
    dwc_report.RowsCopy(ll_row, ll_row, Primary!, dwc_article, ll_nextRow, Primary!)

    // Disable all inputs in dwc_article
    dwc_article.modify("reference.Protect='1'") 
    dwc_article.modify("name.Protect='0'")
    dwc_article.modify("prix.Protect='0'")
    dwc_article.modify("date_de_creation.Protect='1'")

    // Retrieve data in dwc_article to reflect changes
    //dwc_report.retrieve()
	//dwc_article.retrieve()
else
    MessageBox("No row selected", "Please select a row in the report")
end if

and this is the script of "save update" button

// Declare variables
string ls_reference, ls_name
string ls_date_de_creation
decimal ld_prix
date ldt_date_de_creation

// Retrieve data from dwc_article
ls_reference = dwc_article.GetItemString(1, "reference")
ls_name = dwc_article.GetItemString(1, "name")
ld_prix = dwc_article.GetItemNumber(1, "prix")
ls_date_de_creation = dwc_article.GetItemString(1, "date_de_creation")

if Len(ls_date_de_creation) > 0 then
  ldt_date_de_creation = Date(ls_date_de_creation) // Adjust the format as per your DataWindow's date format
end if

// Perform the update operation
UPDATE gest_article
SET name = :ls_name,
    prix = :ld_prix,
    date_de_creation = :ldt_date_de_creation
WHERE reference = :ls_reference;

// Check for SQLCA.SQLCode to handle errors if necessary
if SQLCA.SQLCode <> 0 then
    MessageBox("Update Error", SQLCA.SQLErrText)
else
    MessageBox("Update Successful", "Record updated successfully.")
end if
dwc_report.retrieve()
Comment
There are no comments made yet.
hatem moalla Accepted Answer Pending Moderation
  1. Sunday, 25 February 2024 15:57 PM UTC
  2. PowerBuilder
  3. # 4

THANK YOU SO MUCH YOU SAVED MY LIFE AND I WILL GET HIRED

this script "dw_article.ResetUpdate() " solved the issue

thank you so much

can i ask for one more last help ?? i want to get a messagebox directly if i enter a reference(PK) that already exists before submitting the form and after i clicked the "new" button

here is the script of "new" button

char reference
dwc_article.scrolltorow(dwc_article.insertrow(0))

// Scroll to the newly inserted row
long ll_new_row
ll_new_row = dwc_article.InsertRow(0)
dwc_article.ScrollToRow(ll_new_row)


// Get the current date
date ld_date_creation
ld_date_creation = today()

string ls_formatted_date
ls_formatted_date = String(ld_date_creation, "yyyy-mm-dd")

// Set the current date to the "date_de_creation" column
dwc_article.SetItem(ll_new_row, "date_de_creation", ls_formatted_date)




dwc_article.modify("reference.Protect='0'") // This removes input capability from all columns
	   dwc_article.modify("name.Protect='0'")
	   dwc_article.modify("prix.Protect='0'")
		 dwc_article.modify("date_de_creation.Protect='1'")

 

Comment
There are no comments made yet.
Berka Frenfert Accepted Answer Pending Moderation
  1. Tuesday, 27 February 2024 13:03 PM UTC
  2. PowerBuilder
  3. # 5

Protect='0' mean not protected
Protect='1' means protected

you are inserting 2 blank rows too. and not sure what are the primary key columns

Comment
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.