Search code examples
sqldatabasewebexecute

ASP Q:Executing SQL query failed after MsSQL DB connection

I think this is a silly question. Also know that this is a repeated question. But I cannot solve this problem. Someone please help me.

<!DOCTYPE html>
<html>
<head>
    <title>DataBase Connection Example</title>
</head>
<body>
<%
Dim objConn         'Our Connection Object'
Dim objRecd         'Our Recordest Object'
Dim strSQL          'Our SQL string to access the database'
Dim strConnection   'Out connection string to access the database'
Dim i               'A Counter Variable'

'---Create A Object---'
Response.Write("DB Connect Example<br>")
Set objConn = Server.CreateObject("ADODB.Connection")
Set objRecd = Server.CreateObject("ADODB.RecordSet")
strConnection = "PROVIDER=SQLOLEDB;SERVER=127.0.0.1,1433;UID=sa;PWD=hello1995812;DATABASE=;"
Response.Write("Object Set<br>")

Response.Write("Connecting to DB<br>")
objConn.Open strConnection
Response.Write("Connected<br>")
strSQL = "SELECT * FROM master"
Response.Write("Executing...<br>")
Set objRecd = objConn.Execute(strSQL)
Response.Write("Executed<br>")

If (objRecd.BOF And objRecd.EOF) Then
    Response.Write("No Recoreds Found")
    Response.End
End If
Response.Write("<table border=1 cellpadding=2 cellspacing=1 width=100%")
Response.Write("<tr bgcolor=#CCCCCC>")
For i = 0 To objRecd.Fields.Count-1
    Response.Write("<th>" & objRecd.Fields(i).Name & "</th>")
Next
Response.Write("</tr>")
%>
</body>
</html>

But dis-understandable error occured.

enter image description here

System Environment :

Server 2008 Ver 6.0 x86

Mozila Firefox Web Browser 52.0

Sublime Text 3

Thank you in advance.

Solution

  • Your code is not wrong.

    But you have to see if the environment is set up properly before looking for the error.

    I built this environment and tried it your way, and there was this error. Is this not the solution?

    Set en-coding on your page or your browser.

    The character encoding of the HTML document was not declared

    Press F12 to check it!

    Make SqlQuery as directly connect to DB.

    ex:) 

    PWD=*****;DATABASE=TestDataBase;"
SELECT * FROM TestTable

    After execute SQL Query.

    If(objRecd.BOF And objRecd.EOF) Then
  Response.Write("No Records Found")
End If

For Each x In objRecd.Fields
  Response.Write(x.Name & "=" & x.value)
Next