stored procedure
Hallo, ich habe hier eine gespeicherte prozedur und bekomme nur die erste Zeile zurück.
Ich weis aber nicht ob es an der Prozedur oder an Visual Basic 2010 liegt.
Hier die Prozedur:
Create Procedure GetListenByUsrNo(IN iusrno int, OUT v_beschreibung char(255), OUT v_programm char(255))
COMMENT 'IN=usrno,OUT=comment,programm'
SQL SECURITY INVOKER
begin
declare v_beschreibung char(255);
declare v_programm char(255);
declare v_fertig int default 0;
declare c_liste cursor for
select l.beschreibung,l.programm
from listen as l, grplst as g, users as u
where usrno = iusrno
and l.lstno = g.lstno
and g.grpno = u.grpno;
declare exit handler for not found set v_fertig = 1;
open c_liste;
LOOP
fetch c_liste into v_beschreibung, v_programm;
select v_beschreibung, v_programm;
END LOOP;
close c_liste;
end
//
Und hier die Sub:
Public Sub SPGetListen(ByVal iUsrNo As Integer)
Dim Conn As New MySqlConnection
Dim cmd As New MySqlCommand
Dim reader As MySqlDataReader
Dim p1 As MySqlParameter
Dim p2 As MySqlParameter
Dim p3 As MySqlParameter
Conn.ConnectionString = "Server=" & DB_Server & ";" _
& "Database=" & DB_Database & ";" _
& "UID=" & DB_UserID & ";" _
& "PWD=" & sPwd & ";"
cmd = New MySqlCommand("GetListenByUsrNo", Conn)
cmd.CommandType = CommandType.StoredProcedure
p1 = New MySqlParameter("iusrno", MySqlDbType.Int16)
p1.Direction = ParameterDirection.Input
p1.Value = iUsrNo
cmd.Parameters.Add(p1)
p2 = New MySqlParameter("v_beschreibung", MySqlDbType.Text)
p2.Direction = ParameterDirection.Output
p2.Value = DBNull.Value
cmd.Parameters.Add(p2)
p3 = New MySqlParameter("v_programm", MySqlDbType.Text)
p3.Direction = ParameterDirection.Output
p3.Value = DBNull.Value
cmd.Parameters.Add(p3)
Conn.Open()
'Daten lesen
reader = cmd.ExecuteReader()
myliste.Clear()
Do While reader.Read()
myliste.Add(New fMaincListen(reader("v_beschreibung"), reader("v_programm")))
Loop
reader.Close()
Conn.Close()
'Liste füllen
With fMain
.ListBox1.DataSource = myliste
.ListBox1.DisplayMember = "beschreibung"
.ListBox1.ValueMember = "programm"
End With
End Sub
Beim Debuggen wird die Do-Loop-Schleife nur einmal durchlaufen.
Bitte um Hilfe bzw. Tipps.
Danke
Subject
Views
Written By
Posted
stored procedure
2030
February 06, 2012 03:38AM
1107
February 06, 2012 04:26AM
945
February 06, 2012 05:23AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.