Insert Query - Type Mismatch

We're running an ASP website. I'm trying to run a query from an ASP page that inserts all the records from one table into another (it will eventually be selective). I've started with a very small table to check to see if it would work. The "To" table is exactly the same as the "From" table:

ID      lastName      firstName      midInit
1      Smith      John      L
2      Jones      Jerry      L

SQL query: INSERT INTO mem_rnw SELECT mem.* FROM mem

I'm getting the following error:
An error has occurred!
Error number: -2147352571
Error description: Type mismatch.

Here is the complete code:
<%
Option Explicit
On Error Resume Next

Dim objConn, objRec, strSQL, i, iFields
Const adOpenForwardOnly = 0
Const adLockReadOnly = 1
Const adCmdText = &H0001
%>
<html>
<head>
<title>RUNSQL</title>
</head>
<body>

<form action="runsql.asp" method="post">
<textarea name="sql" cols=60 wrap rows=10>
<%= Request.Form("sql") %>
</textarea>
<input type="submit">&nbsp;&nbsp;<input type="reset">
</form>

<%

If Request.ServerVariables("REQUEST_METHOD") = "POST" Then
  Response.Write "Query:<br><b>" & Request.Form("sql") & "</b><br>"
  Set objConn = Server.CreateObject("ADODB.Connection")
  objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath ("..\DB\ILCAEVENTCALENDAR.MDB") & ";"
  objConn.Open
  strSQL = Request.Form("sql")
  Set objRec = Server.CreateObject("ADODB.Recordset")
 
  objRec.Open objConn, adOpenForwardOnly, adLockReadOnly, adCmdText, strSQL

  If Err.Number <> 0 Then
   Response.Write "An error has occurred!<br>"
   Response.Write "Error number:      " & Err.number & "<br>"
   Response.Write "Error description: " & Err.description & "<br>"

  ElseIf objRec.EOF Then
   Response.Write "Empty recordset returned."

  End If

  objRec.Close
  Set objRec = Nothing
  objConn.Close
  Set objConn = Nothing

End If

%>
</body>
</html>
I can't see what is wrong. Is the query syntax incorrect?
slegyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slegyAuthor Commented:
Sorry, this question is mistitled. I solved the database connection. So it is the type mismatch that is the primary issue.
0
Dave BaldwinFixer of ProblemsCommented:
I would check to see if you could run that command and make it work on your desktop before you try it on the web.  I suspect the syntax isn't doing what you want.  The example on this page http://msdn.microsoft.com/en-us/library/office/bb208861%28v=office.12%29.aspx is almost the same as yours.  It does say that the column or field names must be identical in both tables.  The definitions also need to be the same to avoid type errors.
0
slegyAuthor Commented:
The "append" query works perfectly in Access on my local machine. The column and field names are identical. I copied the structure from the source table to create to target table.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

slegyAuthor Commented:
Testing is currently being done on localhost.
0
Dave BaldwinFixer of ProblemsCommented:
Then does "Microsoft.Jet.OLEDB.4.0" exist on the server?  I've read that it is not installed on newer Windows server OS's.  On my Godaddy Windows Server 2008 hosting , I had to use a 'filedsn' that they had set up to connect to my Access database.
0
slegyAuthor Commented:
I thought you might have been on to something, but if you follow the code, it appears that the connection is successful. The error is apparently resulting from the execution of the query. I seem to remember reading earlier that the query shouldn't be opened but rather should be executed, but I've tried that and it doesn't work either.
0
Dave BaldwinFixer of ProblemsCommented:
Can you write simpler query that just displays the existing table for a test?  In searching, that error number came up also as a mismatch for the 'Provider' which is why I was asking.
0
slegyAuthor Commented:
Thank you, I will give it a try. Wouldn't have guessed that the Provider could be a problem.
0
slegyAuthor Commented:
The plot thickens. I changed the query to: SELECT mem.* FROM mem; I received a new error:
Error number: 438
Error description: Object doesn't support this property or method

I don't have time this evening to research further but will forge ahead tomorrow. The code I provided is not my own but some I found on the web. As a final solution, I would not use a form to submit the query, so I will try to rework tomorrow and see if I get better results. Thank you for your help.
0
Gustav BrockCIOCommented:
Change the SQL to specify the field names needed:

SQL query: INSERT INTO mem_rnw (field1, field2, .., fieldN) SELECT field1, field2, .., fieldN FROM mem

/gustav
0
slegyAuthor Commented:
Same results.
0
Dave BaldwinFixer of ProblemsCommented:
Here are pages of connection strings for Microsoft Access: http://www.connectionstrings.com/access/

What server OS are you using?  What hosting are you using?
0
Gustav BrockCIOCommented:
If the fields don't match you will have to correct that.

/gustav
0
slegyAuthor Commented:
With the help of w3schools, I've finally figured out how to make it work:
<%@LANGUAGE="VBSCRIPT"%>
<%
On Error Resume Next

Dim conn, rs, sql
Const adOpenForwardOnly = 0
Const adLockReadOnly = 1
Const adCmdText = &H0001
%>

<%  
 set conn=Server.CreateObject("ADODB.Connection")
 conn.Provider="Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath ("..\DB\ILCAEVENTCALENDAR.MDB") & ";"
 conn.Open
 set rs = Server.CreateObject("ADODB.recordset")
 
 sql="INSERT INTO mem_rnw SELECT mem.* FROM mem;"
 Set rs=conn.Execute(sql)

  If Err.Number <> 0 Then
   Response.Write "An error has occurred!<br>"
   Response.Write "Error number:      " & Err.number & "<br>"
   Response.Write "Error description: " & Err.description & "<br>"

  ElseIf objRec.EOF Then
   Response.Write "Query Executed"

  End If
 
  rs.Close
  rs = Nothing
  conn.Close
  conn = Nothing
%>

PS-The tables matched exactly - copied structure of from to the to table.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dave BaldwinFixer of ProblemsCommented:
Which part was causing the error?
0
slegyAuthor Commented:
I believe it was:
 
  objRec.Open objConn, adOpenForwardOnly, adLockReadOnly, adCmdText, strSQL

I recall reading somewhere that the sql had to be "executed", not "opened."

 Set rs=conn.Execute(sql)
is what seems to have fixed it. As I'd verified, the connection was good.
0
slegyAuthor Commented:
None of the expert responses pinpointed or solved the problem.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.