Ok I'm pretty new to VB.net and I need some help. Somehow I've been able to get this far, but I'm stumped. I'm managed to connect to both my production database (Sqlbase) and a distributed db (Pervasive) thru ODBC.
What I'm trying to do is compare tickets on both: tickets are entered into Pervasive
and then transferred thru to Sqlbase. Some tickets miss the transfer because
of setup reasons etc. I need to find those tickets that miss the transfer.
what I need done is:
{fetch tickets from pervasive for a particular day in ticketno order}
while not done
{fetch ticket nbr from sqlbase where ticket_nbr=ticketno and
sale_date=date above}
Here's the problem(s):
1. I'm getting a data not numeric when it DOESN'T find a ticket on sqlbase
(ie the result is Null not integer)
2. I get a DataReader associated with this command needs to be closed.
sSelect = "select ticketno from tkhist1 WHERE TicketDate=? order by ticketno"
cmdA.CommandText = "select ticketno from tkhist1 WHERE
TicketDate=? order by ticketno"
cmdA.Parameters.Add("ticdate",
Data.Odbc.OdbcType.Date).Value = TicDate.Value
Dim bReader As System.Data.Odbc.OdbcDataReader =
cmdA.ExecuteReader()
'MsgBox(cmdA.CommandText)
cmdQ.CommandText = "select ticket_nbr from sale where
co_nbr=80 and locn_id=1 and sale_date=? and ticket_nbr=?"
Dim cReader As System.Data.Odbc.OdbcDataReader = Nothing
While bReader.Read()
aTno = bReader.GetInt32(0)
cReader = cmdQ.ExecuteReader()
'sale_date=?
cmdQ.Parameters.Add("ticdate",
Data.Odbc.OdbcType.Date).Value = TicDate.Value
cmdQ.Parameters.Add("ticno",
Data.Odbc.OdbcType.Int).Value = aTno
Try
If Not cReader.IsDBNull(0) Then
'aQno = cReader.GetValue(0)
'If aQno = Nothing Then
tbA.Text = tbA.Text & " " & aTno.ToString
End If
Catch ex As Exception
MsgBox("Error if finding Quest ticket: " +
ex.ToString)
End Try
End While
bReader.Close()
End If
Concerning the the second fetch from sqlbase (the cmdQ and creader above)
do you have build and destroy that EVERY time the next Apex ticket (from breader) changes? Where to you do the 'prepare' and then execute when the parameters change (the aTno value above)?
What I'm trying to do is compare tickets on both: tickets are entered into Pervasive
and then transferred thru to Sqlbase. Some tickets miss the transfer because
of setup reasons etc. I need to find those tickets that miss the transfer.
what I need done is:
{fetch tickets from pervasive for a particular day in ticketno order}
while not done
{fetch ticket nbr from sqlbase where ticket_nbr=ticketno and
sale_date=date above}
Here's the problem(s):
1. I'm getting a data not numeric when it DOESN'T find a ticket on sqlbase
(ie the result is Null not integer)
2. I get a DataReader associated with this command needs to be closed.
sSelect = "select ticketno from tkhist1 WHERE TicketDate=? order by ticketno"
cmdA.CommandText = "select ticketno from tkhist1 WHERE
TicketDate=? order by ticketno"
cmdA.Parameters.Add("ticdate",
Data.Odbc.OdbcType.Date).Value = TicDate.Value
Dim bReader As System.Data.Odbc.OdbcDataReader =
cmdA.ExecuteReader()
'MsgBox(cmdA.CommandText)
cmdQ.CommandText = "select ticket_nbr from sale where
co_nbr=80 and locn_id=1 and sale_date=? and ticket_nbr=?"
Dim cReader As System.Data.Odbc.OdbcDataReader = Nothing
While bReader.Read()
aTno = bReader.GetInt32(0)
cReader = cmdQ.ExecuteReader()
'sale_date=?
cmdQ.Parameters.Add("ticdate",
Data.Odbc.OdbcType.Date).Value = TicDate.Value
cmdQ.Parameters.Add("ticno",
Data.Odbc.OdbcType.Int).Value = aTno
Try
If Not cReader.IsDBNull(0) Then
'aQno = cReader.GetValue(0)
'If aQno = Nothing Then
tbA.Text = tbA.Text & " " & aTno.ToString
End If
Catch ex As Exception
MsgBox("Error if finding Quest ticket: " +
ex.ToString)
End Try
End While
bReader.Close()
End If
Concerning the the second fetch from sqlbase (the cmdQ and creader above)
do you have build and destroy that EVERY time the next Apex ticket (from breader) changes? Where to you do the 'prepare' and then execute when the parameters change (the aTno value above)?
