ADDNew works when using ACCESS but not MSSQL

[jeepxo]

This works on my test box with ACCESS as the DB, but I get a 500 error on it when I move it to the SQL box.

It's ASP with Javascript not VBScript.

if (!(Session("TeacherID")>> 0)) 
		{
			teacherID  = 0;
		}
		sqlString = "select TeacherID, FirstName, LastName, PhoneNumber, Extension,FaxNumber,emailAddress, SchoolID, Username, Password, Region from teachers where teacherID =" + teacherID;
		rsTeacher.Open(sqlString,oConn,adOpenKeyset,adLockOptimistic,adCmdText );
		if (teacherID == 0)
		{
			rsTeacher.AddNew();
		}

I'm thinking it has to do with the curser, but I have tried adOpenDynamic as well.

TeacherID is an autoincrement column in the table.

"Every day is like a precious gift, you have to make it count" James Birrell 1993-2001

 

[Sheco]

Did you double check the permissions on the table?

 

[jeepxo]

select insert update and delete are all enabled for that account on that table.

"Every day is like a precious gift, you have to make it count" James Birrell 1993-2001

 

[Sheco]

And everything else works on the SQL box except the .AddNew() method?

If so make sure the recordset is good by verifying that its .State property == 1 and also check that oConn.Errors == 0

Other than that might also try setting the .CursorLocation property...

 

[bslintx]

how is it updating/adding without the update? i'm used to using it in access...but i guess addnew() takes care of it...if i'm wrong...ummm i tried;-)

Good Luck

rsTeacher.Open sqlString,oConn, 3, 3

if (teacherID == 0)
{
rsTeacher.AddNew;
rsTeacher("FirstName") = Request("firstname")
'etc
'etc
rsTeacher.Update
rsTeacher.Close
}

 

[jeepxo]

The update is further down in the code.
In this case it is irrelevant since it fails at addnew, the rest of the code works fine.

If there is a teacherID in the session object then it updates that teachers information. If this is a new teacher (hence no teacherID) then the if condition is met and the addnew statement is used.

The problem does not occur when I am updating an existing teacher.

"Every day is like a precious gift, you have to make it count" James Birrell 1993-2001

 

[Sheco]

I think your recordset needs to have every field in the table before you can .AddNew()

 

[bslintx]

sorry, i though my implied that w/ etc. etc.etc within addnew and update...thanks for mentioning that sheco...how's the qutting smoking going?

Brian

 

[Sheco]

OK but I've got both the nicotine patch and the candies so I'm getting a double dose right now... that means if I fail then I'll be smoking more than before.

 

[FesterSXS]

try surrounding your fieldnames with square brackets - some of the fieldnames may be classed as keywords within SQL Server. (username or password maybe)

sqlString = "select TeacherID, FirstName, LastName, PhoneNumber, Extension,FaxNumber,emailAddress, SchoolID, [red][[/red]Username[red]][/red], [red][[/red]Password[red]][/red], Region from teachers where teacherID =" + teacherID;

Tony
_______________________________________________________________

http://www.SpiraxSarco.com/

 

[bslintx]

nice one fester