Comments by Member 10376341 (Top 68 by date)

Thank you for the help @Karthik @Sinisa Hajnal.

I figured it out.

Regards

I figured it out how to create a new Table in the Azure Database.

Thank you for your help.

Thank you for the help.

You are right i don't see my tables in the Azure Database. But how come i can see default tables?

Also, please let me know what to do next. How should i add my own tables.

How do i view Database Tables in Azure portal?

Here is my Code behind @Karthik Bangalore:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Configuration;
using System.Web.Configuration;

namespace Synch
{
public partial class Centre_Cre : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

}

protected void Button1_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString);
string IT_ITES = "SELECT (SELECT Count(case when [IT-ITES1] = 'Approved' then 1 end) FROM Capacity WHERE State = '" + DropDownList1.SelectedValue + "')";
string RETAIL = "SELECT (SELECT Count(case when [RETAIL1] = 'Approved' then 1 end) FROM Capacity WHERE State = '" + DropDownList1.SelectedValue + "')";
string LSC = "SELECT (SELECT Count(case when [LSC1] = 'Approved' then 1 end) FROM Capacity WHERE State = '" + DropDownList1.SelectedValue + "')";
string ESSCI = "SELECT (SELECT Count(case when [ESSCI1] = 'Approved' then 1 end) FROM Capacity WHERE State = '" + DropDownList1.SelectedValue + "')";
string TSSC = "SELECT (SELECT Count(case when [TSSC1] = 'Approved' then 1 end) FROM Capacity WHERE State = '" + DropDownList1.SelectedValue + "')";
string THSC = "SELECT (SELECT Count(case when [THSC1] = 'Approved' then 1 end) FROM Capacity WHERE State = '" + DropDownList1.SelectedValue + "')";
string GJSCI = "SELECT (SELECT Count(case when [GJSCI1] = 'Approved' then 1 end) FROM Capacity WHERE State = '" + DropDownList1.SelectedValue + "')";
string ASCI = "SELECT (SELECT Count(case when [ASCI1] = 'Approved' then 1 end) FROM Capacity WHERE State = '" + DropDownList1.SelectedValue + "')";
string CSDCI = "SELECT (SELECT Count(case when [CSDCI1] = 'Approved' then 1 end) FROM Capacity WHERE State = '" + DropDownList1.SelectedValue + "')";
string APPAREL = "SELECT (SELECT Count(case when [APPAREL1] = 'Approved' then 1 end) FROM Capacity WHERE State = '" + DropDownList1.SelectedValue + "')";
//string TOTAL = "SELECT (SELECT Count(case when [CSDCI1] = 'Approved' then 1 end) FROM Capacity WHERE State = '" + DropDownList1.SelectedValue + "')";
SqlCommand showresult1 = new SqlCommand(IT_ITES, con);
SqlCommand showresult2 = new SqlCommand(RETAIL, con);
SqlCommand showresult3 = new SqlCommand(LSC, con);
SqlCommand showresult4 = new SqlCommand(ESSCI, con);
SqlCommand showresult5 = new SqlCommand(TSSC, con);
SqlCommand showresult6 = new SqlCommand(THSC, con);
SqlCommand showresult7 = new SqlCommand(GJSCI, con);
SqlCommand showresult8 = new SqlCommand(ASCI, con);
SqlCommand showresult9 = new SqlCommand(CSDCI, con);
SqlCommand showresult0 = new SqlCommand(APPAREL, con);
//SqlCommand showresult2 = new SqlCommand(TOTAL, con);

con.Open();
lblstate.Text = DropDownList1.SelectedValue;
lblTOTAL.Text = (Int32.Parse(lblIT.Text) + Int32.Parse(lblRETAIL.Text) + Int32.Parse(lblLSC.Text) + Int32.Parse(lblESSCI.Text) + Int32.Parse(lblTSSC.Text) + Int32.Parse(lblTHSC.Text) + Int32.Parse(lblGJSCI.Text) + Int32.Parse(lblASCI.Text) + Int32.Parse(lblCSDCI.Text) + Int32.Parse(lblAPPAREL.Text)).ToString();

}
}

