|
I just discovered what is a big problem with the order that child rows are retrieved from datatables created from a SQLCe database.
Definition
'Front Inner Camber Link
Dim arrFrInnerCamb() As RCCarV2DataSet.FrInnerCambRow
Dim FrInnerCambRow As DataRow
The child rows are retrieved and stored in an array of DataRows.
'Get the array of alternate Inner Camber Positions
arrFrInnerCamb = FrSuspRow.GetChildRows("FrSuspension_FrInnerCamb")
The problem is the order the values are stored in the Sql database are not always stored in the order of the primary key as I expect. This problem only occurs after deleting rows from the database.
To correct this problem I would like to just sort the arrFrInnerCamb by the primary key and all will be good. Unfortunately can't figure out a simple way to do this. I can use a brute force and ignorance method but figure there must be an easier way. Any suggestions would be appreciated.
|
|
|
|
|
Try storing the child rows in a DataTable, you will then have all the manipulating capabilities inherent in the datatable and sorting will be simple.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks I'm trying that. I was just hoping for an easy way to sort the DataRow Array so I don't have to do a major program restructure.
|
|
|
|
|
Using the array of datarows is wrong, so you will benefit from the refactor 
You could sort the datarows BEFORE you load them into the array!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
How to optimize this query.
SELECT share_id, page_id, parent_title, title, share_type, shared_on,
IF(FIND_IN_SET('".$_POST["student_id"]."_Y', students), 'Y', 'N') AS is_viewed
FROM dash_sharing
WHERE book_id = :book_id AND
( FIND_IN_SET('".$_POST["student_id"]."_N', students)
OR FIND_IN_SET('".$_POST["student_id"]."_Y', students))
AND (page_id, shared_on) IN (SELECT page_id, MAX(shared_on)
FROM dash_sharing AS mtab
WHERE
book_id = :book_id AND ( FIND_IN_SET('".$_POST["student_id"]."_N', students)
OR FIND_IN_SET('".$_POST["student_id"]."_Y', students))
GROUP BY page_id)
ORDER BY FIELD(is_viewed, 'N','Y') ASC, shared_on DESC
|
|
|
|
|
I am building a system in asp.net C# with an Sql Server DB.
I have split the work into separate tasks.
I have put a timeline of around 41 days. The requirements are as below.
Can anyone say how long it would take and if my timeline is too long?
1. Requirements Gathering and Associated Documentation - Requirements Specification/Functional Specification and Agreement with Stakeholder(s)/Users - 4 days
2 Create DB and Tables and 4 tables - 4 days
3 Create Login Form - 2 days
4 Create Menu - 2 days
5 Create Initial Input Form - containing all the fields that need to be recorded inititally and the 2 calculated fields - 5 days
6 Create “Update Single File” form - Allow user to update this file and write it back to the table in the DB - 3 days
7 Create “Update Multiple Files” form - Display multiple files maybe based criteria selected from dropdown and allow user to possibly do a bulk update of the records - 4 days
8 Create “Add Staff” form and have it writing to the Staff table - 2 days
9 Create “LogOut” form - 1 day
10 Create “Reports”. This would involve creating reports that would have the Grouping of the data in the various different scenarios e.g. Total for Invoice, Total for Trans Number. A report for Audit trail may be needed also. These reports may need to be created in SSRS reporting. - 5 days
11 Audit Trail - Create a record in the audit table each time a record has been updated. Maybe display this audit trail in the “Update Single File” form - 3 days
12 Create Stored Procedures in DB to carry out the above functions - 3 days
13 Carry out functional reviews with Stakeholder(s)/Users - 1 day
14 Carry out Unit testing - 1 day
15 UAT with Stakeholder(s)/Users and Sign Off of System - 1 day
|
|
|
|
|
That really is like asking "how long is a piece of string?"  Only you will know if you can actually achieve this timeline.
Some observations from my own experiences though
Step 1 - if you get "awkward" stakeholders this step could well take longer than 4 days (actual time spent or elapsed!)
Step 2 - seems a little long as you appear to already have an idea of the tables required - if you're including all of the admin hoops to jump through with DBA's or other control mechanisms I would make it clear in the estimate
Step 14 - seems a bit light, but some of the builds do seem a bit long.
Step 15 - see my earlier comments about stakeholders! I've never run UAT in less than 3 days (and even that was a struggle)
If this timeline is going out to the stakeholders double everything and make it clear whether or not this is a "working days elapsed" timeline or a "working hours spent"
Under promise and over deliver 
|
|
|
|
|
Hi!
On SQL server, when I try to save value 0.4569 to a decimal(9,3) column, the value is truncated. It becomes 0.456. It there a way how to change this behavior of sql server? I would like such numbers to be rounded (to 0.457 in my example) instead of truncating them. Is there some global setting for this?
Thanks!
|
|
|
|
|
Try the round function[^]
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
Yes, I can use ROUND function, off course.
But I would like to change the behavior globally so I will not need to use ROUND function everytime I will need to stop sql server from truncating numbers.
|
|
|
|
|
Only setting that affects rounding that I know of is "NUMERIC_ROUNDABORT". It says "rounded" and not "truncated" in the documentation[^].
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
You could always add 0.0005 to your numbers before saving them... 
|
|
|
|
|
I have data in a Sql Server DB. I want to put a basic front-end on this. The front-end would include a form for adding and updating records and having some reports generated from it also.
Would Asp.net or Access be the best and quickest to develop front-end in this scenario?
|
|
|
|
|
That would depend entirely on your current skill set or ability to learn new stuff.
Personally, I would use C# winforms or WPF for the presentation layer as the fact you are considering Access as a front end rules out ASP.net as that would imply an on-line solution.
I wouldn't touch Access for the GUI with a very long barge pole.
|
|
|
|
|
How many people will use the front-end, and are they located in the same office? Will all your users be working on computers within a single LAN, or are they working from locations that are geographically distributed? How much control do you have over your users' deployment environment?
A Microsoft Access front-end is almost certain to be the simplest and fastest solution to implement, but it comes with some limitations that are important to consider.
I generally prefer ASP.NET because then the UI is accessible to anyone with a web browser. This makes the application easier to distribute and update - but it is likely to be more difficult and more time-consuming to implement.
|
|
|
|
|
Hi.. im newbie in visual basic. May i ask how to code datagrid to microsoft access? My vb is 2008 and my microsoft is 2007. Please help. I want learn more. Thanks in advance.
|
|
|
|
|
Not at all clear what your problem is. Try researching some codeproject articles[^] then come back if you have a specific problem
|
|
|
|
|
id_no first_name last_name
1 X ABC
2 Y ABC
3 M pqr
4 N PQR
Here is the query I am running :
SELECT STUFF((select stuff1 from(SELECT distinct T1.id_no 'sequenceno', ('/' + Col2) + ',' + STUFF((SELECT STUFF2 FROM(SELECT distinct T2.id_no 'sequenceno' ,'-' + substring(first_name, 1, 1)'STUFF2' FROM Table2 AS T2 WHERE T2.last_name = T1.last_name AND T2.id_no = 1234 AND T2.id_no = T1.id_no) dt2
order BY dt2.sequenceno
FOR XML PATH(''),TYPE
).value('.', 'varchar(MAX)'), 1, 1, '') 'stuff1'
FROM Table1 AS T1) dt
order BY dt.sequenceno
FOR XML PATH(''),TYPE).value('.', 'VARCHAR(MAX)'), 1, 1, '') AS Result
OUTPUT - ABc,X-Y/ABc,X-Y/pqr,M-N/PQR,M-N
Desired OUTPUT - ABc,X-Y/pqr,M-N
Can you pls help me on this ?
|
|
|
|
|
This is not a good question. The SQL code is mostly unreadable and there is no description of what the desired output should be. The example desired output does not describe the format well enough.
The difficult we do right away...
...the impossible takes slightly longer.
|
|
|
|
|
Try this
DECLARE @T TABLE(ID_NO INT IDENTITY,FIRST_NAME VARCHAR(50), LAST_NAME VARCHAR(50))
INSERT INTO @T VALUES('X','ABC'),('Y','ABC'),('M','PQR'),('N','PQR')
;WITH CTE AS(
SELECT
Rn = ROW_NUMBER() OVER(PARTITION BY t2.LAST_NAME ORDER BY (SELECT 1))
,t2.LAST_NAME
+ ',' +
STUFF(( SELECT '-' + t1.FIRST_NAME
FROM @T t1
WHERE t1.LAST_NAME = t2.LAST_NAME
FOR XML PATH(''),TYPE) .value('.','NVARCHAR(MAX)'),1,1,'')AS Concat_FirstName
FROM @T t2
)
SELECT
STUFF(( SELECT '/' + c1.Concat_FirstName
FROM CTE c1
WHERE c1.Rn = c2.Rn
FOR XML PATH(''),TYPE)
.value('.','NVARCHAR(MAX)'),1,1,'') AS Concat_FirstName
FROM CTE c2 WHERE Rn= 1
GROUP BY c2.Rn
Result
Concat_FirstName
ABC,X-Y/PQR,M-N
Hope this helps
|
|
|
|
|
Hello Everyone,
I have one old foxpro application which is using dbf files as database.
But now i want to develop a new VB.net application for the same dbf database files.
when i try to read dbf files from VB.Net application at the same time foxpro application and dbf files get crashed.
So anyone can help me to solve the problem?
Thank you
|
|
|
|
|
Exactly the same way you would copy any other file. What have you tried? Where are you stuck?
while (true) {
continue;
}
|
|
|
|
|
Try Google your question
Google > VB NET read FoxPro DBF
Did you tried to use ODBC driver ?
Patrice
“Everything should be made as simple as possible, but no simpler.” Albert Einstein
|
|
|
|
|
<b> I am using Following code to copy the DBF file from Original location to mylocation </b>
Dim di As New IO.DirectoryInfo(SourceFolderPath)
Dim tempname As String
Dim diar1 As IO.FileInfo() = di.GetFiles("*.DBF")
Dim dra As IO.FileInfo
'list the names of all files in the specified directory
For Each dra In diar1
tempname = dra.ToString
Do Until FileInUse(dra.FullName) = False
''Do nothing Until File Is busy
Loop
''once file become free then copy the file
dra.CopyTo(destpath)
Next
|
|
|
|
|
Hello experts,
I am pretty new to DB2. I have a query that returns some result. For each row of the results, I should pass a value from results to external Procedure. Now I want an union of both the results. Here is what I have tried. And I am banging my head to wall since last three days. Can someone please help me with this? It is really really important now.
BEGIN
DECLARE EOF_COND INTEGER DEFAULT 0 ;
DECLARE @ITEM CHAR ( 30 ) ;
DECLARE @BIN CHAR ( 10 ) ;
DECLARE @SIZE CHAR ( 30 ) ;
DECLARE @TIRERACK CHAR ( 10 ) ;
DECLARE @RACKSBIN CHAR ( 10 ) ;
DECLARE @DESC CHAR ( 100 ) ;
DECLARE @DESC1 CHAR ( 100 ) ;
DECLARE @ONHAND CHAR ( 10 ) ;
DECLARE @COMTD CHAR ( 10 ) ;
DECLARE @USEDRACKS CHAR ( 10 ) ;
DECLARE @OPENRACKS CHAR ( 10 ) ;
DECLARE @TEMP CHAR ( 10 ) ;
DECLARE RESULT INTEGER DEFAULT 0 ;
DECLARE CR1 CURSOR WITH RETURN FOR
SELECT T2 . ICITEM , T2 . BABLOC , C . ISSIZE , D . UNITSPER
, E . NBRRCKBINB , F . ICDSC1 , F . ICDSC2 , T2 . TQOH , T2 . TQCM
, CEIL ( INTEGER ( ( T2 . TQOH ) ) / D . UNITSPER )
NBR_USED_RACKS
, E . NBRRCKBINB - ( CEIL ( INTEGER ( ( T2 . TQOH ) ) / D . UNITSPER ) )
NBR_OPEN_RACKS , ( E . NBRRCKBINB * D . UNITSPER ) - T2 . TQOH
FROM (
SELECT A . BACMP , A . BALOC , MIN ( A . BAITEM ) ICITEM
, A . BABLOC , INTEGER ( SUM ( A . BAQOH ) ) TQOH ,
INTEGER ( SUM ( A . BAQCM ) ) TQCM
FROM TESTDATA . VINBINI A WHERE A . BALOC = '13' AND 1 = A . BACMP AND
A . BAQOH - A . BAQCM > 0 GROUP BY A . BACMP , A . BALOC , A . BABLOC HAVING
MIN ( A . BAITEM ) = MAX ( A . BAITEM )
AND SUM ( A . BAQOH - A . BAQCM ) > 0
) AS T2
, TESTDATA . PALITMLOC B , TESTDATA . VINITEMSIZ C , TESTDATA . PALSIZQTY D , TESTDATA . PALBINPF E
, TESTDATA . VINITEM F
WHERE T2 . BACMP = B . TACOMP AND T2 . ICITEM = B . ICITEM
AND T2 . BALOC = B . IALOC AND T2 . ICITEM = F . ICITEM
AND T2 . ICITEM = C . ISITEM
AND B . PALLETID = D . PALLETID
AND C . ISSIZE = D . ISSIZE
AND E . TACOMP = T2 . BACMP
AND E . IALOC = T2 . BALOC
AND E . IMBLOC = T2 . BABLOC
AND E . PALLETID = B . PALLETID
ORDER BY 1 , 2 ;
OPEN CR1 ;
SET EOF_COND = 0 ;
FETCH CR1 INTO @ITEM , @BIN , @SIZE , @TIRERACK , @RACKSBIN , @DESC , @DESC1 , @ONHAND , @COMTD , @USEDRACKS , @OPENRACKS , @TEMP ;
CALL TESTDATA . PARECR24SP ( '01' , '13' , @BIN , RESULT ) ;
RETURN ;
END
Your help is much appreciated.
Thanks
Happy Coding!
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
