Procedure expects parameter - What am I missing

  • Thread starter Thread starter dbuchanan
  • Start date Start date
D

dbuchanan

Hello,

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

But I do supply it!!

What am I missing??

Here is my Stored Procdure [usp_sequ_sel]
\\
CREATE PROCEDURE dbo.usp_sequ_sel
(@sqfkJob char(36)) AS SET NOCOUNT ON;

SELECT
pkSequId,
sqfkJob,
sqComponentDescription,
sqAction,
sqDuration,
sqSooOrder,
sqSooPrecedent,
sqSooDelay,
sqSooNote,
sqCreatedOn,
sqCreatedBy,
sqEditedOn,
sqEditedBy,
sqrowversion

FROM tbl050Sequ

WHERE (sqfkJob = @sqfkJob)

GO
//

Here is the Variable as defined in f050Sequ

\\
Friend Shared _pkJobID As String 'Using a GUID'
//

This variable is populated in a combobox
\\
Me._pkJobID = Me.cboSelectJob.SelectedValue.ToString
//

Here is the button to load the datagrid.
\\
Private Sub btnLoadTasks_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnLoadTasks.Click

'Verifies that the variable '_pkJobID' has gotten the value
MessageBox.Show("From f050SqOO.vb ~ sqfkJob: " & Me._pkJobID)

_dt = _dataSet1.tbl050Sequ 'The dataTable

DAL.da050SequFrm.Fill(_dt)

Me.DataGrid1.DataSource = _dt

End Sub
//

Here is the command located in DataAccess.vb runs the stored procedure.

\\
With cmd050Sequ_sel
.CommandType = CommandType.StoredProcedure
.CommandText = "usp_sequ_sel"
.Connection = sqlConn
With cmd050Sequ_sel.Parameters
.Add(New SqlParameter("@RETURN_VALUE", SqlDbType.Int, 4, _
ParameterDirection.ReturnValue, False, CType(0, Byte), _
CType(0, Byte), "", DataRowVersion.Current, Nothing))

'The line just below fails
'.Add("@sqfkJob", SqlDbType.Text).Value = f050SqOO._pkJobID

'The line just below succeds
.Add("@sqfkJob", SqlDbType.Text).Value = _
"18713191-e116-4d92-8cc2-c2d4e8c34fdb"
End With
End With
//

I use this same scenario in many other forms. For some reason I cannot
see what is different here.

f050SqOO._pkJobID seems not to be getting the value in DataAccess.vb

Help me see the problem.

thank you,
dbuchanan
 
Hi Kalpesh,

Yes it is - and so is the value returned from the variable _pkJobID.
The ID and the variable are both string datatype.

I am providing the same kind of parameter in the same way in eight
other forms and I have never had this issue before.

I create a variable with Friend Shared access. Then In the DataAccess
class I assign its value to the parameter, but the paramter is not
getting it. The variable in the DataAccess class isn't getting it
either but I don't know why. My spelling is not wrong and the IDE
approves of the syntax.

I don't know where to look next. Any suggestions?

Thank you,
dbuchanan
 
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.
 
Hi,
Please post the complete code of required files to identify where is
the problem.

Kalpesh
 
