SQL Server instance connection through ASP

1. Check whether your IIS is correct.

2. Check whether SQL Server is correct.

 

In the IIS directory wwwroot, create a file named testsqlserver. asp and write the following code:

<% <Br/> 'ole dB connection <br/> set cnn1 = server. createobject ("ADODB. connection ") <br/> 'check whether the Instance name of sqlserver is the default value for the connection string. If it is not the default value, it must be written. <br/> cnn1.open" provider = sqloledb; Data Source =. /sqlexpress; initial catalog = Master; user id = sa; Password = 000000; "<br/> SQL =" select * from master .. spt_values "<br/> set rs = server. createobject ("ADODB. recordset ") <br/> Rs. open SQL, cnn1, <br/> 'obtain the connection information and the number of rows in the query result <br/> response. write ("connection 1:" & cnn1.connectionstring & "<br/> Number of spt_values rows:" & RS. recordcount & "<br/>") <br/> 'loop result output <br/> do while not Rs. EOF <br/> response. write (RS (0) & "," & RS (1) & "," & RS (2) & "," & RS (3 )&", "& RS (4) &" <br/> ") <br/> Rs. movenext <br/> loop <br/> Rs. close () <br/> cnn1.close () <br/> %> </P> <p> <% <br/> 'odbc connection <br/> set cnn2 = server. createobject ("ADODB. connection ") <br/> cnn2.open" driver = {SQL Server}; server =. /sqlexpress; database = Master; uid = sa; Pwd = 000000; "<br/> SQL =" select * from master .. spt_values "<br/> set rs = server. createobject ("ADODB. recordset ") <br/> Rs. open SQL, cnn2, 1, 1 <br/> response. write ("connection 2:" & cnn2.connectionstring & "<br/> Number of spt_values rows:" & RS. recordcount & "<br/>") <br/> do while not Rs. EOF <br/> response. write (RS (0) & "," & RS (1) & "," & RS (2) & "," & RS (3 )&", "& RS (4) &" <br/> ") <br/> Rs. movenext <br/> loop <br/> Rs. close () <br/> cnn2.close () <br/> %> <br/>

 

Enter http: // localhost/testsqlserver. asp at the IE address to view the following results:

Connection 1: provider = sqloledb.1; Password = 000000; user id = sa; initial catalog = Master; Data Source =. /sqlexpress; use procedure for prepare = 1; Auto translate = true; packet size = 4096; workstation id = PC-201003062254; use encryption for Data = false; tag with column collation when possible = false <br/> Number of spt_values rows: 2346 </P> <p> RPC, 1, A, <br/> pub, 2, a, <br/> sub, 4, A, <br/> Dist, 8, A, <br/> dpub, 16, ,, <br/> RPC out, 64, A, <br /> Data access, 128, A, <br/> collation compatible, 256, A, <br/> system, 512, ,, <br/> use remote collation, 1024, A, <br/> lazy schema validation, 2048, ,, <br/> ............... </P> <p> serial writes, 32, V, <br/> Read Only, 4096, V, 0, 1 <br/> deferred, 8192, V, 0, 1 <br/> connection 2: provider = msdasql.1; extended properties = "driver = SQL Server; server =. /sqlexpress; uid = sa; Pwd = 000000; APP = Microsoft? Windows? Operating system; wsid = PC-201003062254; database = Master "<br/> spt_values number of rows: 2346 <br/> RPC, 1, A, <br/> pub, 2, a, <br/> sub, 4, A, <br/> Dist, 8, A, <br/> dpub, 16, ,, <br/> RPC out, 64, A, <br/> data access, 128, A, <br/> collation compatible, 256, ,, <br/> system, 512, A, <br/> use remote collation, 1024, A, <br/> lazy schema validation, 2048, ,, <br/> .....

 

It's very easy. You should try it too. encoding is actually very simple.

 

 

Supplement: Call the Stored Procedure

<% <Br/> 'ole dB connection <br/> set conn = server. createobject ("ADODB. connection ") <br/> 'check whether the Instance name of sqlserver is the default value for the connection string. If it is not the default value, you must write it out. <br/> Conn. open "provider = sqloledb; Data Source = 192.168.1.101; initial catalog = Master; user id = sa; Password = 000000; "<br/> %> </P> <p> <% <br/> const adinteger = 3 <br/> const advarchar = 200 <br/> const adparaminput = 1 <br/> const adparamoutput = 2 <br/> const ad1_spstoredproc = 4 <br/> set adocomm = Createobject ("ADODB. command ") <br/> with adocomm <br/>. activeconnection = conn <br/>. commandtype = admo-spstoredproc <br/>. prepared = true <br/>. commandtext = "sp_checklogin" <br/>. parameters. append. createparameter ("@ userid", advarchar, "no001") 'parameter name, parameter type, input/output type, length, value <br/>. parameters. append. createparameter ("@ flag", adinteger, 2) 'Return parameter, total number of records returned, data length can be omitted. <br/>. execute <br/> end with <br/> flag = Docomm (1) <br/> set adocomm = nothing <br/> response. write "after the program is executed, the stored procedure returns the flag:" & flag & "<br>" </P> <p> if flag = 0 then <br/> response. write "prompt: logon successful! (This account is logged on for the first time today) "<br/> else <br/> response. write "prompt: this account has been logged on, you cannot log on to "<br/> end if </P> <p> %> </P> <p> <% <br/> SQL =" select * from master.. spt_values "<br/> set rs = server. createobject ("ADODB. recordset ") <br/> Rs. open SQL, Conn, <br/> 'obtain the connection information and the number of rows in the query result. <br/> response. write ("connection 1:" & Conn. connectionstring & "<br/> Number of spt_values rows:" & RS. recordcount & "<br/>") <br/> 'loop result output <br/> do while not Rs. EOF <br/> response. write (RS (0) & "," & RS (1) & "," & RS (2) & "," & RS (3 )&", "& RS (4) &" <br/> ") <br/> Rs. movenext <br/> loop <br/> Rs. close () <br/> 'conn. close () <br/>%> <br/>

 

 

The SQL stored procedure is as follows:
Create procedure sp_checklogin
@ Userid varchar (6), -- operation account
@ Flag int out -- output parameter 0: No Logon; 1: logged on
As
Declare @ SQL nvarchar (100)
If object_id ('tempdb. DBO. # '+ @ userid) is null
Begin
Set @ SQL = 'create table # '+ @ userid +' (userid varchar (6 ))'
Exec (@ SQL );
Set @ flag = 0
End
Else
Set @ flag = 1
Go