@Sinisa Hajnal. No i didn't change any connection strings. Also Default Login seems working.
You were talking about Published scope, can you please elaborate it.

I have changed <customerror> in Web.Config and set it to 'Off'. Now the previous error vanished and now it throws me "Invalid object name 'Table_Name'". I know that it is a SQL error and that the server is not picking up the exact table from the database but i configured the connection correctly and more over i used Visual Studio Default template for Login and so added custom tables directly into the default database.

The Login seems working but not the pages which i have created.

Any help please..Unable to view it correctly.
Am i missing any steps to deploy?

I used KUDU to view the files.

Yes, i have logged in Azure Server and i can see the file(Test.aspx). Still not accessible.

Thank you so much. Appreciate your help.

Regards

One more thing i wanted to ask you. How do i save it with the column name?
Now it shows the result as (No Column Name).

Thank you got the result.
Thank you so much for your time.

Am trying to count all the records which are relevant to the particular Name and ID

Yes we got the result. But how do i count it, in a table there will be similar name and ID.
It will be nice if you can throw a light on my query.


(SELECT Count(case when [Bank_Approved] IS NOT NULL then 1 end)FROM Table2 WHERE [Certified] = 'Yes' group by Table1.[Name] ) as Bank_Cert

Thank you for the reply, well it gives me all the data present in the respective tables. I need the resultant data to be segregated with the name.

The result shows zero for all Names.

Well i have Name, ID, Bank Approved, Certified columns in two tables. Based on the two am extracting a result, where the result should be bind to two rules
1. Bank should be approved.
2. It should be Certified.

Please, refer the example tables.

Thank you for the reply. Can you give me an example?

Thank you for showing me a right path. I shall now definitely find out the issue using the debugger.

Thanks
Saikrishna

Yes, you told me to put breakpoint and see the results. I was googling around and came across the code which is apt for me, so i adapted it, still am unable to insert data into the database.

What is wrong in my code. Unable to import data from Excel to SQL Server.


using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Collections;
using System.Data.SqlClient;
using System.IO;
using System.Configuration;

namespace SimpleExcelLoader
{
public partial class Form1 : Form
{
string filename="";

public Form1()
{
InitializeComponent();
}

private void button1_Click(object sender, EventArgs e)
{
OpenFileDialog openFileDialog1 = new OpenFileDialog();

openFileDialog1.InitialDirectory = "c:\\";
openFileDialog1.Filter = "Excel 2007 files (*.xlsx)|*.xlsx|All files (*.*)|*.*";
openFileDialog1.FilterIndex = 2;
openFileDialog1.RestoreDirectory = true;

if (openFileDialog1.ShowDialog() == DialogResult.OK)
{
filename = openFileDialog1.FileName;
textBox1.Text = filename;
button2.Enabled = true;
toolStripStatusLabel1.Text = "File Selected. Enter DB Info and click Upload Data";

}
}

private void button2_Click(object sender, EventArgs e)
{



//step 1
// Load Excel data into DataTable
string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + filename + ";Extended Properties='Excel 12.0;HDR=YES;'";

string strSQL = "SELECT * FROM [Sheet1$]";
OleDbConnection excelConnection = new OleDbConnection(connectionString);
excelConnection.Open(); // This code will open excel file.

OleDbCommand dbCommand = new OleDbCommand(strSQL, excelConnection);
OleDbDataAdapter dataAdapter = new OleDbDataAdapter(dbCommand);

// create data table
DataTable dTable = new DataTable();
//fill table with echel data
dataAdapter.Fill(dTable);

//step 2
//connect to server


string str = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
using (SqlConnection con = new SqlConnection(str))
{

con.Open();


using (SqlBulkCopy bulkCopy =
new SqlBulkCopy(con))
{
//Destination Table must match columns in Excel sheet
bulkCopy.DestinationTableName = "dbo.Test";

try
{
// Write from the source to the destination.
bulkCopy.WriteToServer(dTable);
toolStripStatusLabel1.Text="Data Uploaded";
}
catch (Exception ex)
{

toolStripStatusLabel1.Text=ex.Message;
}

con.Close();

}


}



// dispose used objects
dTable.Dispose();
dataAdapter.Dispose();
dbCommand.Dispose();

excelConnection.Close();
excelConnection.Dispose();
}


}


}

