What will cause error Data-Type-Mismatch while using this SQL?

If the following SQL command is used in the ASP scripts:

strSQL= "SELECT * FROM Discuss WHERE ID='" & intID & "'"

We will get this error message:

Actually, this is a correct SQL. But why we still have this problem?

Let us see what is the difference in between the following SQL example:

SELECT * FROM Discuss WHERE ID='1'

And

SELECT * FROM Discuss WHERE ID=1

The first Command, the ID is a string; in the following command, the ID is an integer.

Now you know what is wrong in that example.

In the database, if the ID is integer:

We use this SQL to get the information

SELECT * FROM Discuss WHERE ID='1'

So the "type mismatch" error will come up.

What will happen if I save the data in different datatype?

This will cause an error when we use a different format of datatype.

For example:

The "price" data type is Currency. When you perform "Add" function by trying to add the word "One" to these fields, the system will not allow you to do it:

<%

Set conn = Server.CreateObject("Adodb.Connection")

conn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("db1.mdb")

Set rs = Server.CreateObject("Adodb.Recordset")

rs.Open "Stock",conn,1,3

rs.addNew

rs.fields("ProductName") = "Music"

rs.fields("Price") = "One sen"

rs.update

rs.Close

Set rs = Nothing

%>

The error message as below will be shown:

So, please do not use a different or inappropriate format of "datatype".

What can I do if this error Fetching-Backward occurred?

Error found in this ASP scripts:

<%

Const adOpenForwardOnly = 0

Set conn = Server.CreateObject("Adodb.Connection")

conn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("db1.mdb")

Set rs = Server.CreateObject("Adodb.Recordset")

rs.Open "Member",conn,adOpenForwardOnly

if Not rs.EOF then

rs.movelast ‘The error will occur here

End if

rs.Close

Set rs = Nothing

%>

The problem of this script was that: we set the CursorType to adOpenForwardOnly. We can use the MoveFirst and MoveNext in any kind of CursorType.

However, the MovePrevious and MoveLast cannot be used when the CursorType is adOpenForwardOnly.

What can I do if error Cannot-Find-Input-Table occurred?

Field name typo error happened in the ASP codes below:

<%

Set conn = Server.CreateObject("Adodb.Connection")

conn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("db1.mdb")

Set rs = Server.CreateObject("Adodb.Recordset")

strSQL ="SELECT MemberName FROM Members;" ‘The Error is occur here.

rs.Open strSQL,conn,1,3

..

..

rs.Close

Set rs = Nothing

%>

When this error occured, which means you used an invalid table name, or the table name is not in the database, you will need to correct that specific ASP file to avoid the error message as shown at the diagram above.

What can I do if the error Too-Few-Parameters occurred?

The error of wrong field name normally happened in this type of ASP scripts:

<%

Set conn = Server.CreateObject("Adodb.Connection")

conn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("db1.mdb")

Set rs = Server.CreateObject("Adodb.Recordset")

strSQL ="SELECT MembersName FROM Member;" 'The error occured&ldots;

rs.Open strSQL,conn,1,3

If Not rs.EOF Then

Response.Write("The first record is " & rs.fields(0) & "
")

End If

rs.Close

Set rs = Nothing

%>

This kind of error occurred frequently while you used a wrong field name to call the value in the table.

For example:

When your table contains a field name call "MemberName", but you mistype "MembersName", errors will occured.

Another mistake will be made, such as, you used the field name that was never used in the table.

When you see the error message as shown at the diagram above, you will need to correct the ASP codes to avoid the error message.

How to transfer the table (in a recordset) into the array?

We can transfer the table (in a recordset) into the array.

The ASP codes are written and described as below:

<%

Set conn = Server.CreateObject("Adodb.Connection")

conn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("db1.mdb")

Set rs = Server.CreateObject("Adodb.Recordset")

rs.open "Member",conn,1,3 'Open a Recordset that can perform batch processing

MyArray = rs.getrows

rs.Close

Set rs = Nothing

response.write(MyArray(1,2))

'The first number identify the column(field) in the table and the second identify each record in that column.

%>

How to execute batch updates with ADO?

Normally, when we process a task, we need to update to the database. For example:

<%

rs.AddNew

rs.fields("MemberName") = "C.K."

rs.fields("Location") = "SG"

rs.Update

%>

But, can we perform few tasks, and update only one time?

The ASP codes are written and described as below:

<%

const adLockBatchOptimistic=4 'Use only when doing the batch processing.

Set conn = Server.CreateObject("Adodb.Connection")

conn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("db1.mdb")

Set rs = Server.CreateObject("Adodb.Recordset")

rs.open "Member",conn,1,adLockBatchOptimistic 'Open a Recordset that can perform batch processing

rs.AddNew

rs.Fields("MemberName")="Alvin"

rs.Fields("Location")="UK"

rs.AddNew 'Add another record again

rs.Fields("MemberName")="Eric"

rs.Fields("Location")="UK"

rs.UpdateBatch 'Save all new data.

rs.Close

Set rs = Nothing

%>

If you want to cancel the process, you can use the CancelBatch to cancel it.