Comments by mikybrain1 (Top 171 by date)

Ok. If i use the line var table = await loadTable((period1, period2)); and set it to the DGV it underline table saying "void" can not implicitly typed local variables are allocated.

And if I use this line: datagridview1.DataSource = datatable; it states the name database does not exist in the current context.

Thnx very much I hope I get my homework done.

Hi CHill60;
t is the alias of the tblSales. No I have from Jan - Dec and I have put them all into one table (tblSales) as u adviced me before. Have u please checked my image? If yes u can see that the products soap and cerelac are new products. That means they wern't in Feb. So I want to look through the table and check if the product is not in the first month but in the last month write me "new" and if the products are in both months write me "unchanged". I hope u understand me. I don't really know to get the right syntax.

Thnx.

@Richard. Thnx for the tip. But I do have one problem. I am using a where clause to differentiate the records: WHERE [t].[Monat] = @Periode1 OR [t].[Monat] = @Periode2 AND [t].[Finance] = ''AB'' but This line isn't applying: AND [t].[Region] = ''AB''. It still fires me the whole records Do u please have a clue?

No it's a NVARCHAR

Yeah. They do match match exactly. I have added a where clause to it and it seems to be functioning WHERE [t].[Monat] = ' + @Periode1 +' OR [t].[Monat] = '+ @Periode2 + ' AND [t].[Region] = ''AB'' but then the next problem. This line isn't applying. AND [t].[Region] = ''AB''. Do u please have a clue?

Thnx for your advice

I think it more convienent to apply but it isn't. Can u please help with examples?

Can u please give an example? I'm really stucked

@Chill60. It's all about winForms where the user have to choose the tables from two comboboxes and then get the expected results in a datagridview with a button. So my idea is the user choose the two table, with a sp union operator button it combines the chosen table. With a save button to save the combined tables in the database and then retrieve the records but with the select query. Is it logical?

Yeah I do understand you. I did the same where I created a union view from two tables and then did the SELECT query which worked. But that was based on two static tables. I want the user to have an option to choose which table he/she want to get the records.

Thnx anyway for the tips. I think I'm getting it now :). The foreach loop is working pretty good. I just need to advance & improve it. That's self help effort hahahaha.

@Maciej Los. I'm now getting the values in the first textBox if I debug but not the first value. I' m getting the last value. Is there a way to code like: Get the values from first row and first cell?

That's my edited code:

SqlDataAdapter adapter = new SqlDataAdapter(sqlCmd);
DataSet ds = new DataSet();
adapter.Fill(ds);
foreach(DataRow row in ds.Tables[0].Rows)
{
textBox1.Text = row["North"].ToString();
//textBox1.Text = ds.Rows[0].ItemArray[0].ToString();

}

unfortunately no. It says identifier expected. So i put string infront but no

I've tried this approach and it's not working

@ Maciej Los. Yes I've debug it and everything seems to be okay at this level adapter.Fill(dt);[The records are being fired into dt] BUT not getting into my textBoxes.

New screenshot: http://x53i.imgup.net/167327.PNG

Thnx dude like always. I did also solved it myself using CASE WHEN because it took u soooo long to answer hahahahaha. BUT anyway 'urs looks nicer.

I'm facing a defferent issue.

TOP

Thnx :)

hahaha. Thnx

Have updated my solution.

hahahaha. I did implemented 'ur suggestion and played around it till it worked. Thnx.

@ MaciejLos.I have updated my code (the entire code). I think my logic thinking has droped down.By the way I have edited my question and have post my entire code so far. Please check my comments @ in the method FilterGrant() and FilterLand().

Thnx dude. But one question :) I have three different treeview checkboxes [treeview1, treeview2 and treeview3]
[^]. I tried to Iterate throgh them usind foreach and later connect them together on the rowFilter level but is not functioning. But one good news is that if i seprate them it works.

Thnx.
What I'm intending to archieve to filter my datagridview almost like how excel works.
The datagridview is made up og 6 columns and the filter posibility are a treewview checkboxes [ root node and a child node].
That means I should be able to choose any checkbox dynamically from any of them and based on that the datagridview should sort out. My code above is what I have so far. Will appreciate ur help. The link for the screen shot below:

[^]
[^]

hahahaha
Do u know how i can upload an image here? it will be sooooo simply for the explaination ;)

I really understand u Maciej and u always help me But what if I've 8 options and I wanna click and filter 4. How would I Iterate through them. I wanna archieve something like Excel

If I change the text name of the treeview child node from string (Yes or No) to number,
it works fine and filters my datagridview. And I don't understand why

As I wrote the rowFilter returns Yes or No depending on what I clicked BUT dv.RowFilter isn't receiving it

It now states
The column Luft was not found.

sql server

Hi Original,
I'm getting this error:
The expression contains the undefined function call left ()
??

Ja i did that already but it autmactically changed the column AreaCode too which i don' wanna have the figures to be currency.

