Not finding the record

[jsandoval]

I am not getting back my data from the data source even though it exists and the SQL is using the correct restrictive values. I have attached the code for you to evaluate.

Thank you,

str = "SELECT ACGZCD, ACA1NB, ACADCD FROM PTADTAPD.SCDLRP " & _"Where ACGZCD = '" & Trim(strServiceGroup) & "' and " & _
"ACA1NB = " & intDealerNbr
Dim SelComm AsNew Odbc.OdbcCommand(str, OdbcConnection1)

OdbcDataAdapter1.SelectCommand = SelComm

OdbcDataAdapter1.SelectCommand.Parameters.Add(New _
Odbc.OdbcParameter("@strServiceGroup", Odbc.OdbcType.VarChar, 6))

OdbcDataAdapter1.SelectCommand.Parameters.Add(New _
Odbc.OdbcParameter("@intDealerNbr", Odbc.OdbcType.Int, 7))

OdbcDataAdapter1.SelectCommand.Parameters("@strServiceGroup").Value = strServiceGroup

OdbcDataAdapter1.SelectCommand.Parameters("@intDealerNbr").Value = intDealerNbr

OdbcConnection1.Open()OdbcDataAdapter1.SelectCommand.ExecuteNonQuery()OdbcConnection1.Close()

OdbcDataAdapter1.Fill(dSet)

intCnt = 0

Do
strCoId = CStr(DataBinder.Eval(dSet, _
"Tables[SCDLRP].DefaultView.0].ACADCD"))
intCnt = intCnt + 1
LoopWhile Trim(strCoId) = "" And intCnt < 1000

If strCoId = "" Then
strCoId = "***"
EndIf

 

[TPM]

Ok well the reason is the dataadapter.fill(dataset) need's to be between the connection open/close. I'm supprised your not getting an error in fact...

A couple of other things:
Why are you useing ODBC? (unless your SQL is MySQL or something like that)
Why are you manually adding the values in your select statment and the adding parameters for them?
You can also remove that executeNonQuery.

TPM

 

[jsandoval]

Thank you for your response TPM.

I have made your suggested changes and received the same results.

We use ODBC because the data source resides on an AS/400. This SQL is one of many in this form and all other work correctly. Why this one is not retrieveing the data is a mystery to me.

 

[TPM]

Put a break in after you fill your dataset and use the quickwatch to see if anything is loading from the DB.

TPM

 

[Schenz]

I have not used ODBC in quite a while, but all the concepts are the same so here goes:

[size=2]str = "SELECT ACGZCD, ACA1NB, ACADCD FROM PTADTAPD.SCDLRP " Where ACGZCD = '" & Trim(strServiceGroup) & "' and ACA1NB = " & intDealerNbr
[/size][size=2][color=#0000ff]
Dim[/color][/size][size=2] SelComm [/size][size=2][color=#0000ff]As[/color][/size][size=2][color=#0000ff] New[/color][/size][size=2] Odbc.OdbcCommand(str, OdbcConnection1)
 
[color=SeaGreen] ' Delete your Parameters, your SQL is not using them[/color]
OdbcDataAdapter1.SelectCommand = SelComm
 [/size][size=2]
[color=SeaGreen]' you do not need to open the connection, run ExecuteNonQuery, then
' Close the connection
' ExecuteNonQuery is used for UPDATE, DELETE, and INSERT Commands
' Fill method will automatically open and close the connection

[/color] OdbcDataAdapter1.Fill(dSet)
 
intCnt = 0
 
[/size][size=2][color=#0000ff]Do[/color][/size]
       [size=2]strCoId = [/size][size=2][color=#0000ff]CStr[/color][/size][size=2](DataBinder.Eval(dSet, [/size][size=2]"ACADCD"))[/size]
       [size=2]intCnt = intCnt + 1[/size]
 [size=2][color=#0000ff]Loop[/color][/size][size=2][color=#0000ff]While[/color][/size][size=2] Trim(strCoId) = "" [/size][size=2][color=#0000ff]And[/color][/size][size=2] intCnt < 1000
[/size]
 [size=2][color=#0000ff]If[/color][/size][size=2] strCoId = "" [/size][size=2][color=#0000ff]Then[/color][/size]
       [size=2]strCoId = "***"[/size]
 [size=2][color=#0000ff]End[/color][/size][size=2][color=#0000ff]If

[color=SeaGreen]' if this is a WinForms App lets test the DataSet to see if it has the data
[color=Black]MessageBox.Show(dSet.Tables(0).Rows(0).Item(0))

[color=SeaGreen]' if this is ASP.NET use the response.write to see the data for debugging
[color=Black]Response.Write([/color][/color][/color][/color][/color][/size][size=2][color=#0000ff][color=SeaGreen][color=Black]dSet.Tables(0).Rows(0).Item(0)[/color][/color][/color][/size][size=2][color=#0000ff][color=SeaGreen][color=Black][color=SeaGreen][color=Black])

[color=SeaGreen]' If you have Option Strict turned on you may need to cooerce the above[/color]
[/color][/color][/color][/color] [/color][/size]

 

[jsandoval]

Thank you for your suggestions. All have worked except Response.Write which I am sure is due to the fact that this code resides in a Component.

Thank you all for your help, it is greatly appreciated.

 

[Schenz]

Respone.Write would only work on an ASP.NET project to write something to the Page.

Were you able to return the desired data then?

 

[jsandoval]

Yes, I was able to get the data. Thank you once again.