• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 218
  • Last Modified:

VB.net calling a Stored Procedure

I have allot of SQL calls in my project but now I am calling a stored procedure that will have a output parameter of the newly created row id. I'm not sure about my code and this will run in a Windows service so I cannot easily debug it so I thought I would have for opinions.

The ID field is the primary key and is auto generated by the table when a new record is added.
Public Shared Function NewServerData(ByVal clstemp As clsServer, ByVal strdbpath As String) As Integer
        Dim strConn As SqlConnection = New SqlConnection
        Dim cmd As SqlCommand = New SqlCommand
        Dim intid As Integer

        strConn.ConnectionString = strdbpath
        strConn.Open()

        cmd.CommandText = "add_or_update_server"
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = strConn

        'Write data to db
        cmd.Parameters.AddWithValue("name", clstemp.PCName)
        cmd.Parameters.AddWithValue("os", clstemp.OS)
        cmd.Parameters.AddWithValue("service_pack", clstemp.ServicePack)

        If Not clstemp.SystemUpTime Is Nothing Then
            cmd.Parameters.AddWithValue("system_up_time", clstemp.SystemUpTime)
        Else
            cmd.Parameters.AddWithValue("system_up_time", DBNull.Value)
        End If
        cmd.Parameters.AddWithValue("registered_user", clstemp.RegisteredUser)
        cmd.Parameters.AddWithValue("scan_date", Now)
        
        'output parameter.
        cmd.Parameters.AddWithValue("id", "")
        cmd.Parameters("id").Direction = ParameterDirection.Output


        intid = cmd.ExecuteScalar

        cmd.Parameters.Clear()

        strConn.Close()

        strConn = Nothing
        cmd = Nothing

        Return intid
    End Function

Open in new window

0
tybarton
Asked:
tybarton
  • 6
  • 5
2 Solutions
 
CodeCruiserCommented:
Change this section

        'output parameter.
        cmd.Parameters.AddWithValue("id", "")
        cmd.Parameters("id").Direction = ParameterDirection.Output

to

        'output parameter.
        Dim oParam As New SqlParamater
        oParam.Name="Id"
        oParam.Direction = ParameterDirection.Output
        cmd.Parameters.Add(oParam)


Then after exection, use

oParam.Value

to get the output value
0
 
tybartonAuthor Commented:
I get an error that "name" is not valid. Is value the samue thing?
oParm.Value = "id"

Ty
0
 
CodeCruiserCommented:
Sorry its ParameterName not Name.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
tybartonAuthor Commented:
Ok I just got to try that today and it failes with the error (clsserverADO:NewServerData) - String[6]: the Size property has an invalid size of 0. - 10/28/2011 11:22:13 AM

Here is the updated code.

Public Shared Function NewServerData(ByVal clstemp As clsServer, ByVal strdbpath As String) As Integer
        Dim strConn As SqlConnection = New SqlConnection
        Dim cmd As SqlCommand = New SqlCommand
        Dim intid As Integer

        Try
            strConn.ConnectionString = strdbpath
            strConn.Open()

            cmd.CommandText = "add_or_update_server"
            cmd.CommandType = CommandType.StoredProcedure

            cmd.Connection = strConn

            'Write data to db
            cmd.Parameters.AddWithValue("name", clstemp.PCName)
            cmd.Parameters.AddWithValue("os", clstemp.OS)
            cmd.Parameters.AddWithValue("service_pack", clstemp.ServicePack)

            If Not clstemp.SystemUpTime Is Nothing Then
                cmd.Parameters.AddWithValue("system_up_time", clstemp.SystemUpTime)
            Else
                cmd.Parameters.AddWithValue("system_up_time", DBNull.Value)
            End If

            cmd.Parameters.AddWithValue("registered_user", clstemp.RegisteredUser)
            cmd.Parameters.AddWithValue("scan_date", Now)
            Dim oParam As New SqlParameter
            oParam.ParameterName = "id"
            oParam.Direction = ParameterDirection.Output
            cmd.Parameters.Add(oParam)

            intid = cmd.ExecuteScalar

            cmd.Parameters.Clear()

            strConn.Close()

            strConn = Nothing
            cmd = Nothing

        Catch ex As Exception
            'Write event to ErrLog.txt
            Dim oLog As New System.IO.StreamWriter("C:\LHSystemHealth\ErrLog.txt", True)
            oLog.WriteLine("(clsserverADO:NewServerData) - " & ex.Message & " - " & Now)

            oLog.Close()

            Return 0
        End Try

        Return intid
    End Function

Open in new window

0
 
