1. No Profile Picture
    Contributing User
    ASP Explorer (0 - 99 posts)

    Join Date
    Mar 2004
    Rep Power

    Question 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

     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) 
    Last edited by Shadow Wizard; January 23rd, 2005 at 10:06 AM. Reason: added code tags around code - please do it yourself next.
  2. #2
  3. Contributing User
    ASP Adventurer (500 - 999 posts)

    Join Date
    Mar 2004
    Rep Power
    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.
    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.

Similar Threads

  1. SQL Server problem!
    By abilash_m81 in forum .NET Development
    Replies: 2
    Last Post: January 5th, 2005, 09:58 AM
  2. SQL server connection problem (in hurry)
    By carmen in forum ASP Development
    Replies: 1
    Last Post: December 16th, 2004, 11:13 AM
  3. Query Builder problem with sql statement
    By sirrco in forum .NET Development
    Replies: 3
    Last Post: December 10th, 2004, 02:12 AM
  4. "where" clause problem
    By gilgalbiblewhee in forum ASP Development
    Replies: 6
    Last Post: August 18th, 2004, 09:43 PM
  5. SQL Insertion problem?
    By Tiong in forum ASP Development
    Replies: 2
    Last Post: August 8th, 2004, 04:16 PM

IMN logo majestic logo threadwatch logo seochat tools logo