Thrix

Hi all,

I'll try to describe this as best I can. If it gets a little confusing, let me know and I'll try to rephrase everything I've said!

For the past few weeks I've been trying to get my head around VB2005EE. After reading a few books and tutorials I feel that I'm almost ready to go ahead and build my first "real" program to distribute. The program I have in mind should be nothing complicated, but I'm a little confused on how to build the key feature.

Without giving too much away, my idea is to build a large database on every ride, roller coaster and theme park in the world. Each entry will feature statistical information on the particular attraction, along with media (video/pictures), location etc. - this will mean I'll have to manually create thousands of entries, but I'm up to the challenge. At the program's start screen, the user will enter a keyword in a search field, hit enter, and immediately be taken to a results page with matching entries. If they find the attraction they were looking for, they'd click a link and be taken to a page with the attraction's information.

I initally thought this database would be easy to build, but I'm a little clueless on where to start. How would I go about properly building a searchable database I would like the entry information to be displayed something like this.

Again, if I'm being too vague or confusing on what I'm trying to achieve please let me know.

Thanks!


Re: Visual Basic Express Edition Tackling an issue

ahmedilyas

cool. Wish you the best of luck!

to perform a search on the database is not hard at all. You have many choices in terms of retrieving back the data. you can either use a DataReader to read data returned, row by row, then bind it to a bindible UI control, or an easier way would be to fill a DataSet with data using the DataAdapter. It's a simple "Fill" statement which fills the dataset with data. Then you bind that to a bindable UI control, such as a DataGridView to show your results. It's up to you on how you want to display it but the core functionality is pretty much just that. Take a look at this:

http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=753872&SiteID=1

the search feature is not so difficult. I hope the above does help you.

Basically you will have say a textbox to search for on a particular field you select. you will be using a LIKE clause in the statement, giving it the field to search on and what to search.

SELECT * FROM [TableName] WHERE [FieldName] LIKE

the is like a placeholder where it will be marking the parameter to use. The parameter will contain the data you want to search for and once created, is added to the Command object (Sql command or OleDbCommand, depending on which type you are using)

I hope that gives you a head start :-)






Re: Visual Basic Express Edition Tackling an issue

Thrix

Thanks for your reply!

I'm still having a little trouble understanding how the search feature would work. Say if the Table name is "RollerCoasters" and has 1000-odd entries. The name of each roller coaster is under "CoasterName", would the following work ...

SELECT * FROM [RollerCoasters] WHERE [CoasterName] Like

I've only just started playing around with the database side of things tonight, so I'm not too familiar with the terminology yet.

Before I go any further I just wanted to check if the way I'm building databases is correct. After creating a new database, I create new tables for each "major category" (Roller Coasters, Theme Parks, Water Rides, Flat Rides) - in these categories are a number of Column Names (if that's the correct term ). For example, in the Roller Coasters table I have...

  • Name
  • Height
  • Speed
  • Location
  • Pictures
  • Videos
The columns are then filled with the appropriate data.

Again, thanks for your help... I really appreciate it. Learning VB is something I've always wanted to do, and now that I have 6 week break from School I've finally got the opportunity to do so (and hopefully build this program!).




Re: Visual Basic Express Edition Tackling an issue

ahmedilyas

your doing very well :-) what you have is pretty much correct. lets talk about constrcting the query and executing it.

lets say we have a datagridview control, textbox, and a button on the form. Textbox should have the name of the coaster you want to search for. the button executes the results.....

private sub DoGetCoasterInfo(byval coasterSearchName as String)

Dim theDataSet as new DataSet() 'holds our results

Dim theCommand as new OleDbCommand("SELECT * FROM [RollerCoasters] WHERE [CoasterName] LIKE ", new OleDbConnection(ConnectionString))

Dim parameter as new OleDbParameter("@p1", "%" & coasterSearchName & "%")

theCommand.Parameters.Add(parameter)

Dim theDataAdapter as new OleDbDataAdapter(theCommand)

theCommand.Connection.Open()

theDataAdapter.Fill(theDataSet) 'executes our command and fills the dataset with results

theCommand.Connection.Close()

Me.theDataGridView.DataSource = theDataSet.Tables(0).DefaultView

end sub

and thats pretty much it. Basically when the button is pressed you call the function giving it the text they entered. It will then fetch the records that match similar to the name they entered, fills the dataset with data returned and binds it to a datagridview to show the results






Re: Visual Basic Express Edition Tackling an issue

Thrix

Thanks once again (I noticed your post count went up by 50 overnight - great stuff!)

Is the code you provided what I should use in the program I tried it, but when trying to run the program I had a few errors. As suggested by VB I added OleDb. infront of every OleDb, which removed the errors. See the following...

Private Sub DoGetCoasterInfo(ByVal coasterSearchName As String)

Dim theDataSet As New DataSet() 'holds our results

Dim theCommand As New OleDb.OleDbCommand("SELECT * FROM [RollerCoasters] WHERE [CoasterName] LIKE ", New OleDb.OleDbConnection(DBTEST))

Dim parameter As New OleDb.OleDbParameter("@p1", "%" & coasterSearchName & "%")

theCommand.Parameters.Add(parameter)



Dim theDataAdapter As New OleDb.OleDbDataAdapter(theCommand)

theCommand.Connection.Open()

theDataAdapter.Fill(theDataSet) 'executes our command and fills the dataset with results

theCommand.Connection.Close()



Me.DataGridView1.DataSource = theDataSet.Tables(0).DefaultView

