SQL Database

[Maffegozer]

Hello,

I'm working on a Emergency Dispatch Center Game.

But I've come across a problem.

In my program you get a messagebox saying that someone called 911/112.

You pick up the call and ask him/her what is going on etc.

The usual.

But in order to let that work.

I need to put all the 'strings' of text that will be said into a database.

The problem is: I don't know how Databases work in VB.

So can somebody help me with this problem?

The one that solved the problem will get his name in the readme/credits and maybe can be a beta-tester

Regards,

Maffegozer.

[Guest Francis]

Databases are pretty easy in Visual Basic. Do you work in .NET or not? After you answer, I'll give you an easy example.

[Maffegozer]

Databases are pretty easy in Visual Basic. Do you work in .NET or not? After you answer, I'll give you an easy example.

The normal VB. No VB.net

If it helps: Visual Studio 2010 Proffessional

[Guest Francis]

Btw, you shouldn't be able to script with that if you don't have the money, so you have downloaded the program illegally.

Anyways,

That's VB.Net lol.

First, create a new file into your project, it's a .mdf database.

Second, create the schema of it using the Server Explorer.

Third, you must learn the way MySQL works to be able to query it.

Fourth, to query the database, you need this whole bit of code for SELECT:

        	Dim conn As New SqlConnection(LUMART.My.Settings.ProjectsConnectionString.ToString())
        	Dim rdrQuery As String = "SELECT StartTime, EndTime, Duration, Note, ShowNote FROM WorkedHours WHERE ProjectID = " & ProjectID
        	Try
            	conn.Open()
            	Dim cmd As New SqlClient.SqlCommand(rdrQuery, conn)
            	Dim rdr As SqlClient.SqlDataReader = cmd.ExecuteReader()
            	MainPage.EmptyLabel.Visible = Not rdr.HasRows()
            	Dim Note As String = ""
            	While rdr.Read()
                	Note = rdr.GetString(3) ' The 3 here is the 3 index ( that starts with 0 of course ) from the SELECT statement, so it's "Note".
                	Note = Replace(Note, vbCrLf, "<br />")
                	If rdr.GetInt32(4) = 0 Then
                    	Note = "<i>Aucune note.</i>"
                	End If
                	Content = Content & vbCrLf & "  	<tr>"
                	Content = Content & vbCrLf & "    	<td>" & rdr.GetDateTime(0) & "</td>"
                	Content = Content & vbCrLf & "    	<td>" & rdr.GetDateTime(1) & "</td>"
                	Content = Content & vbCrLf & "    	<td>" & SecondsToText(rdr.GetInt64(2)) & "</td>"
                	Content = Content & vbCrLf & "    	<td>" & Note & "</td>"
                	Content = Content & vbCrLf & "  	</tr>"
            	End While
        	Catch ex As Exception
            	MessageBox.Show(ex.ToString)
            	Return False
        	Finally
            	conn.Close()
        	End Try

[Maffegozer]

Btw, you shouldn't be able to script with that if you don't have the money, so you have downloaded the program illegally.

Anyways,

That's VB.Net lol.

Yep, I did.

Second part: Me failing again

First, create a new file into your project, it's a .mdf database.

Second, create the schema of it using the Server Explorer.

Third, you must learn the way MySQL works to be able to query it.

Fourth, to query the database, you need this whole bit of code for SELECT:

        	Dim conn As New SqlConnection(LUMART.My.Settings.ProjectsConnectionString.ToString())
        	Dim rdrQuery As String = "SELECT StartTime, EndTime, Duration, Note, ShowNote FROM WorkedHours WHERE ProjectID = " & ProjectID
        	Try
            	conn.Open()
            	Dim cmd As New SqlClient.SqlCommand(rdrQuery, conn)
            	Dim rdr As SqlClient.SqlDataReader = cmd.ExecuteReader()
            	MainPage.EmptyLabel.Visible = Not rdr.HasRows()
            	Dim Note As String = ""
            	While rdr.Read()
                	Note = rdr.GetString(3) ' The 3 here is the 3 index ( that starts with 0 of course ) from the SELECT statement, so it's "Note".
                	Note = Replace(Note, vbCrLf, "<br />")
                	If rdr.GetInt32(4) = 0 Then
                    	Note = "<i>Aucune note.</i>"
                	End If
                	Content = Content & vbCrLf & "  	<tr>"
                	Content = Content & vbCrLf & "    	<td>" & rdr.GetDateTime(0) & "</td>"
                	Content = Content & vbCrLf & "    	<td>" & rdr.GetDateTime(1) & "</td>"
                	Content = Content & vbCrLf & "    	<td>" & SecondsToText(rdr.GetInt64(2)) & "</td>"
                	Content = Content & vbCrLf & "    	<td>" & Note & "</td>"
                	Content = Content & vbCrLf & "  	</tr>"
            	End While
        	Catch ex As Exception
            	MessageBox.Show(ex.ToString)
            	Return False
        	Finally
            	conn.Close()
        	End Try

Thankyou so much!

Gonna try it right now.

I'll let you know if it works

Regards,

Niels. (Maffegozer)

[Guest Francis]

Alright, I'll wait for your news.

[Maffegozer]

Have had some problems putting the code in my program, but I think I can figure it out

Thanks!

[Maffegozer]

Have had some problems putting the code in my program, but I think I can figure it out

Thanks!

Sorry for the doublepost.

I've been trying things with this code but i get alot of errors.

Like things saying i haven't declared: Content

In total I had like 24 errors.

Francis?

[Guest Francis]

As I said, you must adapt the code for yourself.

[Maffegozer]

As I said, you must adapt the code for yourself.

So I need to Dim content As String

I guess?

[Guest Francis]

You probably don't need Content, that was part of my script.

[Maffegozer]

You probably don't need Content, that was part of my script.

Ok, So i've been trying things out and a bit of googling.

And came with this in my formload:

	Private Sub telefoon_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    	'Formule: CInt(Int((3 * Rnd()) + 1)) ' This was just a formula that im going to use later on. But I didn't want to forget it so...
    	Dim conn As New SqlConnection("Database=telefoon; Data Source=localhost")
    	Dim query As String = "SELECT id FROM Event"
    	Dim Resultaat As String = ""
    	Dim MyCommando As New SqlCommand(query, conn)
    	Dim Reader As SqlDataReader

    	Try
        	conn.Open()
        	Reader = MyCommando.ExecuteReader

        	While Reader.Read
            	Resultaat += Reader.GetValue(0).ToString + " " + Reader.GetString(1) + " " + Reader.GetInt32(2).ToString
        	End While

        	Reader.Close()
        	Label1.Text = Resultaat

    	Catch x As SqlException
        	MessageBox.Show(x.Message, "Fout", MessageBoxButtons.OK, MessageBoxIcon.Error)
    	Finally
        	conn.Close()
    	End Try

	End Sub

Some information:

My database is called: telefoon

I've got the label1 on my form.

I've got the table id and stuff..

No errors.

What happens?

Well I press the button which leads to this form.

And the form doesn't open, and my program freezes...

[Guest Francis]

I'll take a look tonight.

EDIT: You are only selected "id" from the database, and you want the program to tell you three values, it fails.

[Maffegozer]

I'll take a look tonight.

EDIT: You are only selected "id" from the database, and you want the program to tell you three values, it fails.

Hmmm, Now that you say that!

Trying out with 1 value.

-EDIT-

Tried it out.

like this:

Resultaat += Reader.GetValue(0).ToString

Instead of

Resultaat += Reader.GetValue(0).ToString + " " + Reader.GetString(1) + " " + Reader.GetInt32(2).ToString

Same problem. won't load. program freezes.

Thanks that you've helped me so far.

[Guest Francis]

I suppose "id" is a int type. Use GetInt32(0) instead of GetValue.

[Maffegozer]

Long time since respond.

Was a little busy.

I now got an error:

A network-related or instance-specific error occured while establishing a connection to SQL Server. The Server was not found or was not accessible. Verrify that the instance name is correct and that SQL server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - could not open a connection to SQL Server)

Obviously I need to reconfigure the SQL server.

Any tips of what program to use?

-Microsoft SQL Server (expensive)

- ??

[SirMoo]

Long time since respond.

Was a little busy.

I now got an error:

Obviously I need to reconfigure the SQL server.

Any tips of what program to use?

-Microsoft SQL Server (expensive)

- ??

If it's for a game you may want something like this: http://www.sqlite.org/

I'm not 100% sure if it's compatible for VB programs, however it's a self contained variation for of SQL which would be better than having to ship out a game stuffed with MySQL, which is the leading alternative to MS Sql.

[Maffegozer]

If it's for a game you may want something like this: http://www.sqlite.org/

I'm not 100% sure if it's compatible for VB programs, however it's a self contained variation for of SQL which would be better than having to ship out a game stuffed with MySQL, which is the leading alternative to MS Sql.

I'll have a look

Thanks!

[SirMoo]

How goes it with the database?

[Maffegozer]

How goes it with the database?

Well good news.

Today I downloaded SQL Server 2008 trial.

And got a server running now which i am connected to.

So now I can start working on my database

[Conroy]

Dim conn As New SqlConnection("Database=telefoon; Data Source=localhost")

Does your database require a username and password?

Dim conn As New SqlConnection("Database=telefoon; Data Source=localhost; User Id=Username; Password=Password")

[Maffegozer]

Does your database require a username and password?

Dim conn As New SqlConnection("Database=telefoon; Data Source=localhost; User Id=Username; Password=Password")

Sorry for the late response.

Nope it doesn't as i'm running it on my own pc (for now)

initial catalog and integrated security or something xd

Going well now.

Just trying stuff out and getting familiar with databases.