CODEWITHSUNDEEP
sqlvb.netvariables
rlphilli
rlphilli

Reputation: 111

SQL output as variable in VB.net

I cannot seem to figure out how to get an output of a SQL query as a variable in VB. I need something like what's below so that I can use that variable in another SQL query. SQL doesn't allow the use of variables as column headers when running queries, so I thought I could use VB to insert the actual output of one SQL task as the dynamic variable in a loop of update queries. Let me (hopefully) explain.

I have the following query:

DECLARE @id int = (SELECT max(id) FROM [views]), @ViewType nvarchar(3);

WHILE @id IS NOT NULL
BEGIN

    SELECT @ViewType = (SELECT [View] FROM [views] WHERE id = @id);

    UPDATE a
    SET a.[@ViewType] = '1'
    FROM [summary] a
    INNER JOIN [TeamImage] b
    ON a.[Part_Number] = b.[PartNum]

    WHERE b.[View] = @ViewType;

SELECT @id = max(id) FROM [views] WHERE id < @id;

END;

The SET a.[@ViewType] = '1' obviously will not work in SQL. But if I could have the (SELECT [View] FROM [views] WHERE id = @id) equal to a variable, then I could write the SQL query in VB and execute it and the variable would become part of the string and therefore execute correctly.

I'm newer to VB, but here's what I have so far:

Dim cn As SqlConnection = New SqlConnection("Data Source=Server1;" & _
                                                 "Initial Catalog=DB1;" & _
                                                 "Integrated Security=SSPI")
cn.Open()

Dim cmd As New sqlCommand("SELECT max(id) FROM orientation_view_experiment;", cn)
vID = cmd.ExecuteNonQuery()

Do While vID > 0
    Dim cmd2 As New sqlCommand("SELECT [View] FROM [views] WHERE id ='" + vID + "'"
    vViewType = cmd2.ExecuteNonQuery()

    Dim cmd3 As New sqlCommand("UPDATE a
    SET a.'" + vViewType + "' = '1' & _
    FROM [summary] a & _
    INNER JOIN [TeamImage] b & _
    ON a.[Part_Number] = b.[PartNum] & _

    WHERE b.[View] = '" + vViewType + "';"
    cmd3.ExecuteNonQuery()
vID = vID - 1
Loop

cn.Close()

I hope some of that made sense, but I'm kind of lost at this point. I feel like I know what I need the SQL to do, but can't quite figure out how to make the computer/programs submit to my will and just do what I need it to do.

Thank you for any help/direction you can give.

Upvotes: 0

Views: 14028

Answers (3)

Steve
Steve

Reputation: 216343

Your code is wrong because you insist in using ExecuteNonQuery for SELECT statements. ExecuteNonQuery doesn't return the rows selected but just a count of the rows affected by an INSERT/DELETE/UPDATE query (I think that for SELECT it returns always zero)

What you need is ExecuteScalar to get the MAX value and the VIEW value because ExecuteScalar is the best choice when you expect to get just the first field of the first row from your SQL statement

Dim cmd As New sqlCommand("SELECT max(id) FROM orientation_view_experiment;", cn)
vID = Convert.ToInt32(cmd.ExecuteScalar())

Do While vID > 0
    Dim cmd2 As New sqlCommand("SELECT [View] FROM [views] WHERE id =" + vID.ToString()
    Dim result = cmd2.ExecuteScalar()
    if Not string.IsNullOrEmpty(result)) Then
       vViewType = result.ToString()

       Dim cmd3 As New sqlCommand("UPDATE a SET a.[" + vViewType + "] = '1' " & _
                                  "FROM [summary] a " & _
                                  "INNER JOIN [TeamImage] b " & _
                                  "ON a.[Part_Number] = b.[PartNum] " & _
                                  "WHERE b.[View] = @vType"
       cmd3.Parameters.AddWithValue("@vType", vViewType)
       cmd3.ExecuteNonQuery()
    End If
Loop

The last part of your code is not really clear to me, but you could use a couple of square brackets around the column name in table summary and a parameter for the View field in table TeamImage.

As a last advice, be sure that the column View in table TeamImage is not directly modifiable by your end user because a string concatenation like this could lead to a Sql Injection attacks

Upvotes: 2

BlueMonkMN
BlueMonkMN

Reputation: 25601

Have you tried replacing '" + vViewType + "' with [" + vViewType + "] ... in other words use square brackets to delimit the column name instead of single quotes which are for delimiting string literals?

Also, I would encourage stopping in the debugger, examining the command that you generated into cmd3 and try executing it directly. It might help you identify other similar problems such as the fact that vViewType is giving you a count of records instead of an actual value from the [View] column.

Upvotes: 0

Steve
Steve

Reputation: 5545

Do a little research into what the different methods of a command are. When you call ExecuteNonQuery, this return the number of records effected. I think you want ExecuteScalar as your cmd and cmd2 methods, so you can get a value from the database.

Upvotes: 0

Related Questions