Re: SQL Query - write all in one query - Database Discussion Boards

Re: SQL Query - write all in one query

Mycroft Holmes1-Aug-12 16:25

Mycroft Holmes

1-Aug-12 16:25

Did you do some reading on how to use UNION or did you just stick it in between the select queries. RTFM

Never underestimate the power of human stupidity
RAH

Re: SQL Query - write all in one query

Raman samineni2-Aug-12 8:22

Raman samineni

2-Aug-12 8:22

Hello sir,

Just stick in between the select queries. I dont want use Union , Because this qry goes to in the program.

Regards

Raman

Re: SQL Query - write all in one query

Eddy Vluggen1-Aug-12 22:34

Eddy Vluggen

1-Aug-12 22:34

Raman samineni wrote:
I need to write all in one query

What would you gain from having it in a single statement? As PIEBALD stated, you can put them in a single SqlCommand and execute them.

The first Sql-statement will not work. Test it again, it lacks a FROM clause.

you could embed the count as a sub-select, like this;

SQL

SELECT E.FirstName +' ' + E.LastName AS Agent
      ,(SELECT COUNT(*) FROM CGCSLF WHERE Dispo = 'Comp') AS [Comp]
 FROM [cgs].dbo.Employ E with (NoLock)
WHERE E.EmploId = 2305

What the ELEPHANT is that NOLOCK doing there??
You need to pay attention to your formatting
EmploId is redundant; you're already in the "E" table, just name the damn thing "Id". That way all keys are named Id, saves some time typing and thinking, less chances on a typo, and a lot clearer when reading the code.

Bastard Programmer from Hell Suspicious | :suss:

if you can't read my code, try converting it here[^]

Re: SQL Query - write all in one query

Raman samineni2-Aug-12 8:19

Raman samineni

2-Aug-12 8:19

Hello sir

Thank you. My out put was

Supervisor- AgentName-Hours-Tran-Completed,

when I ran the qry i need to get this report.

Regards
Raman

Re: SQL Query - write all in one query

Eddy Vluggen2-Aug-12 8:59

Eddy Vluggen

2-Aug-12 8:59

Raman samineni wrote:
when I ran the qry i need to get this report.

You could put the other query in there too, as a subquery. The other queries might be combined, depending on "what" you are selecting from. I have not seen a corrected version of that code yet; I suggest you start writing and testing Smile | :)

Bastard Programmer from Hell Suspicious | :suss:

if you can't read my code, try converting it here[^]

Re: SQL Query - write all in one query

Raman samineni6-Aug-12 8:37

Raman samineni

6-Aug-12 8:37

hello sir,

How all three statments be written as one query.

Regards
Raman

Re: SQL Query - write all in one query

Raman samineni6-Aug-12 8:39

Raman samineni

6-Aug-12 8:39

how I will put in the sql Command all three query

Thanks
Raman

Re: SQL Query - write all in one query

Eddy Vluggen6-Aug-12 8:50

Eddy Vluggen

6-Aug-12 8:50

Raman samineni wrote:
how I will put in the sql Command all three query

Do you need them in a single "sql statement", or do you need them in a single "SqlCommand"? For either way you have been given a solution - did they not work, and if so, what went wrong?

Bastard Programmer from Hell Suspicious | :suss:

if you can't read my code, try converting it here[^]

Re: SQL Query - write all in one query

Raman samineni6-Aug-12 11:11

Raman samineni

6-Aug-12 11:11

send me both, No problem

Thanks
Raman

Re: SQL Query - write all in one query

Eddy Vluggen6-Aug-12 11:30

Eddy Vluggen

6-Aug-12 11:30

Raman samineni wrote:
send me both, No problem

AFAIK, we're volunteers here. Why should I provide two pieces of code??

The first query from your list is simply invalid, as it lacks a FROM statement. You can search on the internet what a "subquery" is and how to write one, and try to combine that code as suggested. Also reread PIEBALDs' suggestions, there's a good reason they get upvoted every time.

Good luck, you'll need it Dead | X|

Bastard Programmer from Hell Suspicious | :suss:

if you can't read my code, try converting it here[^]

Re: SQL Query - write all in one query

Raman samineni8-Aug-12 5:30

Raman samineni

8-Aug-12 5:30

hello sir,

My OUT PUT Report was

SuperName Agent Name Hours Trans Compl

Thanks
Raman

Re: SQL Query - write all in one query

Eddy Vluggen8-Aug-12 7:00

Eddy Vluggen

8-Aug-12 7:00

I can't help you, sorry.

We gave quite some answers and options, and your response was "write them both for me". Unless you can answer whether you need them in a single SqlCommand or as a single Sql-statement, there will not be any progress.

Now, it doesn't add anything of value to have them in a single statement, but I'm willing to help. When I say "help", I mean that I can point out how some things are done, it does not mean that I am going to write YOUR code.

Re: SQL Query - write all in one query

Raman samineni8-Aug-12 11:21

Raman samineni

8-Aug-12 11:21

yes, that is true, I need them in SQLCOMMAND or String query in the program

I appreciated

Raman

Re: SQL Query - write all in one query

Eddy Vluggen8-Aug-12 12:12

Eddy Vluggen

