C# Mysql query problem
-
Hi there good people ! I've got strange problem with query into C# code to MySQL database. The point is i got this query:
m_insert_cmd2 = new MySqlCommand("INSERT INTO tb_rezerwacja (kwota, ilosc_osob, w_tym_ilosc_dzieci, data_od, data_do, FK_pokoj_id, FK_zamowienie_id) VALUES (, '" + tbPrice.Text + "', '" + tbNumberOfPeople.Text + "', '" + tbChildren.Text + "', '" + tbStartDate.Text + "', '" + tbEndDate.Text + "', @pokoj_id, @zamowienie_id)", m_conn);
which is working but put all field in database to NULL without parameters : @pokoj_id and @rezerwacja_id - that's good. All tb.. are editboxes no matter what i write to them it always put nulls in database. I tried a lot of combinations with this query but i can;t do it working 100%. For example this query works fine (if i put static data) it's all put in database, no nulls:
m_insert_cmd2 = new MySqlCommand("INSERT INTO tb_rezerwacja (kwota, ilosc_osob, w_tym_ilosc_dzieci, data_od, data_do, FK_pokoj_id, FK_zamowienie_id) VALUES (' 345', '3', '3', '0000-00-21', '0000-00-21', @pokoj_id, @zamowienie_id)", m_conn);
What i do wrong, please help. best regards, nighttrain
-
Hi there good people ! I've got strange problem with query into C# code to MySQL database. The point is i got this query:
m_insert_cmd2 = new MySqlCommand("INSERT INTO tb_rezerwacja (kwota, ilosc_osob, w_tym_ilosc_dzieci, data_od, data_do, FK_pokoj_id, FK_zamowienie_id) VALUES (, '" + tbPrice.Text + "', '" + tbNumberOfPeople.Text + "', '" + tbChildren.Text + "', '" + tbStartDate.Text + "', '" + tbEndDate.Text + "', @pokoj_id, @zamowienie_id)", m_conn);
which is working but put all field in database to NULL without parameters : @pokoj_id and @rezerwacja_id - that's good. All tb.. are editboxes no matter what i write to them it always put nulls in database. I tried a lot of combinations with this query but i can;t do it working 100%. For example this query works fine (if i put static data) it's all put in database, no nulls:
m_insert_cmd2 = new MySqlCommand("INSERT INTO tb_rezerwacja (kwota, ilosc_osob, w_tym_ilosc_dzieci, data_od, data_do, FK_pokoj_id, FK_zamowienie_id) VALUES (' 345', '3', '3', '0000-00-21', '0000-00-21', @pokoj_id, @zamowienie_id)", m_conn);
What i do wrong, please help. best regards, nighttrain
-
I think here is problem:
nighttrain_ wrote:
VALUES (,
It should be
VALUES (
I Love T-SQL "VB.NET is developed with C#.NET" If my post helps you kindly save my time by voting my post. www.cacttus.com
i tried it and the same wrong result, still NULLS
-
Hi there good people ! I've got strange problem with query into C# code to MySQL database. The point is i got this query:
m_insert_cmd2 = new MySqlCommand("INSERT INTO tb_rezerwacja (kwota, ilosc_osob, w_tym_ilosc_dzieci, data_od, data_do, FK_pokoj_id, FK_zamowienie_id) VALUES (, '" + tbPrice.Text + "', '" + tbNumberOfPeople.Text + "', '" + tbChildren.Text + "', '" + tbStartDate.Text + "', '" + tbEndDate.Text + "', @pokoj_id, @zamowienie_id)", m_conn);
which is working but put all field in database to NULL without parameters : @pokoj_id and @rezerwacja_id - that's good. All tb.. are editboxes no matter what i write to them it always put nulls in database. I tried a lot of combinations with this query but i can;t do it working 100%. For example this query works fine (if i put static data) it's all put in database, no nulls:
m_insert_cmd2 = new MySqlCommand("INSERT INTO tb_rezerwacja (kwota, ilosc_osob, w_tym_ilosc_dzieci, data_od, data_do, FK_pokoj_id, FK_zamowienie_id) VALUES (' 345', '3', '3', '0000-00-21', '0000-00-21', @pokoj_id, @zamowienie_id)", m_conn);
What i do wrong, please help. best regards, nighttrain
I know this sounds really basic - but have you tried running this in debug mode and outputting the values of these objects:
System.Diagnostics.Debug.WriteLine(tbPrice.Text);
System.Diagnostics.Debug.WriteLine(tbNumberOfPeople.Text);
System.Diagnostics.Debug.WriteLine(tbChildren.Text);
System.Diagnostics.Debug.WriteLine(tbStartDate.Text);
System.Diagnostics.Debug.WriteLine(tbEndDate.Text);Just to make sure that they contain what you expect them to contain...
Continuous effort - not strength or intelligence - is the key to unlocking our potential.(Winston Churchill)
-
I know this sounds really basic - but have you tried running this in debug mode and outputting the values of these objects:
System.Diagnostics.Debug.WriteLine(tbPrice.Text);
System.Diagnostics.Debug.WriteLine(tbNumberOfPeople.Text);
System.Diagnostics.Debug.WriteLine(tbChildren.Text);
System.Diagnostics.Debug.WriteLine(tbStartDate.Text);
System.Diagnostics.Debug.WriteLine(tbEndDate.Text);Just to make sure that they contain what you expect them to contain...
Continuous effort - not strength or intelligence - is the key to unlocking our potential.(Winston Churchill)
Mayby it's lame but how can I use this debug mode, i create a button and paste Yours:
System.Diagnostics.Debug.WriteLine(tbPrice.Text);
System.Diagnostics.Debug.WriteLine(tbNumberOfPeople.Text);
System.Diagnostics.Debug.WriteLine(tbChildren.Text);
System.Diagnostics.Debug.WriteLine(tbStartDate.Text);
System.Diagnostics.Debug.WriteLine(tbEndDate.Text);mayby it's lame question but how should i do that?
-
Hi there good people ! I've got strange problem with query into C# code to MySQL database. The point is i got this query:
m_insert_cmd2 = new MySqlCommand("INSERT INTO tb_rezerwacja (kwota, ilosc_osob, w_tym_ilosc_dzieci, data_od, data_do, FK_pokoj_id, FK_zamowienie_id) VALUES (, '" + tbPrice.Text + "', '" + tbNumberOfPeople.Text + "', '" + tbChildren.Text + "', '" + tbStartDate.Text + "', '" + tbEndDate.Text + "', @pokoj_id, @zamowienie_id)", m_conn);
which is working but put all field in database to NULL without parameters : @pokoj_id and @rezerwacja_id - that's good. All tb.. are editboxes no matter what i write to them it always put nulls in database. I tried a lot of combinations with this query but i can;t do it working 100%. For example this query works fine (if i put static data) it's all put in database, no nulls:
m_insert_cmd2 = new MySqlCommand("INSERT INTO tb_rezerwacja (kwota, ilosc_osob, w_tym_ilosc_dzieci, data_od, data_do, FK_pokoj_id, FK_zamowienie_id) VALUES (' 345', '3', '3', '0000-00-21', '0000-00-21', @pokoj_id, @zamowienie_id)", m_conn);
What i do wrong, please help. best regards, nighttrain
I don't know, a textbox.Text should never be NULL, but there's an awful lot of bad technique there. 0) Use parameters for all the values 1) Don't use strings for numeric and date values 2) Try using NumericUpDowns and DateTimePickers
-
Mayby it's lame but how can I use this debug mode, i create a button and paste Yours:
System.Diagnostics.Debug.WriteLine(tbPrice.Text);
System.Diagnostics.Debug.WriteLine(tbNumberOfPeople.Text);
System.Diagnostics.Debug.WriteLine(tbChildren.Text);
System.Diagnostics.Debug.WriteLine(tbStartDate.Text);
System.Diagnostics.Debug.WriteLine(tbEndDate.Text);mayby it's lame question but how should i do that?
I don't have the visual studio IDE at work but you will need do something like select debug from a menu at the top and F5 to start debugging - you will also need to open what I believe is called the "output" window. Best thing is to google how to debug in Visual studio - wish I could be of more help... Being able to use the debug facilities is a must have skill for C# clickety[^]
Continuous effort - not strength or intelligence - is the key to unlocking our potential.(Winston Churchill)
-
I don't know, a textbox.Text should never be NULL, but there's an awful lot of bad technique there. 0) Use parameters for all the values 1) Don't use strings for numeric and date values 2) Try using NumericUpDowns and DateTimePickers
i start debug and open Output Window but it's strange what i saw:
'hms.application.vshost.exe' (Managed (v4.0.30319)): Loaded 'C:\Windows\Microsoft.Net\assembly\GAC_32\mscorlib\v4.0_4.0.0.0__b77a5c561934e089\mscorlib.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'hms.application.vshost.exe' (Managed (v4.0.30319)): Loaded 'C:\Windows\assembly\GAC_MSIL\Microsoft.VisualStudio.HostingProcess.Utilities\10.0.0.0__b03f5f7f11d50a3a\Microsoft.VisualStudio.HostingProcess.Utilities.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'hms.application.vshost.exe' (Managed (v4.0.30319)): Loaded 'C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System.Windows.Forms\v4.0_4.0.0.0__b77a5c561934e089\System.Windows.Forms.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'hms.application.vshost.exe' (Managed (v4.0.30319)): Loaded 'C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System.Drawing\v4.0_4.0.0.0__b03f5f7f11d50a3a\System.Drawing.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'hms.application.vshost.exe' (Managed (v4.0.30319)): Loaded 'C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System\v4.0_4.0.0.0__b77a5c561934e089\System.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'hms.application.vshost.exe' (Managed (v4.0.30319)): Loaded 'C:\Windows\assembly\GAC_MSIL\Microsoft.VisualStudio.HostingProcess.Utilities.Sync\10.0.0.0__b03f5f7f11d50a3a\Microsoft.VisualStudio.HostingProcess.Utilities.Sync.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'hms.application.vshost.exe' (Managed (v4.0.30319)): Loaded 'C:\Windows\assembly\GAC_MSIL\Microsoft.VisualStudio.Debugger.Runtime\10.0.0.0__b03f5f7f11d50a3a\Microsoft.VisualStudio.Debugger.Runtime.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'hms.application.vshost.exe' (Managed (v4.0.30319)): Loaded 'D:\_Programowanie\_Moje projekty\OKIENKOWE\HMS\HMS\hms\branches\hms.application\bin\Debug\hms.application.vshost.exe', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'hms.application.vshost.exe' (Managed (v4.0.30319)): Loaded 'C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System.Configuration\v4.0_4.0.0.0__b03f5f7f11d50a3a\System.Configuration.dll', Skipped loading symbols. Modul -
Hi there good people ! I've got strange problem with query into C# code to MySQL database. The point is i got this query:
m_insert_cmd2 = new MySqlCommand("INSERT INTO tb_rezerwacja (kwota, ilosc_osob, w_tym_ilosc_dzieci, data_od, data_do, FK_pokoj_id, FK_zamowienie_id) VALUES (, '" + tbPrice.Text + "', '" + tbNumberOfPeople.Text + "', '" + tbChildren.Text + "', '" + tbStartDate.Text + "', '" + tbEndDate.Text + "', @pokoj_id, @zamowienie_id)", m_conn);
which is working but put all field in database to NULL without parameters : @pokoj_id and @rezerwacja_id - that's good. All tb.. are editboxes no matter what i write to them it always put nulls in database. I tried a lot of combinations with this query but i can;t do it working 100%. For example this query works fine (if i put static data) it's all put in database, no nulls:
m_insert_cmd2 = new MySqlCommand("INSERT INTO tb_rezerwacja (kwota, ilosc_osob, w_tym_ilosc_dzieci, data_od, data_do, FK_pokoj_id, FK_zamowienie_id) VALUES (' 345', '3', '3', '0000-00-21', '0000-00-21', @pokoj_id, @zamowienie_id)", m_conn);
What i do wrong, please help. best regards, nighttrain
Firstly, if at all possible, you should use parameterised queries, and if not, you must escape the contents of those text boxes before creating the query string. At the moment, if those are free text boxes, there is a huge SQL injection hole in this code. Secondly, you should be using a DateTimePicker or similar for the dates, and using myDateTimePicker.Value.ToString("yyyy-MM-dd") to get them in database format. Edit from the subthread below: If you are using parameterised queries, you should (of course) pass the date as a DateTime. The string formatting is only if you are building a query string. Ideally you should use a numeric text box type as well but inexplicably that doesn't exist in the standard framework. Thirdly, I suspect the immediate cause of the problem is that the dates are not in a valid format. See what the string you are actually creating really looks like, and compare it to a static query that works.
modified on Thursday, July 21, 2011 1:02 PM
-
Firstly, if at all possible, you should use parameterised queries, and if not, you must escape the contents of those text boxes before creating the query string. At the moment, if those are free text boxes, there is a huge SQL injection hole in this code. Secondly, you should be using a DateTimePicker or similar for the dates, and using myDateTimePicker.Value.ToString("yyyy-MM-dd") to get them in database format. Edit from the subthread below: If you are using parameterised queries, you should (of course) pass the date as a DateTime. The string formatting is only if you are building a query string. Ideally you should use a numeric text box type as well but inexplicably that doesn't exist in the standard framework. Thirdly, I suspect the immediate cause of the problem is that the dates are not in a valid format. See what the string you are actually creating really looks like, and compare it to a static query that works.
modified on Thursday, July 21, 2011 1:02 PM
BobJanova wrote:
using myDateTimePicker.Value.ToString("yyyy-MM-dd") to get them in database format
No, don't do that; just put the DateTime in a parameter.
-
Hi there good people ! I've got strange problem with query into C# code to MySQL database. The point is i got this query:
m_insert_cmd2 = new MySqlCommand("INSERT INTO tb_rezerwacja (kwota, ilosc_osob, w_tym_ilosc_dzieci, data_od, data_do, FK_pokoj_id, FK_zamowienie_id) VALUES (, '" + tbPrice.Text + "', '" + tbNumberOfPeople.Text + "', '" + tbChildren.Text + "', '" + tbStartDate.Text + "', '" + tbEndDate.Text + "', @pokoj_id, @zamowienie_id)", m_conn);
which is working but put all field in database to NULL without parameters : @pokoj_id and @rezerwacja_id - that's good. All tb.. are editboxes no matter what i write to them it always put nulls in database. I tried a lot of combinations with this query but i can;t do it working 100%. For example this query works fine (if i put static data) it's all put in database, no nulls:
m_insert_cmd2 = new MySqlCommand("INSERT INTO tb_rezerwacja (kwota, ilosc_osob, w_tym_ilosc_dzieci, data_od, data_do, FK_pokoj_id, FK_zamowienie_id) VALUES (' 345', '3', '3', '0000-00-21', '0000-00-21', @pokoj_id, @zamowienie_id)", m_conn);
What i do wrong, please help. best regards, nighttrain
-
Remove the single quotes for text fields if using alphabets in text. And # symbol to insert date.
i tried to use a new two parameters:
m_insert_cmd2.Parameters.Add("@DateStart", MySqlDbType.DateTime).Value = this.dateTimePicker1.Text;
m_insert_cmd2.Parameters.Add("@DateEnd", MySqlDbType.DateTime).Value = this.dateTimePicker2.Text;and tried like this:
m_insert_cmd2.Parameters.Add("@DateStart", MySqlDbType.DateTime).Value = this.dateTimePicker1.Text.ToString();
m_insert_cmd2.Parameters.Add("@DateEnd", MySqlDbType.DateTime).Value = this.dateTimePicker2.Text.ToString();the same problem is communicat:
String was not recognized as a valid DateTime.
my full query look like:
m_insert_cmd2 = new MySqlCommand("INSERT INTO tb_rezerwacja (kwota, ilosc_osob, w_tym_ilosc_dzieci, data_od, data_do, FK_pokoj_id, FK_zamowienie_id) VALUES (' 345', '3', '3', @DateStart, @DateEnd, @pokoj_id, @zamowienie_id)", m_conn);
on form i add 2 dateTimepickers like u see and in MySQL database the date fields are in Date format...
modified on Wednesday, July 20, 2011 3:00 PM
-
BobJanova wrote:
using myDateTimePicker.Value.ToString("yyyy-MM-dd") to get them in database format
No, don't do that; just put the DateTime in a parameter.
-
I did start my answer with 'if at all possible, use a parameterised query'. Yes, that is better if it's possible (and it almost always is).
Yes, but as a DateTime, not as a String.
-
Yes, but as a DateTime, not as a String.
I meant that formatter to only be used in the case that the OP couldn't use parameterised queries, so he was building up the query string (where obviously everything must be a string). But you are right, I didn't make that clear, so I'll go and edit my initial message. Thanks!
-
i tried to use a new two parameters:
m_insert_cmd2.Parameters.Add("@DateStart", MySqlDbType.DateTime).Value = this.dateTimePicker1.Text;
m_insert_cmd2.Parameters.Add("@DateEnd", MySqlDbType.DateTime).Value = this.dateTimePicker2.Text;and tried like this:
m_insert_cmd2.Parameters.Add("@DateStart", MySqlDbType.DateTime).Value = this.dateTimePicker1.Text.ToString();
m_insert_cmd2.Parameters.Add("@DateEnd", MySqlDbType.DateTime).Value = this.dateTimePicker2.Text.ToString();the same problem is communicat:
String was not recognized as a valid DateTime.
my full query look like:
m_insert_cmd2 = new MySqlCommand("INSERT INTO tb_rezerwacja (kwota, ilosc_osob, w_tym_ilosc_dzieci, data_od, data_do, FK_pokoj_id, FK_zamowienie_id) VALUES (' 345', '3', '3', @DateStart, @DateEnd, @pokoj_id, @zamowienie_id)", m_conn);
on form i add 2 dateTimepickers like u see and in MySQL database the date fields are in Date format...
modified on Wednesday, July 20, 2011 3:00 PM
anyone could answer plz