Heres my SP: ( i am trying to add more than 1 field but get the same error
no matter how many i try to add, i thought i would try to insert the primary
key only and work up from there but the primary key insert didnt work
either. I can run the SP manually in Visual Studio .NET and it works. When
i try to do this it doesn't.
Any help is greatly appreciated... newbie here as well so please be gentle
:)
ALTER PROCEDURE dbo.AddJob
@JobNumber varchar(30)
AS
INSERT INTO [Jobs].[dbo].[Job]
(JobRefNum)
VALUES
(@JobNumber)
Code:
SqlCommand1.CommandType = CommandType.StoredProcedure
SqlCommand1.CommandText = "Jobs.dbo.AddJob"
SqlCommand1.Parameters.Add("@JobNumber", txtJobRefNumber.Text)
SqlCommand1.ExecuteNonQuery()
22  4240 
M K wrote: Heres my SP: ( i am trying to add more than 1 field but get the same error
no matter how many i try to add, i thought i would try to insert the primary
key only and work up from there but the primary key insert didnt work
either. I can run the SP manually in Visual Studio .NET and it works. When
i try to do this it doesn't.
Any help is greatly appreciated... newbie here as well so please be gentle
:)
ALTER PROCEDURE dbo.AddJob
@JobNumber varchar(30)
AS
INSERT INTO [Jobs].[dbo].[Job]
(JobRefNum)
VALUES
(@JobNumber)
Code:
SqlCommand1.CommandType = CommandType.StoredProcedure
SqlCommand1.CommandText = "Jobs.dbo.AddJob"
SqlCommand1.Parameters.Add("@JobNumber", txtJobRefNumber.Text)
SqlCommand1.ExecuteNonQuery()
ALTER PROCEDURE dbo.AddJob
(
@JobNumber varchar(30)
)
AS
INSERT INTO [Jobs].[dbo].[Job]
(JobRefNum)
VALUES
(@JobNumber)
Same problem
I copied your code and commented out everything else..
Mark
"Chris" <no@spam.com> wrote in message
news:e6**************@TK2MSFTNGP14.phx.gbl... M K wrote: Heres my SP: ( i am trying to add more than 1 field but get the same
error no matter how many i try to add, i thought i would try to insert
the primary key only and work up from there but the primary key insert
didnt work either. I can run the SP manually in Visual Studio .NET and
it works. When i try to do this it doesn't.
Any help is greatly appreciated... newbie here as well so please be
gentle :)
ALTER PROCEDURE dbo.AddJob
@JobNumber varchar(30)
AS
INSERT INTO [Jobs].[dbo].[Job]
(JobRefNum)
VALUES
(@JobNumber)
Code:
SqlCommand1.CommandType = CommandType.StoredProcedure
SqlCommand1.CommandText = "Jobs.dbo.AddJob"
SqlCommand1.Parameters.Add("@JobNumber", txtJobRefNumber.Text)
SqlCommand1.ExecuteNonQuery()
ALTER PROCEDURE dbo.AddJob
(
@JobNumber varchar(30)
)
AS
INSERT INTO [Jobs].[dbo].[Job]
(JobRefNum)
VALUES
(@JobNumber)
What's the exact error message text you're receiving? Can we assume that
the SP is in the Jobs database as well? A lot of possible problems come to
mind, but without the exact code and DDL it's all guesswork.
"M K" <sk*******@yahoo.com> wrote in message
news:qL*******************@twister.southeast.rr.co m... Heres my SP: ( i am trying to add more than 1 field but get the same error
no matter how many i try to add, i thought i would try to insert the
primary key only and work up from there but the primary key insert didnt
work either. I can run the SP manually in Visual Studio .NET and it
works. When i try to do this it doesn't.
Any help is greatly appreciated... newbie here as well so please be gentle
:)
ALTER PROCEDURE dbo.AddJob
@JobNumber varchar(30)
AS
INSERT INTO [Jobs].[dbo].[Job]
(JobRefNum)
VALUES
(@JobNumber)
Code:
SqlCommand1.CommandType = CommandType.StoredProcedure
SqlCommand1.CommandText = "Jobs.dbo.AddJob"
SqlCommand1.Parameters.Add("@JobNumber", txtJobRefNumber.Text)
SqlCommand1.ExecuteNonQuery()
System.Data.SqlClient.SqlException: Procedure or function AddJob has too
many arguments specified at
..
..(partially omitted)
..
ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean
returnStream)
..
..(partially omitted)
..
at JobBank.frmJobDetails.SaveToDb(String type) in D:. . .\JobDetails.vb:line
417
"Michael C#" <xy*@abcdef.com> wrote in message
news:s_*****************@fe12.lga... What's the exact error message text you're receiving? Can we assume that
the SP is in the Jobs database as well? A lot of possible problems come
to mind, but without the exact code and DDL it's all guesswork.
"M K" <sk*******@yahoo.com> wrote in message
news:qL*******************@twister.southeast.rr.co m... Heres my SP: ( i am trying to add more than 1 field but get the same
error no matter how many i try to add, i thought i would try to insert
the primary key only and work up from there but the primary key insert
didnt work either. I can run the SP manually in Visual Studio .NET and
it works. When i try to do this it doesn't.
Any help is greatly appreciated... newbie here as well so please be
gentle :)
ALTER PROCEDURE dbo.AddJob
@JobNumber varchar(30)
AS
INSERT INTO [Jobs].[dbo].[Job]
(JobRefNum)
VALUES
(@JobNumber)
Code:
SqlCommand1.CommandType = CommandType.StoredProcedure
SqlCommand1.CommandText = "Jobs.dbo.AddJob"
SqlCommand1.Parameters.Add("@JobNumber", txtJobRefNumber.Text)
SqlCommand1.ExecuteNonQuery()
Yes SP is in the jobs db. it does run when i execute the SP from server
explorer
"Michael C#" <xy*@abcdef.com> wrote in message
news:s_*****************@fe12.lga... What's the exact error message text you're receiving? Can we assume that
the SP is in the Jobs database as well? A lot of possible problems come
to mind, but without the exact code and DDL it's all guesswork.
"M K" <sk*******@yahoo.com> wrote in message
news:qL*******************@twister.southeast.rr.co m... Heres my SP: ( i am trying to add more than 1 field but get the same
error no matter how many i try to add, i thought i would try to insert
the primary key only and work up from there but the primary key insert
didnt work either. I can run the SP manually in Visual Studio .NET and
it works. When i try to do this it doesn't.
Any help is greatly appreciated... newbie here as well so please be
gentle :)
ALTER PROCEDURE dbo.AddJob
@JobNumber varchar(30)
AS
INSERT INTO [Jobs].[dbo].[Job]
(JobRefNum)
VALUES
(@JobNumber)
Code:
SqlCommand1.CommandType = CommandType.StoredProcedure
SqlCommand1.CommandText = "Jobs.dbo.AddJob"
SqlCommand1.Parameters.Add("@JobNumber", txtJobRefNumber.Text)
SqlCommand1.ExecuteNonQuery()
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
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]" <no************@planet.nl> wrote in message
news:OV**************@TK2MSFTNGP12.phx.gbl... 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
MK,
Did you read my reply?
Now you show the same style of code. Where do you use that code, if that is
repeatedly done, than you will get an error message.
"Too many arguments"
Which has nothing to do with the stored procedure by the way.
Cor
I tried your code as well and got the same error. The code i used i took
straight from the book and just put in my values instead.
Read my post from 9/15 10:11 as it has the exact error
"Cor Ligthert [MVP]" <no************@planet.nl> wrote in message
news:uk**************@TK2MSFTNGP15.phx.gbl... MK,
Did you read my reply?
Now you show the same style of code. Where do you use that code, if that
is repeatedly done, than you will get an error message.
"Too many arguments"
Which has nothing to do with the stored procedure by the way.
Cor
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" <sk*******@yahoo.com> wrote in message news:ld*******************@twister.southeast.rr.co m... 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]" <no************@planet.nl> wrote in message
news:OV**************@TK2MSFTNGP12.phx.gbl... 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
MK,
If you don't want to give further information than it is your case.
Very clever to show on a newsgroup where all people are active to tell
somebody that he must read your post that you sent on your local time 9.15
10:11. Maybe can you copy it yourself next time.
Although I have read it, and it is exact what I am telling you all the time.
Success
Cor
"M K" <sk*******@yahoo.com> schreef in bericht
news:Er*******************@twister.southeast.rr.co m... I tried your code as well and got the same error. The code i used i took
straight from the book and just put in my values instead.
Read my post from 9/15 10:11 as it has the exact error
"Cor Ligthert [MVP]" <no************@planet.nl> wrote in message
news:uk**************@TK2MSFTNGP15.phx.gbl... MK,
Did you read my reply?
Now you show the same style of code. Where do you use that code, if that
is repeatedly done, than you will get an error message.
"Too many arguments"
Which has nothing to do with the stored procedure by the way.
Cor
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" <ar*****@reidDASHhome.com> schreef in bericht
news:%2******************@TK2MSFTNGP10.phx.gbl... 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" <sk*******@yahoo.com> wrote in message
news:ld*******************@twister.southeast.rr.co m... 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]" <no************@planet.nl> wrote in message
news:OV**************@TK2MSFTNGP12.phx.gbl... > 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
>
>
Cor,
Indeed, if the SqlCommand1 object is declared at the class/module level rather than locally scoped, then each subsequent "Button
Click" will append the parameter again and will result in the "Too many arguments were supplied..." exception.
--
Al Reid
"Cor Ligthert [MVP]" <no************@planet.nl> wrote in message news:%2****************@TK2MSFTNGP15.phx.gbl... 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 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" <ar*****@reidDASHhome.com> wrote in message
news:%2******************@TK2MSFTNGP10.phx.gbl... 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" <sk*******@yahoo.com> wrote in message
news:ld*******************@twister.southeast.rr.co m... 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]" <no************@planet.nl> wrote in message
news:OV**************@TK2MSFTNGP12.phx.gbl... > 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
>
>
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]" <no************@planet.nl> wrote in message
news:%2****************@TK2MSFTNGP15.phx.gbl... 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" <ar*****@reidDASHhome.com> schreef in bericht
news:%2******************@TK2MSFTNGP10.phx.gbl... 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" <sk*******@yahoo.com> wrote in message
news:ld*******************@twister.southeast.rr.co m... 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]" <no************@planet.nl> wrote in message
news:OV**************@TK2MSFTNGP12.phx.gbl...
> 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
>
>
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" <sk*******@yahoo.com> wrote in message news:HT*******************@twister.southeast.rr.co m... 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" <ar*****@reidDASHhome.com> wrote in message
news:%2******************@TK2MSFTNGP10.phx.gbl... 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" <sk*******@yahoo.com> wrote in message
news:ld*******************@twister.southeast.rr.co m... 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]" <no************@planet.nl> wrote in message
news:OV**************@TK2MSFTNGP12.phx.gbl...
> 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
>
>
Yup I've tried that too.. lol.. man this is frustrating.. You use the OLE
object where I am trying to use the SQL object. Would that be the problem?
If i cant figure this out im going to make them inline vs stored procedure..
ugggg
"Al Reid" <ar*****@reidDASHhome.com> wrote in message
news:%2****************@TK2MSFTNGP12.phx.gbl... 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" <sk*******@yahoo.com> wrote in message
news:HT*******************@twister.southeast.rr.co m... 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" <ar*****@reidDASHhome.com> wrote in message
news:%2******************@TK2MSFTNGP10.phx.gbl... > 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" <sk*******@yahoo.com> wrote in message
> news:ld*******************@twister.southeast.rr.co m...
>> 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]" <no************@planet.nl> wrote in message
>> news:OV**************@TK2MSFTNGP12.phx.gbl...
>> > 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
>> >
>> >
>>
>>
>
>
i guess theres something about SQLConnection that was messing me up.. heres
what now works.. need to tweek it but at least i got the insert to work..
Thanks a bunch Al!
I created the connection using the data object.. keeps from having to type
in the connection string .. it does it for you..
Again.. thanks!
Mark
Dim SqlCommand2 As New OleDb.OleDbCommand
Try
OleDbConnection1.Open()
SqlCommand2.CommandType = CommandType.StoredProcedure
SqlCommand2.CommandText = "Jobs.dbo.AddAJob"
SqlCommand2.Connection = OleDbConnection1 '<< Added this line
SqlCommand2.Parameters.Add("@JobName", "Test Job")
SqlCommand2.Parameters.Add("@JobNumber", "123456")
SqlCommand2.Parameters.Add("@Contact", "Al Reid")
SqlCommand2.Parameters.Add("@Phone", "4242502116")
SqlCommand2.Parameters.Add("@Fax", "4022572410")
SqlCommand2.Parameters.Add("@Location", "Here")
SqlCommand2.Parameters.Add("@Skills", "DotNet")
SqlCommand2.Parameters.Add("@Agency", "TechMe Corp")
'optional fields
SqlCommand2.Parameters.Add("@CloseDate", CDate("09/17/2005")) '<< Converted
to a date type
SqlCommand2.Parameters.Add("@Rate", CType("65.50", Decimal)) '<< Converted
to a numeric type
SqlCommand2.Parameters.Add("@Email", "myemail")
SqlCommand2.ExecuteNonQuery()
Catch ex As Exception
MsgBox("Could not add record" & Chr(10) & ex.ToString())
Finally
OleDbConnection1.Close()
End Try
End Sub
"Al Reid" <ar*****@reidDASHhome.com> wrote in message
news:%2****************@TK2MSFTNGP12.phx.gbl... 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" <sk*******@yahoo.com> wrote in message
news:HT*******************@twister.southeast.rr.co m... 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" <ar*****@reidDASHhome.com> wrote in message
news:%2******************@TK2MSFTNGP10.phx.gbl... > 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" <sk*******@yahoo.com> wrote in message
> news:ld*******************@twister.southeast.rr.co m...
>> 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]" <no************@planet.nl> wrote in message
>> news:OV**************@TK2MSFTNGP12.phx.gbl...
>> > 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
>> >
>> >
>>
>>
>
>
I would not expect it to make a difference. However, I don't have VB.Net on
the machine I am at now, so I can't try it (I won't have access to my dev
machine until Monday).
Have you tried to declare your object as I have shown? Does it work? Can
you show how you have declared the Connection and Command Objects?
"M K" <sk*******@yahoo.com> wrote in message
news:2m*******************@twister.southeast.rr.co m... Yup I've tried that too.. lol.. man this is frustrating.. You use the OLE
object where I am trying to use the SQL object. Would that be the problem?
If i cant figure this out im going to make them inline vs stored
procedure.. ugggg
"Al Reid" <ar*****@reidDASHhome.com> wrote in message
news:%2****************@TK2MSFTNGP12.phx.gbl... 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" <sk*******@yahoo.com> wrote in message
news:HT*******************@twister.southeast.rr.co m... 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" <ar*****@reidDASHhome.com> wrote in message
news:%2******************@TK2MSFTNGP10.phx.gbl...
> 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" <sk*******@yahoo.com> wrote in message
> news:ld*******************@twister.southeast.rr.co m...
>> 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]" <no************@planet.nl> wrote in message
>> news:OV**************@TK2MSFTNGP12.phx.gbl...
>> > 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
>> >
>> >
>>
>>
>
>
Mark,
I'm glad to hear you got it working. I was completely out of suggestions.
--
Al Reid
"M K" <sk*******@yahoo.com> wrote in message
news:Xx*******************@twister.southeast.rr.co m... i guess theres something about SQLConnection that was messing me up.. heres
what now works.. need to tweek it but at least i got the insert to work..
Thanks a bunch Al!
I created the connection using the data object.. keeps from having to type
in the connection string .. it does it for you..
Again.. thanks!
Mark
It sounds like there might be an issue with the
..
..(partially omitted)
..
Or perhaps you should check the
..
..(partially omitted)
..
Best of
..
..(partially omitted)
..
"M K" <sk*******@yahoo.com> wrote in message
news:WQ*******************@twister.southeast.rr.co m... System.Data.SqlClient.SqlException: Procedure or function AddJob has too
many arguments specified at
.
.(partially omitted)
.
ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior,
Boolean returnStream)
.
.(partially omitted)
.
at JobBank.frmJobDetails.SaveToDb(String type) in D:. .
.\JobDetails.vb:line 417
"Michael C#" <xy*@abcdef.com> wrote in message
news:s_*****************@fe12.lga... What's the exact error message text you're receiving? Can we assume that
the SP is in the Jobs database as well? A lot of possible problems come
to mind, but without the exact code and DDL it's all guesswork.
"M K" <sk*******@yahoo.com> wrote in message
news:qL*******************@twister.southeast.rr.co m... Heres my SP: ( i am trying to add more than 1 field but get the same
error no matter how many i try to add, i thought i would try to insert
the primary key only and work up from there but the primary key insert
didnt work either. I can run the SP manually in Visual Studio .NET and
it works. When i try to do this it doesn't.
Any help is greatly appreciated... newbie here as well so please be
gentle :)
ALTER PROCEDURE dbo.AddJob
@JobNumber varchar(30)
AS
INSERT INTO [Jobs].[dbo].[Job]
(JobRefNum)
VALUES
(@JobNumber)
Code:
SqlCommand1.CommandType = CommandType.StoredProcedure
SqlCommand1.CommandText = "Jobs.dbo.AddJob"
SqlCommand1.Parameters.Add("@JobNumber", txtJobRefNumber.Text)
SqlCommand1.ExecuteNonQuery()
You're only passing in ten parameters, but I'm counting 11 in the procedure
declaration. Additionally you are potentially passing in a Zero-Length
String as a DATETIME value.
"M K" <sk*******@yahoo.com> wrote in message
news:ld*******************@twister.southeast.rr.co m... 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]" <no************@planet.nl> wrote in message
news:OV**************@TK2MSFTNGP12.phx.gbl... 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
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Boris Nikolaevich |
last post by:
This is backwards of what I usually want--normally if you have a
long-running ASP script, it's a good idea to check to see whether the client
is still connected so you can cancel execution.
...
|
by: Warren Wright |
last post by:
Hi group,
I have a select statement that if run against a 1 million record
database directly in query analyzer takes less than 1 second.
However, if I execute the select statement in a stored...
|
by: Private Pyle |
last post by:
DB2 version 8, fixpack 5. Aix 5.1.0.0 Using C for AIX compiler.
I'm having a problem where I can create stored procedures but I'm
getting -444 when I call them. It's a new environment for...
|
by: John |
last post by:
I have a single server running SQL Server 2000 sp3a, and acting as a ASP.NET
webserver (.net 1.1 sp1). W2k sp4. Dual Xeon processor, 2 gigs RAM.
Everything works fine, but sometimes when I...
|
by: nate axtell |
last post by:
In a VB .NET app I changed the text of a label then proceed to run a stored
procedure which takes about 20 seconds. The label change doesn't show until
after the stored procedure is finished. Is...
|
by: Jerry |
last post by:
I'm trying to execute a stored procedure in a loop while paging through
database table records but the stored procedure isn't running. I get
the folowing error: The component 'adodb.connection'...
|
by: =?Utf-8?B?YWJheWVy?= |
last post by:
In short: I have an application using the CommittableTransaction class that
works fine when run locally, but does not escalate to MSDTC when deployed to
a Windows 2003 R2 server.
More...
|
by: Sureshbari |
last post by:
Dear All,
I have four table in database , each table contain the 5 lacs record, i have created a view on that four table using union clause.
and now i am call that view form my code like
...
|
by: DR |
last post by:
what are the memory caps for threads running as a CLR stored procedure
executed by sql server 2005? is it limited by OS only or also by sql servers
memory limits? e.g. lets say my clr stored...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
| |