Hi 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 = New System.Drawing.Point(8, 88)
Me.DataGrid1.Name = "DataGrid1"
Me.DataGrid1.Size = New System.Drawing.Size(592, 296)
Me.DataGrid1.TabIndex = 30
'
'cboSelectJob
'
Me.cboSelectJob.Location = New System.Drawing.Point(8, 8)
Me.cboSelectJob.Name = "cboSelectJob"
Me.cboSelectJob.NSelectedValue = Nothing
Me.cboSelectJob.Size = New System.Drawing.Size(248, 21)
Me.cboSelectJob.TabIndex = 29
'
'btnReset
'
Me.btnReset.Location = New System.Drawing.Point(528, 40)
Me.btnReset.Name = "btnReset"
Me.btnReset.Size = New System.Drawing.Size(72, 23)
Me.btnReset.TabIndex = 33
Me.btnReset.Text = "Reset"
'
'lblfkJob
'
Me.lblfkJob.BorderStyle = System.Windows.Forms.BorderStyle.Fixed3D
Me.lblfkJob.ForeColor = System.Drawing.SystemColors.GrayText
Me.lblfkJob.Location = New System.Drawing.Point(8, 40)
Me.lblfkJob.Name = "lblfkJob"
Me.lblfkJob.Size = New System.Drawing.Size(248, 24)
Me.lblfkJob.TabIndex = 37
Me.lblfkJob.Tag = ""
'
'btnVerifySelectedValue
'
Me.btnVerifySelectedValue.Location = New System.Drawing.Point(264, 8)
Me.btnVerifySelectedValue.Name = "btnVerifySelectedValue"
Me.btnVerifySelectedValue.Size = New System.Drawing.Size(128, 23)
Me.btnVerifySelectedValue.TabIndex = 38
Me.btnVerifySelectedValue.Text = "Verify Selected Value"
'
'btnVerifyVariable
'
Me.btnVerifyVariable.Location = New System.Drawing.Point(264, 40)
Me.btnVerifyVariable.Name = "btnVerifyVariable"
Me.btnVerifyVariable.Size = New System.Drawing.Size(128, 23)
Me.btnVerifyVariable.TabIndex = 39
Me.btnVerifyVariable.Text = "Verify Variable"
'
'f050SqOO
'
Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
Me.ClientSize = New System.Drawing.Size(616, 398)
Me.Controls.Add(Me.btnVerifyVariable)
Me.Controls.Add(Me.btnVerifySelectedValue)
Me.Controls.Add(Me.lblfkJob)
Me.Controls.Add(Me.btnReset)
Me.Controls.Add(Me.DataGrid1)
Me.Controls.Add(Me.cboSelectJob)
Me.Controls.Add(Me.btnLoad)
Me.Icon = CType(resources.GetObject("$this.Icon"),
System.Drawing.Icon)
Me.Name = "f050SqOO"
Me.Text = "f050SqOO"
CType(Me.DataGrid1,
System.ComponentModel.ISupportInitialize).EndInit()
Me.ResumeLayout(False)

End Sub

#End Region

#Region " Variables "

Private DAL As New CLIP.DataAccess
Private _dataSet1 As CLIP.dsTables
Private _dt As DataTable
Private _bStillLoadingcboSelectJob As Boolean = True

'The code only work if the value is assigned during definition
Friend Shared _fkJob As String '=
"18713191-e116-4d92-8cc2-c2d4e8c34fdb"

#End Region

#Region " Buttons - [ btnLoad | btnReset ] "

Private Sub btnLoad_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnLoad.Click

Try
DAL.da050OperFrm.Fill(_dataSet1, "tbl050Oper")
Catch ex As Exception
MessageBox.Show("Exception Message: " & vbCrLf & ex.Message)
MessageBox.Show("Exception Source: " & vbCrLf & ex.Source)
MessageBox.Show("Exception StackTrace: " & vbCrLf & ex.StackTrace)

End Try

'datagrid
_dt = _dataSet1.tbl050Oper
Me.DataGrid1.DataSource = _dt

End Sub

Private Sub btnReset_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnReset.Click
'clear the datagrid
Me.DataGrid1.DataSource = Nothing
Me.DataGrid1.TableStyles.Clear()
Me.lblfkJob.Text = ""
Call LoadTheForm()

If Me.cboSelectJob.Text.Length > 0 Then
Me._fkJob = Me.cboSelectJob.SelectedValue.ToString
End If

End Sub

#End Region

#Region " Form_Load "

Private Sub f050SqOO_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
Me._dataSet1 = New CLIP.dsTables
Call LoadTheForm()
Me.cboSelectJob.SelectedValue = DBNull.Value
End Sub

Private Sub LoadTheForm()
Call LoadCboSelectJob()
End Sub

