1

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

1
  • If your parameter is of type varchar(50) then you should provide a proper default value - either NULL or a string value (in single quotes) - not a number (0) ..... Commented May 3, 2020 at 11:51

1 Answer 1

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

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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.