Using a parameter for the filename in an SQL query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I have a situation where I want to write the exact same record to two
different files. One file is the "live" file and the second (defined
identically to the first) is a "history log" of all previous states of the
record.

So I'd love to just run the same SQL command twice, with different
filenames.

I could just define two different strings for the text command and swap them
out in the SQLCommand object, but I'd rather use a parameter in the SQL text
to indicate the file name and not have to worry about synchronizing two
versions of the same command for the life of the app.

So I changed my insert command string to...

INSERT INTO @Filename VALUES(@id, @Data1, @Data2, @Datax , etc. etc. )

code is...
Dim cmd as SQLCommand = New SQLCommand(InsertString, connection)
'Add a bunch of parms for the data...
'Set the value of all the data parms...
cmd.Parameters.Add("@Filename", "MyfileName")
cmd.ExecuteNonQuery()

I get an SQL error on the Execute statement that says...
"Must Declare the variable @Filename"

I chose the two parameter version of Parameter.Add because it looked like
the simplest approach. I just need to replace "@Filename" with "MyFileName".


But I'm obviously not doing this right. First I looked to see what it takes
to "Declare" a variable. I looked in my SQL Server book and it says that the
Declare statement is a MS SQL extension, so I gave up this approach as I'd
prefer not to start embedding MS specific SQL into my app.

Is there a way to do this in ado.Net, or am I just going to have to
manipuate the command text string to do the replacement?

Thanks for your help.

BBM
 
The problem isn't with .NET, it's with SQL Server not letting you do
that. You'll need two different command strings, one for each table.

--Mary
 
Thanks. I'll just manipulate the strings.

Mary Chipman said:
The problem isn't with .NET, it's with SQL Server not letting you do
that. You'll need two different command strings, one for each table.

--Mary
Click to expand...
 
Parameterize values are only for parameters, not table names.

Instead of using an INSERT use a stored procedure that accepts your
parameters then execute two inserts.
Example

exec mystoredproc @id, @Data1, @Data2, @Datax


Create Procedure mystoredproc
@id as int,
@Data1 as varchar(10),
@Data2 as varchar(10),
@Datax as varchar(10)
as

set nocount on

insert into table1 (id, Data1, Data2, Datax) values (@id, @Data1, @Data2,
@Datax)
insert into table2 (id, Data1, Data2, Datax) values (@id, @Data1, @Data2,
@Datax)



You'd probably want to do a transaction on this too, if inserting fails into
table2 then you might want to rollback your transaction.
 
Back
Top