That's why I'm asking for help.

It just updates the chart area. it's not all the important. I can comment it out.

By the way my problem still not solved. I have cleared as u suggested without any success :(

Hi Digimanus
I' ve tried that but it' still populating the chart with only Q4

barChart.Series.Clear();
while (myReader.Read())
{

Hi Stephen,
I've updated my question. Can u please take a look whether u can help?

Thnx.
That was my fault.
HAve a nice day

I just tried ur suggestion but it just renamed ABC values to LUFT
ABC GWU VS
LUFT 100
LUFT 80
LUFT 50
LUFT 200
LUFT 220
LUFT 150
Instead of aggregating to a sing value
ABC GWU VS
LUFT 800

The ABC is a column name. The ABC Value of LUFT is my intention. I just wanna force these (collections) values [LA,LI,LS,LU,LX,LY] to be renamed LUFT so that instead of getting the my results, i did rather wanna get the expected value.

I hope my explaination is clear :)

Hi Saral,
The results were displayed horizontally which is ok but i'm try to change it to display vertically using the UNPIVOT without success. hmmm any quick help?

HiCan i int future use a where clause within the subquery if i have records in 2015?
Example: WHERE Year = '2015'

Thnx Dude :) Mine thinking was complicated.

HiUr results is plausible. That's what I'm supose to expect. By the way i haven't get ur modified answer yet to run against mine.

hahaha that's German

Hi Saral,
I was getting the same results so
I've updated my question with a larger sample [for Jan and Feb].

I hope u can help me out :)

Yes.
To get each repeated project once within a month. In my case it is counting each recieved or rejected which is wrong.
That means if a project [AB 007] occurs 10 and also have the value rejected 10 times, it then counts the rejcted value 10 times instead of counting them as 1.

Is it possible to get them within the month one time?

I've tried that already and gives me the value 1 everywhere(from Jan - Dec)

I guess my query logic isn't really right.
Let say if i have 20 Projects in Jan and one project (AB 007) repeats itself ten times with rejected. Because it repeats i wanna have: Jan AB 007 1 instead of this
Jan AB 007 10

@ CHill60
Thnx dude

@Magic

Not really (: How can implement that to get my result :)

@RyanDev. Its seems to be alright but I'm gonna cross check it with a calculator for the outcomes.

Thnx by the way

That was a tip mistake from me. Question has been edited. My Fault. The error still exist

hiI did tried that one but it threw an exception at the level:

dv.RowFilter = " [Areacode] = " + cbFilterEU.Text.Trim();

The Column can't be found.
I wanna have a message instead of the exception

@ all
Thanks for the support but i've solve it.

I don't really understand what u mean but i'm not getting anything like that

HiNo errors come. Let say if i select the first three cells with the values(up to down), it shows me the value 500 instead of the sum value

And if (down to up), it shows 200

Thnx

Thnx for ur help but it seems i'm a liitle bit naive and new to c# and can't integerate ur solution in my question.
But thanx thoug!!!

Hi Varun,
why this line?: Invoke((MethodInvoker)(() => dataGridView1.Rows.Add("item " + i)));

I don't wanna add an item to the dgv. I'm having an error: trying to add an iten

Thnx

Thanks for the link BUT my question was how can i connect it properly with my datagridview.

Yes i want it to calculate the sums and then populate the dgv based on the checkeboxes checked.

They are the column names
PN = ProjectNr
FI = FKZ
RE = Region
CI = City

I've updated my question

I've updated my question with the exact sp

My query is correct and it giving me the right records when execute with static tables.
Example: SELECT DISTINCT *
FROM tbl1 AS t1
LEFT OUTER JOIN tbl2 AS t2
ON t1.[Projektdefinition DB] = t2.[Projektdefinition DB]
WHERE t1.[DBN - Geschäftsbereich] = 'PB' AND t2.[DBN - Geschäftsbereich] = 'PB'
ORDER BY t1.[Projektdefinition DB]

And that's how i exec my sp:

EXEC spExample 'tbl2', 'tbl1'

Thnx experts But it still not working. It was my mistake with the ',' i shorten the code because it was too long

Ok. I tried that one too but the same error
ERROR 105 (GW_Per201408)
and 102 (False syntax in near from GW_Per201408)
Thnx

Hi Sinisa
Can u plz give an example for point 2 excluding the AutoGenerateColumns?

The values are in the DataTable if i debug BUT it not fetching in the cb

It's still not fetching in the cb

HiAs i debugged i saw the values from the column (projektiondefinition)
BUT
the combobox1 is still dispaly "System.Data.Dataview" and i still don't know why although i added the two lines u suggested

Still populating it with system.data.dataview

@ DamithSL
Thnx but it isn't working (:

Thnx

Hiwould u be kind and help please?

Hi,
yes please. The tables selected should be shown

Thnx

