VB.net SUB?

[tsp1lrk72]

Hi,

I'm trying to do an update with a Case Statement to a SQL DB, but I want to do it from my .NET Windows App

I have this, doesn't work...

Sub FindReplace()
        Dim cn As New SqlClient.SqlConnection
        Dim cmd As New SqlClient.SqlCommand
        Dim ISAID As String
        Select Case ISAID
            Case 8473819430
                ISAID = 2016511600
            Case 135926871
                ISAID = "WEYERHAEUSER"
            Case 6015823680
                ISAID = 9784399625
                cn.ConnectionString = strDbConn
                cn.Open()
                cmd.Connection = cn
                Dim MySQL As String = "UPDATE InovisInt_Lisa " & _
                              "SET ISAID=@ISAID, " & _
                              "WHERE ISAID = @ISAID "
                cmd.Parameters.Add(New SqlParameter("@ISAID", ISAID))
                Dim myCommand As New SqlCommand(MySQL, cn)
                myCommand.ExecuteNonQuery()
                cn.Close()
        End Select
    End Sub

it just doesn't do anything, obviously something is off- any ideas?
Thanks

 

[pmegan]

You don't have an end to your Case statement.

The way it's written, the SQL stuff will only kick off if ISAID = 6015823680, but it still doesn't know where to stop.

Pat

 

[HarleyQuinn]

Pat, are you sure about that?

Case 6015823680
                ISAID = 9784399625
                cn.ConnectionString = strDbConn
                cn.Open()
                cmd.Connection = cn
                Dim MySQL As String = "UPDATE InovisInt_Lisa " & _
                              "SET ISAID=@ISAID, " & _
                              "WHERE ISAID = @ISAID "
                cmd.Parameters.Add(New SqlParameter("@ISAID", ISAID))
                Dim myCommand As New SqlCommand(MySQL, cn)
                myCommand.ExecuteNonQuery()
                cn.Close()
        [red]End Select[/red]
    End Sub

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[AlexCuse]

You need to delete the comma here:

SET ISAID=@ISAID,

Only use commas to separate column names, not before your where clause.

Hope this helps,

Alex

[small]----signature below----[/small]
Majority rule don't work in mental institutions

My Crummy Web Page

 

[tsp1lrk72]

I have an end select, is it in the wrong spot???

Thanks

 

[tsp1lrk72]

I took the comma out, still did nothing... seems easy enough, what the heck is wrong?

 

[pmegan]

Nope, not sure at all anymore.

This is why I prefer telecommuting. People don't pop into my home office and distract me like they do here.

Sorry about the crappy 1st response tsp1lrk72. Are you getting an error whe this runs?

 

[tsp1lrk72]

The Windows App builds fine, no errors and when I run the debug, it goes all the way through, it just doesn't make the changes in the DB...

 

[pmegan]

Have you tried putting in a breakpoint and stepping through to make sure the procedure is completing?

 

[Sorwen]

He was right/wrong about the end select. It is there just in the wrong place.

"I have an end select, is it in the wrong spot???"
Yes.

If you haven't already the code should be something like:

Sub FindReplace()
        Dim cn As New SqlClient.SqlConnection
        Dim cmd As New SqlClient.SqlCommand
        Dim ISAID As String = ""
        Select Case ISAID
            Case 8473819430
                ISAID = 2016511600
            Case 135926871
                ISAID = "WEYERHAEUSER"
            Case 6015823680
                ISAID = 9784399625
        End Select

        If ISAID <> "" then 'Don't want to try to connect if the string is not correct.
            cn.ConnectionString = strDbConn
            cn.Open()
            cmd.Connection = cn
            Dim MySQL As String = "UPDATE InovisInt_Lisa " & _
                          "SET ISAID=@ISAID " & _
                          "WHERE ISAID = @ISAID "
            cmd.Parameters.Add(New SqlParameter("@ISAID", ISAID))
            Dim myCommand As New SqlCommand(MySQL, cn)
            myCommand.ExecuteNonQuery()
            cn.Close()
        End if
    End Sub

Also added an if. Try that and step through see where/if it fails.

-I hate Microsoft!
-Forever and always forward.

 

[Sorwen]

Maybe it would be better to add and else case that just makes sure the string is empty.

