Where to place common VB function

[MdotButler]

I need to create a function which will prepare text entered into a textbox to be used in a SQL statement. What I mean is the function will replace a single quote and replace it with two single quotes. I had done this in the past in ASP code and placed it in each form. In ASP.NET I assume there has to be a better way or place to do this.

My question is not how to accomplish this but where to place the code so it can be called from all the forms that need it.

TIA
Mark

 

[BoulderBum]

String replacement is considered a quasi-solution for escaping characters in SQL statements.

What you really want is not a function that will replace all the single quotes with doubles, but a parameterized SQL statement.

http://www.codinghorror.com/blog/archives/000275.html

MCP, MCTS - .NET Framework 2.0 Web Applications

 

[MdotButler]

I agree and do so for inserts and updates. I dynamically build the SQL search command based on where the "Search" form is called from and the users security and some other business logic.

With that said where is the best place to put public functions of this sort?

Thanx
Mark

 

[BoulderBum]

You can still do parameterized SQL with dynamic queries (you just use dynamic parameter names), and it's still important to do so for SELECTs.

A simple way to approach this is to create a class that tracks a set of key/value pairs, then produces bits of a where clause and corresponding parameters.





MCP, MCTS - .NET Framework 2.0 Web Applications

 

[ca8msm]

There's a few places where dynamic SQL is useful or needed. Generally, simple select statements are not one of these places - if you provide your SQL Statement we may be able to show you how to do it with a stored procedure and parameters.


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[MdotButler]

Mark the original question was where to place shared or public VB functions so they may be accessed across forms.

I mentioned a function I had used in the past to parse a string and change a single quote into two single quotes when we got off into the stored procedures and parameters direction.

Thanx
Mark

 

[ca8msm]

But if you follow our advice then, in this case, you won't even need a function...




____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[JoeAtWork]

In C# you would create a static public function (I think it's called "shared" in VB) in one of your classes.

This is an example in C#, I don't know the exact syntax in VB:

    public static string TransformSQL(String OriginalSQL)
    {
        string newSQL = OriginalSQL;

        // Do stuff to newSQL.............

        return newSQL;
    }

Then you can call it anywhere just using the class name (not an instance) and the function:

string mySQL = TheClass.TransformSQL(search_SQL);