Trying to add records to a MDB

[Bluie]

Hello



I am getting the following server error on a Web page that displays MS Access records - and should add new records:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified /display_records2.asp, line 43

Line 43 is this:

connection.Open sConnString

, though this is not necessarily the error. I think the error lies somewhere here:

[<% 
 Dim Connection, Recordset, sSQL, sConnString, firstName (other field names)

fName = Replace( Request.Form("firstName"), "'", "''" )
(other field names)

sSQL="INSERT INTO myTable (firstName, other field names) VALUES ('" & fName & "', other field names)"

sSQL="SELECT TOP 30 ID, firstName, (other field names) FROM myTable"

 Set connection = Server.CreateObject("ADODB.Connection")
 Set recordset = Server.CreateObject("ADODB.Recordset")

 connection.Open sConnString

 recordset.Open sSQL,connection
 
 sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _ 
"Data Source=" & Server.MapPath("myDatabase.mdb") 
 
connection.Execute sSQL

What am I doing wrong please - how SHOULD the code look?

Many thanks for any suggestions.

Blue

[pbaldy]

I don't see where you've given sConnString a value.

[Bluie]

Hello Paul

Thank you for your reply.

The only thing I have for sConnString is

sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("myDatabase.mdb")

What 'value' do you mean - please excuse my naivety!

Blueie

[pbaldy]

Sorry, I didn't see that, because I didn't look down that far. You're using the string before that. You have to set it before you use it. At this point:

connection.Open sConnString

the string is empty.

[ssanfu]

Dim Connection, Recordset, sSQL, sConnString, firstName (other field names)

"Recordset" and "Connection" are reserved words in Access and shouldn't be used for object names.
All of these are declared as varients. Did you mean to do that?


Here is a list of reserved words:
http://allenbrowne.com/AppIssueBadWord.html

[pbaldy]

By the way, with these 2 lines:


sSQL="INSERT INTO myTable (firstName, other field names) VALUES ('" & fName & "', other field names)"
sSQL="SELECT TOP 30 ID, firstName, (other field names) FROM myTable"

The second overwrites the first, so the later execute line will be trying to execute the SELECT statement.

[Bluie]

Thanks for your replies again.

OK, when I comment out this: 'sSQL="SELECT TOP 30 ID, firstName,' I still get that same error:

Data source name not found and no default driver specified

Originally, the 'test' page to display the records looked like this: http://www.proofreading4students.com...y_records1.asp

The (stripped) code for that page looks like this:
<%
Dim Connection, Recordset
Dim sSQL, sConnString

sSQL="SELECT TOP 10 ID, firstName, etc FROM myTable"

sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("myDatabase")

Set connection = Server.CreateObject("ADODB.Connection")
Set recordset = Server.CreateObject("ADODB.Recordset")

connection.Open sConnString

recordset.Open sSQL,connection

'HTML table to house results

Close connection, etc

So, the server error here: http://www.proofreading4students.com...y_records2.asp

has only occurred since I attempted to add new records with INSERT and EXECUTE.

That's what I am stumped on!

[Bluie]

I have replied - message tells me to wait for a moderator to approve the post.

[Bluie]

I will repost because it doesn't look as if the moderator is coming in.

My original display Web page looks like this:

http://www.proofreading4students.com...y_records1.asp

with this code:
<%
Dim Connection, Recordset
Dim sSQL, sConnString

sSQL="SELECT TOP 10 ID, firstName, etc FROM myTable"

sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("myDatabase.mdb")

Set connection = Server.CreateObject("ADODB.Connection")
Set recordset = Server.CreateObject("ADODB.Recordset")

connection.Open sConnString

recordset.Open sSQL,connection

'HTML table to house results

Close connection
%>

The error message I get here:

http://www.proofreading4students.com...y_records2.asp

has only occurred since I tried to add data using 'sSQL="INSERT INTO myTable (firstName, etc)

followed by this in the same code:

Set connection = Server.CreateObject("ADODB.Connection")
Set recordset = Server.CreateObject("ADODB.Recordset")

sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("myDatabase.mdb")

connection.Open sConnString

recordset.Open sSQL,connection

connection.Execute sSQL

Close, etc.

Thank you for your help, but I still get the same error if I comment out:

sSQL="SELECT TOP 30 ID, firstName, etc.

Thank you again.

Blue