I know this question could be similar to others but really I'm looking for reasons why VB6 developers should switch to C#.

My company recently approved project to be written in C#, so we have a lot of VB.Net programmers, however, we have some legacy app developers as well that are in VB6. We have a time frame to re-write those apps into .Net web apps. So no matter what they will have to learn new stuff.

One of the developers today specifically asked "why should we switch to C#?"

I responded that the community largely has decided that C# is the way to go with about 80% of the examples in C#. I am a VB.Net programmer and I am excited to finally cut my teeth on C#, however, being that I'm so new I'm not sure I can answer the "why?" question. My reasons are more because I want to learn it.

So without descending into a VB verses C# I really am curious if there are any resources that I can send to these developers to calm their nerves.

Looking forward to your input!

As far as the migration over to .NET goes, better late than never! As far as my advice goes, your mileage may vary, it's worth every penny you're paying for it!

I personally believe you are making the correct choice. The first instinct for VB developers is to switch to VB.NET. That sounds entirely reasonable, but in my opinion, it's the wrong choice. You really have to break down the reasons for the switch into two categories: Why switch to .NET, and why switch to C#?

Why switch to .NET over VB6:

• Multithreading in VB6 is technically possible from a programming perspective, but just about impossible if you want to use the IDE.

• I do not believe you can create a 64-bit native application in VB6. That rules out a lot.

• No new enhancements are being made to VB6.

• OK, there are so many reasons I can think of, I'll probably just stop there.

Why switch to C# instead of VB.NET

• Developers may be lulled into a false sense of familiarity with VB.NET - treating resources like they did in VB6 without understanding the full concepts. An example: you often see new converts to VB.NET setting objects to Nothing, believing that it's a magical way to release resources. It is not.

• It's true that most examples are now in C#. More importantly, Jeff Richter's book is only in C# now. If you want to understand how .NET really works, IMO his book is pretty much mandatory.

• In .NET, you'll find that you will use lambda expressions all of the time, especially when operating with Linq. IMO VB's verbosity really becomes a barrier to comprehension and readability here, in ways where it simply wasn't before: foo.Select(x => x > 50) is, by just about any standard, much more fluent and readable than foo.Select(Function(x) x > 50). It gets worse as the expressions get more complex.

• Some of the worst practices with VB6 are impossible or at least much less accessible in C# (such as ReDim Preserve and On Error Resume Next).

• VB is saddled with some syntax which makes it pretty cumbersome and confusing to use when creating general-purpose CLR libraries. For example, in C#, you use indexers with brackets[]. In VB, you use parens. That makes it pretty difficult for the user of a subroutine to tell if it's an indexer or a function. If someone tried to use your library outside of VB, the difference would be important, but a VB developer might be inclined to create subroutines which should be indexers as functions, since they look similar.

• I don't have any data on this, but if you are trying to hire a good set of programmers, the best ones will generally be less inclined to work in a shop which writes VB.NET over C#. They usually fear that the code their colleagues will be generating is likely to be substandard .NET code, and let's be frank here -- there's a stigma against VB.NET developers and the quality of their code in the community. There. I said it. Let the flames begin...

As a footnote, from my perspective, VB.NET was a real missed opportunity for MS. What it should have been was a way to seamlessly convert your old VB6 code to the .NET world - with dynamic invocation and high-quality COM interop from the start. What it ended up being was a near-clone of C#'s feature set with a more verbose syntax and little to no backward compatibility. Sad, really. It locked a lot of organizations out of .NET for a long time. Then again, maybe it forced a "cold-turkey" clean break from the past...

Suppose I'm building a StackOverflow clone using webforms ASP.NET and jQuery. The Question page has a question, several answers, and comments under each. Requirements:

1. Users can post new answers and comments, and edit existing ones, without postbacks.
2. No UpdatePanels; the AJAX calls retrieve just the JSON they need, not HTML fragments.
3. The page loads with all existing answers and comments in place (no javascript needs to run to read the page).

What I'm trying to figure out is how to do this without having to maintain two sets of markup (one that's bound on the client using some form of jQuery templating, and one that's bound on the server using traditional WebForms).

