Re: Tricky SQL Question - Visual Basic Discussion Boards

waner michaud26-Sep-12 8:12

26-Sep-12 8:12

Hi everyone,

Can anyone guide me on how to develop a small telephone tracking system through GPS in vb.net with sql database? How to approach this from a conception point of view. and what i need to take into account.

I have no prior knowledge with GPS and to implement it.

this application is to keep track of all cellphone calls thru GPS and store the data in a database for analysis. this must be done in vb.net with sql.

this is an anti-kidnapping small app.

Thanks advance.

Re: Telephone Tracking system through GPS

Eddy Vluggen27-Sep-12 0:16

Eddy Vluggen

27-Sep-12 0:16

waner michaud wrote:
I have no prior knowledge with GPS and to implement it.

..then I suggest you start there. Windows 7 has a sensor API[^], with the C# intro here[^] (for .NET 4)

The cool part;

Location information may come from multiple providers, such as GPS, Wi-Fi triangulation, and cell phone tower triangulation.

waner michaud wrote:
this is an anti-kidnapping small app.

That matters little; you could still abuse the technology and do something illegal. I can't tell from here, can I?

Bastard Programmer from Hell Suspicious | :suss:

if you can't read my code, try converting it here[^]

Datagrid row count equals zero

Cesar00226-Sep-12 6:49

Cesar002

26-Sep-12 6:49

I am new to VB.net but have been doing ok developing a personal project so far.... since yesterday... The problem is that I got stuck when trying to read the row count of a datagrid that has more than 1500 rows filled with data. The result that I am getting is form2.datagridview1.rows.count - 1 = -1. Which makes no sense since the datagrid is plenty of rows.
Something that I must say is that this datagrid belongs to form2 (a second form that pops-up and shows the datagrid when I call it from From1).
Any thoughts on what could be possibly happening?
Thanks for any advice or comment on this matter.
PS: I'll post my code as soon as I get home

Re: Datagrid row count equals zero

Cesar00226-Sep-12 10:05

Cesar002

26-Sep-12 10:05

Actually what I want is to perform a reverse loop search from the last row of the datagrid to the first one.
When I try to obtain the las row index, I always get zero....

Re: Datagrid row count equals zero

Eddy Vluggen26-Sep-12 10:32

Eddy Vluggen

26-Sep-12 10:32

Can you copy/paste the code into your message, formatted with PRE tags?

Bastard Programmer from Hell Suspicious | :suss:

if you can't read my code, try converting it here[^]

Re: Datagrid row count equals zero

Cesar00226-Sep-12 16:28

Cesar002

26-Sep-12 16:28

I just figured out that I had to connect again to the database and count the rows of a new created dataset.

I was getting 0 rows because even if the datagrid was full of data, that data came from a previously created dataset that was no longer available. So I really was referencing to a "empty" datagrid.

Problem solved.

Thank you.

Tricky SQL Question

ttocsmi25-Sep-12 10:42

ttocsmi

25-Sep-12 10:42

I have an Access database for which I'm trying to write a query based on a request from the users.

I have a table of data related to artwork, with three columns: ArtworkId, DescriptionText, and CalloutNumber. Each piece of artwork can have more than one DescriptionText value.

What would be a good way to write an SQL query to return, for example, all ArtworkId records having both DescriptionText = "Start Switch" and DescriptionText = "Stop Switch" records?

ArtworkID / DescriptionText
123 / Motor
234 / Fuse
234 / Start Switch
456 / Stop Switch
789 / Start Switch
789 / Stop Switch
789 / Light

Result = ArtworkID 789

Any assistance will be greatly appreciated. Thanks!

Re: Tricky SQL Question

Martijn Kok25-Sep-12 11:40

Martijn Kok

25-Sep-12 11:40

I think the follow query would do what you want:

SQL

SELECT ArtworkID
FROM Artwork
WHERE DescriptionText In ("Start Switch","Stop Switch")
GROUP BY ArtworkID
HAVING COUNT(*)=2;

Re: Tricky SQL Question

ttocsmi25-Sep-12 14:13

ttocsmi

25-Sep-12 14:13

Thanks. I tried something similar this afternoon, but it didn't work. I'll try your idea tomorrow.

Re: Tricky SQL Question

Bernhard Hiller25-Sep-12 23:13

Bernhard Hiller

25-Sep-12 23:13

Just happens to work with the example data given. It will show wrong results with duplicated entries, e.g. having two times 357,"Start Switch".

Re: Tricky SQL Question

Martijn Kok25-Sep-12 23:27

Martijn Kok

25-Sep-12 23:27

You are right. If for some reason you can't prevent duplicate entries, then you can use the following query:

SQL

SELECT ArtworkID
FROM (SELECT DISTINCT ArtworkID, DescriptionText FROM Artwork) as t1
WHERE DescriptionText In ("Start Switch","Stop Switch")
GROUP BY ArtworkID
HAVING COUNT(*)=2;

