-
August 30th, 2010, 05:24 AM
#1
How to add a record if the result of a SQL query is empty
Hi,
I would like to check if some records exist in MySQL database and if they are missing I want to add some alternative information. I tried to use the following code but made a mistake. I am a beginner in c# and not sure how to do that. Please could you tell me what the problem is:
Thanks
telmessos
Code:
private void button1_Click(object sender, EventArgs e)
{
for (int has = 1; has <= 10; has++)
{
for (int ted = 1; ted <= 15; ted++)
{
for (int cin = 1; cin <= 20; cin++)
{
for (int yas = 1; yas <= 100; yas++)
{
connection.Open();
String testsql = "Select * from v2007 where HASTALIK_GRUBU='" + has + "' and TEDAVI_ILI='" + ted + "' and CINSIYET='" + cin + "' and YAS='" + yas + "'";
MySqlCommand testcommand = new MySqlCommand();
testcommand.CommandText = testsql;
testcommand.ExecuteNonQuery();
MySqlDataAdapter adapter2 = new MySqlDataAdapter();
adapter2.SelectCommand = testcommand;
DataTable dataset = new DataTable();
adapter2.Fill(dataset);
if (dataset.Rows.Count == 0)
{
String sql2 = "Insert into v2007(HASTALIK_GRUBU,TEDAVI_ILI,CINSIYET,YAS,AYAKTA,YATARAK) values('" + has + "','" + ted + "','" + cin + "','" + yas + "','0','0')";
MySqlCommand command2 = new MySqlCommand();
command2.CommandText = sql2;
command2.ExecuteNonQuery();
}
connection.Close();
}
}
}
}
label2.Text = "İşlem bitti";
}
-
August 30th, 2010, 08:27 AM
#2
Re: How to add a record if the result of a SQL query is empty
It would help you you told us what the error was.
Are the last 2 fields on your insert statement text or numeric data types? If numeric you need to loose the single quotes.
Always use [code][/code] tags when posting code.
-
August 30th, 2010, 09:41 AM
#3
Re: How to add a record if the result of a SQL query is empty
The error message is "Connection must be valid and open" but I have no idea why this error message shows. Because the connection is already open.
-
August 30th, 2010, 09:56 AM
#4
Re: How to add a record if the result of a SQL query is empty
Where are you opening the connection? I do not see code for that in what you provided.
Always use [code][/code] tags when posting code.
-
August 30th, 2010, 09:58 AM
#5
Re: How to add a record if the result of a SQL query is empty
Also looking at your code above it looks like the fields all contain numeric values. If these are numeric fields in the db then you shjould not use the 's
Always use [code][/code] tags when posting code.
-
August 30th, 2010, 12:46 PM
#6
Re: How to add a record if the result of a SQL query is empty
We shouldn't guess what's wrong with your code... provide some information about the error. Like... what's the Line number?
Also, you're attempting to connect to the database 15*20*100 times. Why not connecting outside the for loops?
I'd also consider to use a SELECT COUNT query, instead of selecting all the data, since all you really need is to check weather or not the Rows.Count is 0.
Alternatively you could select all the relevant data in one query and look for matches from the records placed in the Dataset.
-
August 30th, 2010, 03:38 PM
#7
Re: How to add a record if the result of a SQL query is empty
Change the SQL Statement:
Code:
Select * from v2007
Select
coalesce (Name, "none"),
coalesce (SomeTextField, "none"),
coalesce (SomeNumberField, 0)
From v2007
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|