ERROR when trying to add a new record to SQL

[Prog76]

I set up the connection and opened the recordset, but when I ran it this is what I got:
Error Type:
ADODB.Recordset (0x800A0E7D)
The connection cannot be used to perform this operation. It is either closed or invalid in this context.
/resultspage.asp, line 12


Here is my code; what am I doing wrong??

<%

DIM objConn

Set objConn = Server.CreateObject("ADODB.Connection")

objConn.ConnectionString = "Provider=SQLOLEDB Data source = 192.168.114.144 Database =ContactInfo; user id = sa; password = eleven"

%>

<%

Dim objRS

Set objRS = Server.CreateObject("ADODB.Recordset")

objRS.Open "CustInfo", objConn, , adLockOptimistic, adCmdTable



objRS.AddNew

objRS("Email") = Request.Form("Email")

objRS("Name") = Request.Form("Name")

objRS("Comments") = Request.Form("Comments")

objRS.Update



objRS.Close

Set objRS = Nothing

objConn.Close

Set objConn = Nothing

%>

<p>

<%

Dim strName

strName = Request.Form("Name")

Response.Write strName

%>,</p>




Thanks, Prog76

 

[Veep]

Try this string:

objConn.ConnectionString = "Provider=sqloledb;" & _ 
           "Data Source=192.168.114.144;" & _
           "Initial Catalog=ContactInfo;" & _
           "User Id=sa;" & _
           "Password=eleven"

Then

objConn.Open 'I don't see you opening the connection

Then

Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open "CustInfo", objConn, , adLockOptimistic, adCmdTable

 

[Prog76]

thank you so much; I can't believe that was my silly mistake. But now that I have the connection open, I am getting this error:

Error Type:
ADODB.Connection (0x800A0E7A)
Provider cannot be found. It may not be properly installed.
/connection.asp, line 12

In my code Provider = SQLOLEDB

 

[Tarwn]

Well, do you have SQLOLEDB on your server?

Personally I prefer using the ODBC driver...

[sub]01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111[/sub]
The never-completed website:

http://www.tiernok.com/

 

[Prog76]

Now I am getting

Microsoft OLE DB Provider for SQL Server (0x80004005)
Invalid connection string attribute

???????????

 

[Tarwn]

Double check and make sure all the semi-colons that are missing in your earlier post have been added per Veeps post

[sub]01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111[/sub]
The never-completed website:

http://www.tiernok.com/

 

[Prog76]

This is how the code looks now:

DIM objConn
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString = "Provider=SQLOLEDB.1; DataSource = 192.168.114.144; Initial Catalog=ContactInfo; user id = sa; password = eleven"
objConn.Open

I am still getting the error. I appreciate your help.

 

[DreXor]

just for testing connectivity, try making a system DSN then

objconn.open "DSN=testdsn;"

and rem out your conn.open and .connectionstring

just to ensure that the machines can at least see and work with each other.

also there's a faq (faq333-3802) i made a while ago under dataconnectivity, there's a good link/resource to try and debug out connection strings.