InsertRecord

Databases
1

the InsertRecord from Xojo is the same as Insert into Table.

I have a problem using postgreSQL i use InsertRecord and it return Ok but when i go to the Remote Data Base Server the Record no found.

2

Did you perform a Db.Commit after the insert?

3

yes and check for errors to

4

and i use DbConnMain.SQLExecute “END TRANSACTION”

5

This is the Code

DbConnMain.SQLExecute “BEGIN TRANSACTION”

SqlStr=“Select * From journaltemp Where sync=‘N’ And custid<>’’ and storenum=” + Str(StoreNum)
Rs=DbConn.SQLSelect(SqlStr)
If DbConn.Error Then
MsgBox "Error UpTrans " + DbConn.ErrorMessage
Exit Sub
End If

sCount=Rs.RecordCount
If Rs.RecordCount > 0 Then
Window1.Show
Window1.LabCount.Text=""
x1=0
Do
x1=x1+1
Window1.LabCount.Text=Format(x1,"###0")

    DbData.IntegerColumn("num")=AutoNumChkUp("Journal")
    DbData.Column("fdate")=Rs.Field("fdate").StringValue
    DbData.DoubleColumn("debit")=Rs.Field("debit").DoubleValue
    DbData.DoubleColumn("credit ")=Rs.Field("credit").DoubleValue
    DbData.IntegerColumn("refid")=Rs.Field("refid").IntegerValue
    DbData.Column("refname")="Up"
    DbData.Column("userid")="Auto"
    DbData.Column("paytype")=""
    DbData.Column("payinfo")=""
    DbData.Column("stationid")=Rs.Field("stationid").StringValue
    DbData.Column("custid")=Rs.Field("custid").StringValue
    DbData.Column("sync")="Y"
    DbConnMain.InsertRecord("journal",DbData)
  
  DbConnMain.SQLExecute(SqlStr)
  If DbConnMain.Error Then
    MsgBox "Sql Error " + DbConnMain.ErrorMessage
    ErrCase=True
    DbConnMain.Rollback
    exit sub
  End If
  
  Rs.MoveNext
  Window1.Refresh
Loop Until Rs.EOF=True
Window1.Close

End If
Rs.Close

DbConnMain.SQLExecute “END TRANSACTION”

6

I use code that would be similar to what is below and it always works for me. I have stayed away from the begin/end transaction (just my coding style I guess).

DbConnMain.InsertRecord(“journal”,DbData)
If DbConnMain.Error Then
DbConnMain.Rollback
Else
DbConnMain.Commit
End If

7

ok Thanks

8

Hang on a sec
DbConnMain.SQLExecute “BEGIN TRANSACTION” <<< DBConnMain

Rs=DbConn.SQLSelect(SqlStr) <<< this is a different connection ???

you seem to have more than one database connection
second where is dbData defined ?
you should create a new one on EVERY iteration of the loop & not reuse one

then AFTER you do the insert & before you check the error you have

[code] DbConnMain.InsertRecord(“journal”,DbData)

  DbConnMain.SQLExecute(SqlStr)

[/code]so you reexecute the query BEFORE you have seen if the insert worked
so sqlexecute probably reset s the error indicators

9

no sorry that is a extra code
DbConnMain.SQLExecute(SqlStr)

i forget to remove the line that line have a comment in my code

10

yes i have two connection One is Local Server and the other is from Remote Server by internet connection

11

Read again what Norman said: Your error check is misplaced.

12

Ok thanks i fix that

But
if i have DbConnMain.Commit error i have to call DbConnMain.Rollback (yes /No)

13

yes