?
Solved

Updating a recordset - problem!

Posted on 2003-04-01
13
Medium Priority
?
199 Views
Last Modified: 2006-11-17
Hi all...

I am trying to update a record using SQL Server and getting the following error:

***********************************************************************************
Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'Hrs'.

/wfm/calctime.asp, line 69
***********************************************************************************

Here is my code:

<!-- #include virtual="/include/database_inc.asp" -->
<%

'connection function with this file
OpenSQL("calctime.asp")

     'declare recordset
     dim objUpd
     set objUpd = Server.CreateObject("ADODB.Recordset")
     
               'set lock and cursor types
               objUpd.locktype = adLockOptimistic
               objUpd.cursortype = adOpenKeyset
     
     'open the VacTable with the connection string from the OpenSQL()
     objUpd.Open "VacTable", connection

     'get unique user ID
     dim strEmpID
     strEmpID = request.cookies("CalcID")
     
     'calculation variables
     dim intCurrBal, intCurrTaken, intCurrDays, intTotal
     
     
     do while not (objUpd.EOF)
          'locate the user ID in the table
          if (strcomp (strEmpID, objUpd("EmplID"), vbtextcompare) = 0) then
         
               'declare variables
               intCurrBal = objUpd("Vac Hrs Bal YTD")
               intCurrTaken = objUpd("Vac Hrs Taken YTD")
               intCurrDays = request.cookies("DaysRequested")
               
               'get total hours used
               intTotal = (intCurrDays * 8)
               
               'update numbers
               intCurrBal = (intCurrBal - intTotal)
               intCurrTaken = (intCurrTaken + intTotal)
               
               'assign numbers back to the table
               objUpd("Vac Hrs Bal YTD") = intCurrBal
               objUpd("Vac Hrs Taken YTD") = intCurrTaken
               
               'update the record
               objUpd.update
               
               response.redirect("amenu.asp")
               
          else

               objUpd.MoveNext
               
          end if

     loop

               CloseSQL()
               
%>
               
And just to note, all the database field names are spelled correctly. Any help would be awesome.
0
Comment
Question by:cgamberg
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
  • +1
13 Comments
 
LVL 9

Accepted Solution

by:
fz2hqs earned 150 total points
ID: 8246227
White space is never a good thing.

If you can, rename the columns replace the spaces with "_" will do.

You can try using square brackets i.e objUpd("[Vac Hrs Bal YTD]") however I am not certain that will do it
0
 

Author Comment

by:cgamberg
ID: 8246283
Thanks for your response. I tried your suggestions, unfortunately, both did not work.

I have been able to update many other recordsets, for example rs("Employee ID") no problem. For some reason, this is giving me a headache!
0
 
LVL 9

Expert Comment

by:fz2hqs
ID: 8246328
If you replaced the spaces with "_" you should have gotten a different message
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 34

Expert Comment

by:arbert
ID: 8246508
What is the exact line above that generates the error?

Have you turned on ODBC tracing to see exactly what's being sent to the server?

Brett
0
 

Author Comment

by:cgamberg
ID: 8246588
The exact line that generates the error is

             objUpd.update

How do I turn on ODBC tracing? Sorry, not familiar with this....
0
 
LVL 34

Expert Comment

by:arbert
ID: 8246638
Go into your datasource administrator and there is a tracing tab.  Give it the trace file name and click on start tracing (you have to do this on the machine that's running your asp page).  DON'T FORGET TO TURN IT OFF WHEN YOU'RE DONE--it slows things down!

Brett
0
 

Expert Comment

by:bmilner
ID: 8246910
Just so that this is clear, you understand that fz2hqs is saying to rename the columns in your database, not the code of the page. Rename those using the "_" in place of spacing. This is a good convention to follow anyway.
0
 

Author Comment

by:cgamberg
ID: 8247076
bmilner, yep, I understand.

What I don't get is that in about 12 other ASP pages, I update and reference those two same fields without trouble.

I just can't seem to figure out why on this single page, it won't work.

Thanks, Chris
0
 

Expert Comment

by:bmilner
ID: 8247171
Yeah, I see why you wouldn't want to change it then. In my experience, that's exactly what tends to happen with names like that though; one place they work and another they don't.

All I can say is that the error is being thrown in the execution of the update meaning that some variable or name is not matching up correctly.

Sorry, I know this is not much help. All I can say is that if I were in your place, I would copy the table containing these values to another table, rename the column names, then change the code in your page to test and see if you get a different error. You could at least elimate that as a possibility then.

0
 
LVL 34

Expert Comment

by:arbert
ID: 8247224
Ya, sometimes ODBC errors aren't that specific--are you sure that the right datatypes are being passed in all cases?

That's why I recommended the ODBC logging--you can see the actual statement that is prepared....

Brett
0
 

Author Comment

by:cgamberg
ID: 8247751
I have figured it out.

When I changed the field names with "_" instead of spaces I received the following error:

***********************************************************************************
Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'Name'.

/wfm/calctime.asp, line 69
***********************************************************************************

"Name" is another field in this table, that's not being used in this routine. So instead of accessing the whole "VacTable" in this line of code:

    objUpd.Open "VacTable", connection

I added the following:

dim strSQL

strSQL = "SELECT [EmplID], [Vac_Hrs_Bal_YTD], [Vac_Hrs_Taken_YTD] FROM [VacTable];"

     objUpd.Open strSQL, connection

Now, it works wondefully.... GO FIGURE!

Thank you for all your suggestions! I'll give it to fz2hqs, who originally told me to change my naming conventions, and thus root out the problem.

Cheers,
Chris


0
 

Author Comment

by:cgamberg
ID: 8247770
Thanks fz2hqs....
0
 
LVL 34

Expert Comment

by:arbert
ID: 8247974
Name is a reserved word.....
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:   The Exchange of informatio…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question