What are my options?

While it is not exactly what you asked for you may want to consider rendering the HTML on the server via a service (not using update panel) and sending it to the client instead of using client templates. It couldn't be that bad because Facebook are doing it: http://www.facebook.com/video/video.php?v=596368660334 If it is suitable in your situation depends on how rich your markup is and what percentage of the data sent over the wire will be markup as opposed to content.

Lately, I've been thinking that I'd like to try some new ideas and a new approach to web application design. I'm mostly used to using ASP.NET (.NET 2.0) but I've toyed with the ASP.NET MVC library a little bit as well. While both are powerful and interesting, I'd really like something more... basic.

Is there anything like that out there? I'd like a framework that's more transparent really: I want to be able to see the request, pass it where I want, render HTML the way I want, and post requests how I want. I can get all that in ASP.NET, but I have to dig pretty hard to get at all of it. I'd like something simpler to use as the basis for some new framework ideas.

So is there something like that out there? Or would I pretty much have to completely reinvent all the basic routing, request, and response code as well to get a platform to build up from?

It's not answering the question but I really strongly think you're making a mistake here.

MVC lets you deal with rendering in the way you want (it's not like the bad old webforms), HttpHandlers let you deal with Requests and Responses as low down as makes reasonable sense (to me). 99 times out of 100 you don't want to have to deal with the raw raw data coming from the web server (and what is raw anyway? "Request" is an abstraction itself...).

I think you have to look long and hard at your motivation and consider the practical costs if you could get there.

For example:

var query = from c in db.Cars select c;
foreach(Car aCar in query)
{
     Console.WriteLine(aCar.Name);
}

How would this translate once it is compiled? What happens behind the scenes?

It is compiled in the following way:

1. First, the LINQ query expression is transformed into method calls:

   public static void Main()
   {
       var query = db.Cars.Select<Car, Car>(c => c);
       foreach (Car aCar in query)
       {
            Console.WriteLine(aCar.Name);
       }
   }
   

2. If db.Cars is of type IEnumerable<Car> (which it is for LINQ-to-Objects), then the lambda expression is turned into a separate method:

   private Car lambda0(Car c)
   {
       return c;
   }
   private Func<Car, Car> CachedAnonymousMethodDelegate1;
   public static void Main()
   {
       if (CachedAnonymousMethodDelegate1 == null)
           CachedAnonymousMethodDelegate1 = new Func<Car, Car>(lambda0);
       var query = db.Cars.Select<Car, Car>(CachedAnonymousMethodDelegate1);
       foreach // ...
   }
   

   In reality the method is not called lambda0 but something like <Main>b__0 (where Main is the name of the containing method). Similarly, the cached delegate is actually called CS$<>9__CachedAnonymousMethodDelegate1.

   If you are using LINQ-to-SQL, then db.Cars will be of type IQueryable<Car> and this step is very different. It would instead turn the lambda expression into an expression tree:

   public static void Main()
   {
       var parameter = Expression.Parameter(typeof(Car), "c");
       var lambda = Expression.Lambda<Func<Car, Car>>(parameter, new ParameterExpression[] { parameter }));
       var query = db.Cars.Select<Car, Car>(lambda);
       foreach // ...
   }
   

3. The foreach loop is transformed into a try/finally block (this is the same for both):

   IEnumerator<Car> enumerator = null;
   try
   {
       enumerator = query.GetEnumerator();
       Car aCar;
       while (enumerator.MoveNext())
       {
           aCar = enumerator.Current;
           Console.WriteLine(aCar.Name);
       }
   }
   finally
   {
       if (enumerator != null)
           ((IDisposable)enumerator).Dispose();
   }
   

