Skip navigation links

MySQL Forums :: German :: stored procedure


Advanced Search

stored procedure
Posted by: Franz Reiter ()
Date: February 06, 2012 03:38AM

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

Options: ReplyQuote


Subject Views Written By Posted
stored procedure 1276 Franz Reiter 02/06/2012 03:38AM
Re: stored procedure 663 Thomas Wiedmann 02/06/2012 04:26AM
Re: stored procedure 569 Franz Reiter 02/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.