Hi Experts any help?

Hi Maciejo
I've been waiting for u all these days for a response

unfortunately no
Your statement suggestion do repeats it self and i' m not getting the results like in my experted solution. Can u please take a look again?

Hi CHill60
I've a pardon for u. Can u please help me with my new post?
http://www.codeproject.com/Questions/864495/How-to-get-all-differences

Thnx very very much for your patience

Thnx very very much for your patience
@ CHill60

It' a float or just ignore the commas

The columns are ProjectNr, Ist_value and Soll_value.
Ok i have this question as a statement which is running good
1.What soll-values are not in Ist value

BUT
I wanna have a ProjectNr(D.01025)
How many time it occurs (let say 3 times)
And sum all Soll values relating to it.
Something like this

D.01025 3 428206

My statement is ok but it' not counting and suming.

If i remove the soll value in the group by then i get error 8120.
That means i need to add it there.

HiBecause i'm trying to have the solution to the ff questions

1.What soll-values are not in Ist value
2.What Ist values are not in Soll values
3.What soll values are in Ist
4.What Ist values are in Soll

And because the projectnrs repeats, i wanna count and sum it values

HiI mean instaed of the outcome, i'm getting the result like

D.01025 1 19010
D.01025 1 0
D.01025 1 409196
.
.
.

it's counting each Projectnr one by one instead of aggregating it and sum the soll value relating to the projectnr

Thnx. I' m now tryin to count the Projectnr and also sum up the Ist or Soll value if the ProjectNr repeats.
My statement is counting but without an aggregation: Example if a Projectnr occurs 3 times and the Ist or Soll value also . it shd be like

D.0126438743 3 45500,00

Here my statement but it counting 1 by 1
D.0126438743 1 12000,00
D.0126438743 1 10000,00
D.0126438743 1 21500,00


SELECT t1.[Projectnr], count(t1.[Projectnr]) AS TotalOfProj, sum(t1.[Soll_value]) as Soll

FROM [dbo].[table1] t1
LEFT OUTER JOIN [dbo].[table1] t2
ON
t1.[Soll_value] = t2.[Ist_value]
WHERE t2.[Ist_value] IS NULL
GROUP BY t1.[Projectnr], t1.[Soll_value]
ORDER BY t1.[Projectnr] ASC

HiI just have one table not two tables. Is it possible?

HiI've updated my question :)

HiCan u please take s look and help me??

Hi Maciejo,

I'm now at home. Here is my code which isn't working

group dgvr by new
{
a = dgvr.Cells["Quarter"].Value,
b = dgvr.Cells["Option"].Value,
c = dgvr.Cells["Value"].Value
} into grp

select new
{
Quarter = grp.Key,
Amount = grp.Sum(x => Convert.ToInt32(x.Cells["Amount"].Value))
};
StringBuilder sb = new StringBuilder();
foreach (var ele in qry)
{
sb.AppendLine(String.Format("{0} = {1}", ele.Quarter, ele.Amount));
sb.AppendLine(String.Format("{1} = {2}", ele.Quarter, ele.Amount));
sb.AppendLine(String.Format("{2} = {3}", ele.Quarter, ele.Amount));

}
MessageBox.Show(sb.ToString(), "Total amount...");


I'm somehow depressed...

I'm on the road using my cellphone gonna write u back when I get home with the code

Hi Maciej
Happy new year!!!
I'm having difficulties with my solution 3 & 4
I did implement your suggestions but it's not working.
It's error at select new
Can you please edit you solution 3 & 4?

Hi Maciej
I have tried my best but it isn't working properly. It is still firing the columns of tblEmp even if i change the combobox column to tblPrdt. i get two main problems
1. i chanhed the query in the loaddata method what abt this line?:
// A new query to to display the records after checkedbox is clicked
string newq = "select " + qry + " from tblEmp where [city] = 'Brazil' ";

2. I couldn't figure out what u meant this in ur help:
//clear CheckedListBox
//add CheckBoxes to CheckedListBox here

I have also updated my post.

Hiit isn' dynamic bcos of this lines:
cmd.CommandText = ("SELECT [column_name] FROM information_schema.columns WHERE table_name = 'tblEmp' ");

and

string newq = "select " + qry + " from tblEmp where [city] = 'Brazil' ";

That means it always take the tblEmp and display its columns.

No the combobx isn't working that's why i have commented it out. It can only load columns and display in the checkedlistbox if Form1 page is loaded.

U guys are right i do know what i want and i have finished coding it but it isn't dynamic and my idea abt the combobox isn't working. I have edit my post

@ Maciej Thnx dude :) was helpful

Hi Marcin
sorry but i getcha worry u on this. Am trying to expand my filtering by adding two more comboboxes (cb3(age)and cb4(FName)). i have edited the help code u provided me but it isn't working right.