#End Region

#Region " Data code - [ LoadCboSelectJob ] "

Private Sub LoadCboSelectJob()
DAL.da010SoO_Cbo.Fill(_dataSet1, "tbl010Job")

'cboSelectJob
Dim dm1 As String = "tbl010Job.JobDescription"
Dim vm1 As String = "tbl010Job.pkJobId"

Me.cboSelectJob.DataSource = _dataSet1
Me.cboSelectJob.DisplayMember = dm1
Me.cboSelectJob.ValueMember = vm1
_bStillLoadingcboSelectJob = False
End Sub

#End Region

#Region " Capture Changes - [ cboSelectJob ] "

Private Sub cboSelectJob_SelectedValueChanged(ByVal sender As Object,
ByVal e As System.EventArgs) Handles cboSelectJob.SelectedValueChanged
'If _bStillLoadingcboSelectJob Then
' 'Do Nothing
'Else
' Me._fkJob = Me.cboSelectJob.SelectedValue.ToString
' Me.lblfkJob.Text = _fkJob
'End If

End Sub

#End Region

Private Sub btnVerifySelectedValue_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles btnVerifySelectedValue.Click
Me._fkJob = Me.cboSelectJob.SelectedValue.ToString
MessageBox.Show("SelectedValue: " &
Me.cboSelectJob.SelectedValue.ToString)
End Sub

Private Sub btnVerifyVariable_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles btnVerifyVariable.Click
Me.lblfkJob.Text = _fkJob
MessageBox.Show("Variable: " & Me._fkJob)
End Sub

End Class

Public Class NComboBox
Inherits System.Windows.Forms.ComboBox

Public Property NSelectedValue() As Object
Get
Return SelectedValue
End Get
Set(ByVal Value As Object)
SelectedValue = Value
If (Value Is DBNull.Value And SelectedIndex <> -1) Then
SelectedValue = Value
End If
End Set
End Property

End Class
//

Here is "DataAccess.vb"
\\
Imports System.Data.SqlClient
Imports System.Data.Common
Imports System.Configuration

Public Class DataAccess

Friend sqlConn As New SqlConnection

Friend da050OperFrm As New SqlDataAdapter
Friend da010SoO_Cbo As New SqlDataAdapter

Private cmd050Oper_sel As New SqlCommand
Private cmd010SoO_Sel As New SqlCommand

'[Constructor]
Public Sub New()
MyBase.New()
Call InitializeComponent()
End Sub

<System.Diagnostics.DebuggerStepThrough()> _
Private Sub InitializeComponent()

'=== [Connection String] ========================

Dim connectionString As String
connectionString =
ConfigurationSettings.AppSettings("ConnectionString")
Me.sqlConn.ConnectionString = connectionString

da050OperFrm.SelectCommand = cmd050Oper_sel
da010SoO_Cbo.SelectCommand = cmd010SoO_Sel

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 = f050SqOO._fkJob
End With
End With

With cmd010SoO_Sel
.CommandType = CommandType.StoredProcedure
.CommandText = "usp_SoO_Cbo"
.Connection = sqlConn
With cmd010SoO_Sel.Parameters
.Add(New SqlParameter("@RETURN_VALUE", SqlDbType.Int, 4,
ParameterDirection.ReturnValue, False, CType(0, Byte), CType(0, Byte),
"", DataRowVersion.Current, Nothing))
End With
End With

End Sub

End Class
//

Here is "dsTables.vb" (dataset class)
\\
Public Class dsTables
Inherits DataSet

'[DataTables]
Friend tbl010Job As New DataTable
Friend tbl050Oper As New DataTable

'[Constructor]
Public Sub New()
MyBase.New()
Call InitializeComponent()
End Sub

'[Initialize class component]
Private Sub InitializeComponent()

'[Add Tables to the DataSet]
Me.tbl010Job = Me.Tables.Add("tbl010Job")
Me.tbl050Oper = Me.Tables.Add("tbl050Oper")

