Software Development Help

RealistSpy

Language: Visual Basic 2010
IDE: Visual Studios Express 2010
Skills: Novice - Just finished second year Software Development
Database: Microsoft Access

Hi,

I hope someone can help me with this little problem. I am working on my own software nothing serious just somthing to improve my programming skills. The problem now is that I am using Microsoft Access as database.

Is it possible to combine the database to the project so that in the release folder I get a single .EXE which includes the database.

The other thing is that am storing the database in the default location. Is there a way for me to be able to track the database, if I take the .EXE + the databse to a different computer for someone to test the program. The reason is because I won't be including the Source code.

Evil Phil

You could install the mdb file (database) into the same location as the EXE by default, using an installer, that way you'll always know where the db is. I don't know if you can combine the DB with the EXE but I doubt it, I could be wrong though.

As part of the install you can setup a file or system DSN to connect to the DB file, that way your code will always find it. In Windows 7 you'll find the ODBC Data Sources applet under Control Panel\All Control Panel Items\Administrative Tools. How you do this depends on what you use to create the installer.

RealistSpy

Yeh that would work on my computer but what if someone else has the program? In that case I would be getting a database not found error. Even though the .accdb is in the same directory as the .exe

km991148

Do you really want to use a MS access db?

What sort of data are you storing? If it is something that is suitable for a db and it is is small enough, maybe consider SQLite, which will require less overhead when installing. If appropriate, maybe even use XML files.

RealistSpy

I only studied MS Access for database in college. I haven't really looked into any other storage systems. I am storing small data.

EG:
User - Name and Age
Weight and date
Workout, reps, sets and date
etc

km991148

well here is a quick and dirty guide for sqlite..

http://www.mikeduncan.com/sqlite-on-dotnet-in-3-mins/
or
http://brennydoogles.wordpress.com/2010/02/26/using-sqlite-with-csharp/

you should factor out your data access making switching from one backend to another a simpler process (I know it sounds overkill, but best to develop good habits early on).

RealistSpy

Thanks I will look into this SQLite thing.

Ginger

There is the option of using SQL Server compact edition for example http://www.microsoft.com/sqlserver/2008/en/us/compact.aspx

Fairly much you could design and do your work in either SQL Server Express edition or Access so that you can play with it. Change your connection string in the config and bobs your uncle. It can be installed as part of the installer and is dairly much hassle free

RealistSpy

Hi all

Still having the database problem but slightly different.

I am now using mysql server + mysql admin. I found to be way easier to use. I have also managed to connect my program to the database on the server.

The problem is that only my computer can connect to the database. I would like to use the program on other computers on the LAN to connect to the database aswell. Later I hope anyone can use the program to connect to the database over the internet (remote users).

If anyone one has any experience with this how do I do this? I heared I need apache? or can I use my main ip address(given by isp).

Thanks in advance.

lynchie

By default, the root user is created allowing accessing from localhost ONLY. You need to allow any hosts to connect with that user name. Best bet is to install the MySQL Workbench on the pc with mysql on it and change the user you are connecting with to allow all hosts connect to it.

GreeBo

For something like this, that you want to be portable to other computers Id use an XML data file as mentioned above.
DB sounds like overkill here.

You could get exciting and create a DAO type interface.
That way you create XML and SQL DAO adapters to your data source and you can switch between the two (maybe allow the user to choose at startup or something?)

Enjoy.

RealistSpy

lynchie wrote: »

By default, the root user is created allowing accessing from localhost ONLY. You need to allow any hosts to connect with that user name. Best bet is to install the MySQL Workbench on the pc with mysql on it and change the user you are connecting with to allow all hosts connect to it.

How do you do this. I have workbench already installed aswell.

lynchie

Go into the server administration / user account section and change the root user or whatever user you are using and change its host from localhost to %

RealistSpy

lynchie wrote: »

Go into the server administration / user account section and change the root user or whatever user you are using and change its host from localhost to %

Thanks.

The problems were:
----I never included the mysql.data.dll with the release application .exe which worked well without it on my pc.
----My connection code was wrong. I was connecting to the server which was on my pc and it worked fine. But from other LAN pc I had to use my Pc's IP address which then enabled any LAN pc to connect to the mysql server.

Now I want to progress this to the next level.

How do I let another computer connect to the server without being on the LAN. I tried using my WAN address but that didn't work. It didn't work because (WAN IP ----> LAN IP

---

> server) well I think thats the problem.

RealistSpy

Imports MySql.Data.MySqlClient

Public Class LoginForm1
    Dim MySqlConnection As MySqlConnection

    Private Sub OK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles OK.Click

        'For testing which connections work
        Dim i As Integer

        For i = 1 To 3
            MySqlConnection = New MySqlConnection()

            If i = 1 Then
                'doesn't work
                MySqlConnection.ConnectionString = "server=WAN IP; user id=root; password=; database=beta; port=PORT"
            Else
                MySqlConnection.ConnectionString = "server=192.168.1.10; user id=root; password=; database=beta; port=3306"
            End If

            Try
                MySqlConnection.Open()
                MessageBox.Show("Connection to Database has been opened")
                i = 3
            Catch myerror As MySqlException
                MessageBox.Show("Cannot connect to database: " & myerror.Message)
            End Try

        Next i
        





        Dim myAdaptor As New MySqlDataAdapter
        Dim sqlQuery = "SELECT * FROM users WHERE username = '" & UsernameTextBox.Text & "' AND password = '" & PasswordTextBox.Text & "';"
        Dim command As New MySqlCommand

        command.Connection = MySqlConnection
        command.CommandText = sqlQuery
        myAdaptor.SelectCommand = command

        Dim myData As MySqlDataReader
        myData = command.ExecuteReader()

        If myData.HasRows = 0 Then
            MsgBox("Invalid User: " & UsernameTextBox.Text)
        Else
            MsgBox("Database has been opened: " & UsernameTextBox.Text)
        End If
        Me.Close()
    End Sub

NOTE: This is just quick work!

lynchie

You have enabled port forwarding from your wan ip to the lan ip for the DB server? Assuming you want any ip outside of 192.168.* to access your server, then add a port forward for port 3306 (tcp) on your router to redirect to port 3306 on ip 192.168.1.10

RealistSpy

Thanks I love you, everything works fine now. I was able to access the database from outside my LAN and inside aswell.

I can take it that this connection is not secure? But thanks very much I can get started with the full program.

lynchie

Opening the port allows anybody to access it. Its not secure at all. Its grand for testing your application, but in a production scenario the DB would be behind a firewall with local access only.

RealistSpy

Yeh I will use it for testing I might look at ssl in the future. But thanks a lot.

RealistSpy

I have my program done. I am now working on a java Program. Does anyone wants to take a look at the source code and see how clean it is?