|
I'm hoping this is the appropriate board for my question. If not I apologize in advanced. My company is using MS Access to store all certificates we issue. Part of the form consist of a combo box with all the inspector names and then a text box next to it with there license number. I wrote some code so that when an inspector's name is selected it first checks if their license is expired. If it is, it displays a warning message. If it's not expired, it then places their license number in the text box. Everything was working fine.
However, now when a user opens the form, if the first thing they do is change the combo box to a different inspector, the VBA code with opens up and breaks at the first line of code. If the code is let to continue, it will not break again until the database is reopened. Now, if any other field is changed first, and then the inspector's name is changed the code does not break. Why is the code breaking only on this subroutine and only if it is the first field changed? There are no break points set, and I couldn't find anything in the properties windows and nothing online. Any help would be greatly appreciated as I'm at a total loss of what to do.
|
|
|
|
|
Could you post the code for the event handler ?
Is it the On Change Event ?
Also post the exact error message that is being displayed.
I'm guessing that there is a reference to something that is not initialized which is causing the error.
I'll do my best to help you.
|
|
|
|
|
|
Don't cross post: you have the exact same question in the c# forum. Delete one or the other before they both get removed.
me, me, me
"The dinosaurs became extinct because they didn't have a space program. And if we become extinct because we don't have a space program, it'll serve us right!"
Larry Niven
|
|
|
|
|
Typically, you connect an application to a database server using a "connection string". This connection string contains the NAME of the server, not the IP address; by doing this your network administrator could change the IP address of your server and your application would not have to change. A DNS server can be used to provide the layer of abstraction between an IP address and the name of a server.
Refer to:
DNS Defined: http://technet.microsoft.com/en-us/library/cc787920(WS.10).aspx[^]
Connection Strings: http://www.connectionstrings.com/[^]
|
|
|
|
|
Until the DNS server dies and your app won't run until it's replaced.
I wrote my own little network name cache/resolve system to handle that problem after I got bit.
|
|
|
|
|
Hi
I want to send Email to particular users stored in table at a regular interval like 1 hours. I will buy a mail server. I want to know is it possible to do this using sql server. If yes then what approach should I follow to implement this.
Thanks in advance
|
|
|
|
|
There are lots of approaches
here is but one :-
Write a .NET Service That
1. Defines a Timer for whatever interval - eg 60 minutes
2 Every interval/timer event, read the database table
3. For every row in the selected users set from the database
3a. construct the email
3b. send email using whatever protocol is applicable for your mail server
'g'
|
|
|
|
|
Thanks
I was also think about this approach. but for eg. Can i host a windows service in web hosting provider like godaddy.com or mochahost.com.
so where i host that windows service?
If you know any provider where I can host my windows service plz tell.
Thanks
|
|
|
|
|
no - by 'service' I meant it would run as a win32 service on a windows server type machine - not in/on a web server
'g'
|
|
|
|
|
Is there any other alternative for this?
like using sql server to send mail at particular interval.
|
|
|
|
|
You could set up a SQL Server job to send the mail from a stored procedure.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks
after setting up sqlserver job am I able to send mail to all the users stored in my table?
Is there option that this process repeat itself after every hour like a timer?
|
|
|
|
|
Yes SQL server has a scheduler which will repeat the job as often as you like.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I have to construct a view to feed a 3rd party application.
On the application database they have a boolean field CanAccess which is a blanket item for access to any location.
On the master database where I'm building my view, the contact has a LocationAccess table containing access rights for each explicite location.
What I need in my view is a way of returning something like Count(select * from LocationAccess) > 0 for each contact I'm running through. Here is what I've got so far. If anyone can suggest how I add this last item I'd greatly appreciate it!
(BTW - yes the orderby is required and thus the TOP 100% is also required)
SELECT TOP (100) PERCENT dbo.CustomerMaster.BSACustomerKey, dbo.ContactMaster.ContactName, dbo.ContactMaster.BusinessPhone, dbo.ContactMaster.MobilePhone,
dbo.ContactMaster.Email, dbo.ContactMaster.FirstName, dbo.ContactMaster.LastName, dbo.ContactMaster.EmergencyMaintenance,
dbo.ContactMaster.NormalMaintenance, dbo.ContactMaster.NetworkAbuse, dbo.ContactMaster.IssuedSwipeCard, dbo.ContactMaster.CardNumber,
dbo.AddressList.CustomerAddressLine1, dbo.AddressList.CustomerAddressLine2, dbo.AddressList.CustomerAddressLine3, dbo.AddressList.CustomerPostBox,
dbo.CityEnumerator.City, dbo.StateEnumerator.State, dbo.AddressList.CustomerCounty, dbo.AddressList.CustomerCountry, dbo.AddressList.CustomerZipcode,
dbo.ContactMaster.BmcCustKey, CASE WHEN dbo.ContactMaster.Role = 1 THEN 1 ELSE 0 END AS IsAdministrator,
CASE WHEN dbo.ContactMaster.Role = 6 THEN 1 ELSE 0 END AS IsDecisionMaker,
CASE WHEN dbo.ContactMaster.Role = 5 THEN 1 ELSE 0 END AS IsAccountingAP
FROM dbo.ContactMaster INNER JOIN
dbo.CustomerMaster ON dbo.ContactMaster.CompanyID = dbo.CustomerMaster.CustomerIdentity LEFT OUTER JOIN
dbo.AddressList ON dbo.ContactMaster.ContactAddress = dbo.AddressList.AddressIdentity LEFT OUTER JOIN
dbo.CityEnumerator ON dbo.AddressList.CustomerCity = dbo.CityEnumerator.CityIdentity LEFT OUTER JOIN
dbo.StateEnumerator ON dbo.AddressList.CustomerStateorProvince = dbo.StateEnumerator.StateIdentity
WHERE (dbo.CustomerMaster.CustomerStatus = 1) AND (dbo.ContactMaster.Status = 1)
ORDER BY dbo.CustomerMaster.BSACustomerKey
|
|
|
|
|
Options to tackle this
sub query (select count(*) from loaction....) - not recommended as it can destroy a server.
Use a join to the location table and a group by your existing fields - just plain ugly
Create another view that counts the location info and then use a join just like any other table/view - probably the way I would go
create a UDF that does the same as the view - can you use a UDF in a view?
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
OK -- so let's say I create a view that gives me ContactIdentifier and Count.
So when my view is running how do I specify that other view as input to the query.
Do I do a join between the contact table and that view?
|
|
|
|
|
Assuming that every contact will have 1 or more counted record then use an inner join from your existing view to the count view and this will filter form the filter. If not then do a left join.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
hy,
I want to fetch records from xlsheet...i used the following query in my local server
DECLARE @SQLconnect VARCHAR(8000), @FileName varchar(256)
SET @FileName='C:\CopyofOTFormat.xls'
SET @SQLconnect =
'SELECT * INTO ##tmpOrders FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'', ''Excel 8.0;Database=' + @FileName + ''', ''SELECT * FROM [Sheet1$] '')'
PRINT @SQLconnect
-- SELECT * INTO ##tmpOrders FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=F:\data\export\excel\Top10.xls', 'SELECT * FROM [Sheet1$]')
EXEC (@SQLconnect)
SELECT * FROM ##tmpOrders
SELECT COUNT(*) FROM ##tmpOrders
-- 2452
DROP TABLE ##tmpOrders
It works in my local server but when i run it in the online server...it says that the xlsheet could not be found.
Is there any way i can access it in online server without actually saing it over there
Thanks & Ragards
raghvendra panda
|
|
|
|
|
raghvendrapanda wrote: Is there any way i can access it in online server without actually saing it over there
The online server needs access to the "F:\" drive that you're referring to in your connectionstring. That means that the Excel-sheet needs to be saved on the machine that's going to read it.
I are Troll 
|
|
|
|
|
Dear All,
I have restored a new version of my database into sql 2005, but in old version there were some procedures, function and tabels which I really need them and they are not in new restored version.
Is there any way i can undo my restore or is there any way to get those tables and procedures from old database before restore.
Abdul Rahaman Hamidy
Database Developer
Kabul, Afghanistan
|
|
|
|
|
Not AFAIK.
Unless you have a more recent backup I think you are screwed.
[tongue firmly in cheek] surely you have all you procedures in a source control system and can retrieve them form there.
Actually we take daily backups of our development boxes and keep them in (spit) sourcesafe. We also keep local copies on our workstations so at any time there will be up to 6 recent copies of a database available. This is useful when some twat (3 of them in the last 6 months) uses SQL Compare and REMOVE all the new procs from the development box.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I also do not know how you can unrestore a database. The only thing I can think of is that the LDF files are immense, and might help an expert undo some changes.
|
|
|
|
|
Given the following Query, I get the desired results, however, the result set is sorted on the PLine Column.
I would like to Order By the ID Column, or Better yet, the Factory.Factory_No (varChar), ProdnLine.ProdnLine_No (int)columns.
DECLARE @LineKgs Table(
ID Int, PLine VarChar(7),kgs Decimal(18,7), TDate nVarChar(10))
INSERT INTO @LineKgs
SELECT f.Factory_No + '/' + CAST(pl.ProdLine_No As VarChar), pl.ProdLine_ID,
mt.Qty, CAST(month(mt.TransDate) AS VArChar(10))
FROM Production pr
LEFT OUTER JOIN ProdLine pl
ON pl.ProdLine_ID = pr.ProdLine_ID
JOIN MaterialTransaction mt
ON pr.Prodn_ID = mt.Prodn_ID
JOIN Factory f
ON pl.Factory_ID = f.Factory_ID
WHERE mt.TransDate BETWEEN '1/1/2010' AND '12/31/2010'
SELECT ID, PLine, [1] As Jan, [2] As Feb, [3] As Mar, [4] As Apr,
[5] As May, [6] As Jun, [7] As Jul, [8] As Aug,
[9] As Sep, [10] As Oct, [11] As Nov, [12] as Dec
FROM( SELECT ID, PLine, kgs, TDate
FROM @LineKgs) p2
PIVOT(SUM(kgs)
FOR TDate In ([1], [2],[3],[4],[5],[6],
[7],[8],[9],[10],[11],[12])) As P
Current Result
ID PLine Jan Feb Mar ...
=============================================
1 Fac1/1 8497 60012 19503 ...
2 Fac1/2 104772 152379 33795
3 Fac1/3 76338 113653 40742
4 Fac1/4 197339 333348 118314
5 Fac1/5 200986 363314 106225
6 Fac1/6 35048 189619 44345
7 Fac1/7 45632 133981 52921
8 Fac1/8 58387 185468 39916
9 Fac1/9 34625 76026 69479
10 Fac2/1 420 4150 828
19 Fac2/10 10816 25213 2681
20 Fac2/11 13680 30765 4597
21 Fac2/12 10576 NULL 19068
11 Fac2/2 251 333 NULL
12 Fac2/3 433 1536 NULL
...
Desired Result:
ID PLine Jan Feb Mar ...
=============================================
1 Fac1/1 8497 60012 19503 ...
2 Fac1/2 104772 152379 33795
3 Fac1/3 76338 113653 40742
4 Fac1/4 197339 333348 118314
5 Fac1/5 200986 363314 106225
6 Fac1/6 35048 189619 44345
7 Fac1/7 45632 133981 52921
8 Fac1/8 58387 185468 39916
9 Fac1/9 34625 76026 69479
10 Fac2/1 420 4150 828
11 Fac2/2 251 333 NULL
12 Fac2/3 433 1536 NULL
...
20 Fac2/11 13680 30765 4597
21 Fac2/12 10576 NULL 19068
...
As always, your guidance is most appreciated.
I don't speak Idiot - please talk slowly and clearly
'This space for rent'
Driven to the arms of Heineken by the wife
|
|
|
|
|
I must be missing something, add an order by to your select after as P
Never underestimate the power of human stupidity
RAH
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
