passing values to a resource file in vb.net

Question

I have a user form that allows a user to select a start and stop date (using DateTimePicker objects calleddtStart and dtEnd respectively). These dates are then used as criteria in an sql query against a database.

When I originally wrote the program, I had the SQL query hard-coded in my application, which worked perfectly. Simplified example below:

Private Sub RunSQL_Click(sender As Object, e As EventArgs) Handles RunSQL.Click

    Dim mySQLString As String

    'Create SQL Query String
    mySQLString = "Select * " & vbLf &
                  " From " & vbLf &
                  "  some.table " & vbLf &
                  " Where a.start_dttm > (' & dtStart.Text & ' - INTERVAL '1' DAY) and a.end_dttm <= ' & dtEnd.Text & '"

   'Run SQL against database
   ...

End Sub

However, given that the real SQL statement is quite complex, I would like instead to call on a file in My.Resources that holds the SQL statement, so that it is more easily updated if and when I want to make changes to the extract. Thus in the above example, I would change writing out the SQL statement to mySQLString = My.Resources.Extract. My problem is how I pass My.Resources.Extract the start and end dates that the user has entered.

If my resource file includes references to dtStart.Text and dtEnd.Text these are obviously just treated as text and therefore running the SQL fails.

Is there any way of passing values into a resource file, or telling vb.net that dtStart.Text within my extract file is not text, but a reference to a DateTimePicker value.

Thanks in advance for your suggestions.

You could consider adding tokens such as `<<1>>` to your query string to then replace with `dtStart.Text`, but it is far from ideal. Somebody will come soon and tell you to use parameterised SQL, and it will be the best solution. — A Friend, Dec 07 '16 at 15:19
Funnily enough I just thought to use `.Replace("dtStart.Text", dtStart.Text).Replace("dtEnd.Text", dtEnd.Text)`, however parameterised SQL sounds like it would be a better solution, I will investigate. Does parameterised SQL change depending on flavour/type of sql/database, or is it more of a .net standard? — paul frith, Dec 07 '16 at 16:02
I believe it depends on the DB provider and connector you use, but most of them seem almost identical. I'm not familiar with specifics, only with what I've seen in SO questions, but you will find many examples with some simple searches. — A Friend, Dec 07 '16 at 16:06

score 1 · Accepted Answer · answered Dec 07 '16 at 18:19

So for anyone interested based on "Pro Grammer"'s comments I have 2 solutions:

1) Do a find and replace of the relevant strings:

mySQLString = My.Resources.Extract.Replace("dtStart.Text", dtStart.Text).Replace("dtEnd.Text", dtEnd.Text)

2) Use parameterized SQL:

My SQL within the resource file has been changed to look like this

Select *
From
 some.table
Where a.start_dttm > (@startdate - INTERVAL 1 DAY) and a.end_dttm <= @enddate

The @startdate and @enddate parameters are then set in the code like this:

Dim mySQLCommnad as IngresCommand 
'NB I am using Ingres, but this will need to relate to whichever database flavor you are using.

mySQLCommand = myVector.myIngresConnection.CreateCommand()
mySQLCommand.CommandText = My.Resources.Extract
mySQLCommand.Parameters.Add("@startdate", SqlDbType.Date).Value = dtStart.Text
mySQLCommand.Parameters.Add("@enddate", SqlDbType.Date).Value = dtEnd.Text

'RunSQL
...

passing values to a resource file in vb.net

1 Answers1