Re: Too many args when running a stored procedure

MK,

Perhaps you have a duplicate definition of "AddAJob" in the master database schema. I noticed that you were not specifying the
schema.owner.spname in the .CommandText. Try

SqlCommand1.CommandText = "Jobs.dbo.AddAJob"

and see if you get the expected results. As I stated, I replicated your schema and SP in my system and had it working using the
code I posted.

--
Al Reid


"M K" <ski_freak@xxxxxxxxx> wrote in message news:HTBWe.48976$Jp.793104@xxxxxxxxxxxxxxxxxxxxxxxxxxx
> 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
> >> >
> >> >
> >>
> >>
> >
> >
>
>


.

Relevant Pages

  • RE: Event ID 447
    ... Please made a backup for Exchange database first. ... Please run the eseutil /mh command to see if the mailbox store is clean ... Microsoft CSS Online Newsgroup Support ...
    (microsoft.public.windows.server.sbs)
  • Re: Sharepoint Database falling over event 1000
    ... Microsoft Small Business Server Support ... > I Have Just run the SQL command from the command Line, ... > affected)1> exitDoes this say the database is Ok?If so why since we loaded ... > I/O operation has been aborted because ofeither a thread exit or an ...
    (microsoft.public.windows.server.sbs)
  • Re: Please Urgent - Update Data Source Problem ???
    ... then there's nothing to submit to the database. ... values that are only assigned by the DB once the update command is executed. ... > the connection. ... > schedule, and they can update the existing schedule, add new schedule ...
    (microsoft.public.dotnet.framework.compactframework)
  • Re: How to handle concurrency issue with better performance?
    ... Hitchhiker's Guide to Visual Studio and SQL Server ... that have been modified since they were pulled from the database. ... select command). ... I create stored procedure for the update command and assign ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: SQLs
    ... the database ... 'Set an active connection to the Connection object using ... 'Create an ADO recordset object ... 'Initialise the strSQL variable with an SQL statement to ...
    (microsoft.public.frontpage.programming)