MSSQL Fields Out Of Order

[DVHost]

For years I have been working with MS Access and enjoying the simplicity and ease of use. I am now making the move and converting all of my sites to MSSQL. Virtually everything works but in the process, I have found an oddity that I am not used to.

I have a question about fields in an MSSQL DB or any type of SQL DB for that matter. My question concerns pulling data from fields out of a particular order.

Let’s say that you have a table with the following fields in their specific order:

tblMyTable:

fld_id
fld _name
fld _email
fld_ip

Using ASP/VBScript I can access the data by connecting to the correct MSSQL server. I can display the information with no problems unless I attempt to pull the information out of order. For instance if I want to display the fld_ip and then the fld_name it will not work. Only fld_ip would be displayed. I presume that this is because fld_name comes BEFORE fld_ip.

SQL Statement: "SELECT * FROM tblMyTable"

Code Examples:

In this example, I am calling the fields in the order in which they appear in the table. Both items display correctly.

<table>
 <tr>
   <td>ID:</td>
   <td><%=(rsRecordset.Fields.Item("fld_id").Value)%></td>
 </tr>
 <tr>
   <td>Name:</td>
   <td><%=(rsRecordset.Fields.Item("fld_name").Value)%></td>
 </tr>
 <tr>
   <td>Email:</td>
   <td><%=(rsRecordset.Fields.Item("fld_email").Value)%></td>
 </tr>
 <tr>
   <td>IP:</td>
   <td><%=(rsRecordset.Fields.Item("fld_ip").Value)%></td>
 </tr>
</table>

In this example, I am calling the fields out of order. Only fld_ip displays correctly. fld_name does not display at all again I presume that this is because fld_name comes BEFORE fld_ip.

<table>
 <tr>
   <td>ID:</td>
   <td><%=(rsRecordset.Fields.Item("fld_id").Value)%></td>
 </tr>
 <tr>
   <td>IP:</td>
   <td><%=(rsRecordset.Fields.Item("fld_ip").Value)%></td>
 </tr>
 <tr>
   <td>Name:</td>
   <td><%=(rsRecordset.Fields.Item("fld_name").Value)%></td>
 </tr>
 <tr>
   <td>Email:</td>
   <td><%=(rsRecordset.Fields.Item("fld_email").Value)%></td>
 </tr>
</table>

In addition to this, I am not able to display fields more than once.

<table>
 <tr>
   <td>ID:</td>
   <td><%=(rsRecordset.Fields.Item("fld_id").Value)%></td>
 </tr>
 <tr>
   <td>Name:</td>
   <td><%=(rsRecordset.Fields.Item("fld_name").Value)%></td>
 </tr>
 <tr>
   <td>Email:</td>
   <td><%=(rsRecordset.Fields.Item("fld_email").Value)%></td>
 </tr>
 <tr>
   <td>IP:</td>
   <td><%=(rsRecordset.Fields.Item("fld_ip").Value)%></td>
 </tr>
 <tr>
   <td>Name:</td>
   <td><%=(rsRecordset.Fields.Item("fld_name").Value)%></td>
 </tr>
</table>

The second instance of the field “fld_name” will not display at all.

With MS Access I could display the data in any order I wanted. I could also display them multiple times without any problems.

The BIG QUESTION… Why on Earth would MSSQL behave this way?

Now, I realize that I could easily assign the data to variables and use them any way I wanted to like this:

<% 
varID = (rsRecordset.Fields.Item("fld_id").Value)
varName = (rsRecordset.Fields.Item("fld_name").Value)
varEmail = (rsRecordset.Fields.Item("fld_email").Value)
varIP = (rsRecordset.Fields.Item("fld_ip").Value)
%>

That’s great but not necessarily the most efficient way to reduce code.

So what is the deal with the limitation on this more advanced DB? What am I missing?

Thanks!

Dean

[RobStocks]

