|
Apologies for the shouting but this is important.
When answering a question please:
- Read the question carefully
- Understand that English isn't everyone's first language so be lenient of bad spelling and grammar
- If a question is poorly phrased then either ask for clarification, ignore it, or mark it down. Insults are not welcome
- If the question is inappropriate then click the 'vote to remove message' button
Insults, slap-downs and sarcasm aren't welcome. Let's work to help developers, not make them feel stupid.
cheers,
Chris Maunder
The Code Project Co-founder
Microsoft C++ MVP
|
|
|
|
|
For those new to message boards please try to follow a few simple rules when posting your question.- Choose the correct forum for your message. Posting a VB.NET question in the C++ forum will end in tears.
- Be specific! Don't ask "can someone send me the code to create an application that does 'X'. Pinpoint exactly what it is you need help with.
- Keep the subject line brief, but descriptive. eg "File Serialization problem"
- Keep the question as brief as possible. If you have to include code, include the smallest snippet of code you can.
- Be careful when including code that you haven't made a typo. Typing mistakes can become the focal point instead of the actual question you asked.
- Do not remove or empty a message if others have replied. Keep the thread intact and available for others to search and read. If your problem was answered then edit your message and add "[Solved]" to the subject line of the original post, and cast an approval vote to the one or several answers that really helped you.
- If you are posting source code with your question, place it inside <pre></pre> tags. We advise you also check the "Encode "<" (and other HTML) characters when pasting" checkbox before pasting anything inside the PRE block, and make sure "Use HTML in this post" check box is checked.
- Be courteous and DON'T SHOUT. Everyone here helps because they enjoy helping others, not because it's their job.
- Please do not post links to your question into an unrelated forum such as the lounge. It will be deleted. Likewise, do not post the same question in more than one forum.
- Do not be abusive, offensive, inappropriate or harass anyone on the boards. Doing so will get you kicked off and banned. Play nice.
- If you have a school or university assignment, assume that your teacher or lecturer is also reading these forums.
- No advertising or soliciting.
- We reserve the right to move your posts to a more appropriate forum or to delete anything deemed inappropriate or illegal.
cheers,
Chris Maunder
The Code Project Co-founder
Microsoft C++ MVP
|
|
|
|
|
We use a legacy application (vb6 / access). The UI uses DAO to connect to the local front-end MDB file. Local tables are linked to the back-end mdb file which is located on the server.
So far so well.
The problem is that the back-end MDB is now 1,8 Gb and it will be 2 Gb pretty soon, which is fatal in MS-Access world.
We're thinking of replacing the back-end db with sql server express, so we'll have to make minimal changes to the front-end databases (just the connection string).
Is it a good idea? What is the correct procedure to import the back-end MDB to SQL server express? Which version (LocalDB etc) should we use?
Thanks in advance.
PS
There is no option of making/buying a new application. We are trying to solve our problem with minimum effort.
modified 4 days ago.
|
|
|
|
|
There seems to be plenty of resources about moving data from Access to SQL Lite, I would be more worried about the queries you have supporting your application, Access sql is not the same as TSQL. You will need to assess whether the queries will work (not a trivial proposition)!
I would be more concerned that the UI is in VB6, and changes to support Sql Lite are going to have no support outside your people.
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
|
Actually I meant SQL Server (Microsoft), not SQL Lite.
|
|
|
|
|
|
Hello all,
Some time ago I created a PHP web page that works against a MySQL database to keep track of all my worked hours as a freelance.
That works well, and I use it extensively.
The biggest issue I have while using it is when I am at a customer company without a reliable internet connection, and I can't remotely connect my office server to use it.
I've been thinking of installing XAMPP in my laptop and copying the server database there to be able to work while being offline.
Then I could backup the database as soon as I have internet connection or use a backup program to keep it safe.
BUT:
I am not the only one working with the database at the same time and this makes things much more interesting.
How would you approach this situation?
Of course, I can keep doing what I am doing nowadays: write everything in any other offline program or editor and do it when I get into a hotel or the office again.
Thank you all!
|
|
|
|
|
|
Thanks for the link, Gerry.
Seeing all that I think I'll keep connecting remotely to reduce the issues + cost.
|
|
|
|
|
Assuming your timesheet entry is flat and there is no conflict between in house and of site work I would store it locally in a copy of the server DB. Then simply bulk copy from the local DB to the server.
Or It should be a simple exercise to write an off site storage and entry app (excel would do) that you can then load into the server DB.
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
|
I have the following query in my own photo database:
select distinct left(Nr, 8) as Date, Ort from Fotos where Typ='D' order by left(Nr, 8) desc, Ort
Where Nr is a unique string with date + a hyphen + a sequential number in the form yyyymmdd-xxx. Ort is a string, the location of the photo.
This works fine so far and I get a list like this:
20220717 Lindabrunn, Symposion
20220717 Feistritzsattel
20220717 Pernitz, Schärfthal
20220715 Mannswörth
20220715 Wienerbergteich
20220715 Zentralfriedhof
20220712 V
20220710 Himberg
But what I really want is the first Nr for a date and a location like this:
20220717-001 Lindabrunn, Symposion
20220717-045 Feistritzsattel
20220717-103 Pernitz, Schärfthal
20220715-001 Mannswörth
20220715-009 Wienerbergteich
20220715-033 Zentralfriedhof
20220712-001 V
20220710-001 Himberg
Can someone help? Thanks
|
|
|
|
|
Assuming SQL Server, try something like this:
WITH cteOrderedData As
(
SELECT
Nr,
Ort,
ROW_NUMBER() OVER (PARTITION BY Left(Nr, 8), Ort ORDER BY Nr) As RN
FROM
Fotos
WHERE
Type = 'D'
)
SELECT
Nr,
Ort
FROM
cteOrderedData
WHERE
RN = 1
;
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Received a solution that has built in .sql script files to create tables and even a stored procedure....
I have built plenty of DataTable structures and used StreamReader to fill the table with csv data. I even use XML to build schemas and import data into DataTables. but, I have never used scripting to build a data structure before.
The solution has Dapper as a dependency but, again, I am not a big time EF developer... Any suggestions how to get the .sql into a DataSet or a DataTable or both would be greatly appreciated.
I have scoured all sorts of sources to give me a concrete example to no avail. When I view the .sql in VS it displays the designer similar to Access.
|
|
|
|
|
An .SQL file is just a text file with SQL statements in it.
You can execute them in C# just like you do for any other query. Just read the file and load the text into a variable and pass that as your statement to an SqlCommand.
|
|
|
|
|
I have a table with the following schema:
DECLARE @images TABLE
(
[Id] BIGINT NOT NULL,
[UserId] NVARCHAR(450) NOT NULL,
[VehID] BIGINT NOT NULL,
[VehImage] VARBINARY(MAX) NULL
);There are no exactly 10 rows per user. Currently, all VehImage columns are null (intentionally).
Here is my sql query (@images is a table variable that I pre-populate with the desired records):
SELECT UserId,VehID
,[Image1],[Image2],[Image3],[Image4],[Image5]
,[Image6],[Image7],[Image8],[Image9],[Image10]
FROM (
SELECT UserId
,VehID
,VehImage
FROM @images
) AS SourceTable
PIVOT (
Count(VehImage)
FOR VehImage IN ([Image1],[Image2],[Image3]
,[Image4],[Image5],[Image6]
,[Image7],[Image8],[Image9]
,[Image10])
) AS PivotTable;I have the pivot working, in that I get exactly one row back with the expected UserId, and VehId, and 10 image columns. My problem is that the Image columns all have the value 0 instead of the expected null.
What am I doing that would cause that?
EDIT ----------------------------------------
I changed the aggregate function from count to max, and all of the images are now showing null as expected. I don't know yet if that's the ultimate solution.
EDIT #2 -------------------------------------
Nope, I plugged a fake value into the first image record, and all of the image columns are still coming back as null...
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013
modified 19-Jun-22 8:13am.
|
|
|
|
|
#realJSOP wrote: Currently, all VehImage columns are null (intentionally).
...
FOR VehImage IN (...)
If all of the VehImage values are Null, they won't match the IN filter.
And it seems odd to be pivoting on the same column you're aggregating.
What are you actually trying to do?
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
i want to return a row that has all of the users images in it.
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013
|
|
|
|
|
Maybe something like this?
WITH cte As
(
SELECT
UserId,
VehId,
VehImage,
ROW_NUMBER() OVER (PARTITION BY UserId, VehId ORDER BY Id) As RN
FROM
@images
)
SELECT
UserId,
VehId,
[1] As Image1,
[2] As Image2,
[3] As Image3,
[4] As Image4,
[5] As Image5,
[6] As Image6,
[7] As Image7,
[8] As Image8,
[9] As Image9,
[10] As Image10
FROM
cte As SourceTable
PIVOT
(
Max(VehImage)
FOR RN IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10])
) As PivotTable
; Or without the pivot:
WITH cte As
(
SELECT
UserId,
VehId,
VehImage,
ROW_NUMBER() OVER (PARTITION BY UserId, VehId ORDER BY Id) As RN
FROM
@images
)
SELECT
UserId,
VehId,
MAX(CASE RN WHEN 1 THEN VehImage END) As Image1,
MAX(CASE RN WHEN 2 THEN VehImage END) As Image2,
MAX(CASE RN WHEN 3 THEN VehImage END) As Image3,
MAX(CASE RN WHEN 4 THEN VehImage END) As Image4,
MAX(CASE RN WHEN 5 THEN VehImage END) As Image5,
MAX(CASE RN WHEN 6 THEN VehImage END) As Image6,
MAX(CASE RN WHEN 7 THEN VehImage END) As Image7,
MAX(CASE RN WHEN 8 THEN VehImage END) As Image8,
MAX(CASE RN WHEN 9 THEN VehImage END) As Image9,
MAX(CASE RN WHEN 10 THEN VehImage END) As Image10
FROM
cte As SourceTable
GROUP BY
UserId,
VehId
;
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
They both worked great, but I went with the non-pivot version. Thanks, man.
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013
|
|
|
|
|
I am doing a Result Analysis project for my college. So basically students will enter the grades that they have obtained for various subjects. If they fail for an exam and take the exam again they need to enter the details of that as well.
For the result, we have to find the list of students who passed for a subject in the first attemt, second attempt upto the nth attempt...What will be an efficient Database design for this problem? What tables should be created and what data should be stored in each?
Please help...struggling with this for the past few weeks
|
|
|
|
|
Consider taking some graph paper and drawing out what your student grade records will look like. Use that as a starting point to design how you will set up one or more tables in a database.
|
|
|
|
|
|
I don't know how to write this PL/SQL in oracle, thanks for your advice.
Because the amount of data in the table is large, it is not suitable to find the max PDate and then compare it from the source table.
Table data:
Process PDate Status
100 2022/05/21 19:58:03 0
100 2022/05/22 07:01:00 0
100 2022/05/22 13:01:10 1
101 2022/05/22 08:23:39 0
101 2022/05/22 10:17:39 0
101 2022/05/22 17:49:39 1
102 2022/05/22 13:14:48 0
Requirement: Get the status of the last record of different processes on 2022/05/22
Select result:
Process PDate Status
100 2022/05/22 13:01:10 1
101 2022/05/22 17:49:39 1
102 2022/05/22 13:14:48 0
|
|
|
|
|
First filter by PDate = datevariable, order by PDate Descending. Select the top 1 record.
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
|
Hello everybody,
It is true that is not a good idea use store procedure actually?
I mean, a) Not a good idea use it in sql
and b) Using o call store procedure in a programming language?
it is so: What is the best way?
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