4. Finally, this is compiled into IL the expected way. The following is for IEnumerable<Car>:

   // Put db.Cars on the stack
   L_0016: ldloc.0 
   L_0017: callvirt instance !0 DatabaseContext::get_Cars()
   
   
   // “if” starts here
   L_001c: ldsfld Func<Car, Car> Program::CachedAnonymousMethodDelegate1
   L_0021: brtrue.s L_0034
   L_0023: ldnull 
   L_0024: ldftn Car Program::lambda0(Car)
   L_002a: newobj instance void Func<Car, Car>::.ctor(object, native int)
   L_002f: stsfld Func<Car, Car> Program::CachedAnonymousMethodDelegate1
   
   
   // Put the delegate for “c => c” on the stack
   L_0034: ldsfld Func<Car, Car> Program::CachedAnonymousMethodDelegate1
   
   
   // Call to Enumerable.Select()
   L_0039: call IEnumerable<!!1> Enumerable::Select<Car, Car>(IEnumerable<!!0>, Func<!!0, !!1>)
   L_003e: stloc.1
   
   
   // “try” block starts here
   L_003f: ldloc.1 
   L_0040: callvirt instance IEnumerator<!0> IEnumerable<Car>::GetEnumerator()
   L_0045: stloc.3
   
   
   // “while” inside try block starts here
   L_0046: br.s L_005a
   L_0048: ldloc.3   // body of while starts here
   L_0049: callvirt instance !0 IEnumerator<Car>::get_Current()
   L_004e: stloc.2 
   L_004f: ldloc.2 
   L_0050: ldfld string Car::Name
   L_0055: call void Console::WriteLine(string)
   L_005a: ldloc.3   // while condition starts here
   L_005b: callvirt instance bool IEnumerator::MoveNext()
   L_0060: brtrue.s L_0048  // end of while
   L_0062: leave.s L_006e   // end of try
   
   
   // “finally” block starts here
   L_0064: ldloc.3 
   L_0065: brfalse.s L_006d
   L_0067: ldloc.3 
   L_0068: callvirt instance void IDisposable::Dispose()
   L_006d: endfinally 
   

   The compiled code for the IQueryable<Car> version is also as expected. Here is the important part that is different from the above (the local variables will have different offsets and names now, but let’s disregard that):

   // typeof(Car)
   L_0021: ldtoken Car
   L_0026: call Type Type::GetTypeFromHandle(RuntimeTypeHandle)
   
   
   // Expression.Parameter(typeof(Car), "c")
   L_002b: ldstr "c"
   L_0030: call ParameterExpression Expression::Parameter(Type, string)
   L_0035: stloc.3 
   
   
   // Expression.Lambda(...)
   L_0036: ldloc.3 
   L_0037: ldc.i4.1           // var paramArray = new ParameterExpression[1]
   L_0038: newarr ParameterExpression
   L_003d: stloc.s paramArray
   L_003f: ldloc.s paramArray
   L_0041: ldc.i4.0                    // paramArray[0] = parameter;
   L_0042: ldloc.3 
   L_0043: stelem.ref 
   L_0044: ldloc.s paramArray
   L_0046: call Expression<!!0> Expression::Lambda<Func<Car, Car>>(Expression, ParameterExpression[])
   
   
   // var query = Queryable.Select(...);
   L_004b: call IQueryable<!!1> Queryable::Select<Car, Car>(IQueryable<!!0>, Expression<Func<!!0, !!1>>)
   L_0050: stloc.1 
   

