Help - Procedure expects parameter - It's There!!!

Help - Procedure expects parameter - It's There!!!

Post by dbuchana » Sat, 24 Sep 2005 04:18:28


The error tells me;
---------------------------
Procedure 'usp_sequ_sel' expects parameter '@sqfkJob', which was not
supplied.
---------------------------

But I *do* supply it!!

The sqlCommand has this parameter...
\\
.Add("@sqfkJob", SqlDbType.Text).Value = f050SqOO._pkJobID
//

The variable in opened form "f050Sqoo" is declaired like this...
\\
Friend Shared _pkJobID As String 'a GUID'
//


Testing the code proves that the variable doe get the proper value, but
the sql command nerver gets it!!!

It works if I put the literal value in the parameter statement like
this...
\\
.Add("@sqfkJob", SqlDbType.Text).Value =
"18713191-e116-4d92-8cc2-c2d4e8c34fdb"
//

I use this same configuration for many other forms, why doesn't it work
here?!?

Help,
dbuchanan

See earlier posts for full code.
 
 
 

Help - Procedure expects parameter - It's There!!!

Post by Kalpes » Sat, 24 Sep 2005 15:22:00

Hi,
Please post the complete code of required files to identify where is
the problem.

Kalpesh

 
 
 

Help - Procedure expects parameter - It's There!!!

Post by dbuchana » Sat, 24 Sep 2005 22:33:05

i Kalpesh,

Thank you for your interset. Here is a summary (and then the code)

Here is the latest of what I have found.

If I assign a literal when I define the variable then the DataAcces
class sqlCommand works and returns the data.
\\
Friend Shared _fkJob As String = "18713191-e116-4d92-8cc2-c2d4e8c34fdb"
//


If I use a comboBox to assign the value to the variable then the
DataAccess class sqlCommand does not work. The value is assigned before
the attempt to fill the dataAdapter

1.) Friend Shared _fkJob As String
2.) User makes selection in ComboBox and SelectedValue become, for
example "18713191-e116-4d92-8cc2-c2d4e8c34fdb"
3.) Verify Selected value = "18713191-e116-4d92-8cc2-c2d4e8c34fdb"
4.) Verify _fkJob = "18713191-e116-4d92-8cc2-c2d4e8c34fdb"
5.) Me._fkJob = Me.cboSelectJob.SelectedValue.ToString

In other words the code only works if the value is assigned during
variable definition.

Here is "f050SqOO.vb" code (includes a class that extends combobox so
it will accept nulls)
\\
Imports System.Data.SqlClient
Imports System.Data.SqlClient.SqlDataAdapter
Imports System.Configuration.ConfigurationSettings

Public Class f050SqOO
Inherits System.Windows.Forms.Form

#Region " Windows Form Designer generated code "

Public Sub New()
MyBase.New()

'This call is required by the Windows Form Designer.
InitializeComponent()

'Add any initialization after the InitializeComponent() call
Me.StartPosition = FormStartPosition.CenterScreen

End Sub

'Form overrides dispose to clean up the component list.
Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
If disposing Then
If Not (components Is Nothing) Then
components.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub

'Required by the Windows Form Designer
Private components As System.ComponentModel.IContainer

'NOTE: The following procedure is required by the Windows Form Designer
'It can be modified using the Windows Form Designer.
'Do not modify it using the code editor.
Public WithEvents DataGrid1 As System.Windows.Forms.DataGrid
Protected Friend WithEvents cboSelectJob As NComboBox
Friend WithEvents btnLoad As System.Windows.Forms.Button
Friend WithEvents btnReset As System.Windows.Forms.Button
Friend WithEvents lblfkJob As System.Windows.Forms.Label
Friend WithEvents btnVerifySelectedValue As System.Windows.Forms.Button
Friend WithEvents btnVerifyVariable As System.Windows.Forms.Button
<System.Diagnostics.DebuggerStepThrough()> Private Sub
InitializeComponent()
Dim resources As System.Resources.ResourceManager = New
System.Resources.ResourceManager(GetType(f050SqOO))
Me.btnLoad = New System.Windows.Forms.Button
Me.DataGrid1 = New System.Windows.Forms.DataGrid
Me.cboSelectJob = New CLIP.NComboBox
Me.btnReset = New System.Windows.Forms.Button
Me.lblfkJob = New System.Windows.Forms.Label
Me.btnVerifySelectedValue = New System.Windows.Forms.Button
Me.btnVerifyVariable = New System.Windows.Forms.Button
CType(Me.DataGrid1,
System.ComponentModel.ISupportInitialize).BeginInit()
Me.SuspendLayout()
'
'btnLoad
'
Me.btnLoad.Location = New System.Drawing.Point(528, 8)
Me.btnLoad.Name = "btnLoad"
Me.btnLoad.Size = New System.Drawing.Size(72, 23)
Me.btnLoad.TabIndex = 2
Me.btnLoad.Text = "Load"
'
'DataGrid1
'
Me.DataGrid1.DataMember = ""
Me.DataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText
Me.DataGrid1.Location = Ne
 
 
 

