How do I program multiple parameters into code for a SQL Server stored procedure

Question

First, apologies if my question has been answered, although I've looked. Second, I'm a rank beginner, and am following an SQL guide that is not two-way : they don't answer queries. I've looked everywhere, but cannot find an answer that I understand.

I have a .mdf database table with 13 columns. I have created a stored procedure that works from outside Visual Studio by calling the exec function.

exec [SeeFirst] [Peter] 

This returns the row containing Peter in the FirstName column.

The SQL Server stored procedure is :

CREATE PROCEDURE [dbo].[SeeFirst]
    @param1 varchar(50) = 0
AS
    SELECT *
    FROM Mentor
    WHERE Mentor.FirstName = @param1 ;

    RETURN 0

and is called like this:

Dim conString As String
conString = My.Settings.Mentor2020DB
Dim con As New SqlClient.SqlConnection(conString)
con.Open()

Dim param

Select Case ci
    Case 1
        Dim cmd As New SqlClient.SqlCommand("SeeFirst", con)
        param = Trim(txtSearch.Text)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.Add(New SqlClient.SqlParameter("@param1", param))
        Dim adapter As New SqlClient.SqlDataAdapter(cmd)
        Dim dataTable As New DataTable()
        adapter.Fill(dataTable)
        DataGridView1.DataSource = Binder
        Binder.DataSource = dataTable
     Case = 2

& etc.

I show all the above to give the full story of how I am trying to work.

Now to my problem: I have a stored procedure with 12 parameters that works in the stored procedure creation area, and inserts a new record into the tabe.

If I execute

exec [AddNew] [param1,param2, .... param12] 

everything works well. So, I am happy with the stored procedure, which is basically similar to the above [SeeFirst] but with more parameters.

The stored procedure which works is :

CREATE PROCEDURE [dbo].[AddNew]
    @FirstName varchar(50) = 0,
    @LastName varchar(50),
    @Company varchar(50),
    @Address varchar(50),
    @TDate varchar(50),
    @TLength int,
    @Miles int,
    @Radius int,
    @Band int,
    @FileName varchar(50),
    @TResult varchar(50),
    @TNotes varchar(2500)
AS
    INSERT INTO Mentor
    VALUES (@FirstName, @LastName, @Company, @Address,
            @TDate, @TLength, @Miles, @Radius, @Band, @FileName, @TResult, @TNotes)
    RETURN 0

But, I cannot get my program to accept these multiple parameters from my program code, though.

Private Sub mnuMainAdd_Click(sender As Object, e As EventArgs) Handles mnuMainAdd.Click
' Trying to add a new record

Dim param(0 To 12)

Dim conString As String
conString = My.Settings.Mentor2020DB
Dim con As New SqlClient.SqlConnection(conString)
con.Open()

param(0) = " "
param(1) = txtFirstName.Text
param(2) = txtLastName.Text
param(3) = txtCompany.Text
param(4) = txtAddress.Text
param(5) = txtTDate.Text
param(6) = txtTLength.Text
param(7) = txtMiles.Text
param(8) = txtRadius.Text
param(9) = txtBand.Text
param(10) = txtFileName.Text
param(11) = txtTResult.Text
param(12) = txtTNotes.Text

Dim cmd As New SqlClient.SqlCommand("AddNew", con)
cmd.CommandType = CommandType.StoredProcedure
Dim access
access = "@FirstName", param(1), "@LastName", param(2), "@Company", param(3), "@Address", param(4), 
"@param6", param(6), "@param7", param(7), "@param8", param(8), "@param9", param(9), "@param10", 
"@param(10), "@param11", param(11), "@param12", param(12)
cmd.Parameters.Add(New SqlClient.SqlParameter(access)
Dim adapter As New SqlClient.SqlDataAdapter(cmd)
Dim dataTable As New DataTable()
adapter.Fill(dataTable)
DataGridView1.DataSource = Binder
Binder.DataSource = dataTable

If I've given too much information, then I'm sorry. But I read so often, here, that an solution cannot be given because there is a lack of information.

Thanking you one and all for reading through this, and I'm hoping for advice leading to a solution.

Best regards,

Pete F

Answer 1

I'm a c# person, so check whether there is syntax error for vb.net You need to add parameters each separately

...
cmd.Parameters.Append .CreateParameter("@Parameter1",adInteger,adParamInput,,1)
cmd.Parameters.Append .CreateParameter("@Parameter2",adVarChar,adParamInput,100,"Up to 100 chars")
cmd.Parameters.Append .CreateParameter("@Parameter3",adBoolean,adParamInput,,true)
...

Here is a similar situation add array of parameters