private void FilterData()
{
DataView dv = new DataView(_dt);

string filter = string.Empty;

var cityFilter = string.IsNullOrWhiteSpace(cb1.Text) ? "All" : cb1.Text.Trim();
var nameFilter = string.IsNullOrWhiteSpace(cb2.Text) ? "All" : cb2.Text.Trim();
var ageFilter = string.IsNullOrWhiteSpace(cb3.Text) ? "All" : cb3.Text.Trim();
var fnameFilter = string.IsNullOrWhiteSpace(cb4.Text) ? "All" : cb4.Text.Trim();

if (cityFilter != "All" && nameFilter == "All" && fFilter == "All" && ageFilter == "All")
{
filter = string.Format(" [City] = '{0}'", cityFilter);
}
else if (cityFilter == "All" && nameFilter != "All" && ageFilter == "All" && fnameFilter == "All")
{
filter = string.Format(" [LName] = '{0}'", nameFilter);
}
else if (cityFilter != "All" && nameFilter != "All" && ageFilter == "All" && fnameFilter == "All"))
{
filter = string.Format(" [City] = '{0}' AND [LName] = '{1}' AND [Age] = '{2}' AND [FName] = '{3}'", cityFilter, nameFilter, fnameFilter, ageFilter);
}

if (!string.IsNullOrEmpty(filter))
{
dv.RowFilter = filter;
}
dataGridView1.DataSource = dv;
}

Can u please help me again on that? will apreciate that!!!

something like(Filter): if cb3 = 25, cb2=Brown, cb1= Berlin and cb4 =Mike
Datagridview shld looks like:
Mike Brown 25 Berlin

Hi Sinisa
I do have ALL the statements already in a sp. but how should the implementations code in the form be like.
I can sum the columns something like this

// Sum and display Amount total
decimal sum = 0;
for (int i = 0; i < dgv1.Rows.Count; ++i)
{
decimal dec = 0;
decimal.TryParse(this.dgv1[8, i].FormattedValue.ToString(), out dec);
sum += dec;
}

sum = Math.Round(sum);
lbSum.Text = " Amount-Total " + sum.ToString();



So how can i solve it like this way?And how can i used multiple sp within a Button click method?

Thnx very much Dude :) Was really a great help.

I' ve updatet it. I 'trying to display certain columns of a dynamic table in a DB based on the selected items (also dynamic) to a second Form.
Would be glad if u can help

what do u please mean with "include the codes"? shd i post it for improvemennt?

Thnx the problem is that i don't wanna choose the column name but rather from the FROM clause dynamically. Is it possible? Or how can i get the records in a different way?

Thnx man for the encouragement. I appreciate that :)

I don't have any id and am a lili' bit confused. Can't u please illustrate based on my question?

yeah i understand u but i need help now about this problem if u can help

updated my question. was a mistake

Thnx very much. It was a great help and i wish u guys a nice day.
@Pikoh & Sinisa

Thnx Pikoh.
But it now fills the dgv automatically without choosing a table.
I wanna first choose a table b4 it loads the dgv

ok. Thnx

HiIt doesn't really indicate which line. It' a message box clue :)

I'm filling it with a self made method:
private void FillCombobox1()
{
// Sql Connection and statement
string C = (@"Data Source=");
SqlConnection con = new SqlConnection(C);


SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = ("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES");
try
{

con.Open();

SqlDataAdapter adapter = new SqlDataAdapter(cmd);

DataTable dt = new DataTable();
adapter.Fill(dt);

//Fill combobox with data in DT
comboBox1.DataSource = dt;

//Display dbo Tables in cbAlt
comboBox1.DisplayMember = "TABLE_NAME";
comboBox1.ValueMember = "TABLE_NAME";

// Empty bzw. clear the combobox
comboBox1.SelectedIndex = -1;

}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
con.Close();
}
}
Can u help?

The tbl looks like this:
tblEmployees
EmplName DeptDesignation salary
John Smith IT Research Research Analyst 23000.00
John Michael IT Operations Manager 25000.00
Will Smith IT Support Manager 13000.00
Lames Brown IT Support Manager 1000.00
Will Emi Support Sales 14000.00
Sam Diva IT Support Manager Assistant 17000.00

And the values from dgv1 looks like:
EmplName
John Smith
John Michael
Will Smith
Lames Brown
Will Emi
Sam Diva