It uses a sub-query to get the distinct values first. The rest functions just the same.

Re: Tricky SQL Question

ttocsmi1-Oct-12 1:56

ttocsmi

1-Oct-12 1:56

Nope. Still nothing....

Re: Tricky SQL Question

ttocsmi1-Oct-12 1:51

ttocsmi

1-Oct-12 1:51

For reasons unknown, this didn't work. My intention was for the search text entries to more resemble wildcard text. For example, return all IDs with "switch" or "cable" or whatever in the DescriptionText field.

Thanks for your response.

Re: Tricky SQL Question

Bernhard Hiller25-Sep-12 23:15

Bernhard Hiller

25-Sep-12 23:15

You must join the table with itself. Something like:

SQL

SELECT a.ArtworkID
FROM Artwork a 
INNER JOIN Artwork b
ON a.ArtworkID=b.ArtworkID
WHERE a.DesciptionText="Start Switch" AND b.DescriptionText="Stop Switch"

Re: Tricky SQL Question

ttocsmi1-Oct-12 2:12

ttocsmi

1-Oct-12 2:12

Well, that worked. I used 'like' for '=' and surrounded the search text with '%' characters.

Now, suppose I wanted to search using up to 5 terms - would I have to nest the inner joins?

This seems really complicated for something I can see with my eyes & describe easily using English words.

(Also, I tried using the Filter By Form command, but it wouldn't work with my form.

Thanks for the feedback.

Re: Tricky SQL Question

ttocsmi1-Oct-12 4:34

ttocsmi

1-Oct-12 4:34

Surely there's an easier way, but this is how I got it to work (I've updated the table and field names):

SQL

SELECT *
FROM art_callout_table
WHERE artboardnumber IN (
    SELECT artboardnumber FROM art_callout_table 
    WHERE artboardnumber IN (
        SELECT artboardnumber FROM art_callout_table 
        WHERE lrutext LIKE "%start%") 
    AND lrutext LIKE "%light%") 
AND lrutext LIKE "%switch%";

What confused me repeatedly during development, and what confused the users (they were expecting the search to work one way & I was assuming it to work another), is that there are two distinct search methodologies:

1) Using the built-in Access table filtering commands (or a basic SELECT FROM WHERE statement) to narrow down a table of description text values. Repeated filters can be applied to narrow the list further, but if you're looking for "switch" and "start", all you'll get is individual description text records having both values, eg "big red starting switch" or "switch start light" but not "green switch". Not terribly helpful.

2) Use a different search technique to return all ArtworkID values which have "switch" or "start" or "light" strings within their DescriptionText fields, eg don't look just for DescriptionText records similar to "switch start light blinker".

I thought this was going to be much easier than it turned out to be. Thanks all for your help.

Re: Tricky SQL Question

Martijn Kok1-Oct-12 5:13

Martijn Kok

1-Oct-12 5:13

Good you solved it! Thumbs Up | :thumbsup:

Remote SQL server connection in visual basic /VBA

bsjhala25-Sep-12 2:17

bsjhala

25-Sep-12 2:17

In a LAN i am having a database on SQL Server express edition.I want to fetch data from this server on some other machine in the same LAN.
I have already done setting in server for allowing remote connections and allowing the firewall setting. etc.

Can anyone tell me from scratch what I have to do on the client PC to fetch data on it in VBA.

Re: Remote SQL server connection in visual basic /VBA

ChandraRam26-Sep-12 3:04

ChandraRam

26-Sep-12 3:04

You can do that using ADO. Create an ADO connection to the remote server and use the ADO recordset objects to retrieve data.

Make sure you have included a reference to ADO (ActiveX Data Objects) in your VBA project.

HTH

wizy@202024-Sep-12 23:05

wizy@2020

24-Sep-12 23:05

in the project i want to build, i already have a school results system and students should be able to request their results via sms.thus by sending their stud. id to the system number(gsm modem number) and the system automatically reading the message, extracting the stud. id and year from the message search the results table for the results and send it back to the student who requested for the results(senders number) i will also store requests to the database

Re: in the project i want to build, i already have a school results system and students should be able to request their results via sms.thus by sending their stud. id to the system number(gsm modem number) and the system automatically reading the mes

Eddy Vluggen25-Sep-12 0:17

Eddy Vluggen

25-Sep-12 0:17

..cool - and what is your question?

If you're stuck with a particular task, then we can help. As is, you only mentioned that you're about to do a project. If you're looking for volunteers, you'd need to open-source it first. Or try the collaboration-forum Smile | :)

Bastard Programmer from Hell Suspicious | :suss:

if you can't read my code, try converting it here[^]

David Mujica25-Sep-12 4:17

David Mujica

25-Sep-12 4:17

You might want to store the cell phone numbers of your students in the db and the incoming message would just be RESULTS. They should not send their student ID. You want to prevents Student A from requesting results for Student B.

Dave Kreskowiak25-Sep-12 4:32