8-Aug-12 12:12

You're welcome Smile | :)

Bastard Programmer from Hell Suspicious | :suss:

if you can't read my code, try converting it here[^]

SSRS

Arul R Ece31-Jul-12 23:57

Arul R Ece

31-Jul-12 23:57

Hi friends,

I want to know the future of SSRS in SQL .

Re: SSRS

Eddy Vluggen1-Aug-12 2:48

Eddy Vluggen

1-Aug-12 2:48

Arul R Ece wrote:
I want to know the future

Hang on a second, while I go fetch my crystal ball.

Arul R Ece wrote:
of SSRS in SQL

There's a 95% chance that it will still be supported in the next five years.

Anything particular that you're worried about? If not, I suggest bookmarking their blog[^].

Bastard Programmer from Hell Suspicious | :suss:

if you can't read my code, try converting it here[^]

Replication Help

Mohamad Shahmardan31-Jul-12 23:09

Mohamad Shahmardan

31-Jul-12 23:09

i need source code of replication database in c#. who can help me?

Re: Replication Help

Eddy Vluggen1-Aug-12 0:51

Eddy Vluggen

1-Aug-12 0:51

pashmakjoon wrote:
i need source code of replication database in c#.

This is not a take-out restaurant.

pashmakjoon wrote:
who can help me?

MSDN[^].

Bastard Programmer from Hell Suspicious | :suss:

if you can't read my code, try converting it here[^]

Re: Replication Help

Mohamad Shahmardan1-Aug-12 5:48

Mohamad Shahmardan

1-Aug-12 5:48

i can't find your code.
where is your code?

Re: Replication Help

Pete O'Hanlon1-Aug-12 6:03

Pete O'Hanlon

1-Aug-12 6:03

He left it under a rock outside. Have you gone out and looked for it?

*pre-emptive celebratory nipple tassle jiggle* - Sean Ewington

"Mind bleach! Send me mind bleach!" - Nagy Vilmos

CodeStash - Online Snippet Management | My blog | MoXAML PowerToys | Mole 2010 - debugging made easier

Re: Replication Help

Eddy Vluggen1-Aug-12 11:01

Eddy Vluggen

1-Aug-12 11:01

I didn't supply any; the relevant sections are in the manual, and it takes some study and configuration.

Bastard Programmer from Hell Suspicious | :suss:

if you can't read my code, try converting it here[^]

Re: Replication Help

jschell1-Aug-12 6:21

jschell

1-Aug-12 6:21

pashmakjoon wrote:
i need source code of replication database in c#

Major databases have tools in the database itself which is used to replicate the database.

programmatically write changes back to Access 2007 using C#

Ming Luo31-Jul-12 5:42

Ming Luo

31-Jul-12 5:42

Dear all:

I am trying to use C# to communicate a small database build in Access 2007. I can read from the database and I can modify the values and save the changes back to database using the DataGridView. However, I am not sure how to modify the values programmatically.

Here is the code building the connection.

string g_ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\TestDB.accdb";
       
DataSet g_ds= new DataSet();
OleDbConnection g_con = new OleDbConnection(g_ConnectionString);
OleDbDataAdapter  g_adapter = new OleDbDataAdapter("Select * from qrySuggestion", g_con);
g_adapter.Fill(g_ds, "qrySuggestion");

this.dataGridView1.DataSource = g_ds.Tables[0];
g_con.Close();

I have a button on the form and when the button is clicked, save the changes back to the database. The code is as follows:

public void SaveTable(string tblName, DataTable tbl)
 {
     g_adapter = new OleDbDataAdapter("select * from " + tblName, g_con);

     OleDbCommandBuilder bu = new OleDbCommandBuilder(g_adapter);

     g_adapter.UpdateCommand = bu.GetUpdateCommand();
     g_adapter.Update(tbl);

     tbl.AcceptChanges();

     g_con.Close();
 }

I can use the above code to read from the database and display the data in the DataGridView with no problem. And I can make modification in the DataGridView and save it back by calling the SaveTable method.

However, the problem is that I want to make modification programmatically and reflect the changes back to the database. What I did was to add two statement at the beginning of the SaveTable method as:

DataRow row = tbl.Rows[0];
row[0] = 30;

assuming the column 0 i.e. row[0] is a int field.

When I add those two statement at the beginning of the SaveTable method. An exception will occur at g_adapter.Update(tbl); the exception is something like:

Missing DataColumn"pk_ID" in SourceColumn "pk_ID" in DataTable "tblSuggestionBase"

Can anyone help me out with the problem please?

Thanks

Asura

split string in MySQL

Jassim Rahma31-Jul-12 3:23

Jassim Rahma

31-Jul-12 3:23

Hi,

I want to know how can I split spilt in MySQL stored procedure.

I have this string Jassim Loves Banana

I want to split it by space and add x to it so it will be

xJassim xLove xBanana

Re: split string in MySQL

Blue_Boy31-Jul-12 5:14

Blue_Boy

31-Jul-12 5:14

use replace as

SQL

SELECT REPLACE('Jassim Loves Banana', ' ', ' x');

I Love T-SQL
"VB.NET is developed with C#.NET"
If my post helps you kindly save my time by voting my post.

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.