CODEWITHSUNDEEP
asp.netsql
Jamie Taylor
Jamie Taylor

Reputation: 3530

SQL Query not inputting value of my variable

I'm having some trouble writing a query using variables. Here's my code

Dim bondnumber as String = "69836"

Dim PasswordCheck As String = "DECLARE @investor varchar(10), 
@thepassword varchar(20), @linkedserver2 varchar(25), @sql varchar(1000) "
PasswordCheck += "SELECT @investor = '" & bondnumber & "', 
@linkedserver2 = 'binfodev', "PasswordCheck += "@sql = 'SELECT * FROM ' + 
@linkedserver2 + ' WHERE bondno = ''@investor'' ' EXEC(@sql)"

It doesn't seem to be passing the variables properly in the query and i'm not sure where i'm going wrong

any ideas?

Upvotes: 0

Views: 405

Answers (2)

Mike Cheel
Mike Cheel

Reputation: 13106

@Jamie - Well I personally tend to find it is much clearer to break things a part a little bit although its not technically necessary. I am just saying to build your parameter variables separately and then add them as parameterized (something like the following): 

Dim sql As String = "SELECT * FROM @LinkedServer WHERE bondno = @BondNumber"
Dim c As New SqlConnection("Your Connection String Here")
Dim cmd As SqlCommand = c.CreateCommand()
With cmd
    .CommandType = CommandType.Text
    .CommandText = sql
    .Parameters.Add(New SqlParameter("@LinkedServer", SqlDbType.VarChar)).Value = "binfodev"
    .Parameters.Add(New SqlParameter("@BondNumber", SqlDbType.VarChar)).Value = "69836"
End With
Dim dt As New DataTable
Dim da As New SqlDataAdapter(cmd)
da.Fill(dt)

Upvotes: 0

Mutation Person
Mutation Person

Reputation: 30520

What is the problem you are seeing specifically? More info would help.

What I can tell, is that you're code translates to a long line of SQL (substituting '69836' for bondnumber)

DECLARE @investor varchar(10), @thepassword varchar(20), @linkedserver2 varchar(25), @sql varchar(1000) SELECT @investor = '69836', @linkedserver2 = 'binfodev', @sql = 'SELECT * FROM ' + @linkedserver2 + ' WHERE bondno = ''@investor'' ' EXEC(@sql)

I'll bet if you execute that in a query window it will fail. Try adding ; at the end of each logical statement.

Have you considered just making this code a stored procedure and passing params to this? Code like this is pretty hazardous (SQL Injection), hard to read, and just a bit ugly in general.

Sample Stored Procedure Code:

CREATE PROCEDURE dbo.usp_MyStoredProcedure 
      @Param1 INT = NULL
AS

SELECT * FROM MyTable Where Col1 = @Param1

Upvotes: 3

Related Questions