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

 

[gmmastros]

Make sure you have a good backup before running this....

[COLOR=blue]UPDATE[/color] InovisInt_Lisa 
[COLOR=blue]SET[/color]    ISAID= [COLOR=blue]Case[/color] ISAID [COLOR=blue]When[/color] [COLOR=red]'8473819430'[/color] [COLOR=blue]Then[/color] [COLOR=red]'2016511600'[/color]
                         [COLOR=blue]When[/color] [COLOR=red]'135926871'[/color] [COLOR=blue]Then[/color] [COLOR=red]'WEYERHAEUSER'[/color]
                         [COLOR=blue]When[/color] [COLOR=red]'6015823680'[/color] [COLOR=blue]Then[/color] [COLOR=red]'9784399625'[/color]
              [COLOR=blue]End[/color]
[COLOR=blue]Where[/color]  ISAID In ([COLOR=red]'8473819430'[/color],[COLOR=red]'135926871'[/color],[COLOR=red]'6015823680'[/color])

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[tsp1lrk72]

Thanks pmegan --- It failed on cmd.ExecuteNonQuery()

Gave me: An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in system.data.dll

Additional information: System error.

???

 

[earthandfire]

Thanks George, that is more or less what I had in mind, but I didn't feel confident enough to post without testing.

 

[gmmastros]

No problem.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[rjoubert]

Function NewISAID(ByVal OldISAID as string) as string
   Dim strTemp as String

   Select Case OldISAID
     Case "123"
        strTemp = "456"
     Case "321"
        strTemp = "BLAH"
     Case "987"
        strTemp = "888"
   End Select

   Return strTemp
End Function

Sub FindReplace()
   Dim cn As New SqlClient.SqlConnection
   Dim cmd As New SqlClient.SqlCommand
   Dim MySQL As String 

   cn.ConnectionString = 'Your connection string goes here
   cn.Open()            
   
   strSQL = "UPDATE InovisInt_Lisa " & _
            "SET ISAID = '" & NewISAID(strOldISIAD) & "'" & _
            "WHERE ISAID = '" & strOldISIAD & "'"

            cmd = new SqlCommand(MySql,cn)

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

 

[tsp1lrk72]

gmmastros...Hi- I need it to update from a Windows App... so I I'm trying to get it to work in a Vb.net Sub...

Can I get that string to work in a VB.net sub??? This is so annoying!! It's a report that comes in daily and they can't change it on their side so we have to come up with something to automate it...

Thanks!

 

[earthandfire]

The SQL that George has provided would become your SQL command string.

Hope this helps.

 

[gmmastros]

tsp1lrk72,

I'm just a database junkie. I am absolutely confident that you can cause that query to be executed from vb.net. I'm sure someone else will be able to help you to get it to run from vb.net.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[pmegan]

Are you always changing the same ID's? If thats the case then why not write this as a stored procedure and call that from your app? This will help if you ever have to change the ID's since you can edit the stored procedure instead of changing the code, rebuilding and redistributing it. Plus the stored procedure will already have an execution plan so it may run a bit quicker.

 

[tsp1lrk72]

How can I turn this into a stored procedure?

UPDATE InovisInt_Lisa SET ISAID = CASE WHEN ISAID = 8473819430 THEN 2016511600
WHEN ISAID = 135926871 THEN 'WEYERHAEUSER'
WHEN ISAID = 6015823680 THEN 9784399625
END
WHERE ISAID IN (8473819430, 135926871,6015823680)

 

[chrissie1]

uhm.

create procedure dbo.blahname
AS
UPDATE InovisInt_Lisa SET ISAID = CASE WHEN ISAID = 8473819430 THEN 2016511600
WHEN ISAID = 135926871 THEN 'WEYERHAEUSER'
WHEN ISAID = 6015823680 THEN 9784399625
END
WHERE ISAID IN (8473819430, 135926871,6015823680)

Christiaan Baes
Belgium

My Blog

 

[gmmastros]

tsp1lrk72, why did you remove the single-quotes?

If you plan on setting ISAID to 'WEYERHAEUSER', then clearly, it must be a string column (char/varchar/nchar/nvarchar). By removing the single-quotes, it's possible that it will fail, but at a minimum, you'll be causing implicit data type conversions and the code will execute slower.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[pmegan]

Log into Query analyzer and run the code below. This will create a stored procedure named usp_UpdateIDs.

In your app change the command type to stored procedure and the command text to "dbo.usp_UpdateIDs", ar whatever you decide to name the procedure.

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE dbo.usp_UpdateIDs AS
	UPDATE InovisInt_Lisa 
	SET ISAID = CASE 	
		WHEN ISAID = 8473819430 THEN 2016511600
		WHEN ISAID = 135926871 THEN 'WEYERHAEUSER'
		WHEN ISAID = 6015823680 THEN 9784399625
	END
	WHERE ISAID IN (8473819430, 135926871,6015823680)

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

 

[chrissie1]

Personally, I don't think we are helping the OP by writting all his code for him.



Christiaan Baes
Belgium

My Blog

 

[earthandfire]

... and where is SET NOCOUNT ON?

 

[tsp1lrk72]

Thanks all, I actually did get the procedure part on my own... don't worry... I had another one and I used that as a guide... I appreciate it....we'll see if this works! Thanks again.

 

[tsp1lrk72]

Okay, here is my sub:

 Sub test()
        Dim MyConn As New SqlClient.SqlConnection
        MyConn.ConnectionString = strDbConn
        Dim MyComm As SqlCommand = New SqlCommand("ChangeGStoISA", MyConn)
        MyComm.CommandType = CommandType.StoredProcedure
        MyConn.Open()
        MyComm.ExecuteNonQuery()
        MyConn.Close()
    End Sub

and here is the SP:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[ChangeGStoISA]  AS

BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
UPDATE InovisInt_Lisa SET ISAID = CASE 
WHEN ISAID = 8473819430 THEN 2016511600
WHEN ISAID = 135926871 THEN 'WEYERHAEUSER'
WHEN ISAID = 6015823680 THEN 9784399625
END
WHERE ISAID IN (8473819430, 135926871,6015823680)	
END
GO

Now it fails on this:

MyComm.ExecuteNonQuery()

with this:

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in system.data.dll

What the heck is this now??

 

[earthandfire]

Have a look at George's post timed 7 Mar 08 14:46, that may explain the problem.

 

[tsp1lrk72]

The ISAID is varchar(50)

 

[pmegan]

Personally, I don't think we are helping the OP by writting all his code for him.

Good point Christiaan.


tsp1lrk72 -
Put your code in a try block and use
Catch ex as SqlClient.SqlException.

The message or stacktrace shuold help you find out what's causing the problem.