Asp and SQL question

[Athmaus]

I have this login script for a certain portion of our website for a while, and have around ~7500 users in a database that the script accesses.

I added two new columns in this table, the reason for this is because i found a new trick for adding more security for the section of the site that this script is protecting. Problem is that now that I have added thee two new colums, the script does not write in any information in these two columns.

I have copied the script and made a test table and everythign works, and the infromation is added in those two new columns.

Does anyone know what might be preventing the sciprt from writing in these tables? Or can you not add columns to a SQL table once it is in use (and that would make no sense to me if it was like that)

I wish i could provide more information other than posting up the script, as i get no errors at all.

Any help would be greatly appreciated. Thanks!

[glenngv]

We need to see the SQL statement you're using and probably some related codes.

[Athmaus]

sorry:

New colums added: download and totaldl

Thing is if i create a brand new table everythign works, yet if i add these colums in the already existing database, this portions doesnt work

Code:



If Session("login") = FALSE Then
Response.Redirect http://www.yahoo.com"
Else

Dim myconn, verify, blnLoggedIn, user, pass, site, logged, objRS, exceeded
Set verify = Server.CreateObject("ADODB.Connection")
verify.open = "connection string"
Set myconn = Server.CreateObject("ADODB.Connection")
myconn.open = "connection string"

'Response.Write(Session("username"))
'Response.Write(Session("password"))

user = CStr(Session("username"))
pass = CStr(Session("password"))

exceeded = 5

Set objRS = myconn.execute("SELECT id, download, totaldl FROM regfreeup WHERE username='" & user & "' AND pass='" & pass & "';")

If objRS.EOF Then '''NO RECORDS MATCH. USER DID NOT LOG IN CORRECTLY
blnLoggedIn = False
Response.Redirect http://www.yahoo.com"


Else
If objRS("download") >= exceeded Then 'LOGGED IN AN ABNORMAL TIME
blnLoggedIn = false
Response.Redirect http://www.google.com

Else '''EVERYTHING PASSED PROCEEDE WITH DOWNLOAD
blnLoggedIn = True
verify.execute("UPDATE regfreeup set download = (download + 1) , totaldl = (totaldl + 1) WHERE username='" & user & "' AND pass='" & pass & "';")

Response.Redirect http://ps2.ign.com

objRS.Close
Set objRS= Nothing
myconn.Close
Set myconn= Nothing
verify.Close
Set verify= Nothing

End If
End If
End If

[miranda]

Why do you have two connection objects and 2 connection strings? are they pointing to 2 different databases? If so did you add the columns to the table of both databases? If not there is no need for 2 different connections.

By looking at your code I am guessing that you have connections to 2 different databases. On one of them you have added the columns in the table but not in the other.

[Athmaus]

both connection strings goto same database, same table, i just use two. I just didnt put the full connection string up for security reasons.

THe thing is that on the existing table it will not update or mess with the download or totaldl columns,

But in an entirely new table, it will use them

[miranda]

both connection strings goto same database, same table, i just use two. I just didnt put the full connection string up for security reasons.




There is no need to do this. You are using server memory for each of these objects.

when this doesn't work is this on a local computer or a hosted one?

[Athmaus]

ok well i can look at the connection string problem later,

My concernt is that if i make an entirely new table the script works fine, updating the database each time there is a succesful download and then going to the "exceeded" page once you have donwloaded to many times.

I just get the problem if it is on the table that i added the download and totaldl colums in. It seems to "skip" the update sql query.

THe scripts are 100% the same except for the table name.

[Freon22]

Ok thats it, I just tested this. If you add a column to your database then that column is null on all the exiting data. So null + 1 = null, you are going to have to add 0 to those null records. Then your code should work fine, does anyone know how to do a batch update? where the field is null.

Ok I did a little more work and here is a batch update.


"UPDATE regfreeup set download = 0, totaldl = 0 WHERE download Is Null And totaldl Is Null;"

Run this one time then check to see if all the null field now = 0.
After that your normal code should work just fine.

I tested it like this and it worked great, Only the two null fields were added. If I already had something in that field it skip it and when to the next null field.

strSQL1 = "Update tip Set ip = 0, name = 'Freon22' Where ip Is Null And name Is Null;"
objConn1.Execute strSQL1

[miranda]

Freon has the right idea. You have to change the value of the field to 0 before you can add 1 to it. Adding 1 to null returns null. I was thinking along the lines of different database that was why I asked if it was local or hosted. 1 Time a few years ago I had made changes to a datatable only to find out I had only changed a copy of the database and not the production database. After that all changes to the database tables were done via script( create table / alter table - add column, drop column) to ensure I was changing the correct database.