'[Add Columns to the DataTables]

' tbl010Job [5-5-7-5-1]
Dim pkJobId As DataColumn = Me.tbl010Job.Columns.Add("pkJobId",
GetType(String))
pkJobId.MaxLength = 36
pkJobId.AllowDBNull = False
Dim JobNumber As DataColumn = Me.tbl010Job.Columns.Add("JobNumber",
GetType(String))
JobNumber.MaxLength = 7
JobNumber.AllowDBNull = False
Dim CustomerName As DataColumn =
Me.tbl010Job.Columns.Add("CustomerName", GetType(String))
CustomerName.MaxLength = 35
CustomerName.AllowDBNull = False
Dim JobDescription As DataColumn =
Me.tbl010Job.Columns.Add("JobDescription", GetType(String))
JobDescription.MaxLength = 75
JobDescription.AllowDBNull = False
Dim ReferenceJobNumber As DataColumn =
Me.tbl010Job.Columns.Add("ReferenceJobNumber", GetType(String))
ReferenceJobNumber.MaxLength = 7
ReferenceJobNumber.AllowDBNull = True
Dim ShopPsi As DataColumn = Me.tbl010Job.Columns.Add("ShopPsi",
GetType(Byte))
ShopPsi.AllowDBNull = True
Dim SystemVoltage As DataColumn =
Me.tbl010Job.Columns.Add("SystemVoltage", GetType(Int32))
SystemVoltage.AllowDBNull = True
Dim SystemPhase As DataColumn = Me.tbl010Job.Columns.Add("SystemPhase",
GetType(Int32))
SystemPhase.AllowDBNull = True
Dim SystemAmps As DataColumn = Me.tbl010Job.Columns.Add("SystemAmps",
GetType(String))
SystemAmps.MaxLength = 3
SystemAmps.AllowDBNull = True
Dim HasCircuitBreakersYN As DataColumn =
Me.tbl010Job.Columns.Add("HasCircuitBreakersYN", GetType(Boolean))
HasCircuitBreakersYN.AllowDBNull = False

Dim AllProcessesAddedYN As DataColumn =
Me.tbl010Job.Columns.Add("AllProcessesAddedYN", GetType(Boolean))
AllProcessesAddedYN.AllowDBNull = False
Dim AllTasksAddedYN As DataColumn =
Me.tbl010Job.Columns.Add("AllTasksAddedYN", GetType(Boolean))
AllTasksAddedYN.AllowDBNull = False
Dim AllProcessesConfiguredYN As DataColumn =
Me.tbl010Job.Columns.Add("AllProcessesConfiguredYN", GetType(Boolean))
AllProcessesConfiguredYN.AllowDBNull = False
Dim ApprovedYN As DataColumn = Me.tbl010Job.Columns.Add("ApprovedYN",
GetType(Boolean))
ApprovedYN.AllowDBNull = False
Dim CompletedYN As DataColumn = Me.tbl010Job.Columns.Add("CompletedYN",
GetType(Boolean))
CompletedYN.AllowDBNull = False
Dim IsMasterYN As DataColumn = Me.tbl010Job.Columns.Add("IsMasterYN",
GetType(Boolean))
IsMasterYN.AllowDBNull = False
Dim Archive As DataColumn = Me.tbl010Job.Columns.Add("Archive",
GetType(Boolean))
Archive.AllowDBNull = False

Dim jcUserNote As DataColumn = Me.tbl010Job.Columns.Add("jcUserNote",
GetType(String))
jcUserNote.MaxLength = 100
jcUserNote.AllowDBNull = False
Dim jcCreatedOn As DataColumn = Me.tbl010Job.Columns.Add("jcCreatedOn",
GetType(DateTime))
jcCreatedOn.AllowDBNull = False
Dim jcCreatedBy As DataColumn = Me.tbl010Job.Columns.Add("jcCreatedBy",
GetType(String))
jcCreatedBy.MaxLength = 25
jcCreatedBy.AllowDBNull = False
Dim jcEditedOn As DataColumn = Me.tbl010Job.Columns.Add("jcEditedOn",
GetType(DateTime))
jcEditedOn.AllowDBNull = False
Dim jcEditedBy As DataColumn = Me.tbl010Job.Columns.Add("jcEditedBy",
GetType(String))
jcEditedBy.MaxLength = 25
jcEditedBy.AllowDBNull = False
Dim jcrowversion As DataColumn =
Me.tbl010Job.Columns.Add("jcrowversion", GetType(Byte()))
jcrowversion.AllowDBNull = True

