?
Solved

Microsoft VBScript runtime error '800a000d'  Type mismatch: '[string: "-1, 0, 1, 2, 3, 4, 5"]' - Update Multiple records from one form using Dreamweaver

Posted on 2006-03-30
12
Medium Priority
?
753 Views
Last Modified: 2007-12-19
Hi all

Sorry about this but I am having trouble in getting the above to work.  I have checked out quite a few websites for answers but still not clicking in my head.

Background.

Creating intranet for my company.
Software using is DreamWeaver
Code is VBScript
Platform Windows Server 2003 SE
DataConnection is ODBC "Microsoft Access Driver (.mdb) +
From dream weaver side Data Source Name.

I have a table (Well loads but this one will do) called "HourlyRate".  Within this table i have fields called "MemberID" (AutoNumber) + "FirstName" (Text) + "LastName" (Text) + "Team" (Text) + "HourlyRates" (Text).

I have created a Web page with all the records showing through a recordset Called "Recordset1" with Repeat Region to show all records.  Also within the Repeat Region i have inserted an updatable text field for the new values to be inputted.

Below is the code used to complete this

HourlyRatesOnly.asp

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="../Connections/MyConnection.asp" -->
<%
Dim HourlyRates
Dim HourlyRates_numRows

Set HourlyRates = Server.CreateObject("ADODB.Recordset")
HourlyRates.ActiveConnection = MM_darbys1_STRING
HourlyRates.Source = "SELECT * FROM qryMyQry ORDER BY HourlyRate DESC"
HourlyRates.CursorType = 0
HourlyRates.CursorLocation = 2
HourlyRates.LockType = 1
HourlyRates.Open()

HourlyRates_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index

Repeat1__numRows = -1
Repeat1__index = 0
HourlyRates_numRows = HourlyRates_numRows + Repeat1__numRows
%>
<HTML><HEAD id=1>
<BODY bgColor=#ffffff leftMargin=0 topMargin=0
rightMargin=0 bottomMargin=0 scroll=auto>
<table width="650" border="0" cellspacing="0" cellpadding="0">
                  <tr>
                    <td width="137">First Name </td>
                    <td width="184">Last Name </td>
                    <td width="164">Team</td>
                    <td width="165">Hourly Rate </td>
                  </tr>
                  <tr>
                    <td>&nbsp;</td>
                    <td>&nbsp;</td>
                    <td>&nbsp;</td>
                    <td>&nbsp;</td>
                  </tr>
                  <tr>
                    <td colspan="4"><form name="form1" method="post"><br>
                     <% Dim iCount
                               iCount = 0
                               %>
                              
                                <%
While ((Repeat1__numRows <> 0) AND (NOT HourlyRates.EOF))
%>
                        <table width="650" border="0" cellspacing="0" cellpadding="0">
                            <tr>
                              <td width="137">&nbsp;</td>
                              <td width="184">&nbsp;</td>
                              <td width="164">&nbsp;</td>
                              <td width="165">&nbsp;</td>
                            </tr>
                            <tr>
                              <td><%=(HourlyRates.Fields.Item("FirstName").Value)%></td>
                              <td><%=(HourlyRates.Fields.Item("LastName").Value)%></td>
                              <td><%=(HourlyRates.Fields.Item("Team").Value)%></td>
                              <td>
                                            <SCRIPT LANGUAGE="JavaScript">
function DeleteLinks()
{
if (confirm("This will delete the links! Are you sure you want to do this!?"))
{
document.forms[0].action = "DeleteHourlyRates.asp";
document.forms[0].submit();
}
}

function UpdateLinks()
{
if (confirm("This will update the links! Are you sure you want to do this!?"))
{
document.forms[0].action = "UpdateHourlyRates.asp";
document.forms[0].submit();
}
}
</SCRIPT>

                                            <input name="Delete" type="checkbox" id="Delete" value="<%=(HourlyRates.Fields.Item("RecordID").Value)%>">
                              <input name="<% =(iCount & ".link") %>" type="text" id="HourlyRate" value="<%=(HourlyRates.Fields.Item("HourlyRate").Value)%>">
                              <input name="<% =(iCount & ".ID") %>" type="hidden" id="MemberID" value="<%=(HourlyRates.Fields.Item("MemberID").Value)%>">
                              <input name="Count" type="hidden" id="Count" value="<% =iCount - 1 %>"></td>
                            </tr>
                                              </table>
                        <br>
                        <%
  Repeat1__index=Repeat1__index+1
  Repeat1__numRows=Repeat1__numRows-1
  HourlyRates.MoveNext()
  iCount = iCount + 1
Wend
%>
                        <p>&nbsp;</p>
                        <p>
                          <label>
                          <input type="submit" name="Submit" value="Delete Selected Records" onClick="DeleteLinks()">
                          </label>
                          <label>
                          <input type="submit" name="Submit2" value="Update Hourly Rates" onClick="UpdateLinks()">
                          </label>
                          <BR>
                                                </p>
                    </form>
                    </td>
                  </tr>
                </table>
