Execute SP from VB 2005 in text 1

[jadams0173]

I'm moving an app from vb6 to vb 2005. In vb6 I have this code.

 For x = .ListView1.LBound To .ListView1.UBound
        For y = 1 To .ListView1(x).ListItems.Count
            LVData(x_ary) = "Exec [dbo].[USP_Save_Data_Master_Scheduler] "
            LVData(x_ary) = LVData(x_ary) & "'" & .ListView1(x).ListItems(y).ListSubItems(LVCols.eMO).Text & "'"  '@mo
            LVData(x_ary) = LVData(x_ary) & ",'" & .ListView1(x).ListItems(y).ListSubItems(LVCols.eItem).Text & "'"  '@ITEM
            LVData(x_ary) = LVData(x_ary) & ",'" & .ListView1(x).ListItems(y).ListSubItems(LVCols.eSchedDate).Text & "'"  '@SCHED_DATE
            LVData(x_ary) = LVData(x_ary) & "," & y '@PRIORITY
            LVData(x_ary) = LVData(x_ary) & "," & .ListView1(x).ListItems(y).ListSubItems(LVCols.ePass).Text  '@PASS
            LVData(x_ary) = LVData(x_ary) & ",'" & .ListView1(x).ListItems(y).ListSubItems(LVCols.eComment).Text & "'"  '@COMMENT
            LVData(x_ary) = LVData(x_ary) & "," & .ListView1(x).ListItems(y).ListSubItems(LVCols.eSchedQty).Text   '@SCHED_QTY
            LVData(x_ary) = LVData(x_ary) & "," & .ListView1(x).ListItems(y).ListSubItems(LVCols.eAction).Text  '@ACTION
            LVData(x_ary) = LVData(x_ary) & "," & .ListView1(x).ListItems(y).ListSubItems(LVCols.eSchedLine).Text   '@SCHED_LINE
            LVData(x_ary) = LVData(x_ary) & ",'" & .ListView1(x).ListItems(y).ListSubItems(LVCols.eOtherSide).Text & "'"  '@OTHER_SIDE
            LVData(x_ary) = LVData(x_ary) & "," & .ListView1(x).ListItems(y).ListSubItems(OldRel).Text
            x_ary = x_ary + 1
             'Debug.Print LVData(x_ary)
        Next y
    Next x
   
End With

'Debug.Print Join(LVData, vbCrLf)
clOpenConn.clOpenConnSQL.Execute (Join(LVData, vbCrLf))

It produces a text string that looks like this:

Exec [dbo].[USP_Save] '3009','80532','11/2/2009',14,0,'',5,-1,9,'N-A',0

I've gotten the same thing in 2005 but it doesn't actually do anyting, but no errors are given. The loop is the same so I'm only posting the part that I'm having trouble with.

Also my connection is

Public clOpenConnSQL As New SqlClient.SqlConnection

2005 code

 cmd = New SqlClient.SqlCommand(String.Join(Environment.NewLine, aryLV), clOpenConn.clOpenConnSQL)
        cmd.ExecuteNonQuery()

How can I make this work?

 

[simian336]

Does it give an error at all?

Ddoes the SP exist in that Database?

Simi

 

[jadams0173]

simian336,
No error at all. The SP does exist in the database. I'm still working on it. I put a watch on the cmd variable and I contains all the calls to the sp. Not sure what I'm doing wrong, but I bet it will be something simple when I find it.

 

[simian336]

You should be able to copy

Exec [dbo].[USP_Save] '3009','80532','11/2/2009',14,0,'',5,-1,9,'N-A',0

to the server and run it from command line to see if it works.

That would at least tell you if it is the command or the options to get the command there.

Simi

 

[SgtJarrow]

Couple of things I see...First a line in your "build" reads:

LVData(x_ary) = "Exec [dbo].[USP_Save_Data_Master_Scheduler] "

But you state your final output is something like:

Exec [dbo].[USP_Save]

So unless you abbreviated, they don't match.

========================

Now on to the more important comment I have. Your moving from VB6 to VB.Net and I would recommend you work with a bit more conventional approach. It seems to me you have a stored procedure with some parameters that you want to execute. In this case, I would recommend creating a sqlcommand object, adding the parameters and executing that. It is much easier to read and maintain. Here's an example:

Given the following stored procedure:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE GetSomeValues
	@RecordId int
	, @Name varchar(50)
	, @Other varchar(50)
AS
BEGIN

	SET NOCOUNT ON;

    SELECT
		Field1
		, Field2
		, Field3
		, Field4
    FROM TableName
    WHERE Id = @RecordId
		AND FirstName = @Name
		AND SomeField = @Other
    
END
GO

You would use something like this to execute it and get the results:

' Add the following to your imports statements
Imports System.Data
Imports System.Data.SqlClient

