Member Avatar for kapilver

Hi friend, i am trying to learn ASP but i face problem to connect ASP wtih
SQL Server.
Anyone help me.

Sushil ..

  • 10 Contributors
  • 9 Replies
  • 420 Views
  • 6 Years Discussion Span
  • Latest Post Latest Post by johnjaafarj

Dani AI

Generated

Short primer: classic ASP uses ADO (ADODB) to talk to SQL Server — the examples from and show the right pattern (Connection -> Recordset), but there are a few modern gotchas to be aware of: prefer the current Microsoft OLE DB driver for SQL Server (provider names vary by driver version), test connectivity with a UDL file before debugging ASP, and avoid shipping credentials or weak SQL logins. (learn.microsoft.com)

Quick checklist to diagnose "page cannot be displayed" or connection errors: (1) confirm the SQL Server service is running and the instance allows remote/TCP connections; (2) enable the TCP/IP protocol and set the correct port (default 1433) or configure a static port for named instances; (3) if you use SQL authentication enable Mixed Mode (SQL + Windows) and create a restricted user for the app; (4) open the port in the server firewall or use a client alias; (5) test with a .udl file on the webserver (and test 32-bit vs 64-bit providers if your IIS app pool is 32-bit). These steps are the usual causes of failures. (learn.microsoft.com)

Use parameterized commands instead of string-building or Recordset.AddNew to avoid SQL injection and improve reliability. Example (classic ASP, ADODB.Command with positional parameters):

<%
Dim cn, cmd
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open "Provider=MSOLEDBSQL;Server=MY-SERVER;Database=MyDB;Trusted_Connection=Yes;"

Set cmd = Server.CreateObject("ADODB.Command")
Set cmd.ActiveConnection = cn
cmd.CommandType = 1
cmd.CommandText = "INSERT INTO dbo.Users (UserName, Email) VALUES (?, ?)"

cmd.Parameters.Append cmd.CreateParameter(, 200, 1, 100, Request("username"))
cmd.Parameters.Append cmd.CreateParameter(, 200, 1, 200, Request("email"))

cmd.Execute
cn.Close
%>

Use prepared/parameterized queries everywhere — it’s the primary defense against SQL injection. Also remember provider bitness: if a provider is 32-bit, enable 32-bit apps in the IIS app pool or install the matching driver. (cheatsheetseries.owasp.org)

If problems persist (HTTP 500, login errors), capture Err.Number/Err.Description in ASP, check the SQL Server error log for 18456 states, and for older servers (example: SQL Server 2008) verify you have a compatible client/provider installed or install Microsoft’s current OLE DB driver. Avoid enabling the builtin 'sa' account unless required; use least-privilege logins. (learn.microsoft.com)

Recommended Answers

All 9 Replies

Member Avatar for sandeep.kharat

Hi Sushil,

use this code to add data in a table using asp and sql server

<%
dim cn, rs
set cn=server.CreateObject("adodb.connection")
cn.Open "Provider=sqloledb;SERVER=localhost;DATABASE=your_database_neme;UID=your_database_login_id;PWD=your_database_login_password;"
set rs=server.CreateObject("adodb.recordset")
rs.open "Select * from table_name",cn,adOpenKeyset,adLockOptimistic
rs.addnew
rs(0) = "here_is_your_value_for_first_field"
rs(1) = "here_is_your_value_for_second_field"
rs.update
rs.close
%>
Edited by Ezzaral because: Added code tags. Please use them to format any code that you post.
Member Avatar for ko ko

Connect to SQL server with ASP needs ADODB connection.
This connection can extract data from database and insert data into database.
What problem do you face?

Member Avatar for Egypt web

Hi Sushil,

connect ASP wtih SQL Server.

<%
dim con, Rs
set con=server.CreateObject("adodb.connection")
con.Open "Provider=sqloledb;SERVER=localhost;DATABASE=database_name;UID=login_ID ;PWD=login_password;"
set Rs=server.CreateObject("adodb.recordset")

%>
Member Avatar for kiran gaba

i donot know about how to set drivers. plz tell me the name of drivers of sql

commented: fdsggsdg +0
Member Avatar for mohamed_r

well i tried the above code to connect the SQL table, but it is always giving me an error and the webpage can not be displayed... any help please.. (btw i'm using SQL server 2008)

Member Avatar for maips

please guys help me i want code to connect my asp code to the MSSQL SERVER
Please help me

Member Avatar for donnybrasco

Have you tried doing it through the designer hence creating the connection for you automatically

Member Avatar for Ezzaral

It's doubtful. They evidently cannot even follow the most basic of instructions, search on their own for information that is available all over the net, or post detailed information about the problems they are experiencing.

Member Avatar for johnjaafarj

hello

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.