Thank you for the reply Danish. Can i get the Ref link?

Hello!

I have deleted the DataGridView and now a new exception raised: "The Column from the data source doesn't match". I have inspected thoroughly and there was no different column names in source and destination tables.

What could be the problem?

Thank you for the reply.
I guess the last line gives me the answer. I seriously think i made a mistake taking data from the DataGridView. I shall recode and take the data from DataTable instead.

Thanks for your time.

Ya been working alot, it was great talking with you and c ya.
Good night!

Ohh ok. i live in INDIA and its 3:40AM. :P

That was quick reply. I owe you Andy, thank you very much.

Greatly appreciate your gesture.

Thanks again!

Hello Andy,

Is there any way to execute the function without group by?
Since am using the query in C# and i suppose we cant pass the value to the Label.

Any help?

Hey! it worked.
Thank you Andy Lanng. Thanks for your time, really appreciate that.

Regards
Saiekrishna

Thank you Praveen for your time. I have figured it out the error.
It was a simple syntax error.

Thanks
Saiekrishna

Hey! thank you so much Manas Kumar, your code worked for me.
Cheers!

Thanks
Saiekrishna

Thank you for the reply Snorkie.
Am relatively new to this SQL am still figuring out how to implement the triggers for my query.
Can you please help me out in segregating the right code?

Thanks

I'm able to execute the query individually but not in the group.
Any other possible solution?

Yes i have done that previously but couldn't get the result.

Tried:
SELECT Results_Pass, Results_Fail, Results_NA,
Total_declared = Results_Pass + Results_Fail + Results_NA
FROM Partner_cre

Got the solution. Now i need to incorporate this query in the first query, that is:

INSERT INTO TABLE3
SELECT Capacity.[SPOC-Partner],
COUNT(PMKVY.CentreID) as Candidate,
COUNT(case when [Aadhar Validation-Aadhar Number Validated] = 'Yes' then 1 end) as Validated,
COUNT(case when [Result Approved by SSC] = 'Pass' then 1 end) as Results_Pass,
COUNT(case when [Result Approved by SSC] = 'Fail' then 1 end) as Results_Fail,
COUNT(case when [Result Approved by SSC] = 'No Value' then 1 end) as Results_NA,
SUM(case when [BankAcountNumber] IS NULL then 0 else 1 end) as Bank,
COUNT(case when [Certified] = 'Yes' then 1 end) as Certified,
COUNT(case when [Monetary Reward Tracker-NSDC Processed] = 'Yes' then 1 end) as NSDC_Certified,
SUM(case when [Monetary Reward Tracker-Disbursement List] = '0' then 0 else 1 end) as DL,
Capacity.[Total Target],
sum(calculated Results_Pass ,calculated Results_Fail, calculated Results_NA) as Total_declared,

FROM PMKVY
INNER JOIN Capacity ON PMKVY.CentreID=Capacity.[Training Centre ID]
GROUP BY Capacity.[SPOC-Partner],Capacity.[Total Target]


is it possible?

I tried with the stored procedure some time back but still am unable to get the result.

it is numeric type.

All my work is abruptly stalled because of this error.

Am using SQL Server 2014

I tried calculating with other option too but in vain, same error.

Tried:
SELECT Results_Pass, Results_Fail,Results_NA,
sum (Calculated Results_Pass + Calculated Results_Fail + Calculated Results_NA ) as Total,
FROM Partner_cre

Error:
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'Results_Pass'.

First option worked for me.
Thank you Manas Kumar, i appreciate your help.

Regards
Saiekrishna

Can you explain me briefly, i couldn't get result specified by you.

Am using ASP template. Nothing changed.

No am not using Stored Procedure
Well when i use: sum(Results_Pass)+ sum(Results_Fail)+ sum(Results_NA)
I get this error:
Msg 207, Level 16, State 1, Line 11
Invalid column name 'Results_Pass'.
Msg 207, Level 16, State 1, Line 11
Invalid column name 'Results_Fail'.
Msg 207, Level 16, State 1, Line 11
Invalid column name 'Results_NA'.

