SQL query

O

ofiras

Is there a command to do a query for an SQL database?
I managed to make a sql connection variable, and to take info from it
by making a SqlDataAdapter with select query in it, and filling the
dataset with it.
If I want to do an insert query, I can't, because it is not saved in
the DB, and when I close the application I can't access it any more.
This is what I did:

SqlConnection cn = new SqlConnection();
SqlDataAdapter da;

cn = table1TableAdapter.Connection;
cn.Open();
da = new SqlDataAdapter("INSERT INTO Table1(item,title,cost)
VALUES('milk','low fat','100')",cn);
da.InsertCommand = da.SelectCommand;
da.Fill(myDBds, "Items");
cn.Close();


How do I query?
Please help,
Ofir.
 
Z

zacks

Is there a command to do a query for an SQL database?
I managed to make a sql connection variable, and to take info from it
by making a SqlDataAdapter with select query in it, and filling the
dataset with it.
If I want to do an insert query, I can't, because it is not saved in
the DB, and when I close the application I can't access it any more.
This is what I did:

SqlConnection cn = new SqlConnection();
SqlDataAdapter da;

cn = table1TableAdapter.Connection;
cn.Open();
da = new SqlDataAdapter("INSERT INTO Table1(item,title,cost)
VALUES('milk','low fat','100')",cn);
da.InsertCommand = da.SelectCommand;
da.Fill(myDBds, "Items");
cn.Close();

How do I query?
Please help,
Ofir.
Click to expand...

SqlConnection cn = new SqlConnection();
SqlCommand cmd;

cn = table1TableAdapter.Connection;
cn.Open();
cmd = new SqlCommand("INSERT INTO Table1(item,title,cost)
VALUES('milk','low fat','100')",cn);
cmd.ExecuteNonQuery();
cmd.Dispose();
cn.Close();
 
J

Jon Skeet [C# MVP]

Is there a command to do a query for an SQL database?
I managed to make a sql connection variable, and to take info from it
by making a SqlDataAdapter with select query in it, and filling the
dataset with it.
If I want to do an insert query, I can't, because it is not saved in
the DB, and when I close the application I can't access it any more.
Click to expand...

The other reply explained how to do it, but the important thing to
note is that an insertion *isn't* a query - it's adding data, not
reading it.

Jon
 
O

ofiras

SqlConnection cn = new SqlConnection();
SqlCommand cmd;

cn = table1TableAdapter.Connection;
cn.Open();
cmd = new SqlCommand("INSERT INTO Table1(item,title,cost)
VALUES('milk','low fat','100')",cn);
cmd.ExecuteNonQuery();
cmd.Dispose();
cn.Close();
Click to expand...

Thenks, but still if I close the aplication, There is no "milk" item
any more.
Why the DB is not changed?
Please help,
Ofir.
 
J

Jon Skeet [C# MVP]

Thenks, but still if I close the aplication, There is no "milk" item
any more.
Why the DB is not changed?
Click to expand...

Do you have something else setting up a transaction? If so, you'll
need to commit the transaction.

Jon
 
Z

zacks

Thenks, but still if I close the aplication, There is no "milk" item
any more.
Why the DB is not changed?
Please help,
Ofir
Click to expand...

As another responder indicated, if the above code doesn't work, then
you aren't showing us the entire problem. I have done this same type
of code hundreds of time, shit thousands, and it works just fine.

Are you sure your code isn't throwing some exception you are telling
us about?
 
I

Ignacio Machin \( .NET/ C# MVP \)

Hi,


You do not need a dataadapter in this case, this code will work

SqlConnection cn = new SqlConnection();
cn.ConnectionStirng = "YOUR CONNECTION STRING";
SqlCommand cmd;

cn.Open();
cmd = new SqlCommand("INSERT INTO Table1(item,title,cost)
VALUES('milk','low fat','100')",cn);
cmd.ExecuteNonQuery();
cmd.Dispose();
cn.Close();
 
O

ofiras

Hi,

You do not need a dataadapter in this case, this code will work

SqlConnection cn = new SqlConnection();
cn.ConnectionStirng = "YOUR CONNECTION STRING";
SqlCommand cmd;

cn.Open();
cmd = new SqlCommand("INSERT INTO Table1(item,title,cost)
VALUES('milk','low fat','100')",cn);
cmd.ExecuteNonQuery();
cmd.Dispose();
cn.Close();
Click to expand...

Thank you all so much for trying to help me! I was trying to do this a
lot of time...
Actually, it's a windows application.
The full code of it is:

Using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace Store
{
public partial class Form1 : Form
{
SqlConnection cn = new SqlConnection();
SqlDataAdapter da;
SqlCommand cmd;

public Form1()
{
InitializeComponent();
cn.ConnectionString = "Data Source=.\
\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\Database1.mdf;Integrated
Security=true;User Instance=True";
}

private void button1_Click(object sender, EventArgs e)
{
cn.Open();
cmd = new SqlCommand("INSERT INTO Table1(item,title,cost)
VALUES('milk','low fat','100')", cn);
cmd.ExecuteNonQuery();
cmd.Dispose();
cn.Close();
}

private void Form1_Load(object sender, EventArgs e)
{


}

private void button2_Click(object sender, EventArgs e)
{
cn.Open();
da = new SqlDataAdapter("SELECT * FROM Table1 WHERE
cost=100", cn);
da.Fill(myDBds, "Items");
MessageBox.Show(myDBds.Tables["Items"].Rows[0]
["item"].ToString());
cn.Close();
}
}
}



The first button adds the "milk" item, and the second shows the item
so I can check if it exists.
If I press on the first one, and then the sound it's all ok, but if I
close the program, open it again and press the sound button there is
an exception that there is nothing with cost 100 - it wasn't saved in
the db itself, and I don't know where it was. I believe that in the
myDBds (the detasource).
 
Z

zacks

Hi,
Click to expand...
You do not need a dataadapter in this case, this code will work
Click to expand...
SqlConnection cn = new SqlConnection();
cn.ConnectionStirng = "YOUR CONNECTION STRING";
SqlCommand cmd;
Click to expand...
cn.Open();
cmd = new SqlCommand("INSERT INTO Table1(item,title,cost)
VALUES('milk','low fat','100')",cn);
cmd.ExecuteNonQuery();
cmd.Dispose();
cn.Close();
Click to expand...

Thank you all so much for trying to help me! I was trying to do this a
lot of time...
Actually, it's a windows application.
The full code of it is:

Using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace Store
{
public partial class Form1 : Form
{
SqlConnection cn = new SqlConnection();
SqlDataAdapter da;
SqlCommand cmd;

public Form1()
{
InitializeComponent();
cn.ConnectionString = "Data Source=.\
\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\Database1.mdf;Integrated
Security=true;User Instance=True";
}

private void button1_Click(object sender, EventArgs e)
{
cn.Open();
cmd = new SqlCommand("INSERT INTO Table1(item,title,cost)
VALUES('milk','low fat','100')", cn);
cmd.ExecuteNonQuery();
cmd.Dispose();
cn.Close();
}

private void Form1_Load(object sender, EventArgs e)
{

}

private void button2_Click(object sender, EventArgs e)
{
cn.Open();
da = new SqlDataAdapter("SELECT * FROM Table1 WHERE
cost=100", cn);
da.Fill(myDBds, "Items");
MessageBox.Show(myDBds.Tables["Items"].Rows[0]
["item"].ToString());
cn.Close();
}
}

}

The first button adds the "milk" item, and the second shows the item
so I can check if it exists.
If I press on the first one, and then the sound it's all ok, but if I
close the program, open it again and press the sound button there is
an exception that there is nothing with cost 100 - it wasn't saved in
the db itself, and I don't know where it was. I believe that in the
myDBds (the detasource).
Click to expand...

The only thing that doesn't look right is the insert statement implies
that the cost column is a varchar field (value wrapped with singe
quotes). But the Where clause on the select statement implies that it
is a numeric field (no single quotes).

Have you verified the actions with the backend? IE, Enterprise Manager
open table function or Query Analyzer?
 
O

ofiras

Thank you all so much for trying to help me! I was trying to do this a
lot of time...
Actually, it's a windows application.
The full code of it is:
Click to expand...
Using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
Click to expand...
namespace Store
{
public partial class Form1 : Form
{
SqlConnection cn = new SqlConnection();
SqlDataAdapter da;
SqlCommand cmd;
Click to expand...
public Form1()
{
InitializeComponent();
cn.ConnectionString = "Data Source=.\
\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\Database1.mdf;Integrated
Security=true;User Instance=True";
}
Click to expand...
private void button1_Click(object sender, EventArgs e)
{
cn.Open();
cmd = new SqlCommand("INSERT INTO Table1(item,title,cost)
VALUES('milk','low fat','100')", cn);
cmd.ExecuteNonQuery();
cmd.Dispose();
cn.Close();
}
Click to expand...
private void Form1_Load(object sender, EventArgs e)
{
}
Click to expand...

private void button2_Click(object sender, EventArgs e)
{
cn.Open();
da = new SqlDataAdapter("SELECT * FROM Table1 WHERE
cost=100", cn);
da.Fill(myDBds, "Items");
MessageBox.Show(myDBds.Tables["Items"].Rows[0]
["item"].ToString());
cn.Close();
}
}
}
Click to expand...

The first button adds the "milk" item, and the second shows the item
so I can check if it exists.
If I press on the first one, and then the sound it's all ok, but if I
close the program, open it again and press the sound button there is
an exception that there is nothing with cost 100 - it wasn't saved in
the db itself, and I don't know where it was. I believe that in the
myDBds (the detasource).
Click to expand...

The only thing that doesn't look right is the insert statement implies
that the cost column is a varchar field (value wrapped with singe
quotes). But the Where clause on the select statement implies that it
is a numeric field (no single quotes).

Have you verified the actions with the backend? IE, Enterprise Manager
open table function or Query Analyzer?
Click to expand...

Everything works, it's just that it doesn't insert what I wanted to
the DB.
I tested it, and it seems like he doesn't save it on the data source,
nor on the connection, nor on the data adaptor.
It seems like he writes it to a temporary DB, and when I close the DB
it is gone, or it might be that he writes it to the db, and erase when
I close the application.
Can you please try it on your computer and tell me why is it
happening?
You can download it from http://www.mediafire.com/?bbe0yty4mzs
To try it I press on the first button (the one that says "Press!!!")
and then on the second one. That should show you a message that says
"milk", but if you the application, open it again, and press on the
second button without pressing the first, it shows an exception while
it shouldn't.

Thank you all so much,
Ofir.
 
O

ofiras

Thank you all so much for trying to help me! I was trying to do this a
lot of time...
Actually, it's a windows application.
The full code of it is:
Click to expand...
Using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
Click to expand...
namespace Store
{
public partial class Form1 : Form
{
SqlConnection cn = new SqlConnection();
SqlDataAdapter da;
SqlCommand cmd;
Click to expand...
public Form1()
{
InitializeComponent();
cn.ConnectionString = "Data Source=.\
\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\Database1.mdf;Integrated
Security=true;User Instance=True";
}
Click to expand...
private void button1_Click(object sender, EventArgs e)
{
cn.Open();
cmd = new SqlCommand("INSERT INTO Table1(item,title,cost)
VALUES('milk','low fat','100')", cn);
cmd.ExecuteNonQuery();
cmd.Dispose();
cn.Close();
}
Click to expand...
private void Form1_Load(object sender, EventArgs e)
{
}
Click to expand...

private void button2_Click(object sender, EventArgs e)
{
cn.Open();
da = new SqlDataAdapter("SELECT * FROM Table1 WHERE
cost=100", cn);
da.Fill(myDBds, "Items");
MessageBox.Show(myDBds.Tables["Items"].Rows[0]
["item"].ToString());
cn.Close();
}
}
}
Click to expand...

The first button adds the "milk" item, and the second shows the item
so I can check if it exists.
If I press on the first one, and then the sound it's all ok, but if I
close the program, open it again and press the sound button there is
an exception that there is nothing with cost 100 - it wasn't saved in
the db itself, and I don't know where it was. I believe that in the
myDBds (the detasource).
Click to expand...

The only thing that doesn't look right is the insert statement implies
that the cost column is a varchar field (value wrapped with singe
quotes). But the Where clause on the select statement implies that it
is a numeric field (no single quotes).

Have you verified the actions with the backend? IE, Enterprise Manager
open table function or Query Analyzer?
Click to expand...

Everything works; it's just that it doesn't insert what I wanted to
the DB.
I tested it, and it seems like he doesn't save it on the data source,
nor on the connection, nor on the data adaptor.
It seems like he writes it to a temporary DB, and when I close the DB
it is gone, or it might be that he writes it to the db, and erase when
I close the application.
Can you please try it on your computer and tell me why is it
happening?
You can download it from http://www.mediafire.com/?bbe0yty4mzs
To try it I press on the first button (the one that says "Press!!!")
and then on the second one. That should show you a message that says
"milk", but if you the application, open it again, and press on the
second button without pressing the first, it shows an exception while
it shouldn't.

Thank you all so much,
Ofir.
 
Z

zacks

On Jun 1, 3:55 pm, "Ignacio Machin \( .NET/ C# MVP \)" <machin TA
laceupsolutions.com> wrote:
Hi,
You do not need a dataadapter in this case, this code will work
SqlConnection cn = new SqlConnection();
cn.ConnectionStirng = "YOUR CONNECTION STRING";
SqlCommand cmd;
cn.Open();
cmd = new SqlCommand("INSERT INTO Table1(item,title,cost)
VALUES('milk','low fat','100')",cn);
cmd.ExecuteNonQuery();
cmd.Dispose();
cn.Close();
Thank you all so much for trying to help me! I was trying to do this a
lot of time...
Actually, it's a windows application.
The full code of it is:
Using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace Store
{
public partial class Form1 : Form
{
SqlConnection cn = new SqlConnection();
SqlDataAdapter da;
SqlCommand cmd;
public Form1()
{
InitializeComponent();
cn.ConnectionString = "Data Source=.\
\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\Database1.mdf;Integrated
Security=true;User Instance=True";
}
private void button1_Click(object sender, EventArgs e)
{
cn.Open();
cmd = new SqlCommand("INSERT INTO Table1(item,title,cost)
VALUES('milk','low fat','100')", cn);
cmd.ExecuteNonQuery();
cmd.Dispose();
cn.Close();
}
private void Form1_Load(object sender, EventArgs e)
{
}
private void button2_Click(object sender, EventArgs e)
{
cn.Open();
da = new SqlDataAdapter("SELECT * FROM Table1 WHERE
cost=100", cn);
da.Fill(myDBds, "Items");
MessageBox.Show(myDBds.Tables["Items"].Rows[0]
["item"].ToString());
cn.Close();
}
}
}
The first button adds the "milk" item, and the second shows the item
so I can check if it exists.
If I press on the first one, and then the sound it's all ok, but if I
close the program, open it again and press the sound button there is
an exception that there is nothing with cost 100 - it wasn't saved in
the db itself, and I don't know where it was. I believe that in the
myDBds (the detasource).
Click to expand...
Click to expand...
The only thing that doesn't look right is the insert statement implies
that the cost column is a varchar field (value wrapped with singe
quotes). But the Where clause on the select statement implies that it
is a numeric field (no single quotes).
Click to expand...
Have you verified the actions with the backend? IE, Enterprise Manager
open table function or Query Analyzer?
Click to expand...

Everything works, it's just that it doesn't insert what I wanted to
the DB.
I tested it, and it seems like he doesn't save it on the data source,
nor on the connection, nor on the data adaptor.
It seems like he writes it to a temporary DB, and when I close the DB
it is gone, or it might be that he writes it to the db, and erase when
I close the application.
Can you please try it on your computer and tell me why is it
happening?
You can download it fromhttp://www.mediafire.com/?bbe0yty4mzs
To try it I press on the first button (the one that says "Press!!!")
and then on the second one. That should show you a message that says
"milk", but if you the application, open it again, and press on the
second button without pressing the first, it shows an exception while
it shouldn't.
Click to expand...

It is starting to sound like a connection issue. I do not have or have
ever used SQLExpress, so sorry, I cannot test your code.
 
O

ofiras

On Jun 1, 5:10 pm, (e-mail address removed) wrote:
On Jun 1, 3:55 pm, "Ignacio Machin \( .NET/ C# MVP \)" <machin TA
laceupsolutions.com> wrote:
Hi,
You do not need a dataadapter in this case, this code will work
SqlConnection cn = new SqlConnection();
cn.ConnectionStirng = "YOUR CONNECTION STRING";
SqlCommand cmd;
cn.Open();
cmd = new SqlCommand("INSERT INTO Table1(item,title,cost)
VALUES('milk','low fat','100')",cn);
cmd.ExecuteNonQuery();
cmd.Dispose();
cn.Close();
Thank you all so much for trying to help me! I was trying to do this a
lot of time...
Actually, it's a windows application.
The full code of it is:
Using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace Store
{
public partial class Form1 : Form
{
SqlConnection cn = new SqlConnection();
SqlDataAdapter da;
SqlCommand cmd;
public Form1()
{
InitializeComponent();
cn.ConnectionString = "Data Source=.\
\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\Database1.mdf;Integrated
Security=true;User Instance=True";
}
private void button1_Click(object sender, EventArgs e)
{
cn.Open();
cmd = new SqlCommand("INSERT INTO Table1(item,title,cost)
VALUES('milk','low fat','100')", cn);
cmd.ExecuteNonQuery();
cmd.Dispose();
cn.Close();
}
private void Form1_Load(object sender, EventArgs e)
{
}
private void button2_Click(object sender, EventArgs e)
{
cn.Open();
da = new SqlDataAdapter("SELECT * FROM Table1 WHERE
cost=100", cn);
da.Fill(myDBds, "Items");
MessageBox.Show(myDBds.Tables["Items"].Rows[0]
["item"].ToString());
cn.Close();
}
}
}
The first button adds the "milk" item, and the second shows the item
so I can check if it exists.
If I press on the first one, and then the sound it's all ok, but if I
close the program, open it again and press the sound button there is
an exception that there is nothing with cost 100 - it wasn't saved in
the db itself, and I don't know where it was. I believe that in the
myDBds (the detasource).
The only thing that doesn't look right is the insert statement implies
that the cost column is a varchar field (value wrapped with singe
quotes). But the Where clause on the select statement implies that it
is a numeric field (no single quotes).
Have you verified the actions with the backend? IE, Enterprise Manager
open table function or Query Analyzer?
Click to expand...
Click to expand...
Everything works, it's just that it doesn't insert what I wanted to
the DB.
I tested it, and it seems like he doesn't save it on the data source,
nor on the connection, nor on the data adaptor.
It seems like he writes it to a temporary DB, and when I close the DB
it is gone, or it might be that he writes it to the db, and erase when
I close the application.
Can you please try it on your computer and tell me why is it
happening?
You can download it fromhttp://www.mediafire.com/?bbe0yty4mzs
To try it I press on the first button (the one that says "Press!!!")
and then on the second one. That should show you a message that says
"milk", but if you the application, open it again, and press on the
second button without pressing the first, it shows an exception while
it shouldn't.
Click to expand...

It is starting to sound like a connection issue. I do not have or have
ever used SQLExpress, so sorry, I cannot test your code.
Click to expand...

Ok, thanks.
Dose someone have an example for SQL insert, select or things like
that?
Everything will work, but SQLExpress will be better.
If you have, please send it to me, or upload it and write the link
here.
Thanks a lot,
Ofir.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

ListView DataView, it shows me this letters "System.Data.Row.View" 1
Strongly typed DataTables 1
REPEATER PROBLEM 2
Column Width in a Gridview 1
About DataTable.Roes.Find 1
LinQ To Sql Provider -- do we have to create our own? 8
A Living Data Connection 1
How to Get Selected Value from ComboBox 2

Top