We have a large ASP.NET MVC project where all numbers output to the screen are formatted as currency (i.e. ToString("c"). However, negative numbers are showing up with ()'s. For example:

decimal d = -8.88m;
Console.WriteLine(d.ToString("c"));
//outputs $(8.88)

This is a bit annoying to our users, particularly since there are in textboxes. We have a few thousand places where we send currency fields to the screen like this, so we'd love a way to change the formatting globally. Is there one? All the methods I've seen indicate that you have to create a new formatter, similar to this:

 string curCulture = System.Threading.Thread.CurrentThread.CurrentCulture.ToString();
 System.Globalization.NumberFormatInfo currencyFormat =
     new System.Globalization.CultureInfo(curCulture).NumberFormat;
 currencyFormat.CurrencyNegativePattern = 1;

We'd prefer not to change all of our ToString("c") methods ... is there a better way? My first thought was to just change our locale to Australia, but realized the date formatting would be screwed up.

Aliostad was close ... try this in your base controller:

        System.Globalization.CultureInfo modCulture = new System.Globalization.CultureInfo("en-US");
        modCulture.NumberFormat.CurrencyNegativePattern = 1;
        Thread.CurrentThread.CurrentCulture = modCulture;

Is it possible in EF4 to change conventions for a whole DbContext rather than on a per entity basis?

Currently when I run EF4 queries I'm getting an error on foreign key relationships

Invalid column name 'Account_Id'.

The table being queried (User) has a column named AccountId which is a foreign key to the related table (Account), if I change the column to Account_Id, it solves the problem.

However, the issue is that I don't want to do this on every table. I've set custom conventions no problem using NHibernate, so hopefully its as simple in EF4.

I don't think there is support for global settings. You can use fluent API, as you probably know, but this works for single entity only. I've decided to keep EF4 conventions and use xxxID for primary keys and table1_table2 for linker tables. It's easier to swim with, not against the current ;-). You might want to fire reflector to check the code for an easy way to hack it...

I wonder, why everybody hates ViewData so much?
I find it quite useful and convenient. I tell you why: typically every controller action has it's own ViewModel, so it's used only once and I find it very tedious to modify ViewData class every time I need to add extra portion of data to view (adding extra field to class usually leads to modifying its constructor). Instead I can write in controller

ViewData["label"] = someValue;
// in mvc 3 even better:
ViewData.Label = someValue

and in view

<%= ViewData["label"] %>
<%-- mvc 3: --%>
<%= ViewData.Label %>

or for complex types:

<% ComplexType t = (ComplexType)ViewData["label"]; %> // and use all benefits of strong typing 
<%= t.SomeProperty %>

Writing a controller action I do not have to switch to another class, when I need to add some data to view. And a big plus for me: no flooding your project with senseless classes and switching between them and others.
I agree that usage of "magic strings" could lead errors which are not caught by compiler, but these errors localized in very small part of code and can be discovered very quickly. Besides, how do you think guys working with dynamic languages (rails, django) lives without strong typing at all?)

What's your opinion about using ViewData?

I think this goes beyond just the magic string argument. I would argue that ViewModels are a good thing and not senseless classes because they help keep the Views cleaner and easier to read than accessing ViewData all over the Views.

When you get to five, ten, twenty pieces of data that need to be displayed in a view are you really going to pass all that data over as ViewData? It will make your view harder to follow and that data won't have any meaning. Making a ViewModel and strongly typing the view to that ViewModel will not only make the code easier to read, but you don't have to go casting ViewData objects all over your code.

I do think ViewData is good for certain cases, but when you are dealing with a lot of data it can easily be abused in my opinion.

this is my first time doing a gallery of videos using ASP.NET, I believe there are many articles on the net regarding this, but I would like to see some recommendation and different point of view :

1. what are the tools used?
2. how to not allow user to download a video?
3. what the difference between vimeo,dimecasts.net choice , what about other choices?
4. Streaming?
5. any valuable information will be appreciated.

From tag "asp.net" I'm assuming that you deal with Microsoft technologies. Our team in the past had estimation project around selling video content, so you problem is close to it.

