|
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!
|
|
|
|
|
Now the GlobalEntityID is a seperate table that holds the company demographics. So each company can have multiple license attached to company. They can also have multiple invoice attached to company.
My question is the end user wants a report that shows company info, license number and invoice attached to each license number. But It does not work cause the invoice is attache to the company not each individual license number.
Is it possible to create get data for each license number when invoices are related to the company table only and not the license number. The report needs to show license numbers with or with out an invoice? When I run the report in SQL Query I can see same invoices for all license numbers, but in the user application I can pull a license number up and see that there is no invoice attached to it. How is this possible when there is no relationship between one license to one invoice?
License Table Fields Invoice Table Fields
PK LICENSEID PK INVOICEID
LICENSENUMBER CASTATUSID
ISSUEDBY INVOICENUMBER
LICENSETYPEID GLOBALENTITYID
LICENSESTATUSID INVOICETOTAL
ISSUEDATE INVOICEDATE
EXPIRATIONDATE INVOICEDESCRIPTION
LASTCHANGEDON ROWVERSION
LASTCHANGEDBY LASTCHANGEDON
ROWVERSION LASTCHANGEDBY
GLOBALENTITYID INVOICEDUEDATE
LICNESEPARENTID
DISTRICTID
LICENSECLASSIFICATIONID
APPLIEDDATE
LASTRENEWALDATE
LICENSEYEAR
LICENSEGROUPID
DESCRIPTION
GLOBALENTITYACCOUNTID
CALENDARDUEDATE
ISAPPLIEDONLINE
IMPORTEDCUSTOMER
|
|
|
|
|
The short answer is: No it can't be done, unless you have some other data that allows you to backtrack the relations.
There should be a table for the invoice rows, what content does that have?
|
|
|
|
|
There are definitely other tables in the mix, as is clear from the License table. I suspect that you can walk a relationship between those. All of those ID fields point to something, chase them down. My suggested starting points are LICENSETYPEID, LICENSESTATUSID, and LICENSECLASSIFICATIONID.
Keep in mind that you may need to join on GLOBALENTITYID to get useful results.
|
|
|
|
|
I am creating one website for external exam duty allotment.I am using msaccess database and jsp to connect it,html and css.
I have 2 tables room and facutly details.
Attribute of room table are total_rooms_available, session and date. Attribute of faculty details are faculty_id,faculty_name experience, designation,mail_id and phone_no.
my question is how to fecth faculties randomly based on no of rooms available i.e (if total_rooms_available is 10 i have to fetch 10+3 faculties randomly from facutlydetails table)
|
|
|
|
|
I'm taking a database driven website course. The teacher wants us to build a database driven website. I haven't touched a database in a yr since taking a sql plus course and even then never built a database from the planning stage. My idea is to build a website of my favorite music artist with their record album, year, song title, label stored in a database. This website will also have a database that when you click on the artist, a bio of the artist will appear. I also want to be able for the visitor to click a link and play song from my mp3 collection database. I have a database model of what I have so far that I can post. The teacher has given us very little direction other than a book called web programming and internet technologies. Everyone in the course is still in the dark about how to go about doing this project. Any ideas would be approciated on helping me design this. Thank you in advance.
|
|
|
|
|
Well, as it is a school assignment you are expected to do the work. However, there are lots of samples of website projects that you can find by doing a Google search, or look at some of the articles referred to at Beginner's Walk - Web Development[^]. The CodeProject articles section also has many database articles.
|
|
|
|
|
Hello experts,
I hope this question falls under the Database category.
I have this query:
Select e.Dept, e.division, a.managerID,e.EmpName,e.empnum, e.Email,e.zip, e.SSN FROM Emp e, Angular a Where e.empID = a.managerid OR e.empID = @empid and password=@pass
What I am attempting to do is that if a user attempts to log in and that user is a manager, redirect him/her to manager screen.
Otherwise, redirect to employee screen.
The issue is that when I run that query, I expect to see records associated with that manager.
It shows ALL employees belonging to different managers.
How do I tweak this to work?
A bit more info:
There are two tables, Emp table with following relevant attributes:
EmpName,
EmpID
Dept
Division
Password
SSN
User logs in with EmpID as username and last digits of SSN as password
Then this other table called Angular with following relevant attributes:
EmpID - FK to EmpID from Emp table
ManagerID FK to EmpID from Emp table
Status (done or pending)
The logic we are trying to employ is that if empID (from Emp table) that the user is logging in with matches ManagerID (from Angular table) then this must be a manager, redirect him or her to manger page.
If empID from Emp table does not match managerID from Angular table, redirect to employee table.
What am I doing wrong?
The code for redirect is asp.net and no need to post it here because if the query is correct, that will work.
Your assistance is greatly appreciated.
modified 29-Sep-15 10:27am.
|
|
|
|
|
Something like this?
SELECT
e.Dept,
e.division,
a.managerID,
e.EmpName,
e.empnum,
e.Email,
e.zip,
e.SSN
FROM
Emp As e
LEFT JOIN Angular As a
ON a.managerid = e.empID
WHERE
e.empID = @empid
And
e.password = @pass
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Ahhh!
Brilliant again Richard.
Thanks as always.
Very much appreciated.
modified 29-Sep-15 10:27am.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
