R0ede R0ede - 7 months ago 64
SQL Question

C# to mysql - inserting new row to table with foreign key

I need to insert a new row in a mysql table using C#.
The table looks like this, and contains one foreign key column.
i use the follwing code to cinnect to the database and insert data.

public virtual void addToDB()
{
try
{
//prepare for query
var cmd = MySQL.readyQuery();

//insert testdata to Vo2test
cmd.CommandText = "INSERT INTO vo2test_tb(ClientID, Weight, Methods, TimeOfDay, Date, StartResistance, Endresistance, TheoreticalMaxPulse, FatPercent3point, FatPercent7point, VO2_max, FitnessRating, HRmax, RERmax, TestTime, Raw_test_data) VALUES((SELECT UserID from user_tb WHERE UserID = '@UserID'), '@Weight', '@Method', '@Timeofday', '@Date', '@Startresistance', '@Endresistance', '@Theoreticalmaxpulse', '@Fatprocent3p', '@Fatprocent7p', '@vo2max', '@fitnessrating', '@hrmax', '@rermax', '@testtime', '@rawtestdata')";

cmd.Prepare();
//insert parameters som skal ├Žndres:
cmd.Parameters.AddWithValue("@UserID", UserID);
cmd.Parameters.AddWithValue("@Weight", Weight);
cmd.Parameters.AddWithValue("@Method", Method);
//coverts date to 0000-00-00
string DateString = Convert.ToString(TestDate.Date.Date.Year) + "-" + Convert.ToString(TestDate.Date.Month) + "-" + Convert.ToString(TestDate.Date.Day);
cmd.Parameters.AddWithValue("@Date", DateString);
//converts time to 00:00:00.
string TimeString = Convert.ToString(TimeOfDay.Hour) + ":" + Convert.ToString(TimeOfDay.Minute) + ":00";
cmd.Parameters.AddWithValue("@Timeofday", TimeString);
cmd.Parameters.AddWithValue("@Startresisstance", StartResistance);
cmd.Parameters.AddWithValue("@Endressistance", EndResistance);
cmd.Parameters.AddWithValue("@TheoreticalMaxPulse", TheoreticMaxPulse);
cmd.Parameters.AddWithValue("@FatPercent3point", FatPercent3Point);
cmd.Parameters.AddWithValue("@FatPercent7point", FatPercent7Point);
cmd.Parameters.AddWithValue("@VO2_max", Vo2Max);
cmd.Parameters.AddWithValue("@FitnessRating", FitnessRating);
cmd.Parameters.AddWithValue("@HRmax", HRmax);
cmd.Parameters.AddWithValue("@RERmax", RERmax);
cmd.Parameters.AddWithValue("@TestTime", TimeOfDay);
cmd.Parameters.AddWithValue("@Raw_test_data", RawTestData);

cmd.ExecuteNonQuery();

//close connection
cmd.Connection.Close();
}
catch (MySqlException ex)
{
Console.WriteLine(ex.Message);
}
}


when i run it, i am told that ClientID can't be null but if i just run the sql query in Adminer it works fine.

Answer

The sub select in your insert query doesnt make sense. Why are you doing a sub select to insert a value you already have.

SELECT UserID from user_tb WHERE UserID = '@UserID'

Just insert @UserID directly.

Also you have wrapped all your @ parameters in single quotes in the query string. You do not need this. Parameterization deals with the quotes for you in the background. Remove all single quotes so that it just reads:

cmd.CommandText = "INSERT INTO vo2test_tb(ClientID, Weight, Methods, TimeOfDay, Date, 
StartResistance, Endresistance, TheoreticalMaxPulse, FatPercent3point, FatPercent7point, 
VO2_max, FitnessRating, HRmax, RERmax, TestTime, Raw_test_data) 

VALUES((SELECT ClientID from user_tb WHERE UserID = @UserID), @Weight, @Method, @Timeofday, 
@Date, @Startresistance, @Endresistance, @Theoreticalmaxpulse, @Fatprocent3p, @Fatprocent7p, 
@vo2max, @fitnessrating, @hrmax, @rermax, @testtime, @rawtestdata)";