|
I'm guessing (becuase you've not provided the code) that FormatDate is a UDF that returns some value that you can test against. I don't know why you need to do this as you can check greater than/less than on a date already.
IF GETDATE() > '2006-04-28'
BEGIN
-- Do stuff
END
ELSE
BEGIN
-- Do other stuff
END
I would recommend that you always use ISO dates (YYYY-MM-DD) - that way you don't get messed around with cultural specific problems.
ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?
|
|
|
|
|
Thanks for ur comments i have solved the problem with this code
SET Dateformat dmy
IF GETDATE() > dbo.FormatDate('24/04/2006')
BEGIN
PRINT 'A'
END
ELSE
BEGIN
PRINT 'B'
END
Thanks
Best Regards,
Tariq Mahmood
|
|
|
|
|
Hello
I'm trying to connect to SQL Server2000 that in another computer in local area network.
but it says login failed for user "Guest"
- I added my windows account (MyDomain\MyWindowsUserName) to SQL Server.
- I use windows only authentication.
- both computers are Windows XP Pro-service pack 2
- When I add Guest windows user to SQL server, it works fine.. But I don't want to use Guest account.
- both computers are in same WorkGroup
- I added user name to server computer with exactly same user name and password.
any help... thanks
|
|
|
|
|
denizmercan wrote: I added user name to server computer with exactly same user name and password.
Have u added username in Sqlserver Database Users?
"Aim to go where U have never been B4 and Strive to achieve it"
http://groups.yahoo.com/subscribe/dotnetforfreshers
http://himabinduvejella.blogspot.com
|
|
|
|
|
Yes I added.. and I gave full permission to that user.
|
|
|
|
|
I am asking whether u have added user name in enterprize manger users
ie database users
Got me
"Aim to go where U have never been B4 and Strive to achieve it"
http://groups.yahoo.com/subscribe/dotnetforfreshers
http://himabinduvejella.blogspot.com
|
|
|
|
|
Yes I did what you say...
|
|
|
|
|
Hi there,
I am installating msde with my application using installshield (10.5) prerequsites(redistributable). The problem i have is that on windows 2000 after msde installation system require a reboot(there is no way out i tried all the commandline arguments but even if i am sucessfull in supressing reboot still sql script with my installation which create database don't get sucessful without reboot). To solve this problem what i tried is to use installscript and add a registryentry in runonce as my installation begins and delete it at the end of installation with another installscript custom action. The only problem is that i can't run my custom action prior to setup prerequisites get installed. I tried to set the sequence of my custom action to -5(means run first up) but still the msde setup starts prior to my custom action. Is there a way to run custom action prior to setup prerquesites or is there any solution to continue setup after msde installation?
Thanx for help in advance.
Qaiser.
|
|
|
|
|
Hi,
I need help on creating an SQL statement that will add up all the numbers in a given column of all the matching rows of data.
At present i have:
OleDbCommand cmdC = new OleDbCommand("SELECT COUNT (bookingNumbers) FROM tbl_Booking WHERE bookingWhen='"+addDays+"'", myConnection);<br />
cmdC.Connection.Open();<br />
covers = Convert.ToInt16(cmdC.ExecuteScalar());<br />
cmdC.Connection.Close();
But this just returns the number of matching records rather than the total of the data in these records.
Any help would be greatly appreciated!
Thanks!
Pablo
|
|
|
|
|
The SQL COUNT function returns only a count of records returned. You need to use SUM instead but you will be forced to use GROUP BY since SUM is a summerization function.
|
|
|
|
|
Hi,
Thanks for the reply.
Im new to .Net so could you give me an example of how to create this statement, and if possible how to run this code in c# .Net ?
Thanks!!!!!!
|
|
|
|
|
First of all, your problem is really SQL related. I am getting ready to go to work and I don't have time to leave a good example so here is how your SQL might look like:
SELECT SUM(bookingNumbers) FROM tbl_Booking WHERE bookingWhen='"+addDays+"'"
The "GROUP BY" might not be necessary. If it is do this:
SELECT SUM(bookingNumbers) FROM tbl_Booking WHERE bookingWhen='"+addDays+"' GROUP BY booingwhen"
|
|
|
|
|
what is a Tenant Database?
Mad Max
|
|
|
|
|
guptalav wrote: what is a Tenant Database?
The only thing I can think of is that it is a database that contains details of tenants (people that rent properties).
It might be useful if you provide the context in which you find this term.
ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?
|
|
|
|
|
I think they use the term Tenant Database when more than one organization share the same datasource.
I think it's relatively new term.
|
|
|
|
|
Strange tot hear that!
"Aim to go where U have never been B4 and Strive to achieve it"
http://groups.yahoo.com/subscribe/dotnetforfreshers
http://himabinduvejella.blogspot.com
|
|
|
|
|
hi
i am facing one starnge problem...............
I have one table in SQL DB which has following constraints........
<b>Indexes</b>
IX_InvoiceTaxDetails_InvoiceDetailIndex clustered located on PRIMARY InvoiceDetailIndex
PK_InvoiceTaxDetails nonclustered, unique, primary key located on PRIMARY InvoiceTaxDetailsIndex
<B>Constarints</B>
FOREIGN KEY FK_InvoiceTaxDetails_InvoiceDetail No Action No Action Enabled Is_For_Replication InvoiceDetailIndex
REFERENCES BMJ.dbo.InvoiceDetail (InvoiceDetailIndex)
FOREIGN KEY FK_InvoiceTaxDetails_TaxCombinationsDetails No Action No Action Enabled Is_For_Replication TaxCombinationsDetailsIndex
REFERENCES BMJ.dbo.TaxCombinationsDetails (TaxCombinationsDetailsIndex)
PRIMARY KEY (non-clustered) PK_InvoiceDetailIndex (n/a) (n/a) (n/a) (n/a) InvoiceTaxDetailsIndex
Even though i have defined <b>Primary key On column Invoic</b>eTaxDetailsIndex my table has <b>multiple data with same </b>Value in InvoiceTaxDetailsIndex field
e.g
InvoiceTaxDetailsIndex InvoiceDetailIndex TaxCombinationsDetailsIndex TaxAmount TaxValueSlabIndex FoxUpdate UpdateDateTime
---------------------- ------------------ --------------------------- ----------------------------------------------------- ----------------- ------------------------------------------------------ ------------------------------------------------------
<b>305123 </b> 143171 2803 778.57000000000005 561 NULL 2005-11-19 19:55:34.763
<b>305123 </b> 143171 2803 778.57000000000005 561 NULL 2005-11-19 19:55:34.763
can any one tell me what could be the reason..........
Regards,
Ritesh
|
|
|
|
|
Ok, I have an assignment in front of my that demands that I build a sql statement on the fly and then populate a listbox. No problem , except only retrieve say first 100 rows. Then as the user hits the right scroll retrieve 100 more at a time till such time no more rows are left.
Any examples out there or suggestions.
Thanks
|
|
|
|
|
SELECT TOP 100 FROM tbl WHERE sortfield > lastid ORDER BY sortfield
----------
There go my people. I must find out where they are going so I can lead them.
- Alexander Ledru-Rollin
|
|
|
|
|
Folks,
I'm trying to get data back from my IIS/SQL Server without having to refresh the client's page. I also have to do some calculations on the returned data, which is to be displayed in a table. I'm using Microsoft.XMLHTTP to communicate between the client and server pages as shown below:
<br />
On the client side:<br />
function GetAssyRecs(TheOperator)<br />
{<br />
var szRequest = "<GetAssyRecs>";<br />
szRequest += "<TheOperator>" + TheOperator + "</TheOperator>";<br />
szRequest += "</GetAssyRecs>";<br />
<br />
var objHTTP = new ActiveXObject("Microsoft.XMLHTTP");<br />
var szURL = "GetAssyRecs.asp";<br />
var szHttpMethod = "POST";<br />
objHTTP.Open(szHttpMethod, szURL, false);<br />
objHTTP.SetRequestHeader("Content-Type", "application/x-www-form-urlencoded");<br />
objHTTP.Send(szRequest);<br />
<br />
var szReply = objHTTP.ResponseText;<br />
alert("status: " + objHTTP.status + " Reply: " + szReply);<br />
}<br />
<br />
On the server side (GetAssyRecs.asp):<br />
<%@ Language=JScript %><br />
<% Response.Expires=0; %><br />
<br />
<%<br />
var adOpenKeyset = 1;<br />
var adPersistXML = 1;<br />
<br />
var objXmlDOM = Server.CreateObject("Microsoft.XMLDOM");<br />
var szXmlRequest = Request.Form;<br />
if (!objXmlDOM.loadXML(szXmlRequest))<br />
{<br />
var sErrMsg = "Invalid XML Request String: " + szXmlRequest;<br />
Response.Write(sErrMsg);<br />
return;<br />
}<br />
<br />
var TheUser = objXmlDOM.selectSingleNode("//TheOperator");<br />
if ((TheUser == null) || (TheUser.text == ""))<br />
{<br />
var sErrMsg = "No TheOperator specified: " + szXmlRequest;<br />
Response.Write(sErrMsg);<br />
return;<br />
}<br />
<br />
var sql="Select idnum, serial_number, rework_done, thedatetime from IDC_OperatorCounts where operator = '" + TheUser.text + "' and thedatetime > dateadd(hh, -13, getdate()) order by thedatetime asc;";<br />
<br />
var conn=Server.CreateObject("adodb.connection");<br />
conn.ConnectionString="driver={SQL Server};server=xidc-mrtline;database=IDC_Xerox;Trusted_Connection=yes";<br />
conn.Open;<br />
var rs=Server.CreateObject("adodb.recordset");<br />
rs.Open (sql,conn,adOpenKeyset);<br />
rs.Save (Response, adPersistXML);<br />
<br />
rs.close;<br />
rs=null;<br />
<br />
conn.Close;<br />
conn=null;<br />
<br />
objXmlDOM = null;<br />
}<br />
%><br />
What I want to do is to cast the returned xml recordset back into an ActiveXObject ("AdoDB.Recordset"), so I can read through the records using the syntax a recordset object offers.
Is this possible?
'til next we type...
HAVE FUN!! -- Jesse
|
|
|
|
|
I have one database that consist of few tables. My scenario is like this.
Goods import from the port can either be delivered to warehouse for storage or delivered direct to customers.
Goods delivered to warehouse for storage, will then be delivered to customers upon orders.
So, basically there's:
1. incoming from the port to warehouse
2. incoming from the port to customer
3. outgoing from the warehouse to customer
* All transports of goods is delivered by trucks
I'm planning to control all this using "Delivery Order Number" which is use for both inbound and outbound of goods. Is this even correct? Please help! Urgent.
Warehouse Table [only used when storage]
- Warehouse ID
- Warehouse Name
- Address
- Postcode
- City
- State
- Office Phone
- Office Fax
Customer Table [only used when product is directly delivered from port]
- Customer ID
- Customer Name
- Address
- Postcode
- City
- State
- Office Phone
- Office Fax
Port Table
- Port ID
- Port Name
Vessel Table
- Vessel ID (Not the true Vessel Number plate but just Identity Field in SQL DB)
- Vessel Name
Truck Table
- Truck ID
- Truck Number Plate
*(2 primary keys?)
Product Table
- Product ID (Not the real product ID but just Identity Field in SQL DB)
- Delivery Order No
- Vessel Name (or use Vessel ID better?)
- Product Stock Code Number (isnt this the same as product ID)
- Product Size
- Product Quantity (is this necessary?)
- Product Gross Weight (used when incoming)
- Product Net Weight (used only when outgoing)
- Product Case Number (used only when outgoing)
*(Actually, it's only ONE TYPE of product but it comes in different sizes/weights)
Inbound Delivery Table
- Order ID (Not the real Order ID by Customer Invoice but just Identity Field in SQL DB)
- Delivery Order Number
- Delivery Date
- Inbound From [dropdownlist of port names]
- Delivery To [radiobutton to choose whether direct to customer or to warehouse for storage. Radiobutton links to dropdownlist of customers/warehouses]
- Vessel ID (Or should I put Vessel Name instead?)
- Truck ID (Or should I put Truck Number Plate instead?)
Outbound Delivery Table
- Order ID (Not the real Order ID by Customer Invoice but just Identity Field in SQL DB)
- Delivery Order Number
- Delivery Date
- Inbound From [dropdownlist of port names]
- Delivery To [radiobutton to choose whether direct to customer or to warehouse for storage. Radiobutton links to dropdownlist of customers/warehouses]
- Vessel ID (Or should I put Vessel Name instead?)
- Truck ID (Or should I put Truck Number Plate instead?)
|
|
|
|
|
Hello every body
i need to get record only from one row from this view i mean this row shout show only book who are taken.The problem in this code is becasue is show me all users who take the book i need only last one.Pleas help me where to fix the code?
use library
select [title], [author], [first_name], [last_name],max([borrow_date]), [telephone], max([id_borrow])
from
[Return View]
where status=1
group by title,author,first_name,last_name,telephone
Good bye
Best regards
|
|
|
|
|
Question is not very clear.
select *
from [Return View]
where id_borrow = (select max(id_borrow)
from [Return View]
where cond....
and group by ..
)
Hope it works !!
Thanks
Mad Max
|
|
|
|
|
I would like to know what people do with phone numbers when storing them for an app. I have several ideas and I have implemented most of them depending on the app, but I want to get to a standard so I'm asking the question:
Where do you store phone numbers for your app?
Assuming I have a Clients table and Contacts table I have done the following in the past:
1. Created fields in both tables for each row such as Phone1, Phone2...
2. Created a seperate table phone numbers and created a reference table to hold the ids of the clinet/contact and the phone number.
3. Created a text field in both tables and stored the phone numbers as and XML string
I know that creating other tables and ref tables is probably the best Normalized way to do this but is it really that much of a memory/time savings over just storing the information in the same table as the Clients/Contact?
Any thoughts would be appreciated,
Richard
|
|
|
|
|
It really depends on what you are going to do with the information. If all that will ever happen is that the phone number gets displayed on the screen for a customer services person to see and then dial the number then having phone1, phone2 etc. is probably fine.
If you are storing more than just the phone number you might consider normalising it. e.g. for each phone number you are going to put what type of number it is (work, home, mobile, fax, etc.) and maybe a description, e.g. "Available between 9:00 and 18:00"
Alternatively, if you are going to do any serious processing with phone numbers (and I really can't imagine what that could be outside of a telco or automated call centre scenario) then you would be better normalising the data otherwise the SQL will get horrendous.
ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
