Hi
I'm putting this in the ASP section as I think the error is on the ASP side of things. Most of the stuff I've done before has been in Perl so I'm basically winging this ASP stuff!
I'm inserting a row, which works fine and then I'm trying to query the row ID, which is auto incremented (or is an Identity row in Microsoft's terminology.) Unfortunately when I run through the results (I'm looping, even though there should only be one result, as I basically don't know any other way!), I get into an infinite loop and then receive a 'Active Server Pages error 'ASP 0113' Script timed out' error. I've not left out the recordset.MoveNext argument, as you can see:
dim sql_get_cust_id
dim rs_get_cust_id
dim last_cust_id
set sql_get_cust_id = "SELECT IDENT_CURRENT('cs_customers') AS 'id_number'"
set rs_get_cust_id = Server.CreateObject("ADODB.Recordset")
rs_get_cust_id.Open sql_get_cust_id, conn
Do Until rs_get_cust_id.EOF
last_cust_id = rs_get_cust_id.fields("id_number")
rs_get_cust_id.MoveNext
Loop
rs_get_cust_id.close
set rs_get_cust_id = nothing
I know it's the loop that's causing the error, because if I comment it out, it works fine. I'd be grateful if anyone has any suggestions.
Thanks in advance If I'm not mistaken, it looks like you are using VBScript here. Try replacing the "Do...Until" with a Do While NOT loop, and remove the loop statement at the end.Just tried that, to no avail. I must admit, I was sceptical about that working because the Do While works on a separate query, this is what I really don't understand (sorry, I forgot to mention that bit)!
The separate query is:
dim sql_get_cust_from_id
dim rs_get_cust_from_id
set sql_get_cust_from_id = nothing
sql_get_cust_from_id = "SELECT * FROM dbo.cs_customers" & vbCrLf
sql_get_cust_from_id = sql_get_cust_from_id & "WHERE cust_id = " & request.querystring("cust_id")
set rs_get_cust_from_id = Server.CreateObject("ADODB.Recordset")
rs_get_cust_from_id.Open sql_get_cust_from_id, conn
Do Until rs_get_cust_from_id.EOF
cust_title = rs_get_cust_from_id.fields("cust_title")
cust_fname = rs_get_cust_from_id.fields("cust_fname")
cust_sname = rs_get_cust_from_id.fields("cust_sname")
cust_add1 = rs_get_cust_from_id.fields("cust_add1")
cust_add2 = rs_get_cust_from_id.fields("cust_add2")
cust_add3 = rs_get_cust_from_id.fields("cust_add3")
cust_add4 = rs_get_cust_from_id.fields("cust_add4")
cust_add5 = rs_get_cust_from_id.fields("cust_add5")
cust_postcode = rs_get_cust_from_id.fields("cust_postcode")
cust_home_tel = rs_get_cust_from_id.fields("cust_home_tel")
cust_mob_tel = rs_get_cust_from_id.fields("cust_mob_tel")
cust_work_tel = rs_get_cust_from_id.fields("cust_work_tel")
cust_email = rs_get_cust_from_id.fields("cust_email")
rs_get_cust_from_id.MoveNext
loop
rs_get_cust_from_id.close
set rs_get_cust_from_id = nothing
The query is fine, as I run it in Query Analyzer and it returns exactly what I want it to.
There must be something different about the code in my first post that is messing it up, but I just can't figure out what!
Thanks for trying though! yeah you are not opening the connection or setting the connection string.No, I am doing that at the top of the page:
data_source = "NPG-SAGEDB2\LINE500V55"
db_to_connect_to = "cooserdb"
db_user = "cooser_user"
db_password = "coopass"
'Connect to the DB
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Provider=SQLOLEDB; Data Source = " & data_source & "; Initial Catalog = " & db_to_connect_to & "; User Id = " & db_user & "; Password = " & db_password & vbCrLf
The connection definitely opens successfully.what is the error message?
in IE go to Tools ->Advanced -> Uncheck Show Friendly Error messages.
It should show you the line, the page and description of the error. If no error, you might be RS.EOF
I'm putting this in the ASP section as I think the error is on the ASP side of things. Most of the stuff I've done before has been in Perl so I'm basically winging this ASP stuff!
I'm inserting a row, which works fine and then I'm trying to query the row ID, which is auto incremented (or is an Identity row in Microsoft's terminology.) Unfortunately when I run through the results (I'm looping, even though there should only be one result, as I basically don't know any other way!), I get into an infinite loop and then receive a 'Active Server Pages error 'ASP 0113' Script timed out' error. I've not left out the recordset.MoveNext argument, as you can see:
dim sql_get_cust_id
dim rs_get_cust_id
dim last_cust_id
set sql_get_cust_id = "SELECT IDENT_CURRENT('cs_customers') AS 'id_number'"
set rs_get_cust_id = Server.CreateObject("ADODB.Recordset")
rs_get_cust_id.Open sql_get_cust_id, conn
Do Until rs_get_cust_id.EOF
last_cust_id = rs_get_cust_id.fields("id_number")
rs_get_cust_id.MoveNext
Loop
rs_get_cust_id.close
set rs_get_cust_id = nothing
I know it's the loop that's causing the error, because if I comment it out, it works fine. I'd be grateful if anyone has any suggestions.
Thanks in advance If I'm not mistaken, it looks like you are using VBScript here. Try replacing the "Do...Until" with a Do While NOT loop, and remove the loop statement at the end.Just tried that, to no avail. I must admit, I was sceptical about that working because the Do While works on a separate query, this is what I really don't understand (sorry, I forgot to mention that bit)!
The separate query is:
dim sql_get_cust_from_id
dim rs_get_cust_from_id
set sql_get_cust_from_id = nothing
sql_get_cust_from_id = "SELECT * FROM dbo.cs_customers" & vbCrLf
sql_get_cust_from_id = sql_get_cust_from_id & "WHERE cust_id = " & request.querystring("cust_id")
set rs_get_cust_from_id = Server.CreateObject("ADODB.Recordset")
rs_get_cust_from_id.Open sql_get_cust_from_id, conn
Do Until rs_get_cust_from_id.EOF
cust_title = rs_get_cust_from_id.fields("cust_title")
cust_fname = rs_get_cust_from_id.fields("cust_fname")
cust_sname = rs_get_cust_from_id.fields("cust_sname")
cust_add1 = rs_get_cust_from_id.fields("cust_add1")
cust_add2 = rs_get_cust_from_id.fields("cust_add2")
cust_add3 = rs_get_cust_from_id.fields("cust_add3")
cust_add4 = rs_get_cust_from_id.fields("cust_add4")
cust_add5 = rs_get_cust_from_id.fields("cust_add5")
cust_postcode = rs_get_cust_from_id.fields("cust_postcode")
cust_home_tel = rs_get_cust_from_id.fields("cust_home_tel")
cust_mob_tel = rs_get_cust_from_id.fields("cust_mob_tel")
cust_work_tel = rs_get_cust_from_id.fields("cust_work_tel")
cust_email = rs_get_cust_from_id.fields("cust_email")
rs_get_cust_from_id.MoveNext
loop
rs_get_cust_from_id.close
set rs_get_cust_from_id = nothing
The query is fine, as I run it in Query Analyzer and it returns exactly what I want it to.
There must be something different about the code in my first post that is messing it up, but I just can't figure out what!
Thanks for trying though! yeah you are not opening the connection or setting the connection string.No, I am doing that at the top of the page:
data_source = "NPG-SAGEDB2\LINE500V55"
db_to_connect_to = "cooserdb"
db_user = "cooser_user"
db_password = "coopass"
'Connect to the DB
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Provider=SQLOLEDB; Data Source = " & data_source & "; Initial Catalog = " & db_to_connect_to & "; User Id = " & db_user & "; Password = " & db_password & vbCrLf
The connection definitely opens successfully.what is the error message?
in IE go to Tools ->Advanced -> Uncheck Show Friendly Error messages.
It should show you the line, the page and description of the error. If no error, you might be RS.EOF