Thatz what am try now. Do have an idea how the query shd be? Or a better approach?
private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
{

if (dataGridView1.Rows[e.RowIndex].Cells[e.ColumnIndex].Value != null)
{
//Connection string
string C = (@"Data Source= ");
SqlConnection con = new SqlConnection(C);

// TSQL sp to choose Content selected dynamic
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = ("");
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
try
{
// Open connection
con.Open();

// Save the results in the DT. Call the adapter statement and fill it in the DT
DataTable dt = new DataTable();
adapter.Fill(dt);
dataGridView2.AutoGenerateColumns = true;
dataGridView2.DataSource = dt;

}

catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
con.Close();
}

Unfortunately i don' have this event. And the results shd show in dgv2.

Can u please help me out with an illustration? i have tried a several times without any reasonable result

I thougt if i select or click a cell Content like eg. John Smith which is the only column in of dgv1, i can then open a connection, write a query and get the entire colums relating to John Smith and display in dgv2.
Something like this:
cmd.CommandText = ("select * from [dbo].[tblEmployees] where [EmplName] = selected cell);
??

Yes i have written the query which populates my dgv1 with just one column (EmplName) from the table in the database. Now i want to populate the second dgv2 with the entire colums (Dept, Designation and salary) if the cell with the EmplName is clicked.

I have tried this codes BUT it's showing me the same record in the dgv1

private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
if (dataGridView1.Rows[e.RowIndex].Cells[e.ColumnIndex].Value != null)
{
MessageBox.Show(dataGridView1.Rows[e.RowIndex].Cells[e.ColumnIndex].Value.ToString());
}

listBox1.Items.Add(dataGridView1.Rows[e.RowIndex].Cells["EmplName"].Value.ToString());

I'm expecting a result like this:
John Smith IT Research Research Analyst 23000.00
Is it possible?

Where don't u understand? Data from one gridview if selected to another

Thnx very much Phil for your support and your patient. I really appreciate it. Am gonna check your solution tomorrow and give you a feed back.

Thnx

hahaha i guess the language is really a barrier. U'r right the tables looks almost the same but the column Area isn't the same that's why am filltering by using the WHERE clause. The first answer is 100% correct.
If u cross check u will notice that for eg. TT.0161110 is in both tables but have a different Area = 'D' in tbl2( So that shd be a new record in tbl2 right?)
I just want to know if there is a trick to get the remaining colums to my answer 1 without having/getting the XX.recordsto it since they also have a different values at Finance (FE & V ) in tbl2.
In tbl1 there are 5 and 2 records to XX...506 and XX...600 respectively BUT in tbl2 there are 6 and 3 records.
My main focus is Answer 1 and its remaining columns. Is it possible? I have tried all i can but no success. Maybe it isn't possible :)

Yes. It should look in tblExample2 and tell me what's new there and not in tblExample1.
The actual queries will be like below respectively:

SELECT [ProjectNr], [ProjectName], [BookingNr]
FROM tblExample2 WHERE [Area] = 'D'
EXCEPT
SELECT [ProjectNr], [ProjectName], [BookingNr]
FROM tblExample1 WHERE [Area] = 'D'
ORDER BY [ProjectNr] DESC


SELECT [ProjectNr], [ProjectName], [BookingNr],[Finance],[Price A]
FROM (
SELECT [ProjectNr], [ProjectName], [BookingNr],[Finance],[Price A]

FROM tblExample2 WHERE [Area] = 'D'
EXCEPT
SELECT [ProjectNr], [ProjectName], [BookingNr],[Finance],[Price A]

FROM tblExample1 WHERE [Area] = 'D'
) AS T
GROUP BY [ProjectNr], [ProjectName], [BookingNr],[Finance],[Price A]
ORDER BY [ProjectNr] DESC

That will be great from u if u can help me accomplish my intention. My intention is to get the records like in possible Answer 3

Thnx

Good Morning,
So let us try again.Have done my home work but i still dont get it. First of i have two tables and the results after the differences depending on the sql sp.There are no primary keys but the two tables are just very identical.
The first possible answer has the a sp like this:
SET @Dynamictbl = N'SELECT [ProjectNr], [ProjectName], [BookingNr]
FROM ' + @Table_Name + ' WHERE [Area] = ''D''' +
' EXCEPT
SELECT [ProjectNr], [ProjectName], [BookingNr]
FROM ' + @Table_Name2 + ' WHERE [Area] = ''D''' +
' ORDER BY [ProjectNr] DESC '

And the 2. answer is based on this statement:

SET @Dynamictbl = N'SELECT [ProjectNr], [ProjectName], [BookingNr],[Finance],[Price A]
FROM (
SELECT [ProjectNr], [ProjectName], [BookingNr],[Finance],[Price A]

FROM ' + @Table_Name + ' WHERE [Area] = ''D''' +
' EXCEPT
SELECT [ProjectNr], [ProjectName], [BookingNr],[Finance],[Price A]

FROM ' + @Table_Name2 + ' WHERE [Area] = ''D''' +
') AS T ' +
' GROUP BY [ProjectNr], [ProjectName], [BookingNr],[Finance],[Price A]

ORDER BY [ProjectNr] DESC '

My Main Question is or the records which am really interested in sholud look like the first possible answer without the X's BuT with the other columns relating to it (like possible answer 3). Is that possible although the finance column to the X's are new in tbl Example2

I have updated my main from yesterday :)
Thanks for ur help

Yes i wanna filter by Area. Let me try to explain to u again.
I want to first have the difference of the two tables based on the ProjectNr WHERE Area = 'D' and if there is a difference (which there is), it should also give me the coresponding columns records related to the differences found.

I hope u understand me a lil' bit now. Thanks

don't really understand you. That a code from me trying to implement. The problem lies ')' +

Hi Maciej Los
I'm trying to implement your solution sugesstion a stroed procedure but it keep giving me the Error
"Incorrect syntax near Keyword Group". What am i doing wrong?



SET @Dynamictbl = N'SELECT [Products], [Price], [Finance]

FROM (
SELECT [Products], [Price], [Finance] FROM ' + @Table_Name + ' WHERE [Area] = ''PB''' +
' EXCEPT
SELECT [Products], [Price], [Finance]
FROM ' + @Table_Name2 + ' WHERE [Area] = ''PB''' +
')' +
' GROUP BY [Products], [Price], [Finance]
ORDER BY [Products] DESC '

ok thanks dude am gonna check it out.

Unfortunately no PK
I need the new products in T2 and it coresponding prices and finances

As i described in my question. i have two tables and wanna know whats new in T2. i wanna get something like the result table.
Would be helpful if u can help

Any suggestions? Please

hi! It wasn' helpful

Thnx it now fine

Thnx @Maciej Los. Tried both but isn't working

Hi
Am trying to limit my search with a WHERE clause but the spaces and '' ain't responding well and the EXCEPT is underlined
Thatz my try. What wrong?:

SET @Dynamictbl = N'SELECT DISTINCT [Projektdefinition DB] AS Zugänge
FROM ' + @Table_Name + ' WHERE [Geschäftsbereich] = PB '
' EXCEPT
SELECT DISTINCT [Projektdefinition DB] AS Zugänge
FROM ' + @Table_Name2 + ' WHERE [Geschäftsbereich] = PB'

I've checked it and it's working great :)

One question are u familiar with c# winForms? Cause i get a question maybe u can help

Thnx bro. Am now on the road but gonna check it out when i get back.
Thnx for helping me out

Yes u damm right but if i pass a second parameter like @Table_Name2 sysname it doesn't work. Can u please help me on that? I have abt 4 tables (201102, 201103, 201104, 201105) and i want to dynamically change them for the differences.
Would u be nice and Alter my stored procedure :)

