Hey Guys!
I need a little help in Sql Database programming in vb.net!
how can i Connect to 2 Databases and Join their queries?
Code:
Dim SQLcon As SqlCeConnection = Nothing
Dim strPDAPfad As String = AppDir & "\Catalogs\" & Config.QuestionFileName
Const strPDAPasswort As String = ""
SQLcon = New SqlCeConnection("Data Source=" & strPDAPfad & ";" & strPDAPasswort)
SQLcon.Open()
Dim SQLString As String = "SELECT * FROM QUESTIONS WHERE ID = '" & QuestionID & "'"
Dim SQLQuery As SqlCeCommand = New SqlCeCommand(SQLString, SQLcon)
i want to join 2 databases so i can order one table with the help of another!
Just Like I would have one Database with 2 Tables
"SELECT Questions.ID FROM Questions ORDER BY Stats.Wrong DESC"
I think this is not possible to to it directly. You might need to load the data of one into a temp table in the other db and then do the query.
Would be bad I need to do this very often
I will try to find out how to attach both files to an SQL Compact server and try that again
[edit] found nothing!
scilor said:
Hey Guys!
I need a little help in Sql Database programming in vb.net!
how can i Connect to 2 Databases and Join their queries?
Code:
Dim SQLcon As SqlCeConnection = Nothing
Dim strPDAPfad As String = AppDir & "\Catalogs\" & Config.QuestionFileName
Const strPDAPasswort As String = ""
SQLcon = New SqlCeConnection("Data Source=" & strPDAPfad & ";" & strPDAPasswort)
SQLcon.Open()
Dim SQLString As String = "SELECT * FROM QUESTIONS WHERE ID = '" & QuestionID & "'"
Dim SQLQuery As SqlCeCommand = New SqlCeCommand(SQLString, SQLcon)
i want to join 2 databases so i can order one table with the help of another!
Just Like I would have one Database with 2 Tables
"SELECT Questions.ID FROM Questions ORDER BY Stats.Wrong DESC"
Click to expand...
Click to collapse
Dim SQLString As String = "SELECT * FROM tbl1, tbl2 WHERE tbl1.ID = tbl2.ID and tbl1.QuestionID = '" & QuestionID & "'"
Thank you But I want to do this out of 2 Databases not out of 2 Tables!
I have not actually had the need to do so.
But I would check out LINQ, I have a feeling it should be able to do that.
I'll check it for you when I get home (at work at the moment).
LINQ how it works? I only find rare information for it!
how can I use it in VB.net 2008 ?
There is SQL Command named
UNION
which can Combined Identical Structure Database to Combine the Record from Both Table have you tried it
But how can I use that practically, i know in php is that easily because I know the alias of the Database but with my technic
New SqlCeCommand(SQLString, SQLcon)
there only can be one Database Per Connection
LINQ will read all the records from both databases into memory then do the merge join and give you the matched resultset. You could probably do it much better yourself. If you are going to repeat this join often, then for the sake of the user's patience, you should at least try to do it yourself.
If one table is known to be smaller (in KB) than the other, then I would cache that table in a Dictionary<keyColumn, DataRow> construct. Then I would open a data reader on the second table and process each of those rows by referring to the cached version of the first table.
Of course, I assume you have already ruled out the possibility of permanently merging the two databases? That would be the best solution. But perhaps there are two separate applications, to which you do not have the source code, responsible for maintaining these two databases, in which case I fully understand.
I need to Seperate both!
It is for my Driving Licence Trainer. and I want to Seperate Training Files from the Statistics
If one table is known to be smaller (in KB) than the other, then I would cache that table in a Dictionary<keyColumn, DataRow> construct. Then I would open a data reader on the second table and process each of those rows by referring to the cached version of the first table.
Click to expand...
Click to collapse
@ftruter Could you make an Example for using it for sorting?
Well, to give you a bit of history on this project - I'm parsing a string that's about 100k in a child thread, and returning the Spanned object from Html.fromHtml().
The issue is when it returns to the main thread - when trying to setText using the Spanned that was just created in another thread, it takes quite a long time processing it from a Spanned object into a displayable format (setText).
I would normally process this in the child thread, but unfortunately, since this is related to the UI, I can't - has to be on the main thread. So, my question is whether there is a way to process the Spanned into something easily interpreted by the setText, so that it doesn't spend a lot of time on the main thread processing it after the child thread has finished.
Code:
testQuestionsExplanations.setText(spannedExplanationsObj);
My biggest issue is that it locks the main thread down doing this.
I'm not sure I understand the problem. If the processing is already being done in a separate thread, why the hang? Are you running the thread synchronously? Couldn't you use a handler, process the thread asynchronously, then send the handler a message once the thread is done?
Gene Poole said:
I'm not sure I understand the problem. If the processing is already being done in a separate thread, why the hang? Are you running the thread synchronously? Couldn't you use a handler, process the thread asynchronously, then send the handler a message once the thread is done?
Click to expand...
Click to collapse
That's what I'm doing already - it sends a message to the handler once the child thread has finished.
The problem lies with the following line - it takes roughly 10 seconds to write the 'Spanned' object to the screen. If i spit out the pure text (minus the html tags), it writes it in about 1 second, but I'm assuming that it has to process/render the Spanned object into something that it can display on the screen.
Code:
testQuestionsExplanations.setText(spannedExplanationsObj);
I found a work around for my problem.
Instead of sending the whole Spanned object and getting it processed onto the TextView all at once, I'm sending smaller pieces, and just appending them to the TextView.
This way my ProgressDialog doesn't lock up completely - it's jerky, but it's better than not moving at all.
I'm developing an organizer app as my bc. thesis and I don't have any real Android developer to consult, so I made this thread and hope somebody will help me and point me in the right direction.
I just started to work on it and I have roughly 1 month to make something useable out of it.
So... the first thing that comes to my mind right now is synchronization.
1. I don't know if I should implement it or not.
I have a hosting with 1.5 GB space, a relatively fast connection. Would syncing data (text (probably xml) only) with this server slow it down significantly ? How many users could an average server take ?
And another one regarding sync:
2. I'd like my application to exchange data with my server under the google account on which the device is logged in, so no registration will be neccessary (I suppose the majority of devices are loggen in with google). I'll probably need to get the user's google account name on every sync session. Is that possible ?
The app will already have a server-side app to edit your events and stuff, so I'll need the user's login information again to retrieve his data from my database, but I won't have his password, so I can't make a usual login form. I guess Google has some API to figure out if a user is logged in, doesn't it ?
Any advice will be appreciated.
Can I open a menu with a simple button click ? Like a context menu but for short clicks.
// I solved the **** outta this one.
Still waiting for answers on the first post.
"Organizer App" really doesn't tell us what you're trying to do. It's hard to answer questions if one doesn't know that basic plan of the project you're trying to create.
Check this -> Basic info about my bc thesis
Nice web page...ambitious project for a 4 week time frame.
As a programmer, you know to start small and add functionality as your program grows.
I would start by making a simple"To Do" list: Add item, Delete item, Edit item, Mark item done, Save this list, Recall a list, Delete a list.
Post back when that's done
Rootstonian said:
Nice web page...ambitious project for a 4 week time frame.
As a programmer, you know to start small and add functionality as your program grows.
I would start by making a simple"To Do" list: Add item, Delete item, Edit item, Mark item done, Save this list, Recall a list, Delete a list.
Post back when that's done
Click to expand...
Click to collapse
Agreed 100%.
@ OP:
I don't know much about syncing, but it won't have a large cost of the server's resources - syncing an CSV or XML file is pretty trivial. Even if you have a low bandwidth cap, text files are quite small. So you should be fine in terms of that.
Nobody can give you a "set number" - ie. the server can take "X people." It greatly depends on the server type, how much bandwidth is allocated to you, your traffic priority, etc. I mean if it's one server with like a xeon processor then it can probably handled a pretty heavy load (say 50 probably? (and yes, I'm kind of pulling that number out of my ass)). Though most web hosts don't just dedicate one server to each customer. Typically, now-a-days, everything is virtualized. Everything is unified using server farms (ie. multiple servers or computers) virtually, then virtual chunks of resources are distributed to clients. So even if somebody could give you exact numbers (which is impossible), they'd most likely be wrong. That is unless you paid extra to have a particular server or particular number of servers dedicated to you...but even then, there's a high chance that it's just a chunk of a virtual server farm.
He's right, this is pretty ambitious in a 4 week period, especially if you have other activities going on as well. It's even more ambitious if this is your first app. You may want to reconsider your objectives given your time frame.
As for the google login, you'd need to look at the google API, and see if there's a way to verify peoples' usernames and passwords. If you can, then it's just a matter of encrypting them and storing them in a properties file or something.
Hope that helps ya out some.
Rootstonian said:
Nice web page
Click to expand...
Click to collapse
Thank you
I know, I know. I could have started 4 months ago. But I didn't, the lazy bastard I am.
This is the practical part of my bc thesis. The ultimate deadline is 3rd of june, but I have to have my theoretical part (30-50 pages of text, if IIRC) done by then, all printed out and ****. So I plan to work as hard as I can on this till the end of this month and then start to work on both at the same time. I really don't have any other duties in school, since I have a nice reserve of credits so this is my priority #1.
The main objective is geo-tasks, since that's what the name of my thesis says. Everything else is just an addition (i.e. the widget, that was just an idea, I doubt I will have time for that). So I'd rather start with that (I know, It's the hardest part). Or do you still think I should start with the to-do's ?
You can check out my last night's post to see what I've been up to the last 2 days and what I'll be up to next.
What is your level of Android programming experience (None, Basic, Intermediate, Advanced)?
And I'm a little fuzzy on the whole "Geo" thing. Is it like I have a list "Get milk, bread eggs", "Pick-up dry cleaning", "Get oil change" and then your app is going to "sense" when I'm by the grocery, dry cleaner's and oil change place? Which grocery? I use 4 different stores. Same dry cleaner and oil change though usually.
If so, you are going to need to code in lat/long coordinates for these places and then compute your location vs one of the merchants, compare that merchant to an "active" TO DO list item and pop-up an alert of some sort if you're within a mile. Wow
Your project, you know your skill level. Start where you want I guess LOL.
I think it's more of an agenda type thing - that syncs.
@Rootstonian
I'd say basic, but quickly crawling up to intermediate.
It's almost like you said, but there's only one location for every task. Maybe in a later version...
Well, good luck. Keep us posted
Will do. Hope you guys will keep on helping me
I need to use my database object in more than one activity. How do I pass it from one activity to another ?
Should I create a new instance in each activity ? Or should I create a content provider ? (I'd rather not - they're quite complicated for me and I only need to access the DB in this app).
I'm going to assume you are using a database helper type class.
No need to pass data from Activity to Activity; just open, use and close the database in each Activity as required.
I have a problem.
I created a LinearLayout for all the tasks in the database. Now I want to register all of them for a context menu. I can do that, but I can't figure out which one's menu was triggered.
LinearLayout has a setId method, but it only takes integer values, which is no good for me, because I have 3 types of tasks and if I assign them their id from the database, then the same ID will probably be assigned to 3 different views.
I could use some sort of multiplication, like for timed tasks I would multiply the ID by 10 000, but that's not elegant at all, and it would crash after a few months of using the app.
So what do I do now ?
I'll paste some code.
Code:
public void drawTasks() {
TextView emptyText = (TextView) findViewById(R.id.empty_todo);
if (tasks.isEmpty()) {
emptyText.setVisibility(View.VISIBLE);
} else {
emptyText.setVisibility(View.GONE);
Iterator<Task> it = tasks.iterator();
//create views
while (it.hasNext()) {
//create a temporary object
Task tmpTask = it.next();
//get it's properties
long id = tmpTask.getId();
String title = tmpTask.getTitle();
String description = tmpTask.getDescription();
int important = tmpTask.getImportant();
int finished = tmpTask.getFinished();
//create new LinearLayout for this task
LinearLayout newTaskLayout = new LinearLayout(this);
newTaskLayout.setId((int)id);
newTaskLayout.setOrientation(LinearLayout.VERTICAL);
newTaskLayout.setPadding(5, 0, 0, 10);
taskLayouts.add(newTaskLayout);
//create new views for these properties :
//title
TextView newTaskTitle = new TextView(this);
newTaskTitle.setText(title);
newTaskTitle.setTypeface(null, Typeface.BOLD);
//important tasks are highlighted
if (important == 1)
newTaskTitle.setTextColor(Color.RED);
//finished tasks are italic
if (finished == 1)
newTaskTitle.setTypeface(null, Typeface.ITALIC);
//description
TextView newTaskDescription = new TextView(this);
newTaskDescription.setText(description);
if (finished == 1)
newTaskDescription.setTypeface(null, Typeface.ITALIC);
//add views to this tasks' LinearLayout
newTaskLayout.addView(newTaskTitle);
newTaskLayout.addView(newTaskDescription);
//add new linearLayout to tasksRootLayout
tasksRootLayout.addView(newTaskLayout);
}
}
}
This is self-explanatory. taskLayouts is an ArrayList of LinearLayouts where I keep the pointers to all layouts that need to have a context menu.
There are 2 other similar methods: drawTimedTasks() and drawGeoTasks() which do basically the same (I couldn't figure out a way to do it with one universal function).
Here's how I register them for context menu:
Code:
private void registerViewsForContextMenu() {
//register control bar buttons
registerForContextMenu(newGeoTaskButton);
registerForContextMenu(newTaskButton);
Iterator<LinearLayout> it = taskLayouts.iterator();
while (it.hasNext()) {
registerForContextMenu(it.next());
}
}
And here's how I'm checking for context menu trigger:
Code:
public boolean onContextItemSelected(MenuItem item) {
switch (item.getItemId()) {
case ...
return true;
}
...
}
return false;
}
What am I doing wrong ?
Anyone ?
----
I haven't really been following this thread so I don't know all the details of your app, but I'll try to help.
So each task has 3 views for it? Each task is in the db once? I'm a little foggy on how you're storing them and what the correlations are between views, events, and types of event, more specifically the latter.
If you explain that a little more clear, I can try to help a little better.
There are 3 types of tasks : simple, timed, and geo tasks. Each type has its own table in the db, hence it's own IDs. That means, the first geo task, first simple task, and the first timed task ever added have all the same ID = 1. That means if I want to give their views unique IDs, I can't use their IDs from the database.
So I'm trying to figure out a way how to give the view ID's so I can find out which database record they represent.
In an ideal world, I would be able to define a string ID (i.e. geo_1, simple_1, timed_1), but this is not the case. I dunno why... in XML you give your views string IDs, but programatically you can't.
grandioso said:
There are 3 types of tasks : simple, timed, and geo tasks. Each type has its own table in the db, hence it's own IDs. That means, the first geo task, first simple task, and the first timed task ever added have all the same ID = 1. That means if I want to give their views unique IDs, I can't use their IDs from the database.
So I'm trying to figure out a way how to give the view ID's so I can find out which database record they represent.
In an ideal world, I would be able to define a string ID (i.e. geo_1, simple_1, timed_1), but this is not the case. I dunno why... in XML you give your views string IDs, but programatically you can't.
Click to expand...
Click to collapse
If all of the tasks have common elements (even if they don't really), you can merge them into one table. Then define an enum or something for the task type, as a simple field in the table. This removes the issue with duplicate ID's, while still being able to differentiate what type they are. You can typically leave fields blank in a database, so you wouldn't have to worry about what's filled in for tasks that don't have particular fields associated with them, just verify all fields prior to mutating the database.
If you're hellbent on keeping three separate tables you can hold a global ID, which can be an intersection between all three tables. So this wouldn't be the primary key, just a global ID, and you can key them up by that. Not entirely sure where you'd keep it to persist over power cycles unless you just make a 4th table with one row and store it in there. Or just throw it in the program's data somewhere.
The reason you can define things in the XML is he XML is compiled into the R class at compile time...though everything in there *should* have a corresponding methodology to get it done programatically. Though if you're talking about the primary key, as far as I know that is always numerical. So the ideal world may not apply - BUT, that doesn't mean you can't create a field that stores a string id (which I wouldn't, because string comparisons take a lot of time, but whatevs).
Hope that helps you out some, I don't really touch databases and am very new to Android programming, just know what I know from absorbing info from others - but I know a pretty decent amount about programming in general and know Java pretty well so I can probably help you out the best I can in some respects.
I'm going to fetch the questions and answers from my web server using JSON, this data are dynamic, sometimes a question has two or three answers, question type can be any combination of the following: multiple choice, true or false, identification, fill in the blanks etc.
The answer on how to do a dynamic layout on android is to let java takeover on adding layout and components instead of XML. (is this correct?)
How can I make my app to have one activity/view per question? Is this memory intensive? For example the data from the web server contains a hundred questions.
I want the user to have access to previous questions. For example I'm currently on question number 21 and I want to go back to question number 11 for some reasons, is this possible? How can I do this? (All questions are randomized)
clonedaccnt said:
I'm going to fetch the questions and answers from my web server using JSON, this data are dynamic, sometimes a question has two or three answers, question type can be any combination of the following: multiple choice, true or false, identification, fill in the blanks etc.
The answer on how to do a dynamic layout on android is to let java takeover on adding layout and components instead of XML. (is this correct?)
How can I make my app to have one activity/view per question? Is this memory intensive? For example the data from the web server contains a hundred questions.
I want the user to have access to previous questions. For example I'm currently on question number 21 and I want to go back to question number 11 for some reasons, is this possible? How can I do this? (All questions are randomized)
Click to expand...
Click to collapse
For your view, you'd make one Layout containing multiple TextViews whose texts you set only in your java code, that way it can have different questions.
Then there is a ListView for scrolling between the questions or for instance a ViewPager.
Remember to start with small components like the question screen and worry about getting the questions from the server and stuff later
Is there anybody else wants to share their insights on this?
clonedaccnt said:
Is there anybody else wants to share their insights on this?
Click to expand...
Click to collapse
Even though it may not seem like it, this is a very simple app...and it comes down to "simple" understanding of "listview,adapters,intents,asyncTasks,JSON/GSON" and then quite a simple design flow.... if you start to learn this most of what you need you will know and what you don't you will be able to ask direct questions about (cause you will have knowledge to do so)