Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 11065
  • Last Modified:

ODBC Count Field Incorrect

I moved my webserver from one server to another. My operating system on my old server was Windows NT. My new server has windows 2000 server. I have visual interdev 6.0. My problem is when I try to run an application, I get an error.

HTTP 500.100 - Internal Server Error - ASP error
Internet Information Services

--------------------------------------------------------------------------------

Technical Information (for support personnel)

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E10)
[Microsoft][ODBC SQL Server Driver]COUNT field incorrect or syntax error
/contactinfoapp/_ScriptLibrary/Recordset.ASP, line 636

I have installed the Front Page extension, I have updated to the latest MDAC.

I'm using a statement select * from table where field = ? and I am using a recordset to pass a paramenter into the question mark. If I hard code it select * from table where field = 'yellow' then the application works just fine. I have a feeling it has something to do with my data connection, but I just can't seem to figure it out.

Thanks in advance.
0
hustedj
Asked:
hustedj
  • 6
  • 5
  • 2
  • +1
1 Solution
 
fruhjCommented:
Are you using an access database?
Can you paste some additional code - I'd like to see the connection string and the other code around line 636
0
 
hustedjAuthor Commented:
I am using SQL 2000

The _ScriptLibrary/Recordset.asp is part of the built in stuff that just automatically gets inserted when you create a new product.

Here is some of the code from the recordset that it is having a problem with...

<SCRIPT LANGUAGE="JavaScript" RUNAT="server">
function _setParametersRecordset1()
{
      Recordset1.setParameter(0,Session("SalesRep"));
}
function _initRecordset1()
{
      Recordset1.advise(RS_ONBEFOREOPEN, _setParametersRecordset1);
      var DBConn = Server.CreateObject('ADODB.Connection');
      DBConn.ConnectionTimeout = Application('Database_ConnectionTimeout');
      DBConn.CommandTimeout = Application('Database_CommandTimeout');
      DBConn.CursorLocation = Application('Database_CursorLocation');
      DBConn.Open(Application('Database_ConnectionString'), Application('Lumbermens_RuntimeUserName'), Application('Database_RuntimePassword'));
      var cmdTmp = Server.CreateObject('ADODB.Command');
      var rsTmp = Server.CreateObject('ADODB.Recordset');
      cmdTmp.ActiveConnection = DBConn;
      rsTmp.Source = cmdTmp;
      cmdTmp.CommandType = 1;
      cmdTmp.CommandTimeout = 10;
      cmdTmp.CommandText = 'select * from table where field = ?';
      rsTmp.CacheSize = 10;
      rsTmp.CursorType = 3;
      rsTmp.CursorLocation = 3;
      rsTmp.LockType = 3;
      Recordset1.setRecordSource(rsTmp);
      Recordset1.open();
      if (thisPage.getState('pb_Recordset1') != null)
            Recordset1.setBookmark(thisPage.getState('pb_Recordset1'));
}
function _Recordset1_ctor()
{
      CreateRecordset('Recordset1', _initRecordset1, null);
}
function _Recordset1_dtor()
{
      Recordset1._preserveState();
      thisPage.setState('pb_Recordset1', Recordset1.getBookmark());
}
</SCRIPT>

Thanks.
0
 
trainsdseCommented:
Parameters using the ? mark can cause this error when converting from access to mssql.  Post the code where you create the query and pass the parameter and I can show you how to change it to make it work.

