Data retrieval

westannex · April 5th, 2009, 12:39 AM

Hello,

I'm trying to use LINQ or SQL to retrieve data from a file and populate the screen. Every example I've seen for LINQ ends by binding the results into a datasource control. I just want to have the data available to do whatever I want - in this case, to fill some screen labels.

This i what I have:

using (Test1DataContext myDataContext = new Test1DataContext())
{
var allUsers = from xx in myDataContext.FILE1
where xx.FIELD1 == "ABC123"
select xx.FIELD2;
}

Then I want to put FIELD2 into Label1.Text.

Where am I going wrong?

I also tried to do this with SQL. I know SQL syntax but where do I type it in??? The code behind file wont accecpt it.

Thanks.

Lee Dumond · April 5th, 2009, 02:46 AM

I guess I'm not getting you. LINQ to SQL is not magic. You return the value like you would from any method, then you call ToString on it (if it isn't already a string) and assign it to the Text property of the label.

Maybe you can provide a more complete explanation of what you're attempting, because this seems pretty straightforward to me.

Imar · April 5th, 2009, 04:22 AM

Hi there,

Your query gives you an IQueryable(Of T) where in this case T is the type of FIELD2 as that's what you're selecting. To display its value, you need to loop over it. Assuming FIELD2 is something like a string:

Code:

 
foreach (var myItem in allUsers)
{
  Label1.Text += myItem + "<br />";
}

or when FIELD2 is another complex type:

Code:

 
foreach (var myItem in allUsers)
{
  Label1.Text += myItem.Name + "<br />";
}

If you are sure that the WHERE clause for FIELD1 results in a single record you can wrap the query in parentheses and retrieve a single value using SingleOrDefault():

Code:

 
using (Test1DataContext myDataContext = new Test1DataContext())
{
  string oneUser = (from xx in myDataContext.FILE1
       where xx.FIELD1 == "ABC123"
       select xx.FIELD2).SingleOrDefault();
}

Hope this helps,

Imar

westannex · April 5th, 2009, 11:27 AM

Thanks Imar.

I tried this but I get an error: Embedded statement cannot be a declaration or labeled statement.

If I replace
string oneUser =
with
Label1.Text =
it works. But does this mean I need a separate Linq statement for every file field I want to return?

Also, I tried normal SQL:
Select FIELD1, FIELD2 from FILE1 where FIELD3 = 'XYZ';
But this is wrong somewhere as the code-behind file wont accept it.

I realise this is very basic stuff but I'm really stuck on it.

Imar · April 5th, 2009, 11:37 AM

Change string oneUser to var oneUser, put a breakpoint on var oneUser and then execute the query. What's the inferred type of the oneUser variable? Is FIELD2 really a string? I can execute similar queries without a problem.

Not sure where you're executing that SQL code so I can't really say anything about it wihout seeing more code.

Cheers,

Imar

westannex · April 6th, 2009, 03:07 PM

I tried that, but I cant even get as far as executing the query - when I press F5 I get the build errors list with the message "Embedded statement cannot be a declaration or labeled statement".

All the file fields are 10 long alphanumeric (this is a test file).

The SQL - I typed it straight into the Page_Load function :
protected void Page_Load(object sender, EventArgs e)
{
Select FIELD1, FIELD2 from FILE1 where FIELD3 = 'XYZ';
Label1.Text = FIELD1;
Label2.Test = FIELD2;
}

Imar · April 6th, 2009, 03:16 PM

If only that were possible.... SQL requires a lot more code to be executed. You need connections, readers, command, maybe parameters, dataset and more: http://quickstart.developerfusion.co...wcontents.aspx

What does this mean:

>> All the file fields are 10 long alphanumeric (this is a test file).

What's the model you are querying and what technology are you using? What "file" are you talking about?

Imar

westannex · April 7th, 2009, 12:36 AM

Thanks for the link - I'll check that out.

Sorry, I slipped into IBM-speak for a moment. I'm talking of the table I specified in the Linq. This is set up in SQL server 2005 for testing with all columns = nchar(10).

Imar · April 8th, 2009, 04:39 AM

With regards to the LINQ query, it must be in something you're not showing us. Here's what I tried and what worked for me:

1. Created a table called File1 with columns Field1 and Field2

2. Dragged the table on the LINQ designer surface

3. Created a page with a Label and added the following to Page_Load:

Code:

 
using (MyDataContext myDataContext = new MyDataContext())
{
  string oneUser = (from xx in myDataContext.File1s
        where xx.Field1 == "SomeValue"
        select xx.Field2).SingleOrDefault();
       Label1.Text = oneUser;
}

I get the value from Field2 successfully on the label.

Cheers,

Imar

westannex · April 8th, 2009, 01:37 PM

Thanks Imar. It works now.
I didnt realise the Linq had to be inside {} after the using datacontext line.