|
|||||||||
|
|||||||||
|
|||||||||
 |
|||||||||
| |
||
|
|
|
|
|||||||||
|
|||||||||
|
|||||||||
|
|||||||||
| |
||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
ASP Free Forums Sponsor:
|
|
|
|
#1
January 20th, 2005, 05:32 AM
|
|||
|
|||
Need help with asp Sql problem
Hello everyvery body....I am in the process of converting a database driven site from Access to MS SQL Server 2000.
Everything works fine but I am having trouble with dates. As we know Access has a way to store a Date and a seperate way to store the time. SQL does not. It handles both items as a Datetime data type. In the code below I have the variable todaydt assigned to now(). which makes it a string and SQL will not accept it into a datetime field as a string. So I need to convert it to datetime format. I have found several websites that talk about stored procedures and using it to convert the string to a date format sql will except, but I have know idea how to apply that to my sql statement below. For more information after it inserts this information into the database it contunues to use the information to insert into other tables after an inner join or two...these new tables will also require the same date. It then pulls information based on the date and time submitted to pass back to an autoresponse page. I know big explanation but I wanted to be thorough.... So how do I convert the string to a datetime format using either asp or a Stored procedure that I can call durring??? my sql statement??? or if someone has a better idea lay it on me. I have never used stored procedures before so treat me like I am five and hold my hand here. Thanks in advance Code:
todaydt = now()
'new sql code for sql server
sql = "insert into mastercustomers("
sql = sql & "fname,lname,zipcode,phone,email,"
sql = sql & "datetimesubmitted)values("
sql = sql & " '"&FirstName&"','"&LastName&"','"&ZipCode&"','"&Phone&"','"&Email&"',"
sql = sql & " '"&todaydt&"')"
set rs = conn.execute(sql)
sql = "select mastercusID from mastercustomers where (fname='"&FirstName&"' and lname='"&LastName&"' and email='"&email&"' and and datetimesubmitted='"&todaydt&"')"
set getmastercusID = conn.execute(sql)
mastercusID = getmastercusID("mastercusID")
sql = "SELECT DISTINCT dealerships.deaID "
sql = sql & "FROM makers INNER JOIN (dealercodes INNER JOIN dealerships ON dealercodes.deaID = dealerships.deaID)ON makers.makerID = dealerships.makerID "
sql = sql & "where dealerships.dealzipcode='"&ZipCode&"' AND dealerships.dactive=1 AND makers.maker='"&SubDivisionName&"' "
set gettingdeaID = conn.execute(sql)
while not gettingdeaID.eof
deaIDins = gettingdeaID("deaID")
sql = "insert into customers("
sql = sql & "mastercusID,deaID,fname,lname,zipcode,phone,email, "
sql = sql & "datetimesubmitted,activeS,activeM,activeABS,status ID,cuID)values("
sql = sql & " "&mastercusID&","&deaIDins&",'"&FirstName&"','"&LastName&"','"&ZipCode&"','"&Phone&"','"&Email&"',"
sql = sql & " '"&todaydt&"',1,1,1,1,1,"&cuID&")"
set inscustomers = conn.execute(sql)
gettingdeaID.movenext
wend
Last edited by Shadow Wizard : January 23rd, 2005 at 10:06 AM. Reason: added code tags around code - please do it yourself next.
|
|
#2
January 22nd, 2005, 09:44 PM
|
||||
|
||||
|
Just a few thoughts for you.
1. Even though sql does not store the date as a string it will still expect it delimited with ' characters what you have in your insert statement looks correct. 2. instead of storing now() to a variable and then putting that variable in your sql statment, let the SQL server figure out what time it is on its own using the getdate() function. Code:
sql = "insert into customers("
sql = sql & "mastercusID,deaID,fname,lname,zipcode,phone,email, "
sql = sql & "datetimesubmitted,activeS,activeM,activeABS,status ID,cuID)values("
sql = sql & " "&mastercusID&","&deaIDins&",'"&FirstName&"','"&LastName&"','"&ZipCode&"','"&Phone&"','"&Email&"',"
sql = sql & " getdate(),1,1,1,1,1,"&cuID&")"
3. SQL will in most cases automatically convert data from one data type to another if it can figure out what needs to be done. So if you were to convert data from a source that had string data and post it into a date/time data type , so long as all of the data was formatted into an acceptable date/time sequence SQL server would understand it. for example it will understand '01/01/2005' or 'January 1, 2005' as valid dates. If you add the time to these, again it just has to be in a valid format. You can also look into the Convert and Cast functions for changing data types. 4. Stored Procedures are not too big a mystery, I would recommend doing some research into passing parameteres to and from them, otherwise they are pretty simple. Think of them as batch files for SQL. I would recommend learing to use them and using them for much of your data processing. This places the load on your server rather than on the front-end software. Also since Stored procedures can be optimized by the server they should improve your performance. Well, thats my 2 cents. Hope some of it helps,
__________________
Neal Schafer The early worm gets eaten.
|
|
| Viewing: ASP Free Forums > Other > Programming Help > Need help with asp Sql problem |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
|
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