For years I have been working with MS Access and enjoying the simplicity and ease of use. I am now making the move and converting all of my sites to MSSQL. Virtually everything works but in the process, I have found an oddity that I am not used to.

I have a question about fields in an MSSQL DB or any type of SQL DB for that matter. My question concerns pulling data from fields out of a particular order.

Let’s say that you have a table with the following fields in their specific order:

tblMyTable:

fld_id
fld _name
fld _email
fld_ip

Using ASP/VBScript I can access the data by connecting to the correct MSSQL server. I can display the information with no problems unless I attempt to pull the information out of order. For instance if I want to display the fld_ip and then the fld_name it will not work. Only fld_ip would be displayed. I presume that this is because fld_name comes BEFORE fld_ip.

SQL Statement: "SELECT * FROM tblMyTable"

Code Examples:

In this example, I am calling the fields in the order in which they appear in the table. Both items display correctly.

<table>
<tr>
  <td>ID:</td>
  <td><%=(rsRecordset.Fields.Item("fld_id").Value)%></td>
</tr>
<tr>
  <td>Name:</td>
  <td><%=(rsRecordset.Fields.Item("fld_name").Value)%></td>
</tr>
<tr>
  <td>Email:</td>
  <td><%=(rsRecordset.Fields.Item("fld_email").Value)%></td>
</tr>
<tr>
  <td>IP:</td>
  <td><%=(rsRecordset.Fields.Item("fld_ip").Value)%></td>
</tr>
</table>

In this example, I am calling the fields out of order. Only fld_ip displays correctly. fld_name does not display at all again I presume that this is because fld_name comes BEFORE fld_ip.

<table>
<tr>
  <td>ID:</td>
  <td><%=(rsRecordset.Fields.Item("fld_id").Value)%></td>
</tr>
<tr>
  <td>IP:</td>
  <td><%=(rsRecordset.Fields.Item("fld_ip").Value)%></td>
</tr>
<tr>
  <td>Name:</td>
  <td><%=(rsRecordset.Fields.Item("fld_name").Value)%></td>
</tr>
<tr>
  <td>Email:</td>
  <td><%=(rsRecordset.Fields.Item("fld_email").Value)%></td>
</tr>
</table>

In addition to this, I am not able to display fields more than once.

<table>
<tr>
  <td>ID:</td>
  <td><%=(rsRecordset.Fields.Item("fld_id").Value)%></td>
</tr>
<tr>
  <td>Name:</td>
  <td><%=(rsRecordset.Fields.Item("fld_name").Value)%></td>
</tr>
<tr>
  <td>Email:</td>
  <td><%=(rsRecordset.Fields.Item("fld_email").Value)%></td>
</tr>
<tr>
  <td>IP:</td>
  <td><%=(rsRecordset.Fields.Item("fld_ip").Value)%></td>
</tr>
<tr>
  <td>Name:</td>
  <td><%=(rsRecordset.Fields.Item("fld_name").Value)%></td>
</tr>
</table>

The second instance of the field “fld_name” will not display at all.

With MS Access I could display the data in any order I wanted. I could also display them multiple times without any problems.

The BIG QUESTION… Why on Earth would MSSQL behave this way?

Now, I realize that I could easily assign the data to variables and use them any way I wanted to like this:

<% 
varID = (rsRecordset.Fields.Item("fld_id").Value)
varName = (rsRecordset.Fields.Item("fld_name").Value)
varEmail = (rsRecordset.Fields.Item("fld_email").Value)
varIP = (rsRecordset.Fields.Item("fld_ip").Value)
%>

That’s great but not necessarily the most efficient way to reduce code.

So what is the deal with the limitation on this more advanced DB? What am I missing?

Thanks!

Dean

Hi Dean,
I think the problem may be with the type of connection you are making with MS SQL Server. The behaviour you describe sounds like the behaviour you get from a connection using a 'forward only' cursor. This is the default cursor type when you call the Execute method of the Command and Connection objects. To provide the functionality you are used to with Access use the static, keyset or dynamic cursor. The forward-only cursor is optimized for performance - a case of 'horses for courses'

