CODEWITHSUNDEEP
sqlvb.netstored-proceduresado.netparameters
Matt Stewart
Matt Stewart

Reputation: 322

Parameterized store procedures in VB.net

I'm getting an error when the 'insertcommand' is executed telling me that I'm missing the parameters for the stored procedure. Do I need to put the parameter names in the Sql statement after the procedure as if I were calling it in SQL? I saw an example online that just added the parameters like I have here, but this doesn't work? I also put the sql state for the stored procedure below the 'AddRepair Sub'

Public Shared Sub AddRepair(ByVal repair As ClubRepair)

    Dim conn As SqlConnection = ClubRentalsDB.getconnection
    Dim insertcommand As New SqlCommand("AddRepair", conn)

    insertcommand.Parameters.AddWithValue("@Name", repair.Name)
    insertcommand.Parameters.AddWithValue("@ID", repair.MemberID)
    insertcommand.Parameters.AddWithValue("@Phone", repair.PhoneNumber)
    insertcommand.Parameters.AddWithValue("@Email", repair.Email)
    insertcommand.Parameters.AddWithValue("@Work", repair.WorkToBeDone)
    insertcommand.Parameters.AddWithValue("@Specification", repair.Specification)
    insertcommand.Parameters.AddWithValue("@SoonestDate", repair.SoonestCompletion)
    insertcommand.Parameters.AddWithValue("@PromisedDate", repair.DatePromised)
    insertcommand.Parameters.AddWithValue("@ClubType", repair.TypeOfClub)
    insertcommand.Parameters.AddWithValue("@GripType", repair.TypeOfGrip)
    insertcommand.Parameters.AddWithValue("@NumOfClubs", repair.NumOfClubs)
    insertcommand.Parameters.AddWithValue("@SpecialInstructions", repair.SpecialInstructions)

    Try
        conn.Open()
        insertcommand.ExecuteReader()
    Catch ex As Exception
        MessageBox.Show(messageBad & ex.ToString)
    Finally
        conn.Close()
    End Try   
End Sub   

USE [ClubRentals]   
GO
 SET ANSI_NULLS ON
GO  
 SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE  [dbo].[AddRepair] (@Name Varchar(50), @ID varchar(20),
  @Phone varchar(50),@Email varchar(50), @Work varchar(20),@Specification varchar(MAX), 
  @SoonestDate date, @PromisedDate Date, @ClubType varchar(50), @Griptype varchar(50), 
  @NumOfClubs int, @SpecialInstructions varchar(MAX)) as

Insert into ClubRepair(Member_Name,Member_ID,Phone,Email,WorkToBeDone,Specification,
     SoonestPossibleCompletion,DatePromised,TypeOfClub, TypeOfGrips ,NumOfClubs,   
     SpecialInstructions)    

values(@Name, @ID, @Phone, @Email, @Work, @Specification,    
       @SoonestDate, @PromisedDate, @ClubType, @GripType,    
       @NumOfClubs,@SpecialInstructions)

GO

Upvotes: 0

Views: 2441

Answers (3)

Jeff Paulsen
Jeff Paulsen

Reputation: 2142

Confirm that every parameter value you are setting is not Nothing. Parameters with no value can cause the missing parameter error. There's a two-argument version of If() that helps:

insertcommand.Parameters.AddWithValue("@Specification", If(repair.Specification, ""))

this will return repair.Specification if it is not Nothing, and "" otherwise.

also, you should consider using Parameters.Add().Value() instead of .AddWithValue(), like so:

insertcommand.Parameters.Add("@ClubType", SqlDbType.VarChar, 50).Value = If(repair.TypeOfClub, "")

This is really useful when you're working with types other than string.

Upvotes: 2

Scott Bruns
Scott Bruns

Reputation: 1981

Try setting sqlcommand commandtype to storedprocedure.

Upvotes: 1

Fredou
Fredou

Reputation: 20100

make sure to respect case sensitivity, for an example 

    insertcommand.Parameters.AddWithValue("@GripType", repair.TypeOfGrip)

is wrong

     insertcommand.Parameters.AddWithValue("@Griptype", repair.TypeOfGrip)

is right

Upvotes: 0

Related Questions