0

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

TeddyRuxpin
  • 99
  • 1
  • 12
  • I hope that the different names for the SP and the different quantity of parameters are not the problem... – gbianchi Oct 04 '11 at 17:18
  • it's calling the correct stored proc "UsersInsert2". as a test I changed the name of one of the parameters and it failed. i've also since removed the size. (I put that in after an initial search) – TeddyRuxpin Oct 04 '11 at 17:35
  • Don't remove the size. It can affect query plan caching negatively. – HardCode Oct 04 '11 at 17:51
  • Not a problem. I learned the hard way :) – HardCode Oct 05 '11 at 20:28
  • So still having this issue. the reason for the discrepency in the EXEC was I was changing some of the parameter names after i posted. I guess my question is. Should the values that are NVarChar or VarChar be show inside single quotes? i.e @ID='ruxtest7' ? – TeddyRuxpin Oct 06 '11 at 15:36

1 Answers1

0

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
TeddyRuxpin
  • 99
  • 1
  • 12