U are the KING
BUT if i exec my
EXEC spDynamic1 '[dbo].[Per201102]' It returns me nothing just the Alias.
My question how can i exec my sp to see the diferance between the table [dbo].[Per201102] AND [dbo].[Per201101]

I tried this but error message too much given argument.
Thnx

So thatz my sp:

ALTER PROC spDynamic1
(
@Table_Name sysname
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Dynamictb2 nvarchar(255)

SET @Dynamictb2 = N'SELECT DISTINCT [Projektdefinition DB] AS Zugänge
FROM [' + @Table_Name + ']' +
' EXCEPT
SELECT DISTINCT [Projektdefinition DB] AS Zugänge
FROM ' + @Table_Name

EXECUTE sp_executesql @Dynamictb2

END
GO

EXEC spDynamic1 '[dbo].[Per201102]'


IF i run the ALTER sp everything seems to be fine BuT if i exec it that when the ERROR message pop in.
And even if i run only the statement u just posted its fine without a message error

Yes just tried it. I even tought of two arguments like this but it isn't working too:
EXEC spDynamic1 '[dbo].[Per2011]', '[dbo].[Per2012]'

thnx but it isn't working. Still the same message

Thanks for the quick response BUT i want to be able to choose the tables dynamically.
In ur example the table (Customers) is predefined and that what i don't want.
Do u feel me?

can u please edit my code if neccessary? am somehow absent in mind

jaa. I've tried it but it isn't working

Form1
open connection
new Form2().ShowDialog();
Form2.Passvalue = dataGridView1.DataSource;


public partial class DataGridView : Form2
{

private string Data;

public string Passvalue
{

get { return Data; }
set { Data = value; }

}

public DataGridView()
{
InitializeComponent();
}

private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
dataGridView1.DataSource = Data;
}

Hithere a no error in the code but if a run the app, it message me the error above and i don't know why

@ Ashok.
Yeah. There is no Error in the code but if i run the app, its message me
"Object reference not set to an instance of an object"

And i don't really know what to do

Thnx Dude. It´swas a great help. hope i can contact u again in case i get a problem.
Thnx very much

I thougt maybe something like this:
Year2009 A.023 East 10000€
Year2009 A.023 East 2000€
Year2009 A.023 East 5000€
Year2010 A.023 East 500€
Year2010 A.023 East 500€

I thougt maybe something like this:
Year2009 A.023 East 10000€
Year2009 A.023 East 2000€
Year2009 A.023 East 5000€
Year2010 A.023 East 500€
Year2010 A.023 East 500€

Ok. i want to get all the amount in year 2009 and 2010 based on the ProjNr.
That means if i set the projNr to A.023, sql server shd give me the amounts regarding A.023 in Year 2009 AND year 2010.
Do u get me now?

i also dont know. i just wanna get the Amount and location for both years

hiit is´nt working. i need only the columns listed above and the corresponding projNr
Thnx

Hi
Am getting problem retrieving information from two tables with exact the same colums.

The tables are:
Year 2009
projNr | location | Amount
A.023 East 10000€
A.023 East 2000€
A.023 East 5000€
C.100 East 8000€
Z.253 East 25000€



Year 2010
projNr | location | Amount
A.023 East 500€
A.023 East 500€
C.100 East 1000€
Z.253 East 5000€


Now i wanna find out the Amount from Year 2009 and 2010
based on projNr A.023 or maybe dynamically.

That what i query but it kept repeating the results:

SELECT a.proNr, a.Amount, a.location, b.Amount
FROM Year 2009 AS a
INNER JOIN Year 2010 AS b
ON a.projNr = b.projNr
WHERE a.projNr = 'A.023' AND b.projNr = 'A.023'

Can someone please help me? Instead of 5 records it repeating the records till 12 rows

Yes. The comboboxes are both fed with the tables and my problem now is how to merge these tables from each combobox and display the union or the merge statement in DataGridview
Something like this:
("SELECT * FROM" + combobox1.selectedValue
UNION ALL + "SELECT * FROM" + combobox2.selectedValue)
Thnx.

Yes.
Hi VCJ thankx for your question but i wanna choose each table from each combobox and then click a btn to display the result
I thougt something like this:
("SELECT * FROM" + combobox1.selectedValue
UNION ALL + "SELECT * FROM" + combobox2.selectedValue)
and maybe an IF Statement that checks the selected item and display the UNION solution in DataGridView if the submit btn is clicked
Can you please help me out?

Thank you bro ;)

