Hi
I have the following function which takes a very large input string from my asp page and passes it into a stored procedure, it all compiles a runs correctly through to executing a the query where it throws the exception - input string was not in correct format
any help much appreciated
boyindie
this is the code for my procedure
DELIMITER $$<br />
CREATE DEFINER=`jshort`@`localhost` PROCEDURE `sp_insert`(IN p_firstname varchar(20) ,IN p_middlename varchar(30), IN p_lastname VARCHAR(20),IN p_email VARCHAR(200),IN p_telNO INTEGER(16),IN p_mobNo INTEGER(16),IN p_faxNo INTEGER(16),IN p_houseName varchar(50), in p_houseNo Int(4), in p_strAdr varchar(200), in p_stradr1 varchar(200),in p_stadr2 varchar(200), in p_city varchar(50), in p_area varchar(50), in p_country varchar(50),in p_postcode integer(12))<br />
BEGIN<br />
<br />
Insert into user_details(user_Firstname, user_middleName, user_LastName, user_email, user_fax, user_tel, user_mobile) Values (p_firstname, p_middlename, p_lastname, p_email, P_telNo,P_mobNO,P_faxNo);<br />
<br />
INsert into user_addresses(address_house_name, address_house_no, address_address, address_extra1, address_extra2, address_city, address_area, address_county, address_postcode) VALUES (p_housename, p_houseNo, p_strAdr, p_strAdr1, p_strAdr2, p_city, p_area, p_country, p_postCode);<br />
<br />
END $$<br />
DELIMITER ;<br />
<br />
<br />
and I have the following code for inputing into
<br />
'Set command string to equal SPROC<br />
cmdString.CommandText = "sp_Insert"<br />
'ONLY PLACE THIS IF SPROC, sets the command to a SPROC<br />
cmdString.CommandType = CommandType.StoredProcedure<br />
<br />
Dim param As New MySqlParameter<br />
'Creats paramter to send to SPROC<br />
param = cmdString.Parameters.Add("?p_firstname", MySqlDbType.VarChar)<br />
'Sets parameter type to input parameter<br />
param.Direction = ParameterDirection.Input<br />
'Sets parameter value to text box<br />
param.Value = txtFirstname.Text<br />
<br />
'Creats paramter to send to SPROC<br />
param = cmdString.Parameters.Add("?p_middlename", MySqlDbType.VarChar)<br />
'Sets parameter type to input parameter<br />
param.Direction = ParameterDirection.Input<br />
'Sets parameter value to text box<br />
param.Value = txtMiddleName.Text<br />
<br />
'Creats paramter to send to SPROC<br />
param = cmdString.Parameters.Add("?p_lastname", MySqlDbType.VarChar)<br />
'Sets parameter type to input parameter<br />
param.Direction = ParameterDirection.Input<br />
'Sets parameter value to text box<br />
param.Value = txtLastName.Text<br />
<br />
'Creats paramter to send to SPROC<br />
param = cmdString.Parameters.Add("?p_email", MySqlDbType.VarChar)<br />
'Sets parameter type to input parameter<br />
param.Direction = ParameterDirection.Input<br />
'Sets parameter value to text box<br />
param.Value = txtEmail.Text<br />
<br />
'Creats paramter to send to SPROC<br />
param = cmdString.Parameters.Add("?p_telNo", MySqlDbType.Int64)<br />
'Sets parameter type to input parameter<br />
param.Direction = ParameterDirection.Input<br />
'Sets parameter value to text box<br />
param.Value = txtTelNo.Text<br />
<br />
'Creats paramter to send to SPROC<br />
param = cmdString.Parameters.Add("?p_mobNo", MySqlDbType.Int64)<br />
'Sets parameter type to input parameter<br />
param.Direction = ParameterDirection.Input<br />
'Sets parameter value to text box<br />
param.Value = txtMobNo.Text<br />
<br />
'Creats paramter to send to SPROC<br />
param = cmdString.Parameters.Add("?p_faxNo", MySqlDbType.Int64)<br />
'Sets parameter type to input parameter<br />
param.Direction = ParameterDirection.Input<br />
'Sets parameter value to text box<br />
param.Value = txtFaxno.Text<br />
<br />
'Creats paramter to send to SPROC<br />
param = cmdString.Parameters.Add("?p_houseName", MySqlDbType.VarChar)<br />
'Sets parameter type to input parameter<br />
param.Direction = ParameterDirection.Input<br />
'Sets parameter value to text box<br />
param.Value = txtHouseName.Text<br />
<br />
'Creats paramter to send to SPROC<br />
param = cmdString.Parameters.Add("?p_houseNo", MySqlDbType.Int64)<br />
'Sets parameter type to input parameter<br />
param.Direction = ParameterDirection.Input<br />
'Sets parameter value to text box<br />
param.Value = txtHouseNo.Text<br />
<br />
'Creats paramter to send to SPROC<br />
param = cmdString.Parameters.Add("?p_StrAdr", MySqlDbType.VarChar)<br />
'Sets parameter type to input parameter<br />
param.Direction = ParameterDirection.Input<br />
'Sets parameter value to text box<br />
param.Value = txtStreetAdd.Text<br />
<br />
'Creats paramter to send to SPROC<br />
param = cmdString.Parameters.Add("?p_strAdr1", MySqlDbType.VarChar)<br />
'Sets parameter type to input parameter<br />
param.Direction = ParameterDirection.Input<br />
'Sets parameter value to text box<br />
param.Value = txtAdressE1.Text<br />
<br />
'Creats paramter to send to SPROC<br />
param = cmdString.Parameters.Add("?p_stAdr2", MySqlDbType.VarChar)<br />
'Sets parameter type to input parameter<br />
param.Direction = ParameterDirection.Input<br />
'Sets parameter value to text box<br />
param.Value = txtAdressE2.Text<br />
<br />
'Creats paramter to send to SPROC<br />
param = cmdString.Parameters.Add("?p_city", MySqlDbType.VarChar)<br />
'Sets parameter type to input parameter<br />
param.Direction = ParameterDirection.Input<br />
'Sets parameter value to text box<br />
param.Value = txtCity.Text<br />
<br />
'Creats paramter to send to SPROC<br />
param = cmdString.Parameters.Add("?p_country", MySqlDbType.VarChar)<br />
'Sets parameter type to input parameter<br />
param.Direction = ParameterDirection.Input<br />
'Sets parameter value to text box<br />
param.Value = txtCountry.Text<br />
<br />
'Creats paramter to send to SPROC<br />
param = cmdString.Parameters.Add("?p_area", MySqlDbType.VarChar)<br />
'Sets parameter type to input parameter<br />
param.Direction = ParameterDirection.Input<br />
'Sets parameter value to text box<br />
param.Value = txtArea.Text<br />
<br />
'Creats paramter to send to SPROC<br />
param = cmdString.Parameters.Add("?p_postcode", MySqlDbType.VarChar)<br />
'Sets parameter type to input parameter<br />
param.Direction = ParameterDirection.Input<br />
'Sets parameter value to text box<br />
param.Value = txtPostCode.Text<br />
<br />
<br />
<br />
<br />
'Insert the records into the database<br />
connection.Open() 'Opens Database Connection<br />
cmdString.ExecuteNonQuery() 'Executes procedure<br />
connection.Close() 'Closes connection<br />
<br />
Catch ex As Exception<br />
litErr.Text = ex.Message<br />
MsgBox(ex.Message) 'Shows error message as messagebox<br />
<br />
End Try<br />
End Sub<br />
<br />
|