PHP (MySQL) to ASP.NET (MsSQL) : Database troubles.

[chromis]

Hi,

I've never coded in ASP before and I'm trying to port a couple of simple PHP files to ASP.NET. The first file addScores.php takes form data and a hash and inserts the data into the db, it returns error or success depending on the success of the query. This is then used by a third party piece of software.

My first problem is how to retrieve the result of an insert query to see if the query is successful or not:

PHP Version:

	// Set key vars
	$key = md5("password");

	// Validate Data
	$name = htmlentities(addslashes($_GET['name']));
	$email = htmlentities(addslashes($_GET['email']));
	$time = urldecode($_GET['time']);
	$milliseconds = $_GET['milliseconds'];	

	$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die('Error connecting to mysql database.');
	
	$dbname = 'test';
	mysql_select_db($dbname);	
	
	// Build Query
	$sql = "insert into scores set name='" . $name . "'"
		   . ", email='" . $email . "'"
		   . ", time='" . $time . "'"
		   . ", milliseconds='" . $milliseconds . "'";
									   
	// Execute and check result
	if(strcasecmp($hash,$key) == 0)
	{
		if($result = @mysql_query($sql))
		{
			print "success: " . $name . " " . $time . ":" . $milliseconds;
		}
		else
		{
			print("Error: ". mysql_errno() . ". " . mysql_error() .  "in sql: ". $sql);		
		}
	}
	else
	{
		print("Error: Incorrect hash.");
	}

My ASP so far:

	<%@ Page Language="VB" Debug="true" %>
    <%@ Import Namespace="System.Data" %>
    <%@ Import Namespace="System.Data.SqlClient" %>
    <!--#include file="md5.inc.asp"-->

    <%
    Dim myDataReader as SqlDataReader
    Dim mySqlConnection as SqlConnection
    Dim mySqlCommand as SqlCommand
    Dim key = MD5("test")
    Dim fName = request.querystring("name")
    Dim fEmail = request.querystring("email")
    Dim fTime = request.querystring("time")
    Dim fMilliseconds = request.querystring("milliseconds")
    Dim fHash = request.querystring("hash")	
	
    ' Build Query
    sqlStatement = "INSERT INTO scores SET name='" & fName & "'"
       & ", email='" & fEmail & "'"
       & ", time='" & fTime & "'"
       & ", milliseconds='" & fMilliseconds & "'" 
	        
    mySqlConnection = new SqlConnection("server=localhost;user=root;password=test;database=test")
    mySqlCommand = new SqlCommand(sqlStatement, mySqlConnection)
    
    mySqlConnection.Open()
    myDataReader = mySqlCommand.ExecuteReader(CommandBehavior.CloseConnection)
    
	If String.Compare(key, fHash) = 0 Then  
		If myDataReader.Read() then
			Response.write("success")
		Else
			Response.write("error")		
		End If
    Else
		Response.write("error: hash not a match")			
    End If  
	
    ' Always call Close when done reading.
    myDataReader.Close()
    
    ' Close the connection when done with it.
    mySqlConnection.Close()  
    %>

I've not been able to test this code yet as I havn't been able to install MSSQL server, but the part which im unsure on is the IF statement for testing where a results is returned from the query, is this correct?

		If myDataReader.Read() then

I'm trawling through the internet trying to find the right examples but this has got to be done for tomorrow so i'm hoping someone can lend a hand!

Thanks,

Chromis

[DrBunchman]

Hi Chromis,

You've posted your question in the ASP Forum which is for Classic ASP only - I've moved it for you but in future please post all ASP.NET questions in the .NET Forum.

Dr B

[r035198x]

Insert does not return any rows so you should not use ExecuteReader. You should use ExecuteNonQuery instead which returns the number of rows affected as an int.

[chromis]

Thanks DrBunchMan and r035198x much appreciated.

I'm having a problem now and it's to do with the timestamp column, the debugger is demanding that I convert the usertime (users time) string into a timestamp using the mssql convert() function. I''ve tryed this but I don't know exactly how to implement it, the msdn docs really aren't that useful.

Error:

Disallowed implicit conversion from data type varchar to data type timestamp, table 'test.dbo.scores', column 'usertime'. Use the CONVERT function to run this query.

Code:

sqlStatement = "INSERT INTO scores (name,email,usertime,milliseconds) VALUES('" & fName & "','" & fEmail & "','" & fTime & "','" & fMilliseconds & "')"

I've tryed wrapping the fTime var in a convert function in this format convert(timesta mp, fTime, 8) but that's incorrect. Any ideas?

Thanks.

[r035198x]

Try using CAST ...

[chromis]

Thanks, I tryed that and I think the problem is that timestamp is a read-only column and so won't accept non-null values, i read that timestamp is updated automatically when the record is inserted or updated.
So i need to use a different column type, datetime perhaps?

I only need to store the time part. The idea is that I'll be able to select and sort the scores by time, eventually anyway.

Will i need to use the convert or cast function for datetime as well?

[r035198x]

..
Will i need to use the convert or cast function for datetime as well?

Yes .

[chromis]

Thanks, I'll try that then. Another problem I have is with the display query:

SELECT   SCORES.ID,
         SCORES.NAME,
         SCORES.EMAIL,
         SCORES.USERTIME
FROM     SCORES
         INNER JOIN (SELECT   T2.ID,
                              T2.NAME,
                              T2.EMAIL,
                              MIN(USERTIME)  AS MINTIME
                     FROM     SCORES T2
                     GROUP BY T2.NAME,T2.EMAIL) T3
           ON SCORES.NAME = T3.NAME
              AND SCORES.EMAIL = T3.EMAIL
              AND SCORES.USERTIME = T3.MINTIME
ORDER BY T3.MINTIME

I've modified my code from the mysql version slightly to accomodate the new column structure.

However the query above throws this error:

T2.id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

The syntax is obviously different in mssql can you tell me where it is incorrect?

Thanks, you've been a great help!

[r035198x]

Why are you selecting T2.Id in that query if you are not using it?
Every column that appears in the select part must also appear in the group by part.

[chromis]

I'm fairly new to sub-queries so I'm not quire sure of the right syntax yet. I've removed that and it seems to be working now thanks.

Now my results are being returned with the date part aswell as the time part and also the milliseconds are not being entered either.

I'm guessing this is probably again to do with the casting.

My query for inserting is:

sqlStatement = "INSERT INTO scores (name,email,usertime,milliseconds) VALUES('" & fName & "','" & fEmail & "',convert(datetime, '" & fTime & "', 114),'" & fMilliseconds & "')"

I've used style 114 which is hh:mi:ss:mmm(24 h), this should be the right format.

My query for selecting is:

sqlStatement = "SELECT scores.id, scores.name, scores.email, scores.usertime FROM scores INNER JOIN ( SELECT T2.name, T2.email, min(usertime) AS mintime FROM scores T2 GROUP BY T2.name, T2.email ) T3 ON scores.name = T3.name AND scores.email = T3.email AND scores.usertime = T3.mintime ORDER BY T3.mintime"

the data I am entering is:
name: test
email: test
usertime: 01:01:01.10

Now the format I want the user times to be outputted as is like this 01:01:01.10.

Do i have to add the date part when I insert the time?