Am now on the road. Will check it out when i get home.
Its was very nice of you helping me out. wish u a nice evening

wow that looks good but i wanna have something like this:
i want to choose dynamically from each combobox. i wanna try and picture it for you once more. Unforturnately i cant send u a screenshot of my winform but here is an illustration

cb1 cb2
[t1,t2,t3,...] [t1,t2,t3,t5,t7...] ->the two comboboxes

[t3] ->selectedItem in cb1 [t7] ->selectedItem in cb2

[btnUnion] ->if this button is clicked,

Datagridview
[ ] Contains the uion results
[ ]

This means i can change the cb values dynamically and get the union results
i can choose

[t3] [t4]
[t1] [t10]
[t4] [t8]
and so on
Hope u get me now

Hi. Thatz my code so far which only populates the two comboboxes

/* CREATE A FUNCTION: Code to fill in Combobox..
And have to call the funcktion later in the InitializedComponent */

private void FillCombobox1()
{
// Sql Connection and statement
string CS = (@"Data Source=local;Initial Catalog=DB;Integrated Security=True");
SqlConnection con = new SqlConnection(CS);

// Statement
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = ("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES");
try
{
// Open connection
con.Open();

SqlDataAdapter adapter = new SqlDataAdapter(cmd);

// Save the results in the DT. Call the adapter statement and fill it in the DT
DataTable dt = new DataTable();
adapter.Fill(dt);

//Fill combobox with data in DT
comboBox1.DataSource = dt;

//Display dbo Tables in cb
comboBox1.DisplayMember = "TABLE_NAME";
comboBox1.ValueMember = "TABLE_NAME";

// Empty bzw. clear the combobox
comboBox1.SelectedIndex = -1;

}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
con.Close();
}
}


/* CREATE a 2. FUNCTION: Code to fill in Combobox. I can always write my own function.
And have to call the funcktion later in the InitializedComponent */

private void FillCombobox2()
{
// Sql Connection and statement
string CS = (@"Data Source=local;Initial Catalog=DB;Integrated Security=True");
SqlConnection con = new SqlConnection(CS);

// Statement
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = ("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES");
try
{
// Open connection
con.Open();

SqlDataAdapter adapter = new SqlDataAdapter(cmd);

// Save the results in the DT. Call the adapter statement and fill it in the DT
DataTable dt = new DataTable();
adapter.Fill(dt);

//Fill combobox with data in DT
comboBox2.DataSource = dt;

//Display dbo Tables in cb
comboBox2.DisplayMember = "TABLE_NAME";
comboBox2.ValueMember = "TABLE_NAME";

// Empty bzw. clear the combobox
comboBox2.SelectedIndex = -1;

}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
con.Close();
}
}


