Stored Procedure runs but displays #Name? in some of the output fi

[Jack]

Hi,
I am calling a stored procedure from an Access unbound form.I made sure that
the control source of the unbound form is named in accordance with the output
of the stored procedure. However, in some of the fields instead of getting
proper value I am getting #Name? in the data. I am not sure exactly why the
values are not being displayed. Is it the stored procedure parameter setting
incorrect or something else. Still a puzzle. Any help is appreciate. Thanks

CODE TO CALL STORED PROCEDURE:

Private Sub Form_Load()

'To make all the fields visible

'MarketChannel.Visible = True
'TBSGroup.Visible = True
'Amount.Visible = True
'GLPeriod.Visible = True
'GLYear.Visible = True


Dim cmdSelect As ADODB.Command
Dim strMarketChannel As String
Dim strTBSGroup As String
Dim strAmount As String
Dim strGLPeriod As String
Dim strGLYear As String
Dim curAmount As Currency
Set conn = New ADODB.Connection
Set cmdSelect = New ADODB.Command

conn.Provider = "SQLOLEDB"

' conn.ConnectionString = "Provider=SQLOLEDB.1;Initial Catalog=pubs;Data
Source=localhost;User ID=sa;Password=1CdoBn98;"
conn.ConnectionString = "Provider=SQLOLEDB.1;Initial Catalog=TBS;Data
Source=USILDEK1-21001;User ID=sa;Password=test;"
conn.CursorLocation = adUseClient
conn.Open

'Code added

cmdSelect.CommandType = adCmdStoredProc
cmdSelect.CommandText = "sp_select_data"
Set cmdSelect.ActiveConnection = conn

' Get the form values
strMarketChannel = Forms!frmSearchRecords!cboBusinessUnit
strTBSGroup = Forms!frmSearchRecords!cboTbsGroup
strGLPeriod = Forms!frmSearchRecords!cboGLPeriod
strGLYear = Forms!frmSearchRecords!cboTBSYear

strTBSGroup1 = Val(strTBSGroup)
strGLPeriod1 = Val(strGLPeriod)
strGLYear1 = Val(strGLYear)

MsgBox ("Procedure complete. Records selected" & vbCrLf & _
"Parameter 1: " & strMarketChannel & vbCrLf & "Parameter 2: " &
strTBSGroup & vbCrLf & "Parameter 3: " & strGLPeriod & vbCrLf & "Parameter
4: " & strGLYear)


' Add the parameters
cmdSelect.Parameters.Append cmdSelect.CreateParameter("@businessunit",
adVarWChar, adParamInput, 1, strMarketChannel)
cmdSelect.Parameters.Append cmdSelect.CreateParameter("@tbsgroup",
adInteger, adParamInput, 8, strTBSGroup1)
cmdSelect.Parameters.Append cmdSelect.CreateParameter("@glperiod",
adInteger, adParamInput, 8, strGLPeriod1)
cmdSelect.Parameters.Append cmdSelect.CreateParameter("@tbsyear",
adInteger, adParamInput, 8, strGLYear1)
'cmdSelect.Parameters.Append cmdSelect.CreateParameter("@Percent",
adCurrency, adParamInput, , curPercent1)



' Execute the command
Set rs = cmdSelect.Execute


Set Me.Recordset = rs



End Sub

CODE FOR STORED PROCEDURE:

CREATE procedure sp_select_data
@businessunit char(1),
@tbsgroup int,
@glperiod int,
@tbsyear int
AS
select MktChannel, [TBS Grp], Amount, [GL Per], [GL Yr] from tblTBS
where
MktChannel = @businessunit and
[TBS Grp] = @tbsgroup and
[GL Per] = @glperiod and
[GL Yr] = @tbsyear
GO

 

[OldPro]

Hi,
I am calling a stored procedure from an Access unbound form.I made sure that
the control source of the unbound form is named in accordance with the output
of the stored procedure. However, in some of the fields instead of getting
proper value I am getting #Name? in the data. I am not sure exactly why the
values are not being displayed. Is it the stored procedure parameter setting
incorrect or something else. Still a puzzle. Any help is appreciate. Thanks

CODE TO CALL STORED PROCEDURE:

Private Sub Form_Load()

'To make all the fields visible

'MarketChannel.Visible = True
'TBSGroup.Visible = True
'Amount.Visible = True
'GLPeriod.Visible = True
'GLYear.Visible = True

Dim cmdSelect As ADODB.Command
Dim strMarketChannel As String
Dim strTBSGroup As String
Dim strAmount As String
Dim strGLPeriod As String
Dim strGLYear As String
Dim curAmount As Currency
Set conn = New ADODB.Connection
Set cmdSelect = New ADODB.Command

conn.Provider = "SQLOLEDB"

' conn.ConnectionString = "Provider=SQLOLEDB.1;Initial Catalog=pubs;Data
Source=localhost;User ID=sa;Password=1CdoBn98;"
conn.ConnectionString = "Provider=SQLOLEDB.1;Initial Catalog=TBS;Data
Source=USILDEK1-21001;User ID=sa;Password=test;"
conn.CursorLocation = adUseClient
conn.Open

'Code added

cmdSelect.CommandType = adCmdStoredProc
cmdSelect.CommandText = "sp_select_data"
Set cmdSelect.ActiveConnection = conn

' Get the form values
strMarketChannel = Forms!frmSearchRecords!cboBusinessUnit
strTBSGroup = Forms!frmSearchRecords!cboTbsGroup
strGLPeriod = Forms!frmSearchRecords!cboGLPeriod
strGLYear = Forms!frmSearchRecords!cboTBSYear

strTBSGroup1 = Val(strTBSGroup)
strGLPeriod1 = Val(strGLPeriod)
strGLYear1 = Val(strGLYear)

MsgBox ("Procedure complete. Records selected" & vbCrLf & _
"Parameter 1: " & strMarketChannel & vbCrLf & "Parameter 2: " &
strTBSGroup & vbCrLf & "Parameter 3: " & strGLPeriod & vbCrLf & "Parameter
4: " & strGLYear)

' Add the parameters
cmdSelect.Parameters.Append cmdSelect.CreateParameter("@businessunit",
adVarWChar, adParamInput, 1, strMarketChannel)
cmdSelect.Parameters.Append cmdSelect.CreateParameter("@tbsgroup",
adInteger, adParamInput, 8, strTBSGroup1)
cmdSelect.Parameters.Append cmdSelect.CreateParameter("@glperiod",
adInteger, adParamInput, 8, strGLPeriod1)
cmdSelect.Parameters.Append cmdSelect.CreateParameter("@tbsyear",
adInteger, adParamInput, 8, strGLYear1)
'cmdSelect.Parameters.Append cmdSelect.CreateParameter("@Percent",
adCurrency, adParamInput, , curPercent1)

' Execute the command
Set rs = cmdSelect.Execute

Set Me.Recordset = rs

End Sub

CODE FOR STORED PROCEDURE:

CREATE procedure sp_select_data
@businessunit char(1),
@tbsgroup int,
@glperiod int,
@tbsyear int
AS
select MktChannel, [TBS Grp], Amount, [GL Per], [GL Yr] from tblTBS
where
MktChannel = @businessunit and
[TBS Grp] = @tbsgroup and
[GL Per] = @glperiod and
[GL Yr] = @tbsyear
GO

Check your references; I had a newer version of Excel selected in my
references and it caused a similar problem.