' This goes inside your sub or function
        Dim con As New SqlConnection(connectionString)
        Dim com As New SqlCommand()
        com.Connection = con ' To let the command object know what connection we want to use
        com.CommandType = CommandType.StoredProcedure ' To let VB know we are using a stored procedure
        com.CommandText = "GetSomeValue" ' Name of stored procedure
        com.Parameters.AddWithValue("RecordId", 3) ' This passes 3 as the value for the RecordId parameter
        com.Parameters.AddWithValue("Name", "Robert") ' This passes Robert as the value for the Name parameter
        com.Parameters.AddWithValue("Other", "Something") ' This passes Something as the value for the Other parameter

        con.Open() ' Open the connection to the server
        Dim dr As SqlDataReader = com.ExecuteReader ' Create a SqlDataReader object and fill it with the results of the stored procedure
        If dr.HasRows Then ' Make sure our result set has some rows
            While dr.Read ' Read through the result set one record at a time
                ' Do some stuff here
                ' If you are reading from the results, it could be something like:
                ' Dim name As String = dr("Name").ToString   or dr(2).ToString
            End While
        End If
        con.Close() ' Close the connection to the server

Based on your stored procedure name, it looks like you might be saving some data to a table with this. If that is so, not much changes. The code you would use would be something like:

        Dim con As New SqlConnection(connectionString)
        Dim com As New SqlCommand()
        com.Connection = con ' To let the command object know what connection we want to use
        com.CommandType = CommandType.StoredProcedure ' To let VB know we are using a stored procedure
        com.CommandText = "GetSomeValue" ' Name of stored procedure
        com.Parameters.AddWithValue("RecordId", 3) ' This passes 3 as the value for the RecordId parameter
        com.Parameters.AddWithValue("Name", "Robert") ' This passes Robert as the value for the Name parameter
        com.Parameters.AddWithValue("Other", "Something") ' This passes Something as the value for the Other parameter

        con.Open() ' Open the connection to the server
        com.ExecuteNonQuery() ' This line executes the save stored procedure
        con.Close() ' Close the connection to the server

Finally, you look to be working in a loop from a set of values and adding them to a table. In this case, you only need to create the command object once and just keep changing the insert values:

        Dim con As New SqlConnection(connectionString)
        Dim com As New SqlCommand()
        com.Connection = con ' To let the command object know what connection we want to use
        com.CommandType = CommandType.StoredProcedure ' To let VB know we are using a stored procedure
        com.CommandText = "GetSomeValue" ' Name of stored procedure
        com.Parameters.Add("RecordId", SqlDbType.Int)
        com.Parameters.Add("Name", SqlDbType.VarChar, 50)
        com.Parameters.Add("Other", SqlDbType.VarChar, 50)

        For Each setofvalues() As String In somecollectionofsetofvalues
            com.Parameters("RecordId").Value = setofvalues(0) ' This passes 3 as the value for the RecordId parameter
            com.Parameters("Name").Value = setofvalues(1) ' This passes Robert as the value for the Name parameter
            com.Parameters("Other").Value = setofvalues(2) ' This passes Something as the value for the Other parameter

            con.Open() ' Open the connection to the server
            com.ExecuteNonQuery() ' This line executes the save stored procedure
            con.Close() ' Close the connection to the server
        Next

I hope these examples make sense. I hope they also show that by replacing your VB6 code with the newer conventions used int VB.Net you will find this much easier to read and maintain. There are many examples if you do some internet searching of these techniques and you can always return here with your questions.

Good luck in your coding.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB.NET Programmer

 

[jadams0173]

Robert,

Thanks for the explanations and info!!! I did abbreviate the name of the SP but I missed one. Good catch.

I'm actually saving data from 11 listviews on a tabbed form back to the database. The reason I did it the way I'm doing is so I wouldn't have to send one trasaction at a time to the server in the loop. The one call runs them at one time.

I will most defiantly take a look at what you have given me!!!

 

[SgtJarrow]

I understand....

Eleven "optimized" calls to the database using the proper command objects should be nothing to your server...Of course, only you can tell us if that is true after evaluating the performance.

I have used this methodology for reading a set of records numbering in the thousands (one select statement for that) and then evaluating and inserting them one at a time via a loop and the impact on my server was almost non-existent.

Just be sure to check the performance. And make use of the SQL Forum here if you need some help with optimizing the queries. There are some super SQL experts here...of which I leverage from time to time.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB.NET Programmer

 

[jadams0173]

Hey again Robert. Could you show me how to apply the for each value as string loop in your last example to use the items and subitems from a listview. Currently I'm trying this and I get error:
'ListViewItemCollection' is a type in 'System.Windows.Forms.ListView' and cannot be used as an expression.

for each item() as string In [blue]LView.ListViewItemCollection[/blue]

next

 

[jadams0173]

I got it sorted out.

for each lvitem as listview.item in lview.items

 

[SgtJarrow]

Assuming you are wanting to apply this to all "SELECTED" items in the ListViw:

        For Each li As ListViewItem In Me.ListView1.SelectedItems

        Next

Assuming you are wanting to apply this to "ALL" items in the ListView:

        For Each li As ListViewItem In Me.ListView1.Items

        Next

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB.NET Programmer