</BODY></HTML>
<%
HourlyRates.Close()
Set HourlyRates = Nothing
%>

UpdateHourlyRates.asp

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="../Connections/darbys1.asp" -->
<HTML><HEAD id=1>
</HEAD>
<BODY bgColor=#ffffff leftMargin=0 topMargin=0
rightMargin=0 bottomMargin=0 scroll=auto>

<%
                        Response.Buffer = True
                        'First we need to get the total number of items that could be updates
                        Dim iCount
                        iCount = Request.Form("Count")
                        
                        'We need to obtain each Link nad ID
                        Dim strLink
                        Dim strID
                        
                        'Create Database Connection
                        Dim Command1
                        set Command1 = Server.CreateObject("ADODB.Connection")
                        Command1.ConnectionString = MM_darbys1_STRING
                        Command1.Open

                        
                        'Loop through the iCount
                        Dim iLoop
                        
                        'Since we pass the number of text boxes to UpdateCosts.asp
                        'we can perform a loop through this number, retrieving each
                        'NAME and associated ID
                        For iLoop = 0 to iCount - 1
                        strLink = Request(iLoop & ".Link")
                        strID = Request(iLoop & ".ID")
                        ' update the appropiate column
                        strSQL = "UPDATE MyDB SET HourlyRates = ' " & strLink & " ' " &_
                        " WHERE MemberID = " & strID
                        Command1.Execute strSQL
                        Command1.Close
                        Set Command1 = Nothing
                        Next
                        
                        Response.Write("Your links were updated")
                        
                        %>
</BODY></HTML>



From help from DrDev and dxmzone and others a second page that the Update records sql should Update and loop through but it does not want to work.  But saying that it is using a different connection command line that i guess i can not get to work.

Please could any help me with this as this seems to be the only mountain (well to me a mountain) left to climb before i finish it.

I hope i have given you enough information.  if you need anything else then please do let me know

Thanking you in advance.
Baker
0
Comment
Question by:Bakersville
  • 7
  • 5
12 Comments
 
LVL 12

Accepted Solution

by:
TheMegaLoser earned 2000 total points
ID: 16332644
First of all, you shall not close the database connection until all of your sql questions are done.

--- change ---
Command1.Execute strSQL
Command1.Close
Set Command1 = Nothing
Next
--- to ---
Next
Command1.Execute strSQL
Command1.Close
Set Command1 = Nothing


Do you know which line causes the error? I assume it's the update line?

strSQL = "UPDATE MyDB SET HourlyRates = ' " & strLink & " ' " &_
                    " WHERE MemberID = " & strID

Command1.Execute strSQL

If it is, have you checked the sql command that is created? Ie, comment the database job (Command1.Execute strSQL)  and write out strSQL

--- change ---
Command1.Execute strSQL
--- to ---
Next
Response.Write(strSQL)

That way you can see if the sql queries are correct. Start with this.
0
 
LVL 12

Expert Comment

by:TheMegaLoser
ID: 16332651
Oops, i write to fast. The first change in the code should be like:

--- change ---
Command1.Execute strSQL
Command1.Close
Set Command1 = Nothing
Next
--- to ---
Command1.Execute strSQL
Next
Command1.Close
Set Command1 = Nothing
0
 
LVL 1

Author Comment

by:Bakersville
ID: 16332726
Thank you for this.  It would help if i indicate the line.

It's

For iLoop = 0 to iCount - 1

The count is not working i think.  But can't get it to.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 12

Expert Comment

by:TheMegaLoser
ID: 16333181
Aaah, you're creating a lot of hidden fields called count. When you read the count from request you get them all back separated by a comma. Move the    

<input name="Count" type="hidden" id="Count" value="<% =iCount - 1 %>"></td>

to just outside the loop, ie after the

Wend
%>

and it should work.
0
 
LVL 12

Expert Comment

by:TheMegaLoser
ID: 16333218
You should probably remove the -1 from count too since you also do a -1 in the for loop on the update page. And dont move the </td>

Ie the moved line should be:

<input name="Count" type="hidden" id="Count" value="<% =iCount %>">
0
 
LVL 1

Author Comment

by:Bakersville
ID: 16333435
Thank you.  I've done this now there seems to be anther little problem

Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

The  
----  
Command1.Execute strSQL

So i commented it out
----- to ----
response.write(strSQL)

and i get
-----

UPDATE LOGIN SET HourlyRates = ' 210 ' WHERE MemberID = 13UPDATE LOGIN SET HourlyRates = ' 210 ' WHERE MemberID = 70UPDATE LOGIN SET HourlyRates = ' 210 ' WHERE MemberID = 2

I see there is no gaps in MemberID = 13 AND UPDATE I have tried to put in a comma with "& ","" but i get

---
[Microsoft][ODBC Microsoft Access Driver] Syntax error (comma) in query expression 'MemberID = 13,'.
---

Any other suggestions.

