Yea I have a linked record showing in a child view. So from what I can gather the first query executes to fill the main record table, and then a second query populates the correct record in the grid view (and a few other text boxes on the form). It is working now - but is it worth the time to write it as it should be? It won't affect any of my datasource bindings by combing it into a single query will it?

Thanks for all the help guys. After reading what you have all posted and doing some more research I managed to solve the problem I was having. The basic query I was running was sufficient, it turned out the problem was in an automatically created linked query. By fixing that I have got ride of the errors I was getting.

Yea, from reading what you have said, I think my error may be in allowing a user to input a value into the query that is not a valid record (ie one that doesn't exist). So would the best approach be to run a query to test if input value is a record, then if true, proceed to the query which returns the record? The loop error is because of my attempting to sort a datagrid with linked data in it - if a null value is returned (ie no record), it gets caught in a loop and error because it can't sort a null record (along those lines). Thanks for all your help, even though I haven't fixed the problem I am picking up some valuable tips!

Yea that will fix up the error from no value being entered. But I'm struggling to understand and make it work if, say, an input of 100 is entered. If 100 isn't a valid ID of a record, it won't return anything. I'd like it to return 1 for ease of avoiding an error and possible loop error (which could also be put down to poor programming by me on the C# side of things). With that said, if 99 is entered and that is a valid record, then 99 would need to be returned by the query. Sorry for not understanding your explanation.

There are also a number of other columns for each row, I assume that I just populate the query with those separated by columns?

Yea I am using SQL Server. At the moment the query is designed to open file which the user requests (file_name). This works really well if the input matches a record in the database. The problem is when it doesn't match a stored record. Will using the query as above with ISNULL work? And if so, am I right in assuming that the query first executes the where part of the query, then the IFNULL portion and, if still valid after that, retrieves the correct record?

Hi all, Firstly, I must confess to being a very amateur programmer with a lot to learn! However, I'm battling away and am slowly getting my head around things. I have a query I have written which as follows:

SELECT File_Name
WHERE (File_Name = @File_Name1)

File_Name1 is a user input parameter. My problem is that, even if the record does not exist, it returns the record, resulting in an error on the client side. Is it possible to edit that query to return a file_name field value of say, 1, if the searched record doesn't exist? Any help appreciated! (Also for note most of my queries are generated by VStudio, so my understanding of them leaves a lot to be desired...)

Sorry about the delay in getting back to you all - have been out of the office and based in Australia. Code that executes with the query is as follows:

        try
        {
            //Run query to open file
            this.file\_InfoTableAdapter.fFile\_File\_Open(this.salesDataSet.File\_Info, ((int)(System.Convert.ChangeType(fFile\_Open\_File\_Text\_Box.Text, typeof(int)))));

            //Sort sales dgv to sort by date
            this.saleInformationDataGridView.Sort(this.saleInformationDataGridView.Columns\["dataGridViewTextBoxColumn9"\], ListSortDirection.Descending);

            //Set photo list box to display correct photo list
            photoListBox.Items.Clear();
            photoListBox.Items.Add(file\_NameTextBox.Text + "\_a.jpg");
            photoListBox.Items.Add(file\_NameTextBox.Text + "\_b.jpg");
            photoListBox.Items.Add(file\_NameTextBox.Text + "\_c.jpg");
            photoListBox.Items.Add(file\_NameTextBox.Text + "\_d.jpg");

            //Display first file photo if one exists
            string photoDisplay = "P:\\\\" + localityTextBox.Text + "\\\\" + file\_NameTextBox.Text + "\_a.jpg";
            this.photoBox.ImageLocation = photoDisplay;

            //Set property sale detail text boxes back to read only
            land\_use\_codeTextBox.ReadOnly = true;
            land\_areaTextBox.ReadOnly = true;
            construction\_yearTextBox.ReadOnly = true;
            room\_countTextBox.ReadOnly = true;
            wall\_construction\_codeTextBox.ReadOnly = true;
            roof\_construction\_codeTextBox.ReadOnly = true;
            building\_areaTextBox.ReadOnly = true;
        }
        catch (System.Exception ex)
        {
            System.Windows.Forms.MessageBox.Show(ex.Message);
        }

I have limited knowledge about avoiding exceptions, but it is my understanding that all the code underneath the running of the query is being run even if the query returns no result? Leading to the exception? But to be honest, I really have no idea... I do know that there is a lot I could improve in the code, but it is a very much "Learn as I go" approach... Which of course isn't the best, but I can only learn and get better! Joe

Hi Guys, I'm having trouble with an exception error after executing a query to return one record from my dataset. If the query returns a result that doesn't exist within the database, I am getting a 'Exception has been thrown by the target of an invocation'. Could someone please explain what this means and where I need to look to fix it and avoid an exception? Thanks, Joe

Sorry about the delayed reply - christmas / new year break over here in sunny Australia! I tried all of the things above, and get identical results when using R% as I do R_. I have found that when using R%% I do get all records, but I have since discovered this is only because the maximum length of a record is 3 characters. I get the same result if I use R%%%. However, when I use the wildcard part of the query on the Locality choice, it works as I would expect it too, and returns all results that start with, say, 'Hob', if I enter Hob%. The only difference I can think of between the two is that Locality is stored as 'NVARCHAR(50)' and Land_Use_Code as 'NCHAR(5)'. I would not have thought this would cause a problem, but I may be wrong! In the mean time I will work around it and add multiple %% signs to the parameter before the query is executed. Unfortunately I have tried to get my head around writing parametrized queries from scratch, but have not been able to find a good resource that explains it along with using it to then fill a DataGridView. Thanks for all your help though guys - has been great and I feel like I have learnt a lot!

It's sitting inside a datasource as a query (so similar to a stored procedure?) and then executed via a button and a table adapter in the programming language. This is the full query as it sits at the moment:

SELECT File_Name, PID, Address_1, Address_2, Locality, Municipality, Postcode, Sale_date, Sale_price, Total, Capital_value, Land_area, Land_use_code, Room_count, Building_area, Construction_year, Wall_construction_code, Roof_construction_code, Land, Rate_1, Val_Ref, Source FROM vSaleSearch
WHERE (Municipality = @Municiaplity1 OR Municipality = @Municipality2)
AND (Locality LIKE @Locality + '%' OR Locality = @Locality1 OR Locality = @Locality2)
AND (Land_use_code LIKE '%' + @Landusecode1 + '%')
AND (Sale_price >= @Sale_Price1 )
AND ( Sale_price <= @Sale_Price2)
AND (Sale_date >= @Sale_Date1)
ORDER BY Sale_price

I'm trying to achieve the same thing on the locality. What I'd ideally like is for a person to be able to enter a string in the locality field and it take it as an "equals" query. Or include a wild card at the start or end to take it as a like and apply the wild card characters. At the moment it achieves half of that, but not as user friendly as one might hope... I see what I want to happen all the time, but of course I'm not sure how exactly it is done...

From the look of it, the original

Land_use_code LIKE '%' + @Landusecode1 + '%

is the correct way. I might need to look into using combo boxes with equals|like in it to control the results a bit more, and code the query as necessary from those. Just a bit more than I wanted to have to do! But no doubt it will be good experience! Thanks for your help :-)

Thanks Luc, I am using SQL Server 2008. So with your C# example there, you are passing in the variable Landusecode1 to the query and then applying the wildcards aren't you. If that is how I need to approach it then I can manage to sort it out, I just thought there might be an easier way (as you said - possibly a multiple character wildcard). Joe

Hey all, I am trying to construct a basic search query and am having trouble with my wildcard searches. I am using C# Visual Studio 2008 and the dataset query designer. The part of the query I am struggling with is:

WHERE (Land_use_code LIKE '%' +@Landusecode1 + '%')

However, when I run the query, it doesn't perform as I would expect. There are multiple table entries, with some as follows: R1 R3 R30 If I enter R and run the query, it returns nothing. If I enter R1, it returns all that match R1. If I enter R%, it will return R1 and R3, but not R30. If I enter R%%, I will get all records. I am wondering of a way to enable me to enter R% and get all records returned. Thanks, Joe

Thanks for the reply Pete, I am fairly new to this whole thing - but am slowly getting my head around it. I'm retrieving the data via DataSet, with the text boxes bound to the dataset(however I have not bound them myself, just dragged and dropped in the designer). Currently the query I have is a simple designer one with 2 parameters for if ID = @ID1 or if ID = @ID2. Joe

Hey all, I am not sure whether this is possible and if I am approaching it the wrong way but... What I'd like to do is have a form that allows 3 separate records from a database to be shown in text boxes similar to: Record 1 Size Record 1 Rate | Record 2 Size Record 2 Rate | Record 3 Size Record 3 Rate | Record 1 Total | Record 2 Total | Record 3 Total | Could someone please push me in the right direction for achieving this? I have a query that returns either 1,2 or 3 records but can currently only get my form to display the same record in all 3 sets of text boxes. Any help would be appreciated! Joe

Thanks again! Great article - definitely helped me understand.

Thanks! I had some trouble making it work - but it all came down to simply having the tables on the wrong sides of the LEFT OUTER JOIN. Switched them around and it is return as I wanted! Cheers for the assistance.

Ahh thanks! That is exactly what I thought would happen... Back to the old drawing board!

Hey all, I am still fairly knew to SQL query writing, and have searched to try and solve this by myself, however have had no luck! I am trying to write a query that returns information from SaleInformation and File_Info, with SaleInformation including all records that match a query and File_Info returning the matching File_Name(I have a relationship between them with PID the primary key in File_Info) if the PID is a match. However, my query is returning a result in every File_Name row return, even though I only have 3 entries in the File_Info PID column that match only 3 records in the Sale_information table. A gentle push in the right direction would be greatly appreciated! Oh, on a side note - If I change the query to "ON File_Info.PID = SaleInformation.PID" I get the 3 records that match returned. Here is the query I have at the moment: USE [C:\DATABASE\SALES.MDF] GO SELECT File_Info.File_Name, SaleInformation.PID, SaleInformation.Sale_date, SaleInformation.Sale_price FROM File_Info LEFT OUTER JOIN SaleInformation ON SaleInformation.Sale_price > 150000 It returns 183 rows. There are only 94 rows in the SaleInformation table and 3 in the File_Info table. I am very confused! Regards, Joe