Using stored proc to insert data to mssql database *Resolved*

[wild_bill]

Here is the routing I use:

Public Sub InsertData(ByRef SQL As String, ByRef parameters() As InputParameter)
        '1. Create a connection
        Dim myConnection As New SqlClient.SqlConnection(GetConnection)
 
        '2. Create the command object, passing in the SQL string or stored proc name
        Dim sqcmd As New SqlClient.SqlCommand(SQL, myConnection)
        sqcmd.Connection = myConnection
        sqcmd.CommandType = CommandType.StoredProcedure
 
        '3. Add parameters as needed
        If Not parameters Is Nothing Then
            For Each parm As InputParameter In parameters
                sqcmd.Parameters.Add(New SqlClient.SqlParameter(parm.Name, parm.Type, parm.Length)).Value = parm.Value
            Next
        End If
 
        'Execute Stored Proc
        myConnection.Open()
        sqcmd.ExecuteNonQuery()
        myConnection.Close()
 
    End Sub

Here's the exception: Exception Details: System.Data.SqlClient.SqlException: String or binary data would be truncated. The statement has been terminated.

It seems very straight forward, but I've rechecked everthing serveral times, and none of my values are larger than the stored procedure is expecting. I'm stumped, am I just doing something very wrong here?

[sevenhalo]

They may not be bigger than what your sproc is expecting, but what about your table itself?

[jmcilhinney]

Sounds like one of your parameters is not the correct length for the data it is trying to pass.

Also, it's not going to hurt but you're setting the Connection for the Command twice: once in the constructor and then explicitly afterwards.

[mendhak]

Must be the tables, as they said... try to compare the length of the data being passed with the size of the column in the table in the database.

[wild_bill]

It looks like a permissions issue. I went through query analyser and executed the proc under my user id, all went well. I logged in under the id my web form uses, and the proc through the error. All input parameters are identical. Go figure.

[wild_bill]

The user id automatically gets inserted into a field in the table. It slipped under my radar because it dynamically grabs the id, and I don't have to pass it as a parameter. This is also the reason why I had no problems executing the sp logged in as myself, and not the web user.