As you can see I also changed ConnectionString to "DBTEST" (the name of my DB's connection string - I just assumed I had to do this ) However VB produces an error when I do this.

Thanks!




Re: Visual Basic Express Edition Tackling an issue

ahmedilyas

sure no worries. Can you tell us the error you get




Re: Visual Basic Express Edition Tackling an issue

Thrix

Using the code you provided, I get the following errors...

  1. Type 'OleDbCommand' is not defined.
  2. Type 'OleDbParameter' is not defined.
  3. Type 'OleDbDataAdapter' is not defined.
  4. 'theDataGridView' is not a member of 'WindowsApplication1.Form1'.
Thanks :)




Re: Visual Basic Express Edition Tackling an issue

ahmedilyas

import the System.Data.OleDb Namespace

also "theDataGridView" is suppose to be an example of binding data to, so drag a datagridview component and name it "theDataGridView"






Re: Visual Basic Express Edition Tackling an issue

Thrix

Here's what I have thus far. Importing the namespace got rid of the defining errors, however new errors are produced when actually putting the connection string in. See below...

Dim theCommand As New OleDb.OleDbCommand("SELECT * FROM [RollerCoasters] WHERE [CoasterName] LIKE ", New OleDb.OleDbConnection(Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True;User Instance=True))

Errors
  1. Comma, ')', or a valid expression continuation expected.
  2. Name 'theCommand' is not declared.
  3. Name 'theCommand' is not declared.
  4. Name 'theCommand' is not declared.
  5. Name 'theCommand' is not declared.
Am I using connection strings properly

Sorry to be such a pain!






Re: Visual Basic Express Edition Tackling an issue

ahmedilyas

you forgot to enclose the string in quotes ;-)

Dim theCommand As New OleDb.OleDbCommand("SELECT * FROM [RollerCoasters] WHERE [CoasterName] LIKE ", New OleDb.OleDbConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True;User Instance=True"))






Re: Visual Basic Express Edition Tackling an issue

Thrix

Fantastic! I now have an errorless code. :)

How would I then use this code to make it work with the "datagridview control, textbox and button on a form" example you provided earlier As far as I can tell, there is nothing in the code that calls the button & textbox. I've tried a few ways to get it to work but whatever I do seems to break it.

Cheers,
Liam




Re: Visual Basic Express Edition Tackling an issue

ahmedilyas

well the button is where you pretty much place the code to do the searching. The textbox is what is used to get the value from, which performs the search based on that value. When and if there is data in the database that matches, it will automatically show it, all from that function/method to perform the search, into the datagridview by binding it from the DataSet to the datagridview. Thats it! So overall...



private sub DoGetCoasterInfo(byval coasterSearchName as String)
 
   Dim theDataSet as new DataSet()
 
   Dim theCommand as new OleDbCommand("SELECT * FROM [RollerCoasters] WHERE [CoasterName] LIKE ", new OleDbConnection(ConnectionString))
   Dim parameter as new OleDbParameter("@p1", "%" & coasterSearchName & "%")
   theCommand.Parameters.Add(parameter)
  
   Dim theDataAdapter as new OleDbDataAdapter(theCommand)
   theCommand.Connection.Open()
   theDataAdapter.Fill(theDataSet)
   theCommand.Connection.Close()
  
   Me.theDataGridView.DataSource = theDataSet.Tables(0).DefaultView
end sub
 
private sub cmdSearch_Click(byval sender as object, byval e as EventArgs) handles cmdSearch.Click
 
   Me.DoGetCoasterInfo(Me.txtSearch.Text)
 
end sub 

 

 

 

when the button is pressed (cmdSearch) it will take the value from the textbox (txtSearch) and call the DoGetCoasterInfo method, which will fetch the results from the database and bind it, if there are any results, into the datagridview






Re: Visual Basic Express Edition Tackling an issue

Thrix

Wow, thank you. I'm almost there!

When typing in "Top Thrill Dragster" (which is a valid entry in the database) into txtSearch and clicking the button, the following line is highlighted in yellow...

theCommand As New OleDbCommand("SELECT * FROM [RollerCoasters] WHERE [CoasterName] LIKE ", New OleDbConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True;User Instance=True"))

An error is also produced...

ArgumentException was unhandled
An OLE DB Provider was not specified in the ConnectionString. An example would be, 'Provider=SQLOLEDB;'.

I (or Microsoft) really should give you a cookie or something. You reply so fast! :P






Re: Visual Basic Express Edition Tackling an issue

ahmedilyas

my apologies!

Change the OleDb classes to Sql classes. Import the System.Data.SqlClient namespace instead also to make this work. For some reason I thought you were working with MS Access but of course you are using SQLExpress. So pretty much change OleDb classes to Sql classes and all should be well.

classes....

SqlCommand

SqlConnection

SqlDataAdapter

SqlParameter

 

and that should be it. Alternatively, just as a guess, try this in the connectionstring before changing the classes to be used to Sql classes:

"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True;User Instance=True;Provider=SQLOLEDB;"

or

"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True;User Instance=True;Provider=" & Environment.MachineName

or

"Driver={SQL Native Client};Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True;User Instance=True"






Re: Visual Basic Express Edition Tackling an issue

Thrix

Thanks, that does fix the error. However another error is produced (sorry to keep doing this to you!)

When searching for "Top Thrill Dragster", the following line is highlighted in yellow...

theDataAdapter.Fill(theDataSet)

An error is also produced...

SqlException was unhandled
Incorrect syntax near ' '.