Regards

Rob

[DVHost]

Hi Dean,
I think the problem may be with the type of connection you are making with MS SQL Server. The behaviour you describe sounds like the behaviour you get from a connection using a 'forward only' cursor. This is the default cursor type when you call the Execute method of the Command and Connection objects. To provide the functionality you are used to with Access use the static, keyset or dynamic cursor. The forward-only cursor is optimized for performance - a case of 'horses for courses'

Regards

Rob

Hi Rob!

You are right on the money. I have found out this very thing from another forum I posted the problem in as well. But there is something else that I need to address and that is the different objects involved.

* Command Object
* Connection Object
* Recordset Object

I use Dreamweaver v8.0.2 to develop and when you create a new Recordset (Query) it automatically writes the best possible code for you as I am sure you might be aware. Here is a sample of what it codes:

<%
Dim Recordset1
Dim Recordset1_cmd
Dim Recordset1_numRows
 
Set Recordset1_cmd = Server.CreateObject ("ADODB.Command")
Recordset1_cmd.ActiveConnection = MM_ADSystemSQL_STRING
Recordset1_cmd.CommandText = "SELECT * FROM tblGBook" 
Recordset1_cmd.Prepared = true
 
Set Recordset1 = Recordset1_cmd.Execute
Recordset1_numRows = 0
%>

As you can see, DW by default uses the Command object and from what I can tell, there is no way to use the CursorType directive with the Command object alone.

Different sources of information on the subject offer different explanations of these objects. For instance, one source indicated that when using the Command object to connect to a database that the Connection object is automatically called into the scenario. In addition to this, using a SELECT statement with a Command object calls the Recordset object as well which I interpreted to mean that all three objects are in use despite only coding the Command object.

Still, the only way it seems possible to specify CursorType is to write in a call to the Recordset object and then specify the CursorType. Typically in these scenarios, you would not code the Command object at all, instead, you would write calls to the Connection and Recordset objects solely.

Why does DW do this?

I have looked into developing a custom Server Behavior but there is no way to mimic the complete automation of DW's default Recordset (Query) behavior. I was successful in getting it to mimic the default behavior to an extent using my default code, special parameters and such. But I cannot get it to automatically create a Connection script and place it into the Connections folder or create a folder for that matter. Nor can I duplicate the tables column selection feature with radio buttons (All, Selected) or the filter and sort features.

Here is a screen shot of DW's default Recordset behavior:

[IMG]http://aboutdean.com/images/subject/hosted/dwrsquery1.jpg[/IMG]

This is the closest I can get to duplicating it:

[IMG]http://aboutdean.com/images/subject/hosted/dwrsquery2.jpg[/IMG]

DW gives you a plethora of options that you can use to create these windows:

[IMG]http://aboutdean.com/images/subject/hosted/dwrsquery3.jpg[/IMG]

But there is no automated means of making many of them work without actually editing the EDML files that contain their logic.

This is certainly off topic and more work than I need. However, the prospect is great if you can actually get it to code what you need on the fly. Unfortunately, not without digressing from your true work to learn how DW works, EDML etc. :rolleyes:

[RobStocks]

Dean,
I do not have any experience with Dreamweaver. I had a quick search on the internet and found an article that says that the cursor type can be changed by 'selecting the recordset in Server Behaviors window and then look in the Property Inspector. The properties of the recordset can easily be changed by selecting different values in the dropdowns'

I'm not sure that this is the information you were looking for

Regards

Rob

[DVHost]

Hi Rob, hey thanks for the effort. The 8.02 release of Dreamweaver updated the server-side code generated by Dreamweaver to protect databases against SQL Injection. Unfortunately, Dreamweaver 8.02 no longer allows the database property called CursorLocation to be set from the Property inspector.

Would have been sweet though!