I'm having a problem with an action stored procedure. It seems to fail to insert data.

Here is the sp:

CREATE PROCEDURE dbo.Stats_SearchTerm_Insert @ID int OUTPUT, @SearchTerm varchar(500) AS select @ID = ID from Stats_SearchTerm where SearchTerm = @SearchTerm if @ID is null begin insert into Stats_SearchTerm(SearchTerm) values(@SearchTerm) select @ID = SCOPE_IDENTITY() end

I've tested it in SQL Server and it works fine, for example the following inserts the data and returns the appropriate ID.

DECLARE @pID int declare @pSearchTerm varchar(500) set @pSearchTerm = 'what a wonderful world test' exec Stats_SearchTerm_Insert @ID = @pID OUTPUT, @SearchTerm = @pSearchTerm

When I run this code it just doesn't insert the data - no errors and no return identity.

Dim SearchTermID As Integer = 0 Dim SearchTerm1 As String = "what a wonderful world" Dim adapter2 As New DataAccessAdapter ActionProcedures.Stats_SearchTerm_Insert(SearchTerm1, SearchTermID, adapter2) adapter2.CloseConnection()

Nada.

The strangest thing is that it does work and returns an ID value if the @pSearchTerm exists in the database. In that case it works fine - it returns the existing ID byref as it should?

How can it be that this code works for existing values but not for inserts?

Any ideas?

Shouldn't

ActionProcedures.Stats_SearchTerm_Insert(SearchTerm1, SearchTermID, adapter2)

be

ActionProcedures.Stats_SearchTerm_Insert(ByRef SearchTerm1, SearchTermID, adapter2)

? (I'm not a VB.NET expert, but if you don't specify ByRef, you're passing Searchterm1 by value IMHO).

And you don't have to pass in an adapter nor close the connection, or do you use other code as well? Like start a transaction?

Nope you don't need to specify - it's declared in the proc.

Not getting the return value is a result of the storedprocedure not executing properly.

It doesn't seem to run when there's an insert. It's pretty crazy stuff - esp since I've tested it (as above) in SQL.

DrM wrote:

Nope you don't need to specify - it's declared in the proc.

Ok, so VB.NET checks the ref on the procedure and then passes by reference? ok.

Not getting the return value is a result of the storedprocedure not executing properly. It doesn't seem to run when there's an insert. It's pretty crazy stuff - esp since I've tested it (as above) in SQL.

When you step into the routine in the debugger (it's reachable code so you can step into it), what actually happens and does the parameter actually get a value?

Your code shows you pass the adapter and also close the connection. My question was: is there more code there which also uses the adapter and for example starts a transaction, you didn't answer that one but it's very important. The reason is that CloseConnection() rollsback a transaction if it's not committed.