[PieMasterPete]

I don't normally post for help but considering this little project has already annoyed me several times and searching for help on Google so far has proven very useless I'm struggling for another option.

Basically I'm working on an easy to use front end for a database (currently Access but maybe being changed to MySQL or something in the future and if that happens I can deal with it quite easily) and upon taking the advice of Sirkent, who got said advice from somebody else (I forget who, my apologes), I now have the front-end working to the point that you can log in to the interface as certain departments and when you do it displays in a DataViewGrid a listing of all the information stored on each "asset" that that department has. Highlighting one item on the grid and clicking "edit" brings up a different screen using textboxes etc to limit what they can put in to each field, with validation on all the fields to stop people from misentering information.

The problem I have is that the ICT department has hundreds of records and they want a search option so that they can just type in a particular room number or supplier name etc and they want a list of all the computers in that room or supplied by that company, make sense? (I'm doing this for a school as an unpaid work placement so feel bad for me having to work 8:30 to 4:30 every day for six weeks, lmao).

Only problem is I can't figure out how to apply a filter to the records being displayed. Logically I thought there must be SOMETHING somewhere that would allow me to enter the SQL that I want and it'd just pull up the results that matched (for example SELECT * FROM assets WHERE assetID = "PCM25002"), and then I can have people type in "PCM25002" in to a textbox named something like "search criteria" and choose the particular field in the WHERE clause from a dropdown menu. Make sense? Only I can't figure out if adding in SQL to filter it is even possible!!!!

I've looked at most of the options for the DataGridView and all I've found so far is the "Add A Query" bit inside table adapter but all that does is create one specific menu toolbar to work on pre-typed query, it doesn't seem that you can generate a different query dynamically whenever you want.

PLEASE somebody tell me there's a simple solution for this because it's driving me nuts.

- Pete

[Karl Buckland]

The chap who provided you advice previously, via myself, was Catalyst aka Jeff. Unfortunately I have no clue, so now I bow out of the topic...

[Catalyst]

I'm guessing that you've bound your datagridview to something like a sqldataadapter. It's the sqldataadapter that you'd want to modify to do your search, not the datagridview itself.

[PieMasterPete]

Catalyst, on Apr 24 2008, 06:47 PM, said:

I'm guessing that you've bound your datagridview to something like a sqldataadapter. It's the sqldataadapter that you'd want to modify to do your search, not the datagridview itself.

Yeah, I just imported the table using the wizard. Any clue how to modify it?

I'll try playing with it a bit more, or just Googing the adapter itself to see if I can make that work. I know how to limit the COLUMNS that show up in it but not the rows.

[Catalyst]

You might check out some of the videos here for the basics http://msdn2.microso...c/bb466226.aspx

[PieMasterPete]

Catalyst, on Apr 25 2008, 08:53 AM, said:

You might check out some of the videos here for the basics http://msdn2.microso...c/bb466226.aspx

Thanks!!

Hopefully one of these will hold the answer to my problems, and hopefully I'll learn a butt-load more new stuff along the way. If not I may be returning later on today with a sad face and puppy dog eyes asking for more help.

[PieMasterPete]

Ok, so let me attempt to better explain what I'm doing and trying to do, because this has just wasted pretty much my whole day!!!

The database file (asset_register.mdb or whatever the format of Access is) was connected to VB.NET by the wizard and in the Data Sources tab I have "asset_SearchDataSet" listed, and when clicking the little plus I come to "assets", which is the only table in there that I currently give a damn about.

Dragging and dropping that on to the form gives me a groovy DataGridView with three connection bits listed at the bottom:

Asset_SearchDataSet
AssetsBindingSource
AssetsTableAdapter

Double clicking the Asset_SearchDataSet lets me see all the fields listed along with the AssetsTableAdapter and it's default listing of "Fill,GetData()" which is used to populate the DataGridView.

One of the options on the side (my brain is totally fried,I can't think what they're called) is SelectCommand, in which the CommandText is the SQL query to display the data, right? Only I can't find any way of editting that!!!! If I could figure out how to get that far I could quite happily mess around with the code until it worked, but nooooo.

The closest thing I have as an option is: assestsTableAdapter.GetData.Select(), in which if I put an SQL query it constantly returns the same error:

"Missing operand after *ROW* operator".

Last example I tried was: "assestsTableAdapter.GetData.Select("SELECT AssetID FROM assets")" and I got the error "Missing operand after AssetID operator".

This is seriously driving me to the brink of insanity. On one of the videos it showed you how to create a very crap looking search box using the code "@ColumnName + '%'" and I tried the same thing but it refused to recognise the @ (they were using an MsSQL database and I'm using an Access one, I thought maybe the commands would be the same but I should have figured it's Microsoft...), so all I could do was manually create a different search for every possible search option they might want to do, and that basically means a new little box to click for EVERY assetID, supplier name and god knows what else. Basically it'd be a nice little box on one side of the screen with several THOUSAND different buttons listed on the opposite side, which is just stupid.

So yeah, there IS SQL in this bloody thing, there absolutely HAS to be a way to do this, I refuse to believe that Microsoft don't let you edit it, just apparently they refuse to let you know how...

PLEASE SOMEBODY HELP!! This is actually going to drive me insane...

I am actually a University student, honest! *cries*

[Catalyst]

Access isn't great, to send you onto another learning tangent you might think about looking at SQL 2005 Express instead. It'll take you much farther.

The @ is used to indicate a SQL parameter, in which case the SELECT query would need to have that parameter in it.

You might check out http://www.c-sharpcorner.com/UploadFile/yo...174917PM/1.aspx
or
http://msdn2.microso...y/aa480727.aspx

I should mention that I don't really do .net forms development, but mostly asp.net web development. There's a lot of similarities but some of the specifics I just don't know so that's why I can only send you links that look like they'd be helpful.

What you're trying to do shouldn't be difficult at all with some basic knowledge of .net win forms, in fact the problem is most likely that there's a dozen different ways to do it. MS likes to give people as many options as they can so that their tools can be used flexibly. I'd highly recommend picking up a book on the basics which should get you doing much more difficult things very quickly with an understanding of what's going on behind the controls.

Maybe something like http://www.amazon.com/Beginning-VB-NET-Dat...2359&sr=8-5 or any similar book

The stuff you find on the web like the links I've been giving might help here and there, but if you have to change much from their examples then you're likely to run into trouble.

Edit: This link looks promising http://www.developer...o.uk/show/4286/

[PieMasterPete]

Many thanks for all your help dude but I figured a way around it. I manually connected to the database entirely in code thanks to code that my lecturer sent me and then played with the controls on the DataGridView until I figured out how to load data directly in to it at runtime.

The code solution, in case anyone is interested, is something like this...

Imports System.Data

Public Class searchResult
	Private ds As New DataSet
	Private sql As String = "SELECT * FROM assets WHERE AssetID LIKE '00002'"
	Private con As New OleDb.OleDbConnection
	Private da As New OleDb.OleDbDataAdapter(sql, con)

	Private Sub assetinfo_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
		loadData()
	End Sub

	Private Sub loadData()
		con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\asset_register.mdb"
		con.Open()

		da.Fill(ds, "AssetRegister")
		maxRows = ds.Tables("AssetRegister").Rows.Count

		NavigateRecords()

		con.Close()
	End Sub

	Private Sub NavigateRecords()
		Dim nullObject As Object
		Dim fixedValue As String

		nullObject = ds.Tables("AssetRegister").Rows(inc).Item(0)
		If IsDBNull(nullObject) = True Then
			fixedValue = FixNull(nullObject)
			txtAssetID.Text = fixedValue
		Else
			txtAssetID.Text = CStr(nullObject)
		End If

		AssetsDataGridView.DataSource = ds.Tables(0)
		AssetsDataGridView.Refresh()
	End Sub

	Public Function IsDBNull(ByVal dbvalue) As Boolean
		Return dbvalue Is DBNull.Value
	End Function

	Public Function FixNull(ByVal dbvalue) As String
		If dbvalue Is DBNull.Value Then
			Return ""
		Else
			'NOTE: This will cast value to string if
			'it isn't a string.

			Return dbvalue.ToString
		End If
	End Function

	Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
		Dim searchVal As String

		searchVal = txtTest.Text

		sql = "SELECT * FROM assets WHERE AssetID LIKE '" & searchVal & "'"
		ds = New DataSet
		con = New OleDb.OleDbConnection
		da = New OleDb.OleDbDataAdapter(sql, con)
		loadData()
	End Sub
End Class

Thanks again for all the help!!

There we go, now I'm all happy...