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
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
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
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
