|
|
Kevin Marois wrote: I take all this back. As soon as I posted I thought, I'll give it one more try - and it connected
Isn't that always the way it goes? 
Glad you got it sorted.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Hi all,
When I exporting Database table to Excel my date fields are giving 23/01/1990 00:00:00 in stat of 23/01/1990
Please can some one help me in the right way.
Quote: protected DataTable ExportDataFromSQLServer()
{
DataTable dataTable = new DataTable();
using (SqlConnection connection = new SqlConnection(@"Data Source= 127.0.0.1\MSSQLSERVERJAN; Initial Catalog=BHV; User ID=sa;Password=XXXXXXX"))
{
connection.Open();
// Define the query to be performed to export desired data
SqlCommand command = new SqlCommand("select * from BHVUsers", connection);
SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
dataAdapter.Fill(dataTable);
var excelApplication = new Excel.Application();
var excelWorkBook = excelApplication.Application.Workbooks.Add(Type.Missing);
DataColumnCollection dataColumnCollection = dataTable.Columns;
for (int i = 1; i <= dataTable.Rows.Count + 1; i++)
{
for (int j = 1; j <= dataTable.Columns.Count; j++)
{
if (i == 1)
excelApplication.Cells[i, j] = dataColumnCollection[j - 1].ToString();
else
excelApplication.Cells[i, j] = dataTable.Rows[i - 2][j - 1].ToString();
}
}
excelApplication.Cells[1, 2] = "Voornaam";
excelApplication.Cells[1, 3] = "Achternaam";
excelApplication.Cells[1, 4] = "Geboorte Datum";
excelApplication.Cells[1, 5] = "Porto nummer";
excelApplication.Cells[1, 6] = "EHBO behaald";
excelApplication.Cells[1, 7] = "EHBO";
excelApplication.Cells[1, 8] = "BHV";
excelApplication.Cells[1, 9] = "Regisseur";
excelApplication.Cells[1, 10] = "Verlopen EHBO";
excelApplication.Cells[1, 11] = "Laatste herhaling EHBO";
excelApplication.Cells[1, 12] = "EHBO nummer";
excelApplication.Cells[1, 13] = "Verdieping";
excelApplication.Cells[1, 14] = "Ruimte";
excelApplication.Cells[1, 15] = "Email";
excelApplication.Cells[1, 16] = "Mobiel";
excelApplication.Cells[1, 17] = "Opmerking";
excelApplication.Cells[1, 18] = "BHV_Behaald";
excelApplication.Cells[1, 19] = "BHV_Verlopen";
excelApplication.Cells[1, 20] = "Laatste_Herhaling_BHV";
excelApplication.Cells[1, 21] = "Ruisnaam";
excelApplication.Cells[1, 22] = "Regisseur_Verlopen";
excelApplication.Cells[1, 23] = "Regisseur_Behaald";
excelApplication.Cells[1, 24] = "Regisseur_Herhaling";
excelApplication.Cells[1, 25] = "Active_no_yes";
excelApplication.Cells[1, 2].Font.Bold = true;
excelApplication.Cells[1, 3].Font.Bold = true;
excelApplication.Cells[1, 4].Font.Bold = true;
excelApplication.Cells[1, 5].Font.Bold = true;
excelApplication.Cells[1, 6].Font.Bold = true;
excelApplication.Cells[1, 7].Font.Bold = true;
excelApplication.Cells[1, 8].Font.Bold = true;
excelApplication.Cells[1, 9].Font.Bold = true;
excelApplication.Cells[1, 10].Font.Bold = true;
excelApplication.Cells[1, 11].Font.Bold = true;
excelApplication.Cells[1, 12].Font.Bold = true;
excelApplication.Cells[1, 13].Font.Bold = true;
excelApplication.Cells[1, 14].Font.Bold = true;
excelApplication.Cells[1, 15].Font.Bold = true;
excelApplication.Cells[1, 16].Font.Bold = true;
excelApplication.Cells[1, 17].Font.Bold = true;
excelApplication.Cells[1, 18].Font.Bold = true;
excelApplication.Cells[1, 19].Font.Bold = true;
excelApplication.Cells[1, 20].Font.Bold = true;
excelApplication.Cells[1, 21].Font.Bold = true;
excelApplication.Cells[1, 22].Font.Bold = true;
excelApplication.Cells[1, 23].Font.Bold = true;
excelApplication.Cells[1, 24].Font.Bold = true;
excelApplication.Cells[1, 25].Font.Bold = true;
int k = 0;
for (k = 1; k <= 23; k++) // this will aply it form col 1 to 10
{
excelApplication.Columns[k].ColumnWidth = 30;
}
Excel.Range formatRange; formatRange = excelApplication.get_Range("a1", "y1");
formatRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightBlue);
//excelApplication.Cells[1,1] = "LightBlue";
formatRange = excelApplication.get_Range("a1", "y1");
formatRange.BorderAround(Excel.XlLineStyle.xlContinuous,
Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
// Save the excel file at specified location
excelApplication.ActiveWorkbook.SaveCopyAs(@"C:\tmp\test1.xlsx");
excelApplication.ActiveWorkbook.Saved = true;
MessageBox.Show("Excel file created , you can find the file " + "c:\\tmp\\test1.xlsx");
System.Diagnostics.Process.Start("c:\\tmp\\test1.xlsx");
// Close the Excel Application
excelApplication.Quit();
connection.Close();
//Release or clear the COM object
releaseObject(excelWorkBook);
releaseObject(excelApplication);
}
Thanks
Regards,
Jan Meeling
modified 19-Aug-19 7:40am.
|
|
|
|
|
Where is the code that transforms the Date fields?
|
|
|
|
|
Hi Richard,
I was trying that with no results.
Do yoy have a solution how to do this.
Thanks,
Regards,
Jan Meeling
|
|
|
|
|
|
jan Meeling wrote: I was trying that with no results.
Trying what? Please show the code that saves, and formats, the Date field(s).
|
|
|
|
|
Did you try to set the cell format as DATE, not DateTime?
|
|
|
|
|
Hi Victor,
Thanks for your quick response.
Do you have the phrase How I have to implement.
Thanks
Regards,
Jan Meeling
|
|
|
|
|
Usually you don't include the password for the sa-account in the question. Usually we also frown on "SELECT *", and weirdly, you use a "using" clause for the connection but not for the disposable command.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
Hi Bastard,
I have removed the password and this is only in a test enviroment,
And what do you mean with
Usually we also frown on "SELECT *", and weirdly, you use a "using" clause for the connection but not for the disposable command.
Thanks
Regards
Jan Meeling
|
|
|
|
|
jan Meeling wrote: Usually we also frown on "SELECT *",
A select all, versus a select where you only select what you will use. You're now fetching more than expected and this may have an impact on speed.
jan Meeling wrote: and weirdly, you use a "using" clause for the connection but not for the disposable command.
You put the connection in a using-block, but not the command. Any reason why?
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
Hi all,
I have it working know.
The solution is in the SQL string.
SELECT ID_BHV_Users, Voornaam , Achternaam , FORMAT (Geboorte_Datum, 'dd/MM/yyyy'), Porto_Nummer , Format (EHBO_Behaald, 'dd/MM/yyyy') , EHBO , BHV , Regiseur , Format ( Verlopen, 'dd/MM/yyyy') , Format (Laatste_herhaling, 'dd/MM/yyyy') , EHBO_nummer , verdieping , Ruimte , Email , Mobiel , Opmerking , Format (BHV_Behaald, 'dd/MM/yyyy') , Format (BHV_Verlopen, 'dd/MM/yyyy') , Format (Laatste_Herhaling_BHV, 'dd/MM/yyyy') , Ruisnaam , Format (Regisseur_Verlopen, 'dd/MM/yyyy') , Format (Regisseur_Behaald, 'dd/MM/yyyy') , Format (Regisseur_Herhaling, 'dd/MM/yyyy') , Active_no_yes FROM BHVUsers"
I use the format in the SQL string
You All thanks for putting me in the right direction.
Thanks
Jan Meeling  
|
|
|
|
|
Well that is even worse than where you started from. Do not use formatted date strings but use proper date values and Date types. Also use proper parameterised queries to avoid SQL injection problems.
|
|
|
|
|
Hi Richard,
Thanks for reaction.
Can you give me the right way how to do is.
I now about SQL injection problems but this not connected to the internet and only in my test environment.
Thanks,
regards,
Jan Meeling
|
|
|
|
|
|
Hi all,
I have it working know.
The solution is in the SQL string.
SELECT ID_BHV_Users, Voornaam , Achternaam , FORMAT (Geboorte_Datum, 'dd/MM/yyyy'), Porto_Nummer , Format (EHBO_Behaald, 'dd/MM/yyyy') , EHBO , BHV , Regiseur , Format ( Verlopen, 'dd/MM/yyyy') , Format (Laatste_herhaling, 'dd/MM/yyyy') , EHBO_nummer , verdieping , Ruimte , Email , Mobiel , Opmerking , Format (BHV_Behaald, 'dd/MM/yyyy') , Format (BHV_Verlopen, 'dd/MM/yyyy') , Format (Laatste_Herhaling_BHV, 'dd/MM/yyyy') , Ruisnaam , Format (Regisseur_Verlopen, 'dd/MM/yyyy') , Format (Regisseur_Behaald, 'dd/MM/yyyy') , Format (Regisseur_Herhaling, 'dd/MM/yyyy') , Active_no_yes FROM BHVUsers"
I use the format in the SQL string
You All thanks for putting me in the right direction.
Thanks
Jan Meeling Smile | Wink |
|
|
|
|
|
I have a product that offers a small lead management system. The architecture of product is simple so far. I have tables called 'companies', 'users', 'leads'. Users within a company have CRUD rights on leads. Now companies want their agents to have rights to operate leads. So we can ask companies to add their agent as a user within the company but there is a problem. Each user needs to have a unique email to register. So if one agent serves 5 companies they can't be added as a user by each of them. What should be the ideal way to handle this.
Thought of having separate database for each company so that problem of unique email is resolved but I'm not sure how to manage multiple databases for updates
|
|
|
|
|
You may want to change the "key" to the Users table to be a compound key.
Something like Company ID + user email, this would ensure that within a single company, the email is unique.
|
|
|
|
|
With company id + user email, the sign in logic will fail.
Right now user can sign in with email as its unique.
|
|
|
|
|
Add another table with 3 fields
AgentID PK
UserID (could be the email if it is the PK in the user table) - FK to users
CompanyID - FK to company
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
|
I have an ecommerce shop online using php, sql, javascript,ajax and sessions.
I have both guest and members cart options at checkout.
Everything works fine.
I store my cart items in a session currently.
Users can log in or have a guest cart.
Guests cart userids are referenced by the current session id.
members can login and their carts are referenced by their usersids from the database.
The problem is, the session expires after a certain amount of time and so the cart items are lost and the user has to start again.
On doing some research I have found that after the user logs in, I can store his user id in a cookie and I can specify how long that cookie lasts for which is ideal!
I am thinking of changing the code so that I store the items added to the cart in my database tables and simply reference them with the user id ive stored in his cookie.
That way He can shop for ages and not lose his cart and I can send abandon cart emails etc...
I think this would work well as nearly every website uses cookies so people have to have them enabled in their browser these days. I could show a warning message if cookies arent enabled anyway..
What does everyone think about this?
Please note I am not seeking security advice here.
|
|
|
|
|
Let's discuss this in a bit proper detail with the application of performance, security and regulations.
You can store anything that is publicly available on your website, and does not contain any personal or identifying information in a cookie. The reason for this is, that a user might be accessing your website from a public machine or PC and that data might be tracked or stored. Next, for a session you can use this storage to store any information that might identify (because, hey, that is what a session is about, right?) the user and help your website perform an operation quickly—without having to query the database again just to find out their user id, or email address. Do remember that on most frameworks (if not all), session data is sent to the client too.
Now as per regulations, make sure you are requesting permissions from the users to store the cookies on their machines—European regulations require you do that, and then there is GDPR, and etc. etc.
Now as for the database part, this space is expensive and filling it with the content that does not help you in anyway—let us for a moment ignore the analytics, user studies, machine learning for recommender systems, etc.—and they will only take away the space that you should pay for the content that helps you generate some business profits.
What I would recommend here is, store the cart information in the cookies, just reference the URLs of the items they want to purchase. This way, the cart information will be stored locally. This will decrease the overall load on your web servers, and will not cause extra charges in the database storage as well since your users will be storing their interests locally and won't add a cart option in database, and forget it leaving there forever.
One tip I can provide here is to use localStorage APIs, instead of cookies, because cookies also have a round trip from and back to server, adding an overhead on the HTTP request.
Window.localStorage - Web APIs | MDN
In this approach, of course you will end up showing what John wanted to purchase to Johanna as well, and that can be fixed by storing the session id(! Remember, store only the session id not the user id and then verify that the sessions are same for the user—which can be done by some backend algorithm to check if user ids of the sessions are the same )
The sh*t I complain about
It's like there ain't a cloud in the sky and it's raining out - Eminem
~! Firewall !~
|
|
|
|
|
So lets say I have a table (Ports) with an Identifier and then a compacted not XML field that is semi structured.
select * from Ports --Yields this.
ID Categories
1 Town:York, Street:Main, Phone:712-542-3423
The Categories field might have two fields in it. Town and Street or it might have 15.
I need to get these out into separate fields as soo
ID Town Street Phone
1 York Main 712......
ideas on SQL SQL server 2016 is what we are running.
Ideas?
To err is human to really mess up you need a computer
|
|
|
|
|
Not particularly simple, since different rows could have different "attributes" within the categories column.
Something like this should work:
DROP TABLE IF EXISTS #T;
CREATE TABLE #T
(
ID int,
PropertyName varchar(50),
PropertyValue varchar(50)
);
INSERT INTO #T
(
ID,
PropertyName,
PropertyValue
)
SELECT
T.ID,
LTRIM(LEFT(V.value, CHARINDEX(':', V.value) - 1)) As PropertyName,
LTRIM(SUBSTRING(V.value, CHARINDEX(':', V.value) + 1, LEN(V.Value))) As PropertyValue
FROM
YourTable As T
CROSS APPLY string_split(T.Categories, ',') As V
;
DECLARE @columns nvarchar(max) = STUFF
(
(
SELECT DISTINCT ',' + QUOTENAME(PropertyName)
FROM #T
FOR XML PATH(''), TYPE
).value('.', 'nvarchar(max)')
, 1, 1, ''
);
DECLARE @query nvarchar(max) = N'SELECT ID, ' + @columns
+ N' FROM (SELECT ID, PropertyName, PropertyValue FROM #T) As T'
+ N' PIVOT (Max(PropertyValue) FOR PropertyName In (' + @columns + N')) As P';
EXECUTE (@query);
DROP TABLE #T; The @query will look something like:
SELECT ID, [Phone],[Street],[Town]
FROM (SELECT ID, PropertyName, PropertyValue FROM #T) As T
PIVOT (Max(PropertyValue) FOR PropertyName In ([Phone],[Street],[Town])) As P
STRING_SPLIT (Transact-SQL) - SQL Server | Microsoft Docs[^]
Using PIVOT and UNPIVOT - SQL Server | Microsoft Docs[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