// Populate dgv with the two comboboxes
private void button7_Click(object sender, EventArgs e)
{
// Sql Server Connection and statement
string CS = (@"Data Source=local;Initial Catalog=DB;Integrated Security=True");
SqlConnection con = new SqlConnection(CS);




// IF STATEMENT & Sql Server Connection and TSQL Statement
if (comboBox1.SelectedValue != null && comboBox2.SelectedValue != null)
{


SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = ("SELECT * FROM " + comboBox1.SelectedValue UNION ALL "SELECT * FROM" +comboBox2.SelectedValue);

// adapter is return the result in Datagridview
SqlDataAdapter adapter = new SqlDataAdapter(cmd);

try
{
// Open connection
con.Open();

// Save the result

Hi. Thats what i so far have:

/* CREATE A FUNCTION: Code to fill in Combobox1.
And have to call the funcktion later in the InitializedComponent */

private void FillCombobox1()
{
// Sql Connection and statement
string C = (@"Data Source=local;Initial Catalog=PracticeDatabase;Integrated Security=True");
SqlConnection con = new SqlConnection(CS);

// Statement
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = ("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES");
try
{
// Open connection
con.Open();

SqlDataAdapter adapter = new SqlDataAdapter(cmd);

// Save the results in the DT. Call the adapter statement and fill it in the DT
DataTable dt = new DataTable();
adapter.Fill(dt);

//Fill combobox with data in DT
comboBox1.DataSource = dt;

//Display dbo Tables in cb
comboBox1.DisplayMember = "TABLE_NAME";
comboBox1.ValueMember = "TABLE_NAME";

// Empty bzw. clear the combobox
comboBox1.SelectedIndex = -1;

}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
con.Close();
}
}


/* CREATE a 2. FUNCTION: Code to fill in Combobox2. I can always write my own function.
And have to call the funcktion later in the InitializedComponent */

private void FillCombobox2()
{
// Sql Connection and statement
string C = (@"Data Source=local;Initial Catalog=PracticeDatabase;Integrated Security=True");
SqlConnection con = new SqlConnection(CS);

// Statement
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = ("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES");
try
{
// Open connection
con.Open();

SqlDataAdapter adapter = new SqlDataAdapter(cmd);

// Save the results in the DT. Call the adapter statement and fill it in the DT
DataTable dt = new DataTable();
adapter.Fill(dt);

//Fill combobox with data in DT
comboBox2.DataSource = dt;

//Display dbo Tables in cb
comboBox2.DisplayMember = "TABLE_NAME";
comboBox2.ValueMember = "TABLE_NAME";

// Empty bzw. clear the combobox
comboBox2.SelectedIndex = -1;

}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
con.Close();
}
}


// Populate dgv with the two comboboxes
private void button7_Click(object sender, EventArgs e)
{
// Sql Server Connection and statement
string CS = (@"Data Source=SOPHIE\SQLEXPRESS;Initial Catalog=PracticeDatabase;Integrated Security=True");
SqlConnection con = new SqlConnection(CS);




// IF STATEMENT & Sql Server Connection and TSQL Statement
if (comboBox1.SelectedValue != null && comboBox2.SelectedValue != null)
{


SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = ("SELECT * FROM " + comboBox1.SelectedValue UNION ALL "SELECT * FROM" +comboBox2.SelectedValue);

// adapter is return the result in Datagridview
SqlDataAdapter adapter = new SqlDataAdapter(cmd);

try
{
// Open connection
con.Open();

I wanna have something like ur first suggestion
{one,two,three,four,five,six} ->table entries=6

Hi Santhosh
Thankx for your sugesstion but i wanna choose each table from each combobox and then click a btn to display the result without a where clause because all columns for the Tables are the same
I thougt something like this:
("SELECT * FROM" + combobox1.selectedValue
UNION ALL + "SELECT * FROM" + combobox2.selectedValue)
and maybe an IF Statement that checks the selected item and display the UNION solution in DataGridView if the submit btn is clicked
Can you please help me out?

Yes. The comboboxes are both fed with the tables and my problem now is how to merge these tables from each combobox and display the union or the merge statement in DataGridview
Something like this:
("SELECT * FROM" + combobox1.selectedValue
UNION ALL + "SELECT * FROM" + combobox2.selectedValue)
Thnx.

Yes.
Hi VCJ thankx for your question but i wanna choose each table from each combobox and then click a btn to display the result
I thougt something like this:
("SELECT * FROM" + combobox1.selectedValue
UNION ALL + "SELECT * FROM" + combobox2.selectedValue)
and maybe an IF Statement that checks the selected item and display the UNION solution in DataGridView if the submit btn is clicked
Can you please help me out?

Hi MukeshSagar thankx for your suggestion but i wanna choose each table from each combobox and then click a btn to display the result
I thougt something like this:
("SELECT * FROM" + combobox1.selectedValue
UNION ALL + "SELECT * FROM" + combobox2.selectedValue)
and maybe an IF Statement that checks the selected item and display the UNION solution in DataGridView if the submit btn is clicked
Can you please help me out?