Sorry for the thickness but only learnt this myself.
0
 
LVL 12

Expert Comment

by:TheMegaLoser
ID: 16333549
The reason your getting them stitched together is that there's no line break in the respone write but that's not a problem.

Since you're looping your sending every UPDATE clause to the server by itself.

Ie, there should be no comma.

As for the actual error I can't see whats wrong this time. I'm a bit hurry right now but I'll look at in a couple of hours. If you have any more information, please post it. You dont get any other errors? The output you're showing is the same as the one that generate the SQL error?
0
 
LVL 1

Author Comment

by:Bakersville
ID: 16333789
I've just went through the entire code once more and i found my mistake.  (First time for everything)

I had HourlyRates not HourlyRate.  Just goes to show how one little thing will throw it out.

Anyway.  thank you very much for all your help in this.

I will update the point to you once i have written this.

By the way.  You would not happen to know anything about Adobe form designer. If so could you have a look at my other question.  No-one has come back to me about this.

http://www.experts-exchange.com/Web/Graphics/Adobe_Acrobat/Q_21774845.html

thank again
0
 
LVL 12

Expert Comment

by:TheMegaLoser
ID: 16335281
Glad you solved it. Unfortunately I have no knowledge of Adobe forms.
0
 
LVL 1

Author Comment

by:Bakersville
ID: 16340819
Thanks anyway.  You could not tell me one more litle thing about this code.

With the value i want to update on the update page (The Hourly Rate) strLink.  Why it puts a space in front of the value and at the end of the value upon update.

I've checked it out to see what happens and this is what i have don.
--------
For iLoop = 0 to iCount - 1
                        Response.Write(" This is the start of the iLoop " & iLoop & " This is the iloop<BR>")
                        strLink = Request(iLoop & ".Link")
                        response.write("This is the strLink"&strLink&"With no spaces")
                        strID = Request(iLoop & ".ID")
                        ' update the appropiate column
                        strSQL = "UPDATE LOGIN SET HourlyRate =" & "'" & strLink & "'" & " WHERE MemberID = " & strID
                        
                        response.write(strSQL)
                        'Command1.Execute strSQL
                        Next
                        Command1.Close
                        Set Command1 = Nothing
-------
And his is what i get
--------
105 this is the Count
This is the start of the iLoop 0 This is the iloop
This is the strLink 245 With no spacesUPDATE LOGIN SET HourlyRate =' 245 ' WHERE MemberID = 107 This is the start of the iLoop 1 This is the iloop
This is the strLink 200 With no spacesUPDATE LOGIN SET HourlyRate =' 200 ' WHERE MemberID = 64 This is the start of the iLoop 2 This is the iloop
etc
------
but HourlyRate =' 245 ' need to be HourlyRate ='245'. but it does not wont to work.

strLink is putting in a space somehere but it can not find.
-------
Long explaination.

On HourlyRatesOnly.asp the code to find have the value is
--
<input name="<%=(iCount & ".link") %>" type="text" id="HourlyRate" value="<%=(HourlyRates.Fields.Item("HourlyRate").Value)%>">
--
As you can see no spaces.  There is no space when displaying it on the page when it is ready to be updated.
---
Then on UpdateHourlyRates.asp
---
Dim strLink
Dim iLoop
For iLoop = 0 to iCount - 1   'iLoop not putting in a space
strLink = Request(iLoop & ".Link")
response.write("This is the strLink"&strLink&"With no spaces")   ' But here the spaces are being put in. But how??
---

Any suggestions?
0
 
LVL 1

Author Comment

by:Bakersville
ID: 16340896
Sorry i should think outside the box before i leep.  Sorted it by
------
strLink1 = Trim(strLink)
-----
Trimmed the spaces.

Thanks anyway.
0
 
LVL 12

Expert Comment

by:TheMegaLoser
ID: 16341128
Ok, just saw another thing though.

In the original code you had:

strSQL = "UPDATE MyDB SET HourlyRates = ' " & strLink & " ' " &_
                    " WHERE MemberID = " & strID

There you inserted spaces. Those was saved into the database and because that old values will probably have spaces around them. If HourlyRates is a pure numeric field I would recommend having it as a number in the database too. That way you can use a lot of more functions in SQL if you require that (for example AVG for computing average hourly rate).

You wont get any other "strange" charachters in the field either since only digits are allowed then.

Just a thought.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When the s#!t hits the fan, you don’t have time to look up who’s on call, draft emails, call collaborators, or send text messages. An instant chat window is definitely the way to go, especially one like HipChat. HipChat is a true business app. An…
When it comes to security, close monitoring is a must. According to WhiteHat Security annual report, a substantial number of all web applications are vulnerable always. Monitis offers a new product - fully-featured Website security monitoring and pr…
Viewers will get an overview of the benefits and risks of using Bitcoin to accept payments. What Bitcoin is: Legality: Risks: Benefits: Which businesses are best suited?: Other things you should know: How to get started:
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question