add records using sql server2000 problem

afradoit · April 14th, 2004, 05:54 AM

hi allz,

really i have a big problem ...
i want to insert data using asp and mssql server 2000 and i write this
code:

<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1256">
<title>New Page 1</title>
</head>
<body>
<%
o = request.querystring("message")
m = request.querystring("mobile_no")

dbconn="Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID=sa;Initial Catalog=tkts;Data Source=localhost"
set rs = server.createobject("adodb.recordset")
rs.open dbconn
'response.end
%>

<%
sql="INSERT INTO numbers (message,mobile_no)"
sql=sql & " VALUES ( "
sql=sql & "'" & Request.querystring("m") & "',"
sql=sql & "'" & Request.querystring("o") & "')"

RESPONSE.WRITE SQL
'RESPONSE.END
conn.Execute sql

%>

</body>
</html>

when i run the script it gives me

ADODB.Recordset error '800a0e7d'

The connection cannot be used to perform this operation. It is either closed or invalid in this context.

/sms/insert.asp, line 14

so i wanna yr help to solve this problem, and please notice that i made the same example using microsoft access database and it success.

happygv · April 16th, 2004, 12:39 PM

The error is because of INVALID CONNECTION.

You are trying to insert record using RECORDSET object with no connection set with your database as used in your connection string.

I dont see any connection object being declared and initialized in your code. What is datatype of "dbconn" there??? And where have you declared/initialized "conn" which you use in "conn.execute sql"?

Also PASSWORD part is missing in your connection string.

You need to first connect using a connection object, not using recordset object

It should be something like
Dim objCon As ADODB.Connection

Set objCon = New ADODB.Connection

'Creating the DB connection string
'Please change the below connection string as per your server and
'database being used.
objCon.ConnectionString = "PROVIDER=SQLOLEDB.1;PASSWORD=;PERSIST SECURITY INFO=TRUE;USER ID=sa;INITIAL CATALOG=DBNAME;DATA SOURCE=SERVERNAME/IP"

'Opening the connection
objCon.Open objCon.ConnectionString

sql="INSERT INTO numbers (message,mobile_no)"
sql=sql & " VALUES ( "
sql=sql & "'" & Request.querystring("m") & "',"
sql=sql & "'" & Request.querystring("o") & "')"

RESPONSE.WRITE SQL
objCon.Execute sql

This should work.

Cheers!

-Vijay G

afradoit · April 17th, 2004, 03:01 AM

hi happygv,

thanks alot for yr helping but when i copy and paste yr example :

<html dir="ltr">

<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1256">
<title>New Page 1</title>
</head>
<body>
<%
o = request.querystring("message")
m = request.querystring("mobile_no")
%>

<%
Dim objCon As ADODB.Connection
Set objCon = New ADODB.Connection
'Creating the DB connection string
'Please change the below connection string as per your server and
'database being used.
objCon.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID=sa;Initial Catalog=tkts;Data Source=localhost""

'Opening the connection
objCon.Open objCon.ConnectionString

sql="INSERT INTO numbers (message,mobile_no)"
sql=sql & " VALUES ( "
sql=sql & "'" & Request.querystring("m") & "',"
sql=sql & "'" & Request.querystring("o") & "')"

RESPONSE.WRITE SQL
objCon.Execute sql

%>
</body>
</html>

and when i run the page it give me the following error :

Microsoft VBScript compilation error '800a0401'

Expected end of statement

/sms/insert.asp, line 14

Dim objCon As ADODB.Connection
-----------^

so really i dont know how i can solve the problem !!?!?!?

please i need yr help

afradoit · April 17th, 2004, 04:45 AM

hi allz,

thanks alot happygv for yr help.
i made it and it's success, here is the code.

<html dir="ltr">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1256">
<title>New Page 1</title>
</head>
<body>
<%
o = request.querystring("message")
m = request.querystring("mobile_no")
%>
<%
Dim myConn, rsTest, prodName
Set myConn = server.createobject("adodb.connection")
myConn.open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID=sa;Initial Catalog=tkts;Data Source=localhost"
sql="INSERT INTO numbers (message,mobile_no)"
sql=sql & " VALUES ( "
sql=sql & "'" & Request.querystring("m") & "',"
sql=sql & "'" & Request.querystring("o") & "')"
RESPONSE.WRITE SQL
myconn.Execute sql
%>
</body>
</html>

happygv · April 17th, 2004, 12:08 PM

Quote:

quote:Microsoft VBScript compilation error '800a0401'

Expected end of statement

/sms/insert.asp, line 14

Dim objCon As ADODB.Connection
-----------^

Sorry about that error. That is usage in VB. not in ASP/VBScript.

It should be like

Dim objCon
Set objCon = server.createobject("adodb.connection")

By mistake I have copied that from VB examples.

-Vijay G

sal · April 17th, 2004, 10:09 PM

I was reading your connection object and I noticed that you are giving it a parameter of Integrated Security = SSPI, which is a windows authenticated security login, but you are also passing a username of sa and a blank password.

You can not really use both combined. Chose either Windows Authentication or SQL Server Username and Password.

Also, do not keep a blank sa password.

Sal