Pull information from database and update

Report · Oct 27, 2009

Hello all,

First off, how do you read these forums with a newsreader? I can't seem to find the server address. I haven't been here for a long time. Thanks for the information.

Second.

I have a web page that is linked to a SQL server database.

I created a recordset to pull the number out of a field in a table in that database. I now want to create some coding that says look at this field, display it and then add a number to it. The field is always a number.

I have as follows (only in part):

set rsCasenumber = Server.CreateObject("ADODB.Recordset")
rsCasenumber.ActiveConnection = MM_ClientsWPrime_STRING
rsCasenumber.Source = "SELECT * FROM subcounters"
rsCasenumber.CursorType = 0
rsCasenumber.CursorLocation = 2
rsCasenumber.LockType = 3
rsCasenumber.Open()
rsCasenumber_numRows = 0

If Request("MM_insert")<> "" Then
rsClients.AddNew
rsClients("Casenum")=rsCasenumber("countervalue")

rsClients.Update
rsClients.Requery

now how do I say update rsCasenumber("countervalue") = the number plus one?

Thanks.

Report · Oct 27, 2009

>First off, how do you read these forums with a newsreader?

You can't. These forums are no longer propogated to nntp.

>now how do I say update rsCasenumber("countervalue") = the number plus one?

I'm not sure which number you mean, but assuming itself

rsCasenumber("countervalue") = rsCasenumber("countervalue") + 1

rsCasenumber.Update

Report · Oct 27, 2009

GREAT! One more thing.

I have a spot where a session variable is passed to the next page. I changed some code so I believe my variable never turns out to what it is supposed to be, therefore, things fail.

On the first page I create recordsets to enable me to insert stuff.

set rsClients = Server.CreateObject("ADODB.Recordset")
rsClients.ActiveConnection = MM_connNewdatabase_STRING
rsClients.Source = "SELECT * FROM CLIENTSW ORDER BY CASENUM DESC"
rsClients.CursorType = 0
rsClients.CursorLocation = 2
rsClients.LockType = 3
rsClients.Open()
rsClients_numRows = 0

set rsCasenumber = Server.CreateObject("ADODB.Recordset")
rsCasenumber.ActiveConnection = MM_connNewdatabase_STRING
rsCasenumber.Source = "SELECT * FROM subcounters"
rsCasenumber.CursorType = 0
rsCasenumber.CursorLocation = 2
rsCasenumber.LockType = 3
rsCasenumber.Open()
rsCasenumber_numRows = 0

Then I do some stuff and pull off the insert

If Request("MM_insert")<> "" Then
rsClients.AddNew
rsClients("Casenum")=rsCasenumber("countervalue")

rsClients.Update

Then I take the code you gave me and add "1" to the number in countervalue
rsCasenumber("countervalue") = rsCasenumber("countervalue") + 1
rsCasenumber.Update

Then I requery the rsClients recordset (I believe this is where it is failing) When I requery this rsClients("Casenum") would then equal rscasenumber ("countervalue") which is now a different number!
rsClients.Requery

Then I say
Session("UserID")=rsClients("CASENUM")
Session("email")=Request.Form("EMailAddress")
response.redirect "done3.asp"

Once I get to the next page it says:

Dim rsClients__svuserid
rsClients__svuserid = "0"
if (Session("UserID") <> "") then rsClients__svuserid = Session("UserID")
%>

<%
set rsClients = Server.CreateObject("ADODB.Recordset")
rsClients.ActiveConnection = MM_connNewdatabase_STRING
rsClients.Source = "SELECT * FROM CLIENTSW WHERE CASENUM=" + Replace(rsClients__svuserid, "'", "''") + " ORDER BY CASENUM"
rsClients.CursorType = 0
rsClients.CursorLocation = 2
rsClients.LockType = 3
rsClients.Open()
rsClients_numRows = 0
%>

I think the problem is that the new Session("UserID") never equals the casenumber because that rsclients recordset = the countervalue which has now changed.

How do I get around this? At the end of the second page I send an email and cannot get connected to it because I think the Session("UserID") never comes out correct.

Any suggestions on how I can get around this?

Thanks.

Report · Oct 27, 2009

A little context would be helpful. Please explain a little about what you are trying to achieve; what this application actually is doing, workflow, etc.

Report · Oct 27, 2009

I will attempt to explain.

I have a page that asks for some information and then inserts that information into a couple tables in the database.

I then want it to pull out a number out of one of the fields in a row that was just input and pass that number in a variable (along with other variables pulled from the page and pass them to the next page.) This next page has a session ID that comes from the page before. If this session ID is passed correctly then I can send out an email using ASPemail. This worked before I made some coding changes, but is broken now.

In part the code says:

So on the first page I create the recordsets to insert into database:

set rsClients = Server.CreateObject("ADODB.Recordset")
rsClients.ActiveConnection = MM_connNewdatabase_STRING
rsClients.Source = "SELECT * FROM CLIENTSW ORDER BY CASENUM DESC"
rsClients.CursorType = 0
rsClients.CursorLocation = 2
rsClients.LockType = 3
rsClients.Open()
rsClients_numRows = 0

