Debugging a vb.net SQL command

Question

I am attempting to call SQL stored procedure that does an INSERT. However, when I walkthrough my vb.net code, I get a message saying "Procedure or function sp_InsertARPlanner has too many arguments specified."

I have double checked, but the SQL string in VB has the same # of params as in the stored procedure.

Any ideas how I can debug this?

Update

    SQLCmd.CommandText = "sp_InsertARPlanner"
    SQLCmd.CommandType = CommandType.StoredProcedure

SQLCmd.Parameters.AddWithValue("@Origin", Trim(txtOrigin.Text))
SQLCmd.Parameters.AddWithValue("@Destination", Trim(txtDest.Text))
SQLCmd.Parameters.AddWithValue("@Miles", iMiles)
SQLCmd.Parameters.AddWithValue("@Rate", iAvgRateperMile)
SQLCmd.Parameters.AddWithValue("@MinCost", dMinCost)
SQLCmd.Parameters.AddWithValue("@Zone", sZone)
SQLCmd.Parameters.AddWithValue("@LaneHaulCost", dLaneHaulCost)
SQLCmd.Parameters.AddWithValue("@TotalCost", dTotalCost)              
SQLCmd.Parameters.AddWithValue("@TotalWithSurch", dTotalWithSurch)
SQLCmd.Parameters.AddWithValue("@AvgTypeRate", sAvgTypeRate)    
SQLCmd.Parameters.AddWithValue("@AvgLoads", sAvgLoads)          
SQLCmd.Parameters.AddWithValue("@FuelLevel", dFuelPercent)    
SQLCmd.Parameters.AddWithValue("@Fuel", dFuelAmount)      
SQLCmd.Parameters.AddWithValue("@AverageRateAmount", dAverageRateAmount)
SQLCmd.Parameters.AddWithValue("@Floor", decFloor)
SQLCmd.Parameters.AddWithValue("@RainBulkRate", decBulkRate)
SQLCmd.Parameters.AddWithValue("@RateComments", txtRateDesc..Text)
SQLCmd.Parameters.AddWithValue("@PremiumField", txtPremium.Text)
SQLCmd.Parameters.AddWithValue("@EquipCategory", Trim(cboEquipType.Text))
SQLCmd.Parameters.AddWithValue("@UpdateDate", DateTime.Now)
SQLCmd.Parameters.AddWithValue("@FreightDesc", "txtFreightDesc.Text")

Try
    SQLCmd.ExecuteNonQuery()
Catch ex As Exception
    MsgBox(ex.Message)
    SQLCon.Close()
    SQLCmd.Parameters.Clear()
    Exit Sub
End Try

ALTER PROCEDURE [dbo].[sp_InsertARPlanner]
    (@Origin nvarchar(150)
           ,@Destination nvarchar(150)
           ,@Miles nvarchar(50)
           ,@Rate nvarchar(5)
           ,@MinCost decimal(5,2)
           ,@Zone varchar(3)
           ,@LaneHaulCost decimal(5, 2)
           ,@TotalCost decimal(5, 2)
           ,@TotalWithSurch decimal(5, 2)
           ,@AvgTypeRate varchar(50)
           ,@AvgLoads varchar(4)
           ,@FuelPercent decimal(5,2)
           ,@FuelAmount decimal(5,2)
           ,@AverageRateAmount decimal(5,2)
           ,@Floor decimal(5, 2)
           ,@RainBulkRate decimal(5, 2)
           ,@RateComments nvarchar(50)
           ,@PremiumField nvarchar(50)
           ,@EquipCategory nvarchar(50)
           ,@UpdateDate datetime
           ,@FreightDesc nvarchar(50))

If you can post the code and the SQL procedure, maybe someone here can find out that extra argument for you ;) — Jude Cooray
– Jude Cooray, Commented Nov 23, 2010 at 15:31
Are you using parametered queries or a straight string INSERT? — Tom
– Tom, Commented Nov 23, 2010 at 15:37

kemiller2002 · Accepted Answer · 2010-11-23 15:33:49Z

1

Try this:

Don't start your stored procedure name with sp_ That stands for system procedure in sql server, and will cause sql server to do extra work looking for your procedure (this isn't your problem though, but it's good practice).
Take the procedure string that the program has created and paste it into the sql server management studio window, to make sure that it works there. If not then you know where the issue is.
If so, make sure you are hitting the db you think your are. If you have multiple environments it maybe that the database you are hitting isn't the one that has the correct number of parameters.

answered Nov 23, 2010 at 15:33

kemiller2002

116k28 gold badges201 silver badges255 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user279521 Over a year ago

crazy resolution, but the cmd object had not been closed in a prior routine, and so was point at another stored proc.

DOK · Accepted Answer · 2010-11-23 15:34:20Z

0

When stepping through your code, there will be a point where the stored procedure and its parameters have been populated, perhaps in a SqlCommand object. You can capture that sproc call at a breakpoint. Then, copy it to your query tool, call EXEC and paste the sproc call in. Then run it. That way, you will be executing the exact stored procedure call that your code is sending.

If you provide more details of your code, we can be more specific on exactly how to capture the sproc call.

answered Nov 23, 2010 at 15:34

DOK

32.8k8 gold badges64 silver badges93 bronze badges

Collectives™ on Stack Overflow

Debugging a vb.net SQL command

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related