String.Format For SQL Connection String

Question

I need to convert a SQL connection string into a String.Format line

The line that I need to convert is this:

WHERE full_name = @FullGraveNumber AND cemetery_id = @CemeteryID

Both thefull_name and cemetery_id are variables, but I have not idea how to construct the String.Format line.

I'm confused, you're talking about connection strings but the line you're converting is part of a query? I have to admit I'm loathed to touch this one because I think you're underestimating the problem, you've not given us a complete list of values of the clauses... have you considered you'll need to select which parameters go with the column names? — Liath, Dec 20 '13 at 14:02
When using parameterized queries, you should use a built in function like [`SqlParameterCollection.AddWithValue`](http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparametercollection.addwithvalue(v=vs.110).aspx), not `String.Format`. — valverij, Dec 20 '13 at 14:04

score 1 · Answer 1 · answered Dec 20 '13 at 14:12

1

Seems you are looking for

Dim selectCommand = "SELECT * FROM mytable WHERE full_name = @FullGraveNumber AND cemetery_id = @CemeteryID"
Dim cmd = New SqlCommand(selectCommand, yourconnetion)

cmd.Parameters("@FullGraveNumber").Value = value1
cmd.Parameters("@CemeteryID").Value = value2

or

cmd.Parameters.AddWithValue("@FullGraveNumber", value)

answered Dec 20 '13 at 14:12

huMpty duMpty

14,346
14
60
99

This is what I thought originally however the OP seemed to imply that the columns/parameters could change... dynamic SQL style – Liath Dec 20 '13 at 14:14

score 0 · Answer 2 · edited May 23 '17 at 10:25

First of all, ConnectionString is already a String with no parameters, it does not need to converted using String.Format. Usually stored in app.config, you feed it directly into SqlConnection object upon creation.

Part of the query that you have is also a String, but this time it may be substituted with parameters. However, please don't do so and use SQL parameters instead (see @huMpty's answer).

full_name and cemetery_id are SQL parameters, variables is something else.

My suggestion it to learn the terminology first, before you do any coding. No offense, it would benefit you a lot, because you would be able to ask a proper question. Proper question means a fast and qualified answer. Improper questions are usually closed. These are the rules of StackOverflow.

String.Format For SQL Connection String

2 Answers2