Yes of course this is the full code:
Snippet
void PullDataButton_Click(object sender, EventArgs e)
{
string DatabaseName = "UserData.db3";
string documents = System.Environment.GetFolderPath(
System.Environment.SpecialFolder.Personal);
string db = Path.Combine(documents, DatabaseName);
var conn = new SqliteConnection("Data Source=" + db);
var strSql = "select Name from Customer where STATEID=@STATEID";
var cmd = new SqliteCommand(strSql, conn);
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add(new SqliteParameter("@STATEID", 2));
tv.Text = "";
try
{
conn.Open();
SqliteDataReader sdr = cmd.ExecuteReader();
while (sdr.Read())
{
tv.Text = Convert.ToString(sdr["Name"]);
}
}
catch (System.Exception sysExc)
{
tv.Text = sysExc.Message;
}
finally
{
if (conn.State != ConnectionState.Closed)
{
conn.Close();
}
conn.Dispose();
}
}
void CreateDataBaseButton_Click(object sender, EventArgs e)
{
string DatabaseName = "UserData.db3";
string documents = System.Environment.GetFolderPath(
System.Environment.SpecialFolder.Personal);
string db = Path.Combine(documents, DatabaseName);
bool exists = File.Exists(db);
if (!exists)
{
SqliteConnection.CreateFile(db);
}
var conn = new SqliteConnection("Data Source=" + db);
var commands = new[] {
"DROP TABLE IF EXISTS TWITTERDATA",
"DROP TRIGGER IF EXISTS TWITTERDATA_INSERT",
"CREATE TABLE IF NOT EXISTS STATE (STATEID INT PRIMARY KEY, STATENAME VARCHAR(50))",
"CREATE TABLE IF NOT EXISTS CUSTOMER(CUSTOMERID BIGINT PRIMARY KEY, " +
"NAME VARCHAR(100), CONTACTNAME VARCHAR(100), DATEJOINED DATETIME, " +
"PHONE VARCHAR(25), ADDRESS VARCHAR(100), CITY VARCHAR(50), " +
"STATEID INT, ZIPCODE VARCHAR(25), DATEENTERED DATETIME, " +
"DATEUPDATED DATETIME, FOREIGN KEY(STATEID) REFERENCES STATE(STATEID))",
"CREATE TRIGGER IF NOT EXISTS CUSTOMER_INSERT INSERT ON CUSTOMER " +
"BEGIN UPDATE CUSTOMER SET DATEENTERED=DATE('now') " +
"WHERE CUSTOMERID=NEW.CUSTOMERID; END;",
"CREATE INDEX IF NOT EXISTS IDX_CUSTOMERNAME ON CUSTOMER (NAME)",
"CREATE INDEX IF NOT EXISTS IDX_STATEID ON CUSTOMER (STATEID)",
"CREATE INDEX IF NOT EXISTS IDX_DATEENTERED ON CUSTOMER (DATEENTERED)",
"INSERT INTO STATE (STATENAME) VALUES ('TENNESSEE');",
"INSERT INTO STATE (STATENAME) VALUES ('GEORGIA');"};
try
{
foreach (var cmd in commands)
using (var sqlitecmd = conn.CreateCommand())
{
sqlitecmd.CommandText = cmd;
sqlitecmd.CommandType = CommandType.Text;
conn.Open();
sqlitecmd.ExecuteNonQuery();
conn.Close();
}
SqliteCommand sqlc = new SqliteCommand();
sqlc.Connection = conn;
conn.Open();
string strSql = "INSERT INTO CUSTOMER (NAME, " +
"CONTACTNAME, STATEID) VALUES " +
"(@NAME, @CONTACTNAME, @STATEID)";
sqlc.CommandText = strSql;
sqlc.CommandType = CommandType.Text;
sqlc.Parameters.Add(new SqliteParameter("@NAME", "The Coca-Cola Company"));
sqlc.Parameters.Add(new SqliteParameter("@CONTACTNAME", "John Johns"));
sqlc.Parameters.Add(new SqliteParameter("@STATEID", 1));
sqlc.ExecuteNonQuery();
if (conn.State != ConnectionState.Closed)
{
conn.Close();
}
conn.Dispose();
tv.Text = "Commands completed.";
}
catch (System.Exception sysExc)
{
tv.Text = "Exception: " + sysExc.Message;
}
}
Thanks,
Mattia
Il 18/05/2012 13:30, ledz [via Mono for Android] ha scritto:
can you tell me table creation line so I can get the
types descriptions?
On 18 May 2012 12:12, Mattia Durli < [hidden email] >
wrote:
Hello Wally,
thanks for the quick response.
On that specific example, there should be
another button on that form. IIRC, the other
button should insert some data into the table.
When I get down to my office, I'll verify this.
Yes there is a button and it completes the DB creation and
sample data insertion with success, so that part works.
The problem is with the second button that should fetch the
data with a datareader.
What I don't understand is why I can follow the debug until
the "while (sdr.Read())" line, and then clicking F10 the
debug jumps at the end of the file... without rising any
error, even if it's in the try/catch block.
I tried both in emulator and in real device.
Debug should work, because there is a third button that
connects to a SQLServer DB, that fails correctly during the
connection in its try/catch block.
Thanks,
Mattia
You should be able to create a DB and store
it in the assets directory. When the
application is first deployed and run, you can
copy the db file out. I haven't done this,
but you "should" be able to do this. I'm a db
guy at heart, so sql commands are easy to put
together for me, plus I like to use them when
I update apps.
Accessing the db remotely is way more
complicated than we are used to with VS. You
can use adb to connect to the db, but you have
to have the device. I'm sure that there are
higher level tools that you can use, but I am
not aware of them.
Wally
> Date: Fri, 18 May 2012 03:35:23 -0700
> From: [hidden
email]
> To: [hidden
email]
> Subject: [mono-android] SQLite error
>
> Hello,
>
> I'm having this problem with the sample
code of the wrox book "Pro Android
> programming with Mono for Android",
InternalNetworkData sample.
> First it creates a DB and fills it with
data, with success, then tries to
> read the data with a SqliteDataReader.
> I tried to debug it and when it gets to the
line "while (sdr.Read())" the
> debug line jumps to the end of the file, no
errors, and non processing,
> application stil running, and I can press
the button to get the data again.
> What's wrong? with the code and with my
debug that can't get the error.
>
> string DatabaseName = "UserData.db3";
> string documents =
System.Environment.GetFolderPath(
> System.Environment.SpecialFolder.Personal);
> string db = Path.Combine(documents,
DatabaseName);
> var conn = new SqliteConnection("Data
Source=" + db);
> var strSql = "select Name from Customer
where STATEID=@STATEID";
> var cmd = new SqliteCommand(strSql, conn);
> cmd.CommandType = CommandType.Text;
> cmd.Parameters.Add(new
SqliteParameter("@STATEID", 2));
>
> tv.Text = "";
>
> try
> {
> conn.Open();
> SqliteDataReader sdr = cmd.ExecuteReader();
> while (sdr.Read())
> {
> tv.Text = Convert.ToString(sdr["Name"]);
> }
> }
> catch (System.Exception sysExc)
> {
> tv.Text = sysExc.Message;
> }
> finally
> {
> if (conn.State != ConnectionState.Closed)
> {
> conn.Close();
> }
> conn.Dispose();
>
> }
>
>
> By the way, is there a way with a thirdy
part software or even with vs to
> create a sqlite DB and deploy it with the
application? and maybe even to
> access the sqlite DBs in the connected
device? asking too much?
>
> Thanks!
> Mattia
>
> --
> View this message in context:
http://mono-for-android.1047100.n5.nabble.com/SQLite-error-tp5709898.html
> Sent from the Mono for Android mailing list
archive at Nabble.com.
>
_______________________________________________
> Monodroid mailing list
> [hidden
email]
>
> UNSUBSCRIBE INFORMATION:
> http://lists.ximian.com/mailman/listinfo/monodroid
_______________________________________________
Monodroid mailing list
[hidden email]
UNSUBSCRIBE INFORMATION:
http://lists.ximian.com/mailman/listinfo/monodroid
--
Gonçalo Oliveira
_______________________________________________
Monodroid mailing list
[hidden email]
UNSUBSCRIBE INFORMATION:
http://lists.ximian.com/mailman/listinfo/monodroid
If you reply to this email, your
message will be added to the discussion below:
http://mono-for-android.1047100.n5.nabble.com/SQLite-error-tp5709898p5709905.html
To unsubscribe from SQLite error, click
here .
NAML
--
View this message in context:
http://mono-for-android.1047100.n5.nabble.com/SQLite-error-tp5709898p5709907.html
Sent from the Mono for Android mailing list archive at Nabble.com.
_______________________________________________
Monodroid mailing list
[email protected]
UNSUBSCRIBE INFORMATION:
http://lists.ximian.com/mailman/listinfo/monodroid
