Re: Too many args when running a stored procedure

Tech-Archive recommends: Speed Up your PC by fixing your registry

Al thanks for the great information. I have the SQLCommand And
SQLConnection objects due to the fact when i created the SP i dragged it to
the form which created both objects for me

I thought there was a Null object but when i put in Null it didnt work
thanks for correcting my use of DBNull

I have tried the @MyDate parm this way as well and still get the same error.
I have tried so many things i cant remember what i have tried or not but it
always results in the same error. I even tried just inserting just the
primary key and still the same error, ive even put the parameters in code in
the same order as below.
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, GetDate(), @Location,
@Skills, @Agency, @CloseDate, @Rate)

RETURN





"Al Reid" <areidjr@xxxxxxxxxxxxxxxx> wrote in message
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
>> >
>> >
>>
>>
>
>


.