Question Build an SQL string from Variables

[ChrisN]

This is my first post so hope I get it right, and hoping someone can help me
The part of my application I am having a problem with is a mail merge function that replaces pre-defined text in a string with data from a SQL Database.

This function is used for sending out personalised emails, account information, customizing data on a web page etc.

I want to take text from a pre-defined template.
I.E. DataStr = "Thank you [FirstName] for using our service."

Step one:
Get the users first name from the database by using information stored in the Mailmerge Table of database.
In the MailMerge table there are two fields MergeCode and MergeData
MergeCode has the code to match the string in the replace function [FirstName]. The MergeData stores a comer delimited string to find the correct piece of data in the database.

MergeData = DBTable,RetrieveField,KeyField,MatchString
It looks like this Users,FirstName,ID,UserID
I then split the data from the MergeData into an array called TableStr = Split(DR("MergeData"), ";", -1, CompareMethod.Text)
This gives me an array with 5 elements
TableStr(0) = "Users"
TableStr(1) = "FirstName"
TableStr(2) = "ID"
TableStr(3) = "UserID"
So the Database Select Command is built like this.
ReturnStr = "Select " & TableStr(2) & "," & TableStr(1) & " from " & TableStr(0) & " where " & TableStr(2) & "=" & TableStr(3))

When I view this string all works except TableStr(3) "UserID"
Select ID,FirstName from Users where ID=? & UserID

Here is the problem. I have a global variable UserID that is populated with a User Number when the user logs on, for this scenario let?s make that UserID = 100
So I want the SQL string to be "Select UserID,FirstName from Users where ID=" 100

But it comes out as "Select UserID,FirstName from Users where ID=" UserID
I am sure there is a simple answer for this but I just can't seem to find it.


Other Information
I am using Visual Studio 2010 Ultimate
Building a web site asp.net using vb.net
This is code behind issue

Thanks in advance
Chris

 

[krishnachris]

i think it should be :
ReturnStr = "Select " & TableStr(2) & "," & TableStr(1) & " from " & TableStr(0) & " where " & TableStr(2) & "=" & TableStr(3) & ""

 

[ChrisN]

Hi Krishnachris...
Thanks for your time in looking at my problem.
You are right about the syntax for the SQL string, the problem is that I still get = "UserID", I guess what my issue is that I need to return the value of the variable UserID not the variable name.

My hope is that i can store lots of different variable names in my SQL build string in the database so i can utilise this string to recover lots of data with a single string.
In VB you used to be able to say value(UserID) and you could retrieve it’s value, I can’t seem to find the equivalent in .net.
Cheers mate and once again thanks for your thoughts
Chris

 

[jmcilhinney]

Firstly, you should look into using the String.Format method for building Strings like that in a more readable and less error-prone way.

As for the question of getting data by specifying a String, you can use a Dictionary for that. A Dictionary(Of String, Object) will allow you to store arbitrary objects against String keys, e.g.

Dim values As New Dictionary(Of String, String)

values.Add("Given Name", "John")
values.Add("Family Name", "McIlhinney")

MessageBox.Show(String.Format("Hello, my name is {0} {1}.", values("Given Name"), values("Family Name")))

 

[ChrisN]

Hi jmcilhinney,
Yea mate that nailed it.
Just what I was looking for.

Works a treat, I agree with your comments and in fact all the great feed back Igot and will implement them in this and other functions.
I want to thank everyone for their help and will surelybe using this forum next time I get stumped, I can power on with the work now.
Thanks again to everyone for their time and help
Chris