[Athmaus]

Hey!

I just got back in office and before i checked out the answers and i looked at my code once more, then I figured out the answer. THe problem is that i didnt select the username and password in that select statemnt. Stupid me!!! :mad:

Yeah i knew about the nulls so i created a custom scipt that just updated the download and the totaldl to 0. In the signup script i set those two fields to 0 starting wtih everyone who signed up yesterday


Set objRS = myconn.execute("SELECT id, download, totaldl FROM regfreeup WHERE username='" & user & "' AND pass='" & pass & "';")

Once i did that everythign worked. Wierd how i never got an error on it though... -_-

Thanks for your help! :)

[doomz]

Wanna ask everybody, I got this error while running?

THE ERROR IS BELOW:
Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.

MY POSTED VARIABLE IS THIS:
IdEdit : 85 --> text
isi : Testing text test 123 hello 123 --> text in multiline
tglpost : 6/6/2005 --> date/time
jampost : 10:41:29 AM ---> date/time
PostID : 200506030935 ---> text

MY TABLE 'NAME' IS 85 AND THE FIELDS IS:
tPostID ---> text
tisi ---> memo
postby ---> text
postdate ---> date/time
posttime ---> date/time

THE CODE LINE WHICH ERROR IS BELOW:
koneksi2.execute("UPDATE "&IdEdit&" SET tIsi = '"&isi&"', postdate = #"&tglpost&"#, posttime = #"&jampost&"# WHERE tPostID = "&PostID&"")

where is my error, is there SQL QUERY error?
I have check it for two days and still didn't find the cause of error
pls somebody help me !!! which can posibbly cause the mismatch type?

any posibble thing can cause this error pls tell

[Freon22]

I did a google search on your error and here is a link where I found this.

Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.

This error usually happens when you do one of the following things:
attempt to insert a date in Access with ' delimiters;
attempt to insert a date in SQL Server with # delimiters;
attempt to insert a date in Access or SQL Server with no delimiters; or,
attempt to insert a malformed date.

http://www.aspfaq.com/show.asp?id=2289

[miranda]

Try to do a Response.Write on your sql statement to see what it is generating. Quite often that will show an error that is not otherwise seen. The easiest way is to do so like this

SQL = "UPDATE "&IdEdit&" SET tIsi = '"&isi&"', postdate = #"&tglpost&"#, posttime = #"&jampost&"# WHERE tPostID = " & PostID
Response.Write(SQL)
koneksi2.execute (SQL)

[doomz]

Ouch! I got it thank evevybody ^_^.
thanks to Miranda's suggestion. I see it more clear with your troubleshooting method.

I'm too concern about the date format, the problem is after the word "WHERE" variable. (see the PostID one variable, check it)

before:
koneksi2.execute("UPDATE "&IdEdit&" SET tIsi = '"&isi&"', postdate = #"&tglpost&"#, posttime = #"&jampost&"# WHERE tPostID = "&PostID&"")

it should be:
koneksi2.execute("UPDATE "&IdEdit&" SET tIsi = '"&isi&"', postdate = #"&tglpost&"#, posttime = #"&jampost&"# WHERE tPostID = '"&PostID&"' ")


thx thx thx ^_^. it like gonna kill my head while the programming halt.
r u guys feel the same ? ^^

[miranda]

Using response.Write can be a valuable debugging tool. When I first started to use asp I learned to use that and to always use option explicit.

[doomz]

Another SQL Question...

I have two table or more. (tableA, tableB, etc..)
I want to update the value of tableA where the value I need is taken from tableB. is it posibble to update the value directly from TableB without call out to ADO Recordset?

from what I see most SQL tutorial everywhere, I don't find the explain to do this from UPDATE statement.

most of them just give this sample, too simple:
UPDATE tableA SET valueA = 'I still a newbie' WHERE id = '0001'

[miranda]

Assuming that tableA.ID is a text field then use
"UPDATE tableA, tableB SET tableA.valueA = tableB.valueA WHERE (tableA.ID = '0001');"

[doomz]

I asking step by step to make sure the answer is meet the point i need, soorry is I asking too much maybe look like the same.

the next question is:

now the same question. but what I want to change is the date type, here:
I want to update the tableA where the value is from tableB which the value type is date, and the value I want to select is the latest date from all of the records from tableB.

sometime when somebody delete is post if the post he delete is the latest post then the last post date should be change to the one before it. that is what I want to update. how the SQL