CodeCruiserCommented:
Specify a size and datatype of the parameter as well.
0
 
tybartonAuthor Commented:
I tried to add it like oParam.DbType = DbType.Int32 and I get a error that the function or procedure has too many arguments.

I can run the stored procedure in SQL and know that it does return the new row id so the error is definitely in the VB code.

Tyler
0
 
CodeCruiserCommented:
And you are sure that sp takes 7 parameters? Show me the sp signature and VB code you r using.
0
 
tybartonAuthor Commented:
It takes five parameters and one output. I removed the scan_date parameter as it is filled in the stored procedure itself.

The VB code is above. This is the stored procedure in the SQL Server. As I siad running the procedure on the server returns a id.


USE [LHSystemHealth]
GO
/****** Object:  StoredProcedure [dbo].[add_or_update_server]    Script Date: 10/31/2011 06:54:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[add_or_update_server]
	@name					VARCHAR(100),
	@os						VARCHAR(100),
	@service_pack			VARCHAR(100),
	@system_up_time_minutes	INT,
	@registered_user		VARCHAR(100),
	@id						INT				OUTPUT
AS
BEGIN
	
	SET NOCOUNT ON;
	
	SELECT @id = id FROM lhsh_servers WHERE name = @name;
	
	IF ( @id IS NULL )
	BEGIN
	
		INSERT INTO lhsh_servers ( name, os, service_pack, system_up_time_minutes, registered_user, last_update )
		VALUES ( @name, @os, @service_pack, @system_up_time_minutes, @registered_user, GETDATE() )
	
		SELECT TOP 1 @id = id FROM lhsh_servers WHERE name = @name;
	
	END
	ELSE
	BEGIN
					
		UPDATE lhsh_servers
		SET os = @os, service_pack = @service_pack, system_up_time_minutes = @system_up_time_minutes, registered_user = @registered_user, last_update = GETDATE()
		WHERE id = @id;
	
	END
	
END

Open in new window

0
 
CodeCruiserCommented:
Do you still get the too many arguments error?

You have

cmd.Parameters.AddWithValue("system_up_time", clstemp.SystemUpTime)

but

@system_up_time_minutes

in sp.
0
 
tybartonAuthor Commented:
I think I got it. Yes someone on the team changed the SP parameter and that was causing on error. I then was getting a conversion error of the system_up_time_minutes which I solved by converting it in the paramater add statement.

I then found another error about a parameter size setting for the return paramter so I added oParm.Size = 1000 however my question now is figuring out what the correct size would be. The output paramter is the automatically created id field so over the life of the app that number is going to continue to grow. Any thoughts would be apperciated.

Here is the new working code.


Public Shared Function NewServerData(ByVal clstemp As clsServer, ByVal strdbpath As String) As Integer
        Dim strConn As SqlConnection = New SqlConnection
        Dim cmd As SqlCommand = New SqlCommand
        Dim intid As Integer

        Try
            strConn.ConnectionString = strdbpath
            strConn.Open()

            cmd.CommandText = "add_or_update_server"
            cmd.CommandType = CommandType.StoredProcedure

            cmd.Connection = strConn

            'Write data to db
            cmd.Parameters.AddWithValue("name", clstemp.PCName)
            cmd.Parameters.AddWithValue("os", clstemp.OS)
            cmd.Parameters.AddWithValue("service_pack", clstemp.ServicePack)

            If Not clstemp.SystemUpTime Is Nothing Then
                cmd.Parameters.AddWithValue("system_up_time_minutes", CInt(clstemp.SystemUpTime))
            Else
                cmd.Parameters.AddWithValue("system_up_time_minutes", DBNull.Value)
            End If

            cmd.Parameters.AddWithValue("registered_user", clstemp.RegisteredUser)

            Dim oParam As New SqlParameter
            oParam.ParameterName = "id"
            oParam.Direction = ParameterDirection.Output
            oParam.Size = 1000
            cmd.Parameters.Add(oParam)

            cmd.ExecuteNonQuery()

            intid = cmd.Parameters("id").Value

            cmd.Parameters.Clear()

            strConn.Close()

            strConn = Nothing
            cmd = Nothing

        Catch ex As Exception
            'Write event to ErrLog.txt
            Dim oLog As New System.IO.StreamWriter("C:\LHSystemHealth\ErrLog.txt", True)
            oLog.WriteLine("(clsserverADO:NewServerData) - " & ex.Message & " - " & Now)

            oLog.Close()

            Return 0
        End Try

        Return intid
    End Function

Open in new window

0
 
CodeCruiserCommented:
I would think a size of 10 would be sufficient given that it would be number of digits in value.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now