-1

I've got a DB connection to my SQL Server on Azure. Two columns are showing on the page but not showing the other 2. Columns are company name and further details

Company Name is standard business name

Further Details stores URLs to more information

I've tried the SQL statement in SQL Management and it displays data what should be shown.

I've checked the parameters of the column, they're both nvarchar(Max) it shows as -1 when clicking to modify the size of the column. Does this mean it has unlimited size?

The file type is Classic ASP with inline CSS and HTML inside.

Below is what is being called in the database. 

<%
Session.LCID=2057

Dim connpiudb, rspiudata, strSQL, fldThis, strSQL2, strSQL3, strcategory

strcategory = Request.querystring("category")

Set connpiudb = Server.CreateObject("ADODB.Connection")
Set rspiudata = Server.CreateObject("ADODB.Recordset")

strSQL = "SELECT companyname, courtnumber, cronumber, furtherdetails FROM piudata WHERE category LIKE '" & strCategory & "%' Order by companyname"
 strValue = Request.QueryString(<%= rspiudata.Fields("companyname") %>)

response.write strSQL
response.write strValue

<!-- strSQL = "SELECT companyname , courtnumber , cronumber, furtherdetails FROM piudata WHERE category LIKE '%c%' Order by companyname" -->

connpiudb.Open strConnect
rspiudata.open strSQL, connpiudb
%>

Below is how the database is stored in the HTML table. 

<!--content start-->

<FONT FACE="Arial" size="2">

<TABLE BORDER="0" cellpadding="2" width="60%"><TR>

  <%
  Do Until rspiudata.EOF
  %>
  <TR><TD width="40%"><B>Company Name:</B></TD><TD width="60%"><%= rspiudata.Fields("companyname") %></TD></TR>  
  <TR><TD width="40%"><B>Further Details:</B></TD><TD width="60%"><%=rspiudata.Fields("furtherdetails") %></TD></TR>
  <TR><TD width="40%"><B>Court Number:</B></TD><TD width="60%"><%= rspiudata.Fields("courtnumber") %></TD></TR>
  <TR><TD width="40%"><B>Company Registered Number:</B></TD><TD width="60%"><%= rspiudata.Fields("cronumber") %></TD></TR>
  <TR><TD width="100%" colspan="2"><HR></TD></TR>
  <%
  rspiudata.movenext
  Loop

  rspiudata.Close
  Set rspiudata = Nothing
  connpiudb.close
  %>
</TABLE>

I am struggling to understand what the actual issue is. Only thing I've thought of is that the columns sizes default to -1 when setting it as MAX or that Classic ASP does not support NVARCHAR(max)

Any help will be great.

thom4s94
  • 107
  • 1
  • 9
  • It’s nothing to do with what Classic ASP supports it’s about *(based on the SQL Server Provider)* what is supported by ADODB. Check you're using a provider in your connection string that supports varchar(max) and nvarchar(max). – user692942 Mar 26 '20 at 17:55
  • As already pointed out you can't do this `strValue = Request.QueryString(<%= rspiudata.Fields("companyname") %>)` it should be `strValue = Request.QueryString(rspiudata.Fields("companyname").Value)`. – user692942 Mar 27 '20 at 15:13

1 Answers1

0

Before going further on this, following code needs to be corrected 

<%
...
strValue = Request.QueryString(<%= rspiudata.Fields("companyname") %>)

I understood you have it for debug purposes however it has neither valid syntax (you inject <% %> within existing server code block nor valid operator (Request.QueryString is for query strings) nor right place (recordset is defined but not opened).

And read about sql injections.

user2316116
  • 6,726
  • 1
  • 21
  • 35
  • So it would be something along those lines? Company Name:<% strValue = Request.QueryString(<%= rspiudata.Fields("companyname") %>) I tried it but it's giving me a syntax error. I do think it's a compatible issue as when I change the column types to nvarchar(100) let's say it calls out the data. It's just one of those columns is pulling URLs. As the previous comment put was that the SQL Server Provider may not be compatible with nvarchar(MAX) the driver I'm using is SQL Server Native Client 11.0. – thom4s94 Mar 27 '20 at 09:25
  • 1
    Update your question with exact copy of your current code. Given example is invalid because of the line `strValue = Request.QueryString(<%= rspiudata.Fields("companyname") %>)` – user2316116 Mar 27 '20 at 09:37
  • 1
    The entire sample block that starts with `<% Session.LCID=2057` and should stop with an error on the line with `<%= rspiudata.Fields("companyname") %>` i.e. at the moment it has nothing to do with Azure because asp code is incorrect. – user2316116 Mar 27 '20 at 12:40
  • you inject `<% %>` within existing server code block `<% %>` – user2316116 Apr 01 '20 at 10:57