select certain set of data from database

Visual Basic Language

kidwizz

I want to be able to select what seasons the randomizer is picking from. (where you select them is on another form). Keeping in mind that I've only been doing VB in school for 1 term now.

Code Snippet

Imports System.Data
Imports System.drawing.Size
Public Class frmMain
Inherits System.Windows.Forms.Form

Dim con As New OleDb.OleDbConnection
Dim ds As New DataSet
Dim da As OleDb.OleDbDataAdapter
Dim sql As String
Dim Num As Integer



Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = Episodes.mdb" 'select database

con.Open() ' open database
Dim test As String = "1"

sql = "SELECT season FROM Episodes" ' set sql
da = New OleDb.OleDbDataAdapter(sql, con)

da.Fill(ds, "episodes")


End Sub

Private Sub btnPick_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPick.Click
Dim maxrows As Integer = ds.Tables("episodes").Rows.Count
Dim random As New System.Random() ' Create random number
Num = random.Next(1, maxrows).ToString ' Create random number

lblEp.Text = ds.Tables("episodes").Rows(Num).Item(0)
lblName.Text = ds.Tables("episodes").Rows(Num).Item(1)
lblSeason.Text = ds.Tables("episodes").Rows(Num).Item(2)
lblDiscNum.Text = ds.Tables("episodes").Rows(Num).Item(3)
lblDiscEp.Text = ds.Tables("episodes").Rows(Num).Item(4)
End Sub
End Class


thanks
Top

Re: Visual Basic Language select certain set of data from database

Muhammad Akhtar Shiekh

Hi Dear,

Whats the problem u face

I think you must use it when genrating a rondom number........(bcoz Rows index start with 0 and therefore total indexes must be maxrows -1 )

Code Snippet
Num = random.Next(0, maxrows-1).ToString ' Create random number


if you are facing problem due to some other reason then please explain

Thanks

Regards

Muhammad AKhtar Shiekh





Top

Re: Visual Basic Language select certain set of data from database

kidwizz

yeah at the moment it picks a random number for the episode number. but i want to be able to select what seasons it chooses from.

so like it picks a random episode and then checks it if belongs to a season that has been selected say seasons 4 and 8.

and the thing is if i add a

Code Snippet

WHERE season = 4


to the sql = part then when i run the program it errors.

i could zip the folder and post it on here.


Top

Re: Visual Basic Language select certain set of data from database

Muhammad Akhtar Shiekh

Hi Dear,

Really can't understand ur problem.... can u please explain what is a 'season'.......and 'episode' ....it will be better if you write your table structure....

Thanks

Regards,

Muhammad Akhtar SHiekh





Top

Re: Visual Basic Language select certain set of data from database

PEng1

What error do you get when you change the select statement Why can't you use parameters Then you could use the random number in you select statement, and only return records that reference episodes in seasons 4 and 8, or whatever.

Also, as a side note you should always use a Try..Catch block and Using block when connecting to your database.

Code Snippet

Using Conn

Try

conn.open()

'data operations

Catch ex As Exception

MessageBox(ex.message)

Finally

conn.close()

End Try

End Using





Top

Re: Visual Basic Language select certain set of data from database

kidwizz

I uploaded my project as a zip file (http://kidwizz.quotaless.com/WindowsApplication1.zip) so everyone can see what is what.


Top

Re: Visual Basic Language select certain set of data from database

kidwizz

I forgot to say this but at

Code Snippet

da.Fill(ds, "episodes")


I get the error 'Data type mismatch in criteria expression.' after i add WHERE season = 1 to the sql.


Top

Re: Visual Basic Language select certain set of data from database

kidwizz

Anyone ive been trying myself and i just cannot figure it out...


Top

Re: Visual Basic Language select certain set of data from database

PEng1

Sorry, I am not allowed to down load your code, so I cant really say what the problem is without seeing a more complete code snippet. It sounds like from your error that the SQL statement is expecting a string not a number. So maybe WHERE season = "1" Other than that I don't really know I would have to see some more code. Again, sorry I can't do more.



Top

Re: Visual Basic Language select certain set of data from database

kidwizz

[user="PEng1"]Sorry, I am not allowed to down load your code, so I cant really say what the problem is without seeing a more complete code snippet. It sounds like from your error that the SQL statement is expecting a string not a number. So maybe WHERE season = "1" Other than that I don't really know I would have to see some more code. Again, sorry I can't do more.


here is all the code from that form

Code Snippet

Imports System.Data
Imports System.drawing.Size
Imports System.IO

Public Class frmMain
Inherits System.Windows.Forms.Form

Dim con As New OleDb.OleDbConnection
Dim ds As New DataSet
Dim da As OleDb.OleDbDataAdapter
Dim sql As String
Dim Num As Integer
Dim strFileName As String = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) & "\" & "Episodes.mdb"

Private Sub frmMain_FormClosed(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosedEventArgs) Handles Me.FormClosed
con.Close()
System.IO.File.Delete(strFileName)
End Sub




Private Sub frmMain_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim objStream As Stream
Dim objStreamOutput As FileStream


If Not File.Exists(strFileName) Then
objStream = System.Reflection.Assembly.GetExecutingAssembly.GetManifestResourceStream("Episodes.mdb")
objStreamOutput = New FileStream(strFileName, FileMode.CreateNew)