set rsCasenumber = Server.CreateObject("ADODB.Recordset")
rsCasenumber.ActiveConnection = MM_connNewdatabase_STRING
rsCasenumber.Source = "SELECT * FROM subcounters"
rsCasenumber.CursorType = 0
rsCasenumber.CursorLocation = 2
rsCasenumber.LockType = 3
rsCasenumber.Open()
rsCasenumber_numRows = 0

THEN I TELL IT TO INSERT WHEN I PUSH THE SUBMIT BUTTON

<%If SubmitFail <> True Then %>
<%
'Insert record into recordset when form is submitted
If Request("MM_insert")<> "" Then
rsClients.AddNew
rsClients("Casenum")=rsCasenumber("countervalue")

rsClients.Update

THEN THE CODING I GOT FROM YOU TO ADD ONE TO THE COUNTERVALUE NUMBER
rsCasenumber("countervalue") = rsCasenumber("countervalue") + 1
rsCasenumber.Update
rsClients.Requery

THEN I TRY TO CREATE THE SESSION ID TO PASS TO THE OTHER PAGE
Session("UserID")=rsClients("CASENUM")
Session("email")=Request.Form("EMailAddress")
response.redirect "done3.asp"

ON THE NEXT PAGE I HAVE

<%
Dim rsClients__svuserid
rsClients__svuserid = "0"
if (Session("UserID") <> "") then rsClients__svuserid = Session("UserID")
%>
<%
set rsClients = Server.CreateObject("ADODB.Recordset")
rsClients.ActiveConnection = MM_connNewdatabase_STRING
rsClients.Source = "SELECT * FROM CLIENTSW WHERE CASENUM=" + Replace(rsClients__svuserid, "'", "''") + " ORDER BY CASENUM"
rsClients.CursorType = 0
rsClients.CursorLocation = 2
rsClients.LockType = 3
rsClients.Open()
rsClients_numRows = 0
%>

<%
Dim userAreaCode, userPhoneNumber, userAddress, userCity, userState, userZipcode, userFirstname, userLastname, useremail
userAreaCode = (rsClients.Fields.Item("CACODE").Value)
userPhoneNumber = (rsClients.Fields.Item("CPHONE").Value)
userAddress = (rsClients.Fields.Item("CADDRESS").Value)
userCity = (rsClients.Fields.Item("CCITY").Value)
userState = (rsClients.Fields.Item("CSTATE").Value)
userZipcode = (rsClients.Fields.Item("CZIP").Value)
userFirstname = (rsClients.Fields.Item("CFNAME").Value)
userLastname = (rsClients.Fields.Item("CLNAME").Value)
useremail = (rsClients.Fields.Item("email").Value)

Set Mail = Server.CreateObject("Persits.MailSender")

Mail.Host = "192.168.10.1"
Mail.Port = 25

Mail.From = useremail
Mail.FromName = userFirstname & " " & userLastname
Mail.AddAddress useremail
Mail.AddCC "XXXXXX@XXXXXXXXX"
Mail.AddBCC "XXXXXX@XXXXXXXXX"
Mail.AddBCC "XXXXXXX@XXXXXX"
Mail.Subject = "Application submitted"
Mail.Body = "<HTML><BODY>Thank you for applying for our services. Please be reminded that it may take up to one week to get a reply." & " "&" "& "If you have an emergency, please call the toll free number to apply for services, and indicate that you have already submitted an application online. XXXXXXXXX." & " " & " " & "The phone number you provided was:" &" " &"(" & userAreaCode &")"& " " & userPhoneNumber &"."&" " &" "& "The mailing address you provided was:" & " " & " " & userFirstname & " " & userLastname & " " & userAddress &" "& userCity & " " & userState & " " & userZipcode & " " & " " &"If you find that this information is wrong, please send an email to: webmaster@legalassist.org, and indicate the changes.</HTML></BODY>"
Mail.isHTML = True

Mail.Send

When it gets to the Mail.Send spot, it fails and says "connection refused". I still have a copy that works before I changed the insert page and added: rsClients("Casenum")=rsCasenumber("countervalue"). So I am sure it has something to do with that.

I know its hard to look at this and try to figure out what someone is trying to do. Does that help?

Report · Oct 27, 2009

Alright, I fixed it. The second page was causing me problems, I started from scratch and worked it out.

One last thing (I hope)

I want to take this line

rsClients("Casenum")=rsCasenumber("countervalue")

And say rsClients("Casenum") = Take today's date and trim everything off it except for the last two digits of the year and add "E-100" after that and then add rscasenumber("countervalue") if the countervalue is 4 digits long,
otherwise add "E-1000" after today date trimmed down and then rscasenumber("Countervalue") if countervalue is less than 3 digits long.

In other words say rsClients("Casenum") = rscasenumber("countervalue") (Right now countervalue is 4500)

so if countervalue is 4500 (4 digits long) then take the last two digits of the year 09 and add E-100 after than and then ad the countervalue which is 4500. Everything would make up 09E-1004500 and that is what would be inserted into the field Casenum.

Report · Oct 28, 2009

Do you still need help with this? Is the year coming from a database field, or the current server time?

Report · Oct 28, 2009

Nope, I fixed this too. It all stemmed from the variable that was carried over from the first page.