error using mysql

[griffitd]

hi i have the foolowing code

Dim pm1 As New MySqlParameter
Dim pm2 As New MySqlParameter

cmdMain = New MySqlCommand("rInsertCustomer", cnMain)
cmdMain.CommandType = CommandType.StoredProcedure

''cmdMain.Parameters.Add(AddSQLParam("CustomerId", SqlDbType.Int, ParameterDirection.Input, 9))
''cmdMain.Parameters.Add(AddSQLParam("CustomerName", SqlDbType.VarChar, ParameterDirection.Input, "Test"))

pm1 = New MySqlParameter("CustomerId", MySqlDbType.Int16, 0)
pm1.Direction = ParameterDirection.Input
pm1.Value = 9
cmdMain.Parameters.Add(pm1)
pm2 = New MySqlParameter("CustomerName", MySqlDbType.VarChar, 45)
pm2.Direction = ParameterDirection.Input
pm2.Value = "Test"
cmdMain.Parameters.Add(pm2)


'cmdMain.Parameters.AddWithValue("CustomerID", 9)
'cmdMain.Parameters.AddWithValue("CustomerName", "Test")
cmdMain.ExecuteNonQuery()

the db connects ok in another procedure.

the routine in mysql is as follows:

CREATE DEFINER=`root`@`localhost` PROCEDURE `rInsertCustomer`(
IN CustomerId INT,
IN CustomerName VARCHAR(45)
)
BEGIN
INSERT INTO tblCustomer
(
CustomerId,
CustomerName
)
VALUES
(
@CustomerId,
@CustomerName
);
END

when i run the executenonquery i get the error:

Column 'CustomerId' Cannot be null

any ideas.

thanks

 

[MarkSweetland]

Not a user of MySQL, but I think the values parameter on your insert don't match the IN parameter because you've added the "@" prefix. I believe the @ is used in the CALL to get the OUT or INOUT values...

VALUES
(
[red]@[/red]CustomerId,
[red]@[/red]CustomerName
);
[/code

 
     Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[griffitd]

hi

thanks for the reply.

Working now.

 

[griffitd]

hi i have another issue:
my .net code is as follows:

cDB_MYSql.ConnectDB()
cDB_MYSql.BuildSP("rModifyCustomer")
cDB_MYSql.AddMySQLParam("CId", MySqlDbType.Int32, ParameterDirection.Input, 0, IIf(txtCustomerId.Text.Length = 0, -1, txtCustomerId.Text))
cDB_MYSql.AddMySQLParam("CRef", MySqlDbType.VarChar, ParameterDirection.Input, 45, txtCustomerRef.Text)
cDB_MYSql.AddMySQLParam("CName", MySqlDbType.VarChar, ParameterDirection.Input, 45, txtCustomerName.Text)
cDB_MYSql.AddMySQLParam("GendId", MySqlDbType.Int32, ParameterDirection.Input, 0, cmbGender.SelectedValue)
cDB_MYSql.AddMySQLParam("TitId", MySqlDbType.Int32, ParameterDirection.Input, 0, cmbTitle.SelectedValue)
cDB_MYSql.AddMySQLParam("FName", MySqlDbType.VarChar, ParameterDirection.Input, 30, txtFirstName.Text)
cDB_MYSql.AddMySQLParam("SName", MySqlDbType.VarChar, ParameterDirection.Input, 30, txtSurname.Text)
cDB_MYSql.AddMySQLParam("Init", MySqlDbType.VarChar, ParameterDirection.Input, 2, txtInitial.Text)
cDB_MYSql.AddMySQLParam("DateOB", MySqlDbType.Date, ParameterDirection.Input, 0, dtpDOB.Value.ToString("dd/MM/yyyy"))

cDB_MYSql.ExecuteNonQuery()

and the routine in MySQL is:
-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `rModifyCustomer`(
IN CId INT,
IN CRef VARCHAR(45),
IN CName VARCHAR(45),
IN GendId INT,
IN TitId INT,
IN FName VARCHAR(30),
IN SName VARCHAR(30),
IN Init CHAR(2),
IN DateOB DATE
)
BEGIN
DECLARE iNewCId INT;

SELECT iNewCId = MAX(CustomerId) + 1 FROM
tblCustomer;

IF Cid = -1 THEN
INSERT INTO tblCustomer
(
CustomerId,
CustomerRef,
CustomerName,
GenderId,
TitleId,
FirstName,
Surname,
Initial,
DOB
)
VALUES
(
iNewCId,
CRef,
CName,
GendId,
TitId,
FName,
SName,
Init,
DateOB
);
ELSE
UPDATE
tblCustomer
SET
CustomerRef = CRef,
CustomerName = CName,
GenderId = GendId,
TitleId = TitId,
FirstName = FName,
Surname= SName,
Initial = Init,
DOB = DateOB
WHERE CustomerId = CId;
END IF;
END

when i execute the cDB_MYSql.ExecuteNonQuery() line i get the following error:

Column CustomerId cannot be null

Help please

thanks

 

[MarkSweetland]

I'm guessing there is no customer data in the tblCustomer table and MAX(CustomerId) is returning NULL. You might have to wrap it with COALESCE().

SELECT iNewCId = COALESCE (MAX(CustomerId) + 1, 1) FROM tblCustomer;

My suggestion too, is that you change the tblCustomer CustomerId to be an AUTO_INCREMENT column where the MySQL engine will create the CustomerId for you without you having to determine what the next value should be.



Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach