Not finding the record

jsandoval

Active member
Joined
Dec 27, 2004
Messages
28
Location
Denver
Programming Experience
10+
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

Well-known member
Joined
Dec 7, 2004
Messages
623
Location
CA
Programming Experience
3-5
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

Active member
Joined
Dec 27, 2004
Messages
28
Location
Denver
Programming Experience
10+
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

Well-known member
Joined
Dec 7, 2004
Messages
623
Location
CA
Programming Experience
3-5
Put a break in after you fill your dataset and use the quickwatch to see if anything is loading from the DB.

TPM
 

Schenz

Well-known member
Joined
Sep 11, 2004
Messages
181
Location
Cincinnati, OH
Programming Experience
5-10
I have not used ODBC in quite a while, but all the concepts are the same so here goes:

VB.NET:
[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

Active member
Joined
Dec 27, 2004
Messages
28
Location
Denver
Programming Experience
10+
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

Well-known member
Joined
Sep 11, 2004
Messages
181
Location
Cincinnati, OH
Programming Experience
5-10
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?
 
Top Bottom