Re: Too many args when running a stored procedure

Tech-Archive recommends: Fix windows errors by optimizing your registry

Cor,
I have tried that as well. I have 2 objects command and connection that
i dragged from server explorer (the SP) to the form which creates the 2
objects and fills in all the fields (ie connection string, sp name etc)

I have tried to insert just the 1 primary key and it still has the same
error. I only push the button 1time and wait


"Cor Ligthert [MVP]" <notmyfirstname@xxxxxxxxx> wrote in message
news:%23WiN9PtuFHA.2948@xxxxxxxxxxxxxxxxxxxxxxx
> Al,
>
> I am asking if he is stepping in a simple error and he does not want to
> answer if he has used it like this.
>
> \\\
> public sqlcommand1 as new sqlcommand
> Button Click
> SqlCommand1.CommandType = CommandType.StoredProcedure
> SqlCommand1.CommandText = "Jobs.dbo.AddJob"
> SqlCommand1.Parameters.Add("@JobNumber", txtJobRefNumber.Text)
> SqlCommand1.ExecuteNonQuery()
> ///
>
> With the second buttonclick does this gives the error "Procedure or
> function AddJob has too many arguments specified at ..."
>
> Cor
>
>
> "Al Reid" <areidjr@xxxxxxxxxxxxxxxx> schreef in bericht
> news:%23QL%23RnsuFHA.1132@xxxxxxxxxxxxxxxxxxxxxxx
>> Ok, let me take a shot at this. To start with, I created your database
>> structure and SP in MSSQL.
>>
>> First, you don't show that you are associating the connection object with
>> the command. Have you done that? Secondly, your
>> procedure has 11 parameters and your code has 10, the "@MyDate" is
>> missing. If you aren't going to use the parameter, remove it
>> from the SP parameters and replace the "@MyDate" with GetDate in the
>> Values() line.
>>
>> In order to get your code to work I had to create a connection object and
>> connection string before opening it. I had too create a
>> new connection object. After opening the connection, I had to associate
>> it with the command object. After that, I had to add a
>> Parameter object for the @MyDate parameter. Furthermore, I had to
>> convert the date and money value (*.Text) to the appropriate data
>> types. Having done that, I was able to insert data into the table with
>> the following code:
>>
>> /////////////////
>> Public Sub TestIt()
>>
>> Dim SqlConnection1 As OleDb.OleDbConnection
>> Dim SqlCommand1 As New OleDb.OleDbCommand
>>
>> Try
>> SqlConnection1 = Connect("sa", "gobblygoop",
>> dbtDatabase.dbtSqlServer, , "(local)")
>> ' Connect is a general purpose function for connecting to various
>> database types
>> ' MsAbbess, MSSQL, Oracle, CSV, Excel, ...
>>
>>
>> SqlCommand1.CommandType = CommandType.StoredProcedure
>>
>> SqlCommand1.CommandText = "Jobs.dbo.AddAJob"
>>
>> SqlCommand1.Connection = SqlConnection1 '<< Added this line
>>
>> SqlCommand1.Parameters.Add("@JobName", "Test Job")
>>
>> SqlCommand1.Parameters.Add("@JobNumber", "123456")
>>
>> SqlCommand1.Parameters.Add("@Contact", "Al Reid")
>>
>> SqlCommand1.Parameters.Add("@Phone", "4242502116")
>>
>> SqlCommand1.Parameters.Add("@Fax", "4022572410")
>>
>> SqlCommand1.Parameters.Add("@MyDate", CDate("09/17/2005")) '<<<
>> Added this line
>>
>> SqlCommand1.Parameters.Add("@Location", "Here")
>>
>> SqlCommand1.Parameters.Add("@Skills", "DotNet")
>>
>> SqlCommand1.Parameters.Add("@Agency", "TechMe Corp")
>>
>> 'optional fields
>>
>> SqlCommand1.Parameters.Add("@CloseDate", CDate("09/17/2005")) '<<
>> Converted to a date type
>>
>> SqlCommand1.Parameters.Add("@Rate", CType("65.50", Decimal)) '<<
>> Converted to a numeric type
>>
>> SqlCommand1.ExecuteNonQuery()
>>
>> Catch ex As Exception
>>
>> MsgBox("Could not add record" & Chr(10) & ex.ToString())
>>
>> Finally
>>
>> SqlConnection1.Close()
>>
>> End Try
>>
>> End Sub
>> \\\\\\\\\\\\\\\\\\\\
>>
>> Also, I question the code that checks if a TextBox.Text is an empty
>> string:
>>
>>> If txtFax.Text <> "" Then
>>> SqlCommand1.Parameters.Add("@Fax", txtFax.Text)
>>> Else
>>> SqlCommand1.Parameters.Add("@Fax", "")
>>> End If
>>
>> This results in the same parameter data being sent to the SP as
>>
>> SqlCommand1.Parameters.Add("@Fax", txtFax.Text)
>>
>> Did you mean to substitute DbNull.Value for the "" in the else clause?
>>
>> In any event, I was able to get your SL and modified code to function
>> properly and never got an error indicating "Too many
>> arguments."
>>
>>
>> I hope this helps.
>>
>> --
>> Al Reid
>>
>> "M K" <ski_freak@xxxxxxxxx> wrote in message
>> news:ldyWe.44044$SL.744831@xxxxxxxxxxxxxxxxxxxxxxxxxxx
>>> Did you see my first post?
>>> I just tried creating a new SP within Visual Studio and I get the same
>>> thing
>>> Here is all the code that I am trying to run. It does work fine if i
>>> run
>>> the SP in Server Explorer and it updates
>>> the table just fine.
>>>
>>> SP:
>>> ALTER PROCEDURE dbo.AddAJob
>>>
>>> (
>>>
>>> @JobName varchar(50),
>>>
>>> @JobNumber varchar(30),
>>>
>>> @Contact varchar(40),
>>>
>>> @Phone char(10),
>>>
>>> @Fax char(10),
>>>
>>> @MyDate datetime = GetDate,
>>>
>>> @Location varchar(40),
>>>
>>> @Skills text,
>>>
>>> @Agency varchar(40),
>>>
>>> @CloseDate datetime,
>>>
>>> @Rate money
>>>
>>> )
>>>
>>> AS
>>>
>>> INSERT INTO [Jobs].[dbo].[Job]
>>>
>>> (JobName, JobRefNum, Contact, Phone, Fax, DateApplied, JobLocation,
>>> Skills,
>>> Agency, CloseDate, Rate)
>>>
>>> VALUES
>>>
>>> (@JobName, @JobNumber, @Contact, @Phone, @Fax, @MyDate, @Location,
>>> @Skills,
>>> @Agency, @CloseDate, @Rate)
>>>
>>> RETURN
>>>
>>>
>>> Code:
>>>
>>> Try
>>> SqlConnection1.Open()
>>>
>>> 'mandatory fields
>>>
>>> SqlCommand1.CommandType = CommandType.StoredProcedure
>>>
>>> SqlCommand1.CommandText = "AddAJob"
>>>
>>> SqlCommand1.Parameters.Add("@JobName", txtJobName.Text)
>>>
>>> SqlCommand1.Parameters.Add("@JobNumber", txtJobRefNumber.Text)
>>>
>>> SqlCommand1.Parameters.Add("@Contact", txtContact.Text)
>>>
>>> SqlCommand1.Parameters.Add("@Phone", txtPhone.Text)
>>>
>>> If txtFax.Text <> "" Then
>>>
>>> SqlCommand1.Parameters.Add("@Fax", txtFax.Text)
>>>
>>> Else
>>>
>>> SqlCommand1.Parameters.Add("@Fax", "")
>>>
>>> End If
>>>
>>> SqlCommand1.Parameters.Add("@Location", txtLocation.Text)
>>>
>>> SqlCommand1.Parameters.Add("@Skills", txtSkills.Text)
>>>
>>> SqlCommand1.Parameters.Add("@Agency", txtAgency.Text)
>>>
>>> 'optional fields
>>>
>>> If calCloseDate.Text <> "" Then
>>>
>>> SqlCommand1.Parameters.Add("@CloseDate", Format(calCloseDate.Text,
>>> "General
>>> Date"))
>>>
>>> Else
>>>
>>> SqlCommand1.Parameters.Add("@CloseDate", "")
>>>
>>> End If
>>>
>>> If txtRate.Text <> "" Then
>>>
>>> SqlCommand1.Parameters.Add("@Rate", Format(txtRate.Text, "Currency"))
>>>
>>> Else
>>>
>>> SqlCommand1.Parameters.Add("@Rate", "")
>>>
>>> End If
>>>
>>> SqlCommand1.ExecuteNonQuery()
>>>
>>> Catch ex As Exception
>>>
>>> MsgBox("Could not add record" & Chr(10) & ex.ToString())
>>>
>>> Exit Function
>>>
>>> Finally
>>>
>>> SqlConnection1.Close()
>>>
>>> End Try
>>>
>>>
>>> "Cor Ligthert [MVP]" <notmyfirstname@xxxxxxxxx> wrote in message
>>> news:OV$jVjouFHA.3000@xxxxxxxxxxxxxxxxxxxxxxx
>>> > MK,
>>> >
>>> > Are you maybe adding the parameter to the parameterlist more times,
>>> > this
>>> > is not unlikely because normally we would have seen an instrucktion as
>>> >
>>> > \\\
>>> > SqlCommand1.Parameters("@JobNumber").value = xtJobRefNumber.Text
>>> > SqlCommand1.ExecuteNonQuery()
>>> > ///
>>> > I hope this helps,
>>> >
>>> > Cor
>>> >
>>> >
>>>
>>>
>>
>>
>
>


.

Quantcast