Search code examples
vb.netsqlparameter

Sql paramters appear to not add in the single quote for strings(commit transaction issue)


I wasn't sure exactly how to phrase the question Title.

I have this block of code that sets up my sql parameters for a stored proc that does an insert.

 Dim sproc As StoredProcedure = New StoredProcedure("UsersInsert2", DataAccessConfiguration)
    sproc.AddInput("@ID", SqlDbType.NVarChar, 10, entity.UserId)
    sproc.AddInput("@PCode", SqlDbType.SmallInt, entity.PriviledgeCode)
    sproc.AddInput("@Pwd", SqlDbType.NVarChar, 10, entity.Password.ToString())
    sproc.AddInput("@Lang", SqlDbType.NVarChar, 1, entity.DefaultLanguage)
    sproc.AddInput("@Name", SqlDbType.NVarChar, 40, entity.UserName)
    sproc.AddInput("@Notice", SqlDbType.TinyInt, entity.SaveNotice)
    sproc.AddInput("@CreatedBy", SqlDbType.VarChar, 50,CurrentUserCredentials.UserName)

I've tested the stored proc in SSMS and it works. The problem is when I try to call it from the application. it fails. the @@rowcount = -1. I've tried returning an error code from the db... no dice. keeps coming back as -1

what is going to get executed looks like this

sproc = {EXEC UsersInsert2 @ID=ruxtest7, @PCode=0, @Pwd=1234, @Lang=E, @Name=ruxpint, @Notice=1, @CreatedBy=ruxpint}

any idea what the issue is? I've re-used this code several times. only difference being I'm using NVarChar and it's vb.net.

thanks.

TR


Solution

  • So turns out my stored proc call was fine. in my base class where I perform the transaction, I forgot to commit it. D'oh! (This may help someone in the future)

                Try
                    x = command.ExecuteNonQuery()
                    transaction.Commit() <- I was missing this line!!!
                Catch ex As Exception
                    transaction.Rollback()
                Finally
                    If conn.State And ConnectionState.Closed = 0 Then
                        conn.Close()
                    End If
                End Try