' tbl050Oper
Dim pkOperId As DataColumn = Me.tbl050Oper.Columns.Add("pkOperId",
GetType(String))
pkOperId.MaxLength = 36
pkOperId.AllowDBNull = False
Dim sqfkJob As DataColumn = Me.tbl050Oper.Columns.Add("sqfkJob",
GetType(String))
sqfkJob.MaxLength = 36
sqfkJob.AllowDBNull = False
Dim sqComponentDescription As DataColumn =
Me.tbl050Oper.Columns.Add("sqComponentDescription", GetType(String))
sqComponentDescription.MaxLength = 50
sqComponentDescription.AllowDBNull = False
Dim sqAction As DataColumn = Me.tbl050Oper.Columns.Add("sqAction",
GetType(String))
sqAction.MaxLength = 50
sqAction.AllowDBNull = True
Dim sqDuration As DataColumn = Me.tbl050Oper.Columns.Add("sqDuration",
GetType(Decimal))
sqDuration.AllowDBNull = True
Dim sqSooOrder As DataColumn = Me.tbl050Oper.Columns.Add("sqSooOrder",
GetType(Decimal))
sqSooOrder.AllowDBNull = True
Dim sqSooPrecedent As DataColumn =
Me.tbl050Oper.Columns.Add("sqSooPrecedent", GetType(Byte))
sqSooPrecedent.AllowDBNull = True
Dim sqSooDelay As DataColumn = Me.tbl050Oper.Columns.Add("sqSooDelay",
GetType(String))
sqSooDelay.MaxLength = 255
sqSooDelay.AllowDBNull = True
Dim sqSooNote As DataColumn = Me.tbl050Oper.Columns.Add("sqSooNote",
GetType(String))
sqSooNote.MaxLength = 255
sqSooNote.AllowDBNull = True
Dim sqCreatedOn As DataColumn =
Me.tbl050Oper.Columns.Add("sqCreatedOn", GetType(DateTime))
sqCreatedOn.AllowDBNull = False
Dim sqCreatedBy As DataColumn =
Me.tbl050Oper.Columns.Add("sqCreatedBy", GetType(String))
sqCreatedBy.MaxLength = 25
sqCreatedBy.AllowDBNull = False
Dim sqEditedOn As DataColumn = Me.tbl050Oper.Columns.Add("sqEditedOn",
GetType(DateTime))
sqEditedOn.AllowDBNull = False
Dim sqEditedBy As DataColumn = Me.tbl050Oper.Columns.Add("sqEditedBy",
GetType(String))
sqEditedBy.MaxLength = 25
sqEditedBy.AllowDBNull = False
Dim sqrowversion As DataColumn =
Me.tbl050Oper.Columns.Add("sqrowversion", GetType(Byte()))
sqrowversion.AllowDBNull = True

'[Additional Column properties for primary keys] [4-12]
pkJobId.ReadOnly = True 'Not AutoIncrement
pkOperId.ReadOnly = True 'Not AutoIncrement

'[Simple PK Constraints - (one line)] [16]
tbl010Job.PrimaryKey = New DataColumn() {tbl010Job.Columns("pkJobID")}

tbl050Oper.PrimaryKey = New DataColumn()
{tbl050Oper.Columns("pkOperId")}

End Sub

End Class
//


Thank you,
dbuchanan
 
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
 
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
 
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
 
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
 
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.
 
Back
Top