And when i use: Calculated Results_Pass + Calculated Results_Pass + Calculated Results_Pass
I get:
Msg 102, Level 15, State 1, Line 11
Incorrect syntax near '+'.

This time the error is:

Msg 174, Level 15, State 1, Line 11
The sum function requires 1 argument(s).

Nope! same error remains.

Msg 102, Level 15, State 1, Line 11
Incorrect syntax near 'Results_Pass'.

Error is in the "line 11" that is:
sum(calculated Results_Pass ,calculated Results_Fail, calculated Results_NA) as Total_declared,

Thank you for the reply.
Well, i have tried it but i get an error:

Msg 102, Level 15, State 1, Line 11
Incorrect syntax near 'Results_Pass'.

Thank you for the answer. As you suggested, i shall do some research on the Relational Databases and get a grip on it.

Thank you for your time.
Saiekrishna

Am sorry, i have tried it in the MS-Excel sheet and i have used SUMIFS() function with sum range and criteria range.

Here is the statement for it.
INSERT INTO Table3
SELECT Table1.Name, COUNT(Table2.ID),Table1.Target
FROM Table2
INNER JOIN Table1 ON Table2.ID=Table1.ID
GROUP BY Table1.Name,Table1.Target

Sure, below is the data generated through your query.

Partner || Candidate ||Total Target
Ramu Naidu Gorle || 55 || 50
Harsha || 50 || 75
Ramu Naidu Gorle || 144 || 100

Well, as you can see the partner name "Ramu Naidu Gorle" is appeared two times but with different candidate and total target. I need those to be sum'd up and resultant should be in a single row.

Example:

Partner || Candidate || Total Target
Ramu Naidu Gorle || 199 || 150
Harsha || 50 || 75

Hope am clear in explaining my stance.

Thank you for the reply Ashish.
A Quick question: What if there is a two similar names with different ID's in the table1. How can i merge the corresponding data?

Example:
Saikrishna: 1245
Saikrishna: 1246

Both names is of a single person but he is associated with two ID's. How can i count his number of candidates.

Any help will be appreciated.

Thanks
Saikrishna

Error resolved. There were no column name specified in the table3.

A Quick question: What if there is a two similar names with different ID's in the table1. How can i merge the corresponding data?

Example:
Saikrishna: 1245
Saikrishna: 1246

Both names is of a single person but he is associated with two ID's. How can i count his number of candidates.

Any help will be appreciated.

Thanks
Saikrishna

@Suvendu Shekhar Giri
Thank you for the reply, i have done exactly the way you described but getting an error.
Following is the error:

Msg 208, Level 16, State 1, Line 1
Invalid object name 'Table2'.

Hello Maciej Los

How do i save consolidated data in third table from tabel1 and table2.

For example:
Table one contains following column and data.
ID | Name | Target
11 | Abcd | 123
12 | xyzx | 135

Table two contains following columns and data.
ID | Candidate | Roll_Number
11 | asdeff | 12345
11 | dfrsf | 12346
11 | dfesf | 12347
12 | erfgv | 12348
12 | dsfdv | 12349
12 | dasfr | 12350
12 | dsadsa | 12351

Third table should store data from table1 & table2 after applying functions.
Name | Number of Candidate | Target
Abcd | 3 | 123
xyzx | 4 | 135

Note: In the third table the "Number of candidate" column should be COUNTED from table2, respectively.

It will be very helpful if you can provide me with the SQL statements.

Thanks
Saiekrishna

A quick question, is there any way to replicate Excel formulas to SQL query?

Thank you for the reply.

Well, my SQL queries are bit more complex and very confusing. Though i shall try SQL View in Access.

Thank you for your time.
Saiekrishna

Thank you for the answers.

I agree with @Caroline @Sinisa Hajnal @Maciej Los and @CHill60 that a database would be far more better in terms of storing and manipulating the data but am inexperienced in SQL query writing and the project which am working on needs to have a lots of complex queries to be written.

I have an excel file with all the functions and operations, i thought of using the same file to show in the front end and each time when a user enters the data it will store in the same file and do the operations accordingly.

A random question, is there any query builder which takes the functionalities from the excel file and build a SQL query?

Any help is appreciated.

Thanks
Saiekrishna