Case like this instead.

        Select Case ISAID
            Case 8473819430
                ISAID = 2016511600
            Case 135926871
                ISAID = "WEYERHAEUSER"
            Case 6015823680
                ISAID = 9784399625
            Case Else
                ISAID = ""
        End Select

Shouldn't matter right now, but you never know how you might change the code and it could be important later.

-I hate Microsoft!
-Forever and always forward.

 

[AlexCuse]

You are also assigning the parameter to the first SqlCommand ("cmd") and trying to execute the second one ("myCommand").

The sql string in myCommand is expecting a parameter that is not there. Why do you have two different commands anyways?

[small]----signature below----[/small]
Majority rule don't work in mental institutions

My Crummy Web Page

 

[Sorwen]

I hate to say this because my SQL isn't the greatest so I could be totally wrong, but isn't this wrong?

"SET ISAID=@ISAID " & _
                          "WHERE ISAID = @ISAID "

It seems you are trying to set those that are already that way. So those that are alreay "WEYERHAEUSER" for example will be made "WEYERHAEUSER" again.

-I hate Microsoft!
-Forever and always forward.

 

[rjoubert]

There are a few things wrong with your code snippet.

1. The "End Select" is in the wrong place. The way you have it the DB update code will ONLY run for 6015823680.

2. Your ISIAD variable will always be blank. You are declaring the variable in your dim statements then immediately using the case statements to look at it's value. You haven't assigned a value to the variable yet. You need to pass it into your Sub or assign it some other way.

3. Even if you have assigned a value to the ISIAD variable, since it's a string, your case statements should have double quotes around each value (ie. "6015823680"). But since those values appear to be numeric, it might be better to declare your ISIAD variable as a numeric type (integer, long integer, etc).

 

[pmegan]

Is this the actual code or is it modified a bit to post in a public place? There are a few other things going on here the way it's written:
1) ISAID is being assigned as "" so the Case will never have a result. This should be an argument passed into the procedure
2) ISAID is reassigning itself inside the Case, taking away the original reference
3) The Case block returns a string sometimes and a numeric other times.

So to expand on Alex's suggestion, try something like this:

Sub FindReplace([red]ByVal ISAID as string[/red])
        Dim cn As New SqlClient.SqlConnection
        Dim cmd As New SqlClient.SqlCommand
        [red]Dim strNewVal as string[/red]
        Select Case ISAID
            Case 8473819430
                [red]strNewVal[/red] = [red]"[/red]2016511600[red]"[/red]
            Case 135926871
                [red]strNewVal[/red] = "WEYERHAEUSER"
            Case 6015823680
                [red]strNewVal[/red] = [red]"[/red]9784399625[red]"[/red]
            Case Else
                [red]strNewVal[/red] = ""
        End Select

        If [red](ISAID <> "" and @NewVal<> "")[/red] then 'Don't want to try to connect if the string is not correct.
            cn.ConnectionString = strDbConn
            cn.Open()            
            Dim MySQL As String = "UPDATE InovisInt_Lisa " & _
                          "SET ISAID=[red]@NewVal[/red] " & _
                          "WHERE ISAID = @ISAID "
           [red]cmd = new SqlCommand(MySql,cn)[/red]
           [red]cmd.Parameters.Add(New SqlParameter("@NewVal", strNewVal))[/red]
            cmd.Parameters.Add(New SqlParameter("@ISAID", ISAID))

            cmd.ExecuteNonQuery()
            cn.Close()
        End if
    End Sub

 

[tsp1lrk72]

Ugh-- Thanks for all of the help... Now I'm really confused! How Can I get this to work, all I want to do is a change 3 ID's from something to something else via code in my windows app!!

I appreciate all the help on this...there has to be a way...

 

[pmegan]

Is this something that you'll be doing on a regular basis or is it a one time shot? You may be better off just running a few updates in SQL if this isn't going to be a recurring task.

 

[earthandfire]

If the original values and the replacement values are hard-coded as they seem to be in your post, then why bother with a Case statement at all? Just do it directly in the SQL string.


Hope this helps.

 

[tsp1lrk72]

How can I do it in the SQL String without the parameters and Case statement?

 

[earthandfire]

One way would be to use the SQL CASE statement.

I don't have VS or SQL Server on this machine and my SQL isn't good enough to do this without testing it.

Hope this helps.