trying to add a new record(row) in database and get back the id(primary key)
from xojo documentation:
AddRow (tableName As [String], row As [DatabaseRow], idColumnName As string=“”) as integer
my syntax:
var io as integer
‘’''pid is the column name for the table that is the primary key
io=isdb.AddRow(“demographics”, row,“pid”)
databaseexception error in syntax
suggestions for correct syntax?
I am not at my home computer right now, but I don’t see an option where it will return the PK as an integer.
From the documentation:
Var row As New DatabaseRow
row.Column("Name").StringValue = "Penguins"
row.Column("Coach").StringValue = "Bob Roberts"
row.Column("City").StringValue = "Boston"
Try
myDB.AddRow("Team", row)
Catch error As DatabaseException
MessageBox("DB Error: " + error.Message)
End Try
https://documentation.xojo.com/api/databases/databaserow.html#databaserow
IF it does in fact return an integer then the above code could be:
Var row As New DatabaseRow
Var pid As Integer
row.Column("Name").StringValue = "Penguins"
row.Column("Coach").StringValue = "Bob Roberts"
row.Column("City").StringValue = "Boston"
Try
pid = myDB.AddRow("Team", row)
Catch error As DatabaseException
MessageBox("DB Error: " + error.Message)
End Try
I see this mention in a recent Blog post, maybe this will help…
New Database Connections – Xojo Programming Blog
“We’ve enhanced Database.AddRow with a new signature that returns the unique ID of the newly inserted row.”
my problem is I am not a software engineer. i dont think in computer language.
i am trying all kinds of syntax and keep getting an error during compile: there is more than one method with this name but this does not match any of the available signatures
most recent try:
io=isdb.AddRow(“demographics”, row,“pid” = “”)
i know its gotta be my syntax but…
I will be able to open up Xojo and take a look in the IDE in a couple hours.
Hopefully someone will beat me to the punch. 
There is an interesting document here.
Also, sqlite.com can be useful.
I recall about RowID (or _rowID ?), but I am sure this is an API1 command and do not remember how to use it…
Have-you checked - in debug mode - the SQLiteDatabase properties ?
What I am trying to do is right from the Xojo documentation. It appears to be a second addrow method that returns the ID from the new table when used …
Hopefully this image makes it into this reply
Don’t get any compile errors at all with the example from Xojo docs with:
Var db As New PostgreSQLDatabase
db.Host = "192.168.1.172"
db.Port = 5432
db.DatabaseName = "BaseballLeague"
db.UserName = "broberts"
db.Password = "streborb"
Var row As New DatabaseRow
' ID will be updated automatically
row.Column("Name") = "Penguins"
row.Column("Coach") = "Bob Roberts"
row.Column("City") = "Boston"
Var ID As Integer
Try
ID = db.AddRow("Team", row, "id")
Catch error As DatabaseException
MessageBox("DB Error: " + error.Message)
End Try
Well…just crashes when it wants to try to a PSQL server where I don’t have exposed for obvious reasons (o;
Besides… you have shown us only a snippet of your code…
Do you try to add this maybe to an older API 1 project where the column returning didn’t exist?
I started this project about 3 weeks ago with an all new download of Xojo, on windows.
This is an addrow that WAS working within a method. I just need to acquire the row id (auto-increment) that MySQL assigns and to set the appropriate textfield.text. (Basically, it’s a patient account number, and I am adding a new patient)
Trying to change the addrow sql statement with this third parameter “id” isn’t working. In your example you use “id” at the end of the sqlselect statement- is that the variable you created or the name of your database-tables -id- column?
The column name I used is just an example….whatever you use as your primary key name, put it there….I always use id on my psql servers for every table. But then again I never used Xojo to connect to a DB server (o;
But you say that you still get compiler errors…
My example did compile without errors and no existent database….
The following code works against a SQLite DB:
Var NewArtistID As Integer
Var row As New DatabaseRow
Var DbID As Integer
If tfArtistName.Text.Length > 0 Then //Checks to make sure the artist name is at least 1 character long
NewArtistID = DataAccess.getNextId("artist", "ArtistId")
row.Column("ArtistId") = NewArtistID
row.Column("Name") = tfArtistName.Text
Try
DbID = app.db.AddRow("artist", row)
InitListBox()
SaveControlsToggle(False)
Catch error As DatabaseException
MessageBox("Error: " + error.Message)
End Try
Else //If the artist name is of zero length then:
MessageBox("You must supply an artist name before saving.")
End If
MessageBox("The id is: " + str(DbID))
Sorry but I don’t have access to a MySQL db
MySQL is evil as it belongs to the license monster Oracle 
Yes and amen. As foretold by, um, The Oracle. <== Circular Reference? 
I appreciate immensely everyone attempting to help me out!!! Thank you everyone. You guys are seriously awesome and helpful🤛
Apparently this is a situation I should address with Xojo…
I can get it to compile but it throws a database exception while running. In the mean time I will figure a work-around.
Well if compiling throws no error anymore then I would check the how the database/table is setup.
Can you show us how your table is defined and what DB you’re using?
For example with PostgreSQL you have no primary key that gets incremented automatically. You have to create a sequence first and assign that to the column you want to be incremented.
But how about just querying your database and return some rows? Is that working? Or does it throws the same dbexception?
PostgreSQL has had the Serial data type for a wee bit which is their “autoincrement” data type and comes in three flavors:
smallserial
serial
bigserial
the database is mysql - i am inserting and updating rows and the primaryKey autoincrement field is working perfectly. It is definitly this “new” function that returns the primary key ID# that is throwing it. I did try about every combo I could come up with, twice, all to no avail. I don’t but maybe think this primary key is not the “first” field in the table (i had changed the pK field part way along and deleted the original pK field). Not sure i can move it though and i can’t imagine that would matter since the function asks for the name of the pK field.
I did figure a workaround that will work for me as this dB is going to be lightly used. Thank you for your thought, everyone. I am pretty convinced there may be a bug since this is a pretty new function
Interesting…
Curios to try that tomorrow (already Feierabend here ;o) out myself on a spare RPi with MySQL/MariaDB and/or PSQL.
If you do try, try with MySQL- haha
Just create a VM in your machine, make it in bridge mode so it gets an IP like another physical computer, install all DBs you want, connect to your local isolated and disposable DBs as you wish.