Help - Procedure expects parameter - It's There!!!

Post by Kalpes » Sat, 24 Sep 2005 23:31:40

Hi,

Do the following

1) add a property named FKJobID (String) to your dataaccess.vb class
2) Set this property when user selects anything from the dropdown to
the instance of "DataAccess" class
3) Instead of this line
.Add("@fkJob", SqlDbType.Text).Value = f050SqOO._fkJob

Write
.Add("@fkJob", SqlDbType.Text).Value = me.FKJobID (the newly added
property which will have the value assigned by you)

Does this help ?

Kalpesh
 
 
 

Help - Procedure expects parameter - It's There!!!

Post by dbuchana » Sun, 25 Sep 2005 00:41:48

Hi Kalpesh,

Still the same problem.

Here is the code added to DataAccess.vb ...

At the top
\\
Public ReadOnly Property FkJobID() As String
Get
Return f050SqOO._fkJob
End Get
End Property
//

Here is how I called the parameter
\\
.Add("@fkJob", SqlDbType.Text).Value = FkJobID
//

And I did test that the _fkJob got its value.

I tried to have the property directly access the comboBox but got
problems.
Friend access does not work and Friend Shared access is not an option
in for the properties. If I enter Friend Shared in the Designer
generated code the control disappears.

What next?

Thank you,
dbuchanan
 
 
 

Help - Procedure expects parameter - It's There!!!

Post by Kalpes » Tue, 27 Sep 2005 15:59:10

Hi,

Have a read/write property & assign the value of f050SqOO._fkJob to it

e.g. DataAccess d = new DataAccess();
d.FkJobID = f050SqOO._fkJob ;

Seperate your property such that it doesnt depend on the form (which is
happening in your case).

HTH
Kalpesh
 
 
 

Help - Procedure expects parameter - It's There!!!

Post by dbuchana » Tue, 27 Sep 2005 22:34:42

KalPesh,

I've done the best I know to follow your suggestion, but I am still
getting the same error.

Here is the code within DataAccess.vb
\\
Public PropertyValue As String

Public Property FKJobID() As String
Get
Return PropertyValue
End Get
Set(ByVal Value As String)
PropertyValue = Value
End Set
End Property
//

Here is code within the form...

\\
Private DAL As New CLIP.DataAccess
//

and ...

This code is executed by a button on the form so I can see that I can
see that it is doing what I expect. And yes the messagebox returns the
appropriate GUID string.
\\
Private Sub btnVerifyVariable_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles btnVerifyVariable.Click
Me.lblfkJob.Text = _fkJob
DAL.FKJobID = Me.cboSelectJob.SelectedValue.ToString
MessageBox.Show("Get property from DataAccess.vb: " & DAL.FKJobID)
End Sub
//

Again, here is the sql command from DataAccess.vb that is trying to get
the value from the property...
\\
With cmd050Oper_sel
.CommandType = CommandType.StoredProcedure
.CommandText = "usp_050Oper_sel"
.Connection = sqlConn
With cmd050Oper_sel.Parameters
.Add(New SqlParameter("@RETURN_VALUE", SqlDbType.Int, 4,
ParameterDirection.ReturnValue, False, CType(0, Byte), CType(0, Byte),
"", DataRowVersion.Current, Nothing))

'Filter... WHERE (sqfkJob = @sqfkJob)
.Add("@fkJob", SqlDbType.Text).Value = FkJobID
End With
End With
//

Am I wiring it up properly? I'm new to using properties.

Again here is the error and stacktrace;

---------------------------
Exception Message:
Procedure 'usp_050Oper_sel' expects parameter '@fkJob', which was not
supplied.
---------------------------

and ...

---------------------------
Exception StackTrace:
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
behavior)
at
System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior
behavior)
at System.Data.Common.DbDataAdapter.FillFromCommand(Object data,
Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand
command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String
srcTable)
at CLIP.f050SqOO.btnLoad_Click(Object sender, EventArgs e) in
C:\Documents and Settings\dbuchanan\My
Documents\CLIP\Clip03_2005-09-23_diagnostic\Clip02\f050SqOO.vb:line 169
---------------------------

Thank you,
dbuchanan
 
 
 

Help - Procedure expects parameter - It's There!!!

Post by dbuchana » Wed, 28 Sep 2005 06:18:07

I found it!

The problem was that I put this statement ...
\\
Private DAL As New CLIP.DataAccess
//

... in the variables section of my code (note the "New") while at the
same time having my sql command with its parameters in the initialize
section of the DataAccess.vb class.

I should put the sql command within a method outside the initialize
area then called that method just before asked the dataAdapter to fill
the dataSet. There are outher nuances to consider but this is the gist
of it.