The base idea is usage of Microsoft Windows Media Services 9 Series( for brief overview look at http://www.microsoft.com/windows/windowsmedia/howto/articles/webserver.aspx ). So this answer #1. The most difficult question is #2. Because you can just reduce possibility to download, but to watch video user needs have some frames at client side. That is why content always can be intercepted. Following list is my recommendations how to reduce risks:

• Use "Stream without downloading" (see article above for details) or broadcast
• Manage stream content with Digital Rights Management
• Use authentication of users that watch video and provide unique URL for them to disallow special tool of downloading to intercept results.

Question #3 relates to format war. Vimeo, youtube and many others use FLV format to provide video. In theory client-side player of vimeo can play without problem video from youtube and vice verse. The benefit of it is crossplatforming - Linux, Mac, Windows users can recognize this format and play it. On other hand usage of WMV (windows media) potentially can be restricted (in real I have never seen any problem). But usage of Microsoft formats are more secured since usage of Digital Rights Management tags embedded to content.

Answer on #4, #5 you can find in article above.

I'm trying to build a streaming solution for an internal app, but am drawing blanks for a solution to get past a roadblock. Currently, in my working example, I'm using APE, but due to restrictions I can't have any foreign running processes on the host machine, so I can't run the APE server.

I'm looking for alternatives, but everything I've found so far has required running processes on the server.

Some details about the project.

• There will be approximately 25 people connected at one time
• Ideally everyone should see the updates at the same time, as soon as they're available.
• It will be running in a Windows environment, so C#/.NET solutions would be preferable over things like PHP.

Anyone have any ideas, if node.js is capable of handling this, or of any other solutions?

The issue is that traditional web servers use a thread per socket approach to handling concurrent users which is not always optimal for comet/long polling techniques. (Newer versions of IIS have a way to plug in your own connection handlers however, which I will get too below.)

For the traditional web servers, more often the goal is to get a connection, serve the user up something as quick as possible, and move to the next connection. If a connection is lingering for a long time, its because its probably doing something intensive, like a big download or huge query but overall its actively using the CPU so the threaded model works pretty well.

In comet (long polling), normally you are connecting to a web server where you just wait for an event to occur, and more often than not. This promotes more concurrent connections. Also chacnes are that many of these users are waiting on the same events to occur across the board.

Allocating a thread then for a user to primarily just spin and wait is not a very optimal model for this type of thing. A better model is a event loop based web server that does everything in an asynchronous kind of fashion, and where dispatching off an event to multiple users doesn't involve a costly context switch for each client. This what Node.js is built on (using libevent as its core), as well as Ruby Eventmachine, Twisted Python, Friendfeed's Tornado, Jetty, and the C# based Manos server.

That is why there it's often more advantageous to have comet done on its own process custom a custom server since traditional web servers like Apache and older versions of IIS do not function in a matter that is efficient for Comet's needs.

Standard ASP.NET apps are little bit screwed because thread pool in .NET is limited to 25 general threads and 25 IO threads (and http connections take an IO thread). You may be effectively limited to be slightly less than that in reality because the thread pool is shared with all the other things in .NET. You can bump the thread pool up though with a configuration setting however, but performance has a tendency to decay exponentially the more threads you throw in. You could in theory bump this number up if you can guarantee you won't grow too much, and then possibly just use standard thread monitors in .NET to build your own comet event dispatching thing.

However .NET apps running newer versions of IIS do have a ray of hope though. You can create a custom IAsyncHttpHandler. There are some great guides online for you read up on how this works. With that you can build up your own connection pool and serve your clients more efficiently. It's not a perfect solution and you have to build out lot of plumbing on your own. WebSync is a commercial product that wraps this interface for you and gives you some high level framework pieces you can work with however.

Is it a bad practice to use Integrated Security=True on a production server in ASP.NET?

Nope - perfectly safe*

All you are doing is saying that you are going to use the credentials of (usually) the Windows user that the process is running under in order to authenticate with SQL Server (as opposed to supplying a username and password).

In fact in general using integrated security is considered more secure.

(*) Of course it always depends on your exact situation, but in the general case yes its fine.

We have an ASP.NET MVC site that uses Entity Framework abstractions with Repository and UnitOfWork patterns. What I'm wondering is how others have implemented navigation of complex object graphs with these patterns. Let me give an example from one of our controllers:

var model = new EligibilityViewModel
   {
       Country = person.Pathway.Country.Name,
       Pathway = person.Pathway.Name,
       Answers = person.Answers.ToList(),
       ScoreResult = new ScoreResult(person.Score.Value),
       DpaText = person.Pathway.Country.Legal.DPA.Description,
       DpaQuestions = person.Pathway.Country.Legal.DPA.Questions,
       Terms = person.Pathway.Country.Legal.Terms,
       HowHearAboutUsOptions = person.Pathway.Referrers
   };

It's a registration process and pretty much everything hangs off the POCO class Person. In this case we're caching the person through the registration process. I've now started implementing the latter part of the registration process which requires access to data deeper in the object graph. Specifically DPA data which hangs off Legal inside Country.

The code above is just mapping out the model information into a simpler format for the ViewModel. My question is do you consider this fairly deep navigation of the graph good practice or would you abstract out the retrieval of the objects further down the graph into repositories?

In my opinion, the important question here is - have you disabled LazyLoading?

If you haven't done anything, then it's on by default.

So when you do Person.Pathway.Country, you will be invoking another call to the database server (unless you're doing eager loading, which i'll speak about in a moment). Given you're using the Repository pattern - this is a big no-no. Controllers should not cause direct calls to the database server.

Once a C ontroller has received the information from the M odel, it should be ready to do projection (if necessary), and pass onto the V iew, not go back to the M odel.

This is why in our implementation (we also use repository, ef4, and unit of work), we disable Lazy Loading, and allow the pass through of the navigational properties via our service layer (a series of "Include" statements, made sweeter by enumerations and extension methods).

We then eager-load these properties as the Controllers require them. But the important thing is, the Controller must explicitly request them.

Which basically tells the UI - "Hey, you're only getting the core information about this entity. If you want anything else, ask for it".

We also have a Service Layer mediating between the controllers and the repository (our repositories return IQueryable<T>). This allows the repository to get out of the business of handling complex associations. The eager loading is done at the service layer (as well as things like paging).

The benefit of the service layer is simple - more loose coupling. The Repository handles only Add, Remove, Find (which returns IQueryable), Unit of Work handles "newing" of DC's, and Commiting of changes, Service layer handles materialization of entities into concrete collections.

It's a nice, 1-1 stack-like approach:

personService.FindSingle(1, "Addresses") // Controller calls service
 |
 --- Person FindSingle(int id, string[] includes) // Service Interface
      |
       --- return personRepository.Find().WithIncludes(includes).WithId(id); // Service calls Repository, adds on "filter" extension methods
           |
            --- IQueryable<T> Find() // Repository
                |
                 -- return db.Persons; // return's IQueryable of Persons (deferred exec)

We haven't got up to the MVC layer yet (we're doing TDD), but a service layer could be another place you could hydrate the core entities into ViewModels. And again - it would be up to the controller to decide how much information it wishes.