Thomas
0
[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

 
hustedjAuthor Commented:
I'm not converting from access to mssql. I've always used sql for my database. I'm switching from windows NT to windows 2000 server. Above is the recordset where I pass in the parameter.
0
 
trainsdseCommented:
Okay, sorry about that, good luck.
0
 
bigbillydotcomCommented:
how about doing a response.write at the top of the page to check your session variable that you are passing as a parameter?
put this as the second line after setting the code language
<%
response.write "Salesrep="+cstr(Session("SalesRep"))
%>

and see what the value of that session variable is
0
 
bigbillydotcomCommented:
also - try hardocding the session variable to make sure it is going to work

Session("SalesRep")="bigbilly"
0
 
bigbillydotcomCommented:
it has to be something with your parameters going into the recordset
no other reason the recordset object should return that error unless its not dealing wit hthe passed paramter properly
0
 
hustedjAuthor Commented:
I hard coded in the parameter and it works just fine. It is only when I have the question mark (select * from table where field = ?)

I transfered an intranet based application from windows NT to windows 2000 using the same code and that works just fine. Do you think it has anything to do with the firewall?
0
 
bigbillydotcomCommented:
no - i dont beleive it is a firewall issue at all

It has to do with the setting of the parameter - since it works when hard coded
the code you are posting - it looks like the output of a DTC recordset control
is that correct?
0
 
bigbillydotcomCommented:
something looks fishy about your recordset objecti cant seem to figure out how you set it up to work like that
can you tell me more about what you are trying to accomplish?
I have used VI for a long time, and have found alot easier methods than using the DTC recordset controls
i'd be glad to share if you tell me what you are trying to do
cheers

0
 
hustedjAuthor Commented:
I did figure my problem out, but if there is an easier method that I should be using, I'm all about learning!

First let me explain how I fixed my problem.

I was coping all files to my new server, including the files that normally get created by creating a new project. I decided to delete those and only copied my .asp/.htm pages. I created a new project (on the new server with the same name as it was on the old server), set up my dataconnection, then I copied my .asp/.htm pages. That was set one of fixing my problem.

Set two. I forgot about that I was using the global.asp to pass in some parameters/login information. I added that code, which gave me another error.

I then checked my ODBC connection and realized I was still pointing at our old database. Once I established an odbc connection to the correct database, it started working.

It is always the little things that should be so easy that always get you!

Thank you to all your suggestions!

Back to what BIGBILLYDOTCOM was talking about.

What I'm trying to do is when a sales rep enters their username (salesrep number), they get their customer information. That mainmenu page when they login takes their salesrep number and puts it as a session variable.

Another thing it does is there are other people that have access to login that are not salesreps that need access particular pages. The mainmenu determines if they are a sales rep, if they are a sales rep it displays buttons that a non-salesrep would not see. If they aren't a sales rep then they get other buttons and other access to different pages.

Other pages do other things. I always use the recordset DTC control and use the recordset.updaterecord, etc. But if there is a better way, by all means, share!
0
 
bigbillydotcomCommented:
i thought something was missing - i just couldnt figure out how that ODBC error could come out of the code you posted

I am not going to say the way I do things are "better" - esp. if you are already productive
but there are some alternative methods to using the DTC's in VI - that is, create your own methods/subs/functions to create those design elements

One of the great "Eurekas" I had was that basically all asp was doing was "writing" html back to the browser, and the DTC's accomplished this by means of using some java libraries and exposing those libraries subs/functions/properties/methods via the VI user interface

I spent FOREVER trying various hacks on the DTC's (for instance, displaying clickable images in the the Grid Control) to accomplish what I wanted to do
before I gave up and learned how to create my own functions/subs that would spit out the html code I wanted

most of the code would therefore be considered "classic asp"

Anyway - to make a long story short here, is an example of making a simple grid from a sql stored procedure

<%
Function Prt(stuff)
     response.write cstr(stuff)+vbcrlf
End Function

set con=server.CreateObject("adodb.connection")
con.Open(application("ConnectionString")),,,adcmdStoredProc
mysql = "Exec ProcAuto_sp5 @ID='"+ID+"'"
set rs = con.Execute(mysql)

     Prt("<table><tr><td>Name</td><td>Address></td><td>Phone</td></tr>")

if Not rs.EOF then
     Do until rs.EOF
          Prt("<tr><td>")
          Prt(rs("Name"))
          Prt("</td><td>")
          Prt(rs("Address"))
          Prt("</td><td>")
          Prt(rs("Phone"))
          Prt("</td></tr>")
   rs.movenext
     loop
end if

     Prt("</table>")
rs.close
set rs = nothing
con.close
set con = nothing
%>
0
 
hustedjAuthor Commented:
Thanks everyone for all your help. Bigbilldotcom, I'll have to try out what you are talking about.

Thanks again.
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 6
  • 5
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now