YAIC (yet ANOTHER identity crisis)

[Lee Gillie]

I've been watching the posts revolving around Identity columns in SQL
Server. It seems one SHOULD be able to add rows to a table without a
stored procedure. I've seen where the data is requeried, but it SEEMS
like one should be able to do a tiny "scalar" query. But what I have
tried does not work. Can anyone explain the problem with this... (it
crashed (all but one time) on the ExecuteScalar line with: Unhandled
Exception: System.Data.SqlClient.SqlException: Prepared statement
'(@PackageGUID char(38),@PackageVersionGUID char(38),@PackageVers'
expects parameter @PackageDescription, which was not supplied.

The SQL 2000 table has an identity column, PackageID.

The exception doesn't make sense to me.

TIA - Best regards, Lee Gillie, Spokane WA

Public Function ADDtblCORPDTSPackages( _
ByVal PackageGUID As String, _
ByVal PackageVersionGUID As String, _
ByVal PackageVersionSaved As DateTime, _
ByVal PackageName As String, _
ByVal PackageDescription As String, _
ByVal PackagePassword As String _
) As Integer
Dim PackageID As Integer
Dim SQL As String
SQL = "insert into tblCORPDTSPackages " & _

"(PackageGUID,PackageVersionGUID,PackageVersionSaved,PackageName," & _
"PackageDescription,PackagePassword) " & _
"values " & _

"(@PackageGUID,@PackageVersionGUID,@PackageVersionSaved,@PackageName,"
& _
"@PackageDescription,@PackagePassword)" & _
";select @@identity"
Dim cmd As SqlCommand = New SqlCommand

With cmd
.CommandText = SQL
.CommandType = CommandType.Text
.Connection = mSQLCn
.Parameters.Add(New SqlParameter("@PackageGUID",
Data.SqlDbType.Char, 38, "PackageGUID"))
.Parameters.Add(New SqlParameter("@PackageVersionGUID",
Data.SqlDbType.Char, 38, "PackageVersionGUID"))
.Parameters.Add(New SqlParameter("@PackageVersionSaved",
Data.SqlDbType.DateTime, 0, "PackageVersionSaved"))
.Parameters.Add(New SqlParameter("@PackageName",
Data.SqlDbType.VarChar, 128, "PackageName"))
.Parameters.Add(New SqlParameter("@PackageDescription",
Data.SqlDbType.VarChar, 512, "PackageDescription"))
.Parameters.Add(New SqlParameter("@PackagePassword",
Data.SqlDbType.VarChar, 128, "PackagePassword"))
.Parameters.Item("@PackageGUID").Value = PackageGUID
.Parameters.Item("@PackageVersionGUID").Value = PackageVersionGUID
.Parameters.Item("@PackageVersionSaved").Value = PackageVersionSaved
.Parameters.Item("@PackageName").Value = PackageName
.Parameters.Item("@PackageDescription").Value = PackageDescription
.Parameters.Item("@PackagePassword").Value = PackagePassword
End With

PackageID = CInt(cmd.ExecuteScalar())

Return PackageID

End Function

 

[Miha Markic]

Hi Lee,

Could it be that PackageDescription is null?

 

[Lee Gillie]

Miha -

Yes, you are absolutely correct. I know better, yet I found myself
looking for something more sinister. I appreciate your careful eye.

Best regards - Lee Gillie