Again, it's all about loose coupling. Your controllers should be as simplistic as possible, and not have to worry about complex associations.

In terms of how many Repositories, this is a highly debated topic. Some like to have one per entity (overkill if you ask me), some like to group based on functionality (makes sense in terms of functionality, easier to work with), however we have one per aggregate root.

I can only guess on your Model that "Person" should be the only aggregate root i can see.

Therefore, it doesn't make much sense having another repository to handle "Pathways", when a pathway is always associated with a particular "Person". The Person repository should handle this.

Again - maybe if you screencapped your EDMX, we could give you more tips.

This answer might be extending out a little too far based on the scope of the question, but thought i'd give an in-depth answer, as we are dealing with this exact scenario right now.

HTH.

I have an existing Asp.Net MVC Website and I would also like to provide a Web Service from the same domain.

What is the best way to approach creating a web service in this scenerio?

Do I add to this project or...?

You should be able to add an WebService file directly to the MVC project. Right click on solution and select add new item, then select the web category and att the bottom of the list there should be Web Service.

Just remember to check that the routes does not eat up the call to the webservice.

That way the webservice can get access to the same model classes as the MVC application.

I have this code

UPDATE OPENQUERY (db,'SELECT * FROM table WHERE ref = ''"+ Ref +"'' AND bookno = ''"+ Session("number") +"'' ') 

How would I prevent SQL Injections on this?

Thanks

Jamie

UPDATE

Here's what i'm trying

SqlCommand cmd = new SqlCommand("Select * from Table where ref=@ref", con); 
cmd.Parameters.AddWithValue("@ref", 34);

For some reason everything I try and add it doesn't seem to work I keep getting SQL Command mentioned below.

The error is this

'SqlCommand' is a type and cannot be used as an expression

