Selecting a table to do an insert

I’m new to Xojo and have a little experience of SQL. I have an existing database called MGA with two tables called: Cards and Classes. These I am displaying in a Listbox. I now want to insert a record in table Cards. I have written some code but, as I don’t know how to select the table Cards, and can’t find an example in the Xojo documentation. I’m getting an error saying ‘There is more that one item with this name and it’s not clear to which this refers’. Here is my code. How do I select table Cards?
Thanks in advance,
Keith

Dim dbFile as foldeitem = SpecialFolder.Desltop.Child(“MGA.s3db”)
DB = new SQLiteDatabase
DB.DatabaseFile = dbFile

// Here I need to select table Cards

Dim row as new DatabaseRecord
row.Column(“Title1”) = “John”
row.Column(“Title2”) = “Sam”

db.InsertRecord(“Cards”, row)

Personally I dislike that method for manipulating records… I believe SQL is a much easier way to do it

This is off the top of my head… so you may need to refer to the LangRef but this should give you the idea

dim t1 as string="John"
dim t2 as string="Sam"
db.ExecuteSQL("INSERT into CARDS (title1,title2) VALUES("+chrb(34)+t1+chrb(34)+","+chrb(34)+t2+chrb(34)+")")

Which line generates that error?

This isnt your actual code, I guess…

Dim dbFile as foldeitem = SpecialFolder.Desltop.Child(“MGA.s3db”)

Dim dbFile as folderitem = SpecialFolder.Desktop.Child(“MGA.s3db”)

You do need to OPEN the database

If Not db.Connect Then MsgBox("Open database failed") End If

Once you have the database open, you need a recordset (which is where you specify which table you want to hit)
And you do the insert into the recordset

And as Dave says, often its easier just to do the whole thing in one shot by saying
add a new record to table cards, by populating the fields title1 and title2 with the values ‘apple’ and ‘orange’

Yes the folder error was a typo!

All the row.column lines gave the error I reported

I’ll try the SQL approach as it sounds simpler.

Be careful not to open yourself to SQL Injection. This is the main danger and has been the down fall of probably millions of servers/applications. The more correct way is a little longer but should always be used:

dim ps as PreparedStatement = db.Prepare("INSERT into CARDS (title1,title2) VALUES($1, $2)")
ps.SQLExecute(t1, t2)
if db.Error then
  // handle the error in some way
end if

Depending on what database you are using, you may need to add in some BindType calls to the prepared statement. Even though it is longer, it is very, very, very important to do this the right way.

Also, I do not see a problem with your code. When the compiler says that, click on the error line and it should highlight where the problem is in code. I’m guessing that it is somewhere else in your code, not the code in the original post.

I’m using SQLite

http://documentation.xojo.com/index.php/SQLitePreparedStatement

Or you could use the SQL Insert command.

SQLite.org has full documentation on their website, the command you are looking for can be found here:
https://www.sqlite.org/lang_insert.html

Simon.

DatabaseRecord is the simplest way to do this and it uses a prepared statement behind the scenes (if I recall). Your code as posted looks correct. There must be something else going on.

This error message has nothing to do with your database. Somewhere in your code – on the line where the compiler stops – you are using a method and it is not clear for the compiler which one you are using. Mostly this is because of overloaded methods and when using Variants the compiler does not know which method to use.

Check the line which the compiler nags and post it here.

[quote=190965:@Dave S]Personally I dislike that method for manipulating records… I believe SQL is a much easier way to do it

This is off the top of my head… so you may need to refer to the LangRef but this should give you the idea

dim t1 as string="John" dim t2 as string="Sam" db.ExecuteSQL("INSERT into CARDS (title1,title2) VALUES("+chrb(34)+t1+chrb(34)+","+chrb(34)+t2+chrb(34)+")") [/quote]
Dave, please don’t spread information like this to somebody who admits to being new to the topic. It helps to perpetuate bad habits. Nobody wants a visit from Bobby Tables.

Keith, if you want to write SQL inserts, you need to use the SQLitePreparedStatement class as Jeremy points to. Otherwise, DatabaseRecord is the way to go.

The reason for this that is both classes will sanitize values. This is extremely important due to SQL Injection, a common form of attack. It basically allows an attacker to execute any SQL they please. These classes are your defense.

But in the case of a local SQLite database, this isn’t so big of a deal. Until somebody with an apostrophe in their name causes the statement to fail. Using an escape function is common, but not advisable. Best option is to let the database engine do the work, as it is the only thing that knows the right way to do it.

Yes, they make your code more verbose, but develop these good habits now and it won’t be so big of a deal.

Excuse me? Using SQL to manipulate a Database is BAD INFORMATION? and I assume “Bobby Tables” is some attempt at a joke?

If you want to work in this profession, and you distain the use of SQL in favor of syntax that is proprietary to a given programming language you are going to find yourself coming up short in the future.

And if you please notice… I prefaced my response with “I PERSONALLY…” it is the job of this forum to present solutions to those that ask, it is the responsibilty of those that ask to weigh those solutions and to determine what path they wish to take.

And as far as bad information, “you NEED to use” is not correct. “you SHOULD use” [with an explantion] would be more correct.

No, that is not right. Working with SQL is awesome, encouraged and can be a huge time saver. Simply inserting strings into a SQL statement is BAD INFORMATION. For example:

db.SQLExecute("INSERT INTO customer (name) VALUES ('" + CustomerName.Text + "')")

That is VERY bad. Imagine the user enters ');BEGIN; UPDATE users SET password='abc123'; COMMIT;. Your blindly executed SQL is now:

INSERT INTO customer (name) VALUES (''); BEGIN; UPDATE users SET password='abc123';COMMIT;

You should never allow anything like that to happen. That is called SQL Injection. Any data coming from an outside source HAS to be sanitized and it’s best to let the database sanitize it properly. If you were to use Prepared SQL Statements or the DatabaseRecord class, all user entered data is sanitized for you automatically.

The example you gave did no sanitization and that should be avoided. That is what people were saying.

Here’s Bobby :slight_smile:

Yeah, what they said. I’m not trying to insult you.