Question Sql command help to improve my code

trialer

Well-known member
Joined
Oct 4, 2010
Messages
64
Programming Experience
1-3
VB.NET:
Connect()
        Dim cmd As New SqlCommand("select max(Reg_No) from table_subsprofile", con)
        Dim n As Integer
        n = cmd.ExecuteScalar
        txtReg_No.Text = n + 1
 Disconnect()


Help me to get through this problem. if there is no value in the sql database table_subsprofile an error occured.

Now if there is no data in the reg_no then the first value will start by 1

Thanks.
 
If i understand the question then you can certainly improve your sql code e.g. (if you prefer, you can still use an in-line code)

VB.NET:
CREATE PROCEDURE SP_RegNumber
	(
	        @RegNumber int OUTPUT
	)
AS
	SET NOCOUNT ON
	
	IF (SELECT COUNT(*) FROM table_subsprofile) > 0 
	        BEGIN
                          SELECT @RegNumber = MAX(Reg_No) FROM table_subsprofile
                   END
            ELSE
	        BEGIN
	               SET @RegNumber = 0
	        END


In the VB.NET code:
VB.NET:
       Try
            connection.Open()
            Dim command As SqlCommand = connection.CreateCommand
            command.CommandType = Data.CommandType.StoredProcedure
            command.CommandText = "SP_RegNumber"
            command.Parameters.Add("@RegNumber", Data.SqlDbType.Int, 4).Direction = Data.ParameterDirection.Output

            command.ExecuteNonQuery()
            If Not IsDBNull(command.Parameters("@RegNumber").Value) Then
                Dim value As Integer = Convert.ToInt32(command.Parameters("@RegNumber").Value)
                txtReg_No.Text = value + 1
            End If
        Catch ex As Exception

        Finally
            connection.Close()
        End Try

Using output parameters is very efficient ;)
 
You might even be able to use the SQL function ISNULL()

VB.NET:
select ISNULL(max(Reg_No),0) from table_subsprofile

If max(reg_no) returns a value then you get that value. If it returns null you get 0(or whatever you put in the place of 0)
 
Back
Top