I'm taking over someone else's work so this is all new to me and I would like do things the right way so if anyone can provide any more help on how to make my query above safe from SQL injections then please do.

Thanks

Jamie

UPDATE NO 2

I added in the code as VasilP said like this

Dim dbQuery As [String] = "SELECT * FROM table WHERE ref = '" & Tools.SQLSafeString(Ref) & "' AND bookno = '" & Tools.SQLSafeString(Session("number")) & "'"

But I get an error Tools is not declared do I need to specify a certain namespace for it to work?

Thanks

Jamie

UPDATE

Has anyone got any ideas on the best of getting my query safe from SQL injection without the errors that i'm experiencing?

Thanks

Jamie

UPDATE

I now have it so it work without the parameters bit here's my updated souce code any idea why it won't add the parameter value?

Dim conn As SqlConnection = New SqlConnection("server='server1'; user id='w'; password='w'; database='w'; pooling='false'")
   conn.Open()


Dim query As New SqlCommand("Select * from openquery (db, 'Select * from table where investor = @investor ') ", conn)
query.Parameters.AddWithValue("@investor", 69836)

dgBookings.DataSource = query.ExecuteReader
dgBookings.DataBind()

It works like this

Dim conn As SqlConnection = New SqlConnection("server='server1'; user id='w'; password='w'; database='w'; pooling='false'")
   conn.Open()


Dim query As New SqlCommand("Select * from openquery (db, 'Select * from table where investor = 69836') ", conn)

dgBookings.DataSource = query.ExecuteReader
dgBookings.DataBind()

The error i'm getting is this

An error occurred while preparing a query for execution against OLE DB provider 'MSDASQL'. 

And it's because it isn't replacing the @investor with the 69836

Any ideas?

SOLUTION

Here is how I solved my problem

Dim conn As SqlConnection = New SqlConnection("server='h'; user id='w'; password='w'; database='w'; pooling='false'")

conn.Open()

Dim query As New SqlCommand("DECLARE @investor varchar(10), @sql varchar(1000) Select @investor = 69836 select @sql = 'SELECT * FROM OPENQUERY(db,''SELECT * FROM table WHERE investor = ''''' + @investor + ''''''')' EXEC(@sql)", conn)

dgBookings.DataSource = query.ExecuteReader
dgBookings.DataBind()

Now I can write queries without the worry of SQL injection

Thanks everyone

Try using a parameterized query here is a link http://www.aspnet101.com/2007/03/parameterized-queries-in-asp-net/

Also, do not use OpenQuery... use the this to run the select

SELECT * FROM db...table WHERE ref = @ref AND bookno = @bookno

---

More articles describing some of your options:

http://support.microsoft.com/kb/314520

http://stackoverflow.com/questions/125457/what-is-the-t-sql-syntax-to-connect-to-another-sql-server

---

Edited

Note your original question was asking about distributed queries and Linked servers. This new statement does not reference a distributed query. I can only assume you are directly connecting to the database now. Here is an example that should work. Here is another reference site for using SqlCommand.Parameters

SqlCommand cmd = new SqlCommand("Select * from Table where ref=@ref", con); 
cmd.Parameters.Add("@ref", SqlDbType.Int);
cmd.Parameters["@ref"] = 34;

---

Edited:

Ok Jamie taylor I will try to answer your question again.

You are using OpenQuery becuase you are probably using a linked DB

Basically the problem is the OpenQuery Method takes a string you cannot pass a variable as part of the string you sent to OpenQuery.

You can format your query like this instead. The notation follows servername.databasename.schemaname.tablename. If you are using a linked server via odbc then omit databasename and schemaname, as illustrated below

    Dim conn As SqlConnection = New SqlConnection("your SQL Connection String")
    Dim cmd As SqlCommand = conn.CreateCommand()
    cmd.CommandText = "Select * db...table where investor = @investor"
    Dim parameter As SqlParameter = cmd.CreateParameter()
    parameter.DbType = SqlDbType.Int
    parameter.ParameterName = "@investor"
    parameter.Direction = ParameterDirection.Input
    parameter.Value = 34