Stored Procedures with VB.Net
Posted by:
Lara B
Date: August 02, 2005 11:30AM
Hi all I'm having a problem with using stored procedures from MySQL 5.0 with VB.Net. The procedures work perfectly on the MySQL side. Though when calling from VB.Net I'm getting an error that the Parameter ? is not defined. I've be utilizing MySQL stored pros for a while now and the majority of them work in my application. (So I know it's not my connector.) It appears that the few store pros that have more than 8 variables passed to them are throwing errors. I tried splitting up the large stored pros into smaller ones with less variables passed.
Currently all of them are working on the development machine, but when I transfered everything to the production server I'm now getting the same errors again. Any suggestions as to why an error of this type would be thrown? Is there a limit to the number of variables that I can pass to a stored pro? Is there a limit to the length of horizontal text that can be displayed on one line in the creation of stored pros? I've also tried splitting the variables up onto their own line (displayed vertically from the creation statement) but VB.Net didn't recognize the syntax and threw errors.
Below is an example of one of my stored pros. Like I said, it works fine when calling from within MySQL.
drop procedure EditJuror //
CREATE PROCEDURE EditJuror (nameOld varchar(100), name varchar(100), address varchar(100), address2 varchar(100), city varchar(30), state varchar(20), zip varchar(12), phone varchar(15), birth varchar(15))
Begin
Declare lastName varchar(100);
Set lastName = '';
/*check if juror's name is from the Random table*/
If nameOld = (Select R_vcrFullName from Random where R_vcrFullName = nameOld) Then
/*extract the users last name*/
Set lastName = ExtractLastName(name);
/*update random info*/
Update Random set R_vcrFullName = name, R_vcrLastname = lastName, R_vcrAddress = address,
R_vcrAddress2 = address2, R_vcrCity = city, R_vcrState = state, R_vcrZip = zip,
R_vcrPhone = phone, R_vcrBirthdate = birth
where R_vcrFullName = nameOld;
End If;
/*check if juror's name is from the SelectedList table*/
If nameOld = (Select S_vcrFullName from SelectedList where S_vcrFullName = nameOld) Then
/*extract the users last name*/
Set lastName = ExtractLastName(name);
/*update selectedList info*/
Update SelectedList set S_vcrFullName = name, S_vcrLastname = lastName, S_vcrAddress = address,
S_vcrAddress2 = address2, S_vcrCity = city, S_vcrState = state, S_vcrZip = zip,
S_vcrPhone = phone, S_vcrBirthdate = birth
where S_vcrFullName = nameOld;
End If;
End;
//
Any suggestions would be great. Thanks.