Problems moving MySQL to MSSQL with .asp classic

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Problems moving MySQL to MSSQL with .asp classic

Author

Topic

633shane
Starting Member

4 Posts

Posted - 2011-12-01 : 04:52:00

We are trying to move one of our sites over to our dedicated server, from a MySQL database to our MSSQL database. I have created the database the site seems to be working well.

However, we are having a problem with a piece of code that seems to only work for MySQL and not MS SQL - SELECT LAST_INSERT_ID() AS cus_id;
This is supposed to create a customer ID which is then inserted into the table along with other values. This is the error we get:

An error occurred executing the following query;
SELECT LAST_INSERT_ID() AS cus_id;
Error Description :

'LAST_INSERT_ID' is not a recognized built-in function name.

Page of error :

/quote_status.asp

Query String :

The actual code on the page 'quote_status.asp' is as follows:

strQuery = "INSERT INTO tbl_customers (cus_title, cus_firstname, cus_lastname, cus_telephone, cus_postcode, cus_email, cus_uniquecode) VALUES (" & _
"'" & sqlsafe(cus_title) & "'" & _
",'" & sqlsafe(cus_firstname) & "'" & _
",'" & sqlsafe(cus_lastname) & "'" & _
",'" & sqlsafe(cus_telephone) & "'" & _
",'" & sqlsafe(cus_postcode) & "'" & _
",'" & sqlsafe(cus_email) & "'" & _
",'" & sqlsafe(cus_uniquecode) & "'" & _
");"
Call ExecuteSQL(strQuery, numCursorType, objRecordSet)

strQuery = "SELECT LAST_INSERT_ID() AS cus_id;"
Call ExecuteSQL(strQuery, numCursorType, objRecordSet)
If objRecordSet.Recordcount > 0 Then
cus_id = objRecordSet.Fields("cus_id")
End If
objRecordSet.Close

There are several places where this type of code is used, I'm hoping I can work this one out and apply it to the other areas of the site to get it fully functional again.

Are you able to offer any advice on how to proceed?

jassi.singh
Posting Yak Master

122 Posts

Posted - 2011-12-01 : 05:03:49

check some migration examples

http://www.codeproject.com/KB/database/migrate-mysql-to-mssql.aspx

633shane
Starting Member

4 Posts

Posted - 2011-12-01 : 05:26:46

That is mainly for moving the data from one database to the other, that part is already done. It's the code in the website I'm now having problems with.

'LAST_INSERT_ID' is not a recognized built-in function name.

Means that cus_id is not created and the values are not entering the DB = website not working

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-01 : 06:17:37

quote:
Originally posted by 633shane

That is mainly for moving the data from one database to the other, that part is already done. It's the code in the website I'm now having problems with.

'LAST_INSERT_ID' is not a recognized built-in function name.

Means that cus_id is not created and the values are not entering the DB = website not working

is LAST_INSERTED_ID function created by you?
MS SQL server doesnt have such a function which is why it throws the error

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-01 : 06:22:54

ok...so you were using MySQL
if you want to implement same in MS SQL SERver use SCOPE_IDENTITY() instead of LAST_INSERTED_ID()

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

633shane
Starting Member

4 Posts

Posted - 2011-12-01 : 08:53:28

Ok thanks for your help, I have changed it to be:

strQuery = "INSERT INTO tbl_customers (cus_title, cus_firstname, cus_lastname, cus_telephone, cus_postcode, cus_email, cus_uniquecode) VALUES (" & _
"'" & sqlsafe(cus_title) & "'" & _
",'" & sqlsafe(cus_firstname) & "'" & _
",'" & sqlsafe(cus_lastname) & "'" & _
",'" & sqlsafe(cus_telephone) & "'" & _
",'" & sqlsafe(cus_postcode) & "'" & _
",'" & sqlsafe(cus_email) & "'" & _
",'" & sqlsafe(cus_uniquecode) & "'" & _
");"
Call ExecuteSQL(strQuery, numCursorType, objRecordSet)

strQuery = "SELECT SCOPE_IDENTITY() AS cus_id;"
Call ExecuteSQL(strQuery, numCursorType, objRecordSet)
If objRecordSet.Recordcount > 0 Then
cus_id = objRecordSet.Fields("cus_id")
End If
objRecordSet.Close

It now gives the following error

INSERT INTO tbl_enquiries (enq_cus_id, enq_mnf_id, enq_mod_id, enq_derivative, enq_bodystyle, enq_transmission, enq_fueltype, enq_fspec, enq_enginesizeother, enq_colourother, enq_optionother, enq_comments, enq_exdemo, enq_prereg, enq_testdriven, enq_finished, enq_approved, enq_responseconfirmed, enq_dateentered) VALUES (,19,159,'SE',3,9,11,'n','','','','','n','n','n','y','n','n',now());

Error Description :

Incorrect syntax near ','.

If you look at the values it is writing (,19,159,'SE',3,9,11,'n','','','','','n','n','n','y','n','n',now())
you can see the first entry is blank and so just has comma (i.e next) which is because the cus_id is not generated (i assume).

If you look at the error it is not referring to the above insert statement, it is actually referring to the next part of my code which is:

strQuery = "INSERT INTO tbl_enquiries (enq_cus_id, enq_mnf_id, enq_mod_id, enq_derivative, enq_bodystyle" & _
", enq_transmission, enq_fueltype, enq_fspec, enq_enginesizeother, enq_colourother, enq_optionother, enq_comments" & _
", enq_exdemo, enq_prereg, enq_testdriven, enq_finished, enq_approved, enq_responseconfirmed, enq_dateentered) VALUES (" & _
cus_id & _
"," & mnf_id & _
"," & mod_id & _
",'" & sqlsafe(car_version) & "'" & _
"," & car_bodystyle & _
"," & car_transmission & _
"," & car_fueltype & _
",'" & car_fspec & "'" & _
",'" & sqlsafe(car_enginesize_other) & "'" & _
",'" & sqlsafe(car_colour_other) & "'" & _
",'" & sqlsafe(car_options_other) & "'" & _
",'" & sqlsafe(car_notes) & "'" & _
",'" & car_exdemo & "'" & _
",'" & car_prereg & "'" & _
",'" & car_testdriven & "'" & _
",'y'" & _
",'n','n',now()" & _
");"
Call ExecuteSQL(strQuery, numCursorType, objRecordSet)

strQuery = "SELECT SCOPE_IDENTITY() AS enq_id;"
Call ExecuteSQL(strQuery, numCursorType, objRecordSet)
If objRecordSet.Recordcount > 0 Then
enq_id = objRecordSet.Fields("enq_id")
End If
objRecordSet.Close

Which prompts me to ask if the cus_id is working and enq_cus_id is not, then why? It is doing cus_id first!

I checked the database and it is writing the row to the customer table but not to the enquiries table.

Firstly I thought it was because there was no data in that table so I manually put some valid data in and the error is still produced.

I checked to see if it would auto increment and it seems to do that. I have specified in the design:
Identity Specification = yes
(is identity) = yes
Identity increment = 1

633shane
Starting Member

4 Posts

Posted - 2011-12-01 : 09:38:09

Ok I have got to the root of the problem I think, the first statement is working:

strQuery = "INSERT INTO tbl_customers (cus_title, cus_firstname, cus_lastname, cus_telephone, cus_postcode, cus_email, cus_uniquecode) VALUES (" & _
"'" & sqlsafe(cus_title) & "'" & _
",'" & sqlsafe(cus_firstname) & "'" & _
",'" & sqlsafe(cus_lastname) & "'" & _
",'" & sqlsafe(cus_telephone) & "'" & _
",'" & sqlsafe(cus_postcode) & "'" & _
",'" & sqlsafe(cus_email) & "'" & _
",'" & sqlsafe(cus_uniquecode) & "'" & _
");"
Call ExecuteSQL(strQuery, numCursorType, objRecordSet)

strQuery = "SELECT SCOPE_IDENTITY() AS cus_id;"
Call ExecuteSQL(strQuery, numCursorType, objRecordSet)
If objRecordSet.Recordcount > 0 Then
cus_id = objRecordSet.Fields("cus_id")
End If
objRecordSet.Close

But the second was not so I changed it to match the first in terms of format:

strQuery = "INSERT INTO tbl_enquiries (enq_cus_id, enq_mnf_id, enq_mod_id, enq_derivative, enq_bodystyle" & _
", enq_transmission, enq_fueltype, enq_fspec, enq_enginesizeother, enq_colourother, enq_optionother, enq_comments" & _
", enq_exdemo, enq_prereg, enq_testdriven, enq_finished, enq_approved, enq_responseconfirmed, enq_dateentered) VALUES (" & _
"'" & sqlsafe(enq_cus_id) & "'" & _
",'" & sqlsafe(enq_mnf_id) & "'" & _
",'" & sqlsafe(enq_mod_id) & "'" & _
",'" & sqlsafe(car_version) & "'" & _
",'" & sqlsafe(enq_car_bodystyle) & "'" & _
",'" & sqlsafe(enq_car_transmission) & "'" & _
",'" & sqlsafe(enq_car_fueltype) & "'" & _
",'" & sqlsafe(enq_car_fspece) & "'" & _
",'" & sqlsafe(car_enginesize_other) & "'" & _
",'" & sqlsafe(car_colour_other) & "'" & _
",'" & sqlsafe(car_options_other) & "'" & _
",'" & sqlsafe(car_notes) & "'" & _
",'" & sqlsafe(enq_car_exdemo) & "'" & _
",'" & sqlsafe(enq_car_prereg) & "'" & _
",'" & sqlsafe(enq_car_testdriven) & "'" & _
",'y'" & _
",'n','n',getdate()" & _
");"
Call ExecuteSQL(strQuery, numCursorType, objRecordSet)

strQuery = "SELECT SCOPE_IDENTITY() AS enq_id;"
Call ExecuteSQL(strQuery, numCursorType, objRecordSet)
If objRecordSet.Recordcount > 0 Then
enq_id = objRecordSet.Fields("enq_id")
End If
objRecordSet.Close

This seems to be working except for the now() function is not supported in MS SQL so I have changed it to getdate() which seems ok.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources