Randy,

As Bob pointed out, the key needs to be an OUTPUT parameter in your SP, as he showed you.

Then when you need a key, you need to use SqlCommand.ExecuteNonQuery rather than using the .Fill(). The code will be something like this (excuse my poor VB, I'm better at C#):

' Some of your original code
strSQL = "spGetKey"
objConn = ""
dim objCmd As SqlClient.SqlCommand
objCmd = New SqlClient.SqlCommand(strSQL, objConn)
            lADO.objCmd.CommandType = CommandType.StoredProcedure

Dim TableName As SqlClient.SqlParameter = _
    lADO.objCmd.Parameters.Add("@TableName", SqlDbType.VarChar)
TableName.Value = lcTableName

' I added this code
objCmd.ExecuteNonQuery()
' int or whatever your data type is for your key
dim nKey As int = (int)objCmd.Parameters["@Key"].Value

' then do whatever you need to with the key, you can add it
' directly to your dataset if you need to, or as Bob suggested
' make this a generic GetKey method and return the key from here.
return nKey

HTH
~~Bonnie


>How do you put the value in the dataset? The key is updating, but I keep getting a null reference error for my dataset. Here is my process so far:
>
>'Stored Procedure
>CREATE PROCEDURE spGetKey
>@TableName varchar(40)
>AS
>UPDATE tblESCGlob
>SET
>intKeyValue = intKeyValue + intIncrementValue
>FROM
>tblESCGlob
>WHERE
>strTableName = @TableName
>GO
>
>'Here is my code
>strSQL = "spGetKey"
>objConn = ""
>dim objCmd As SqlClient.SqlCommand
>objCmd = New SqlClient.SqlCommand(strSQL, objConn)
> lADO.objCmd.CommandType = CommandType.StoredProcedure
>
>Dim TableName As SqlClient.SqlParameter = _
> lADO.objCmd.Parameters.Add("@TableName", SqlDbType.VarChar)
>TableName.Value = lcTableName
>
>objDS = New DataSet()
>objConn.Open()
>objDA = New SqlClient.SqlDataAdapter(Me.objCmd)
>objDA.Fill(objDS, "PrimaryKey")
>objConn.Close()
>
>Thanks for the help.