Do While objStream.Position < objStream.Length
objStreamOutput.WriteByte(CByte(objStream.ReadByte))
Loop

objStreamOutput.Close()
objStream.Close()

End If
con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source =" & Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) & "\" & "Episodes.mdb" 'select database
con.Open() ' open database

sql = "SELECT * FROM Episodes WHERE seasons = 1 " ' set sql
da = New OleDb.OleDbDataAdapter(sql, con)

da.Fill(ds, "episodes")


End Sub

Private Sub btnPick_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPick.Click
Dim maxrows As Integer = ds.Tables("episodes").Rows.Count
Dim random As New System.Random() ' Create random number
Num = random.Next(1, maxrows).ToString ' Create random number

lblEp.Text = ds.Tables("episodes").Rows(Num).Item(0)
lblName.Text = ds.Tables("episodes").Rows(Num).Item(1)
lblSeason.Text = ds.Tables("episodes").Rows(Num).Item(2)
lblDiscNum.Text = ds.Tables("episodes").Rows(Num).Item(3)
lblDiscEp.Text = ds.Tables("episodes").Rows(Num).Item(4)
End Sub

Private Sub ExitToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ExitToolStripMenuItem.Click
Me.Close()
End Sub

Private Sub mnuSeason_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuSeason.Click
Dim frmseasons As New frmSeason
If frmseasons.ShowDialog() = DialogResult.OK Then
End If
End Sub

End Class


Top

Re: Visual Basic Language select certain set of data from database

PEng1

Well I see a couple of problems, First Like I mentioned before you should encapsulate any connection to your data base in a Using and Try..Catch..Finally block. Second, you should only open the connection when you need it open, ADO.NET is designed to function with short term Transaction type connections. Unfortunately that dosen't answer your question, and so regretably I must ask for more information, Could I see your DataBase Schema (Tables,Column Names, and DataTypes) this way I could possibly determine the error in your SQL. Also, is the Name of your Table "season" or "Season", that may make a difference. Sorry I can't help more at this time.

You might try this as your Select Statement and see what happens, parrameters should always be a string I believe this is what is causeing the error.

Instead of

sql = "SELECT * FROM episodes WHERE season = 1"

Try

sql = "SELECT * FROM episodes WHERE season = '1'"

Then if that works I would look into usind a parameter then your sql would look like this

sql = "SELECT * FROM episodes WHERE season = @season"





Top

Re: Visual Basic Language select certain set of data from database

kidwizz

Ok. can you please put the try catch thing in for me i'm new to VB and still learning.

OK thanks!

Code Snippet

sql = "SELECT * FROM episodes WHERE season = '1'"


worked!

now i've just got to implement that into my second from and then bring the data from the second from to the main for and get it to work... Tongue Tied


Top

Re: Visual Basic Language select certain set of data from database

PEng1

No problem.

Code Snippet

Try

con.Open() ' open database

sql = "SELECT * FROM Episodes WHERE seasons = '1'"' set sql
da = New OleDb.OleDbDataAdapter(sql, con)

da.Fill(ds, "episodes")

Catch ex As Exception

MessageBox(ex.Message)

Finally

con.Close() ' close database

End Try

Any time that you need to connect to your database you should use this format, at least you may also elect to use the Using block but that is not entierly neccisary in your case, I would still recomend that you look into it so that you understand what it provides you. The Try block enables you to better handle exceptions that occur while your conncetion is open. So the app trys to run the code in the Try section, if there is a Exception thrown then code execution jumps to the appropriate Catch section, there can be multible Catches, they should be listed from most specific to least specific, in this case I have only included one, but you should do some research to find out what types of exceptions you should expect to have to handle. Then no matter if the Try section executes completly or there is an exception thrown the Finally section will execute, insuring that no matter what the connection is always closed. Try..Catch blocks are extreamly useful when you need to comit or rollback a transaction. For instance in and exception is thrown then you would want to rollback any changes in the transaction, but if not then you would want to commit them. Hope that this helps.





Top

Re: Visual Basic Language select certain set of data from database

kidwizz

OK thanks, but whats
Code Snippet

MessageBox(ex.Message)



VB says that it can't be used in an expression.


Top

Re: Visual Basic Language select certain set of data from database

kidwizz

I have another problem Sad...

Code Snippet

sql = "SELECT * FROM Episodes WHERE Season = '1'"



works, it picks episodes only from season 1. BUT when i use

Code Snippet

sql = "SELECT * FROM Episodes WHERE Season = '1' AND '2'"



It only selects episodes from season 1. How do i get it to select episodes from season 1 and 2 (or others)

heres something that could help what happens on the button click.

Code Snippet

Private Sub btnPick_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPick.Click
Dim maxrows As Integer = ds.Tables("episodes").Rows.Count
Dim random As New System.Random() ' Create random number
Num = random.Next(1, maxrows).ToString ' Create random number

lblEp.Text = ds.Tables("episodes").Rows(Num).Item(0)
lblName.Text = ds.Tables("episodes").Rows(Num).Item(1)
lblSeason.Text = ds.Tables("episodes").Rows(Num).Item(2)
lblDiscNum.Text = ds.Tables("episodes").Rows(Num).Item(3)
lblDiscEp.Text = ds.Tables("episodes").Rows(Num).Item(4)
End Sub


Top