Help Adding Records to Access DB

Hey everyone,

I wrote this code in order to add a couple of input boxes worth of values to aan Access DB stored on the domain. There are no errors and the the code seems to input and transfer values into the DB properly but it will not add it as a new record. It just keeps overwriting the old one. What am I missing? Please, any help would be greatly appreciated.

Code:

---

<%@ LANGUAGE=VBSCRIPT ENABLESESSIONSTATE=TRUE %>
<%
if Session("goodlogon") = "False" then
        response.redirect "nogood.asp"
end if

                Set dataConn = Server.CreateObject("ADODB.Connection")
                dataConn.ConnectionString="DSN=Coopersburg"
                               
                                dataConn.Mode = adModeReadWrite
                            dataConn.Open

set dataCmd = Server.CreateObject("ADODB.Command")
dataCmd.ActiveConnection = Session("dataConn")

Function StripQuotes(strStrip)
 'Strips all single quotes from strStrip & returns cleaned string
 
 For i = 1 To Len(strStrip)
  If Mid(strStrip, i, 1) = "'" Then
  strStrip = Left(strStrip, i - 1) & Right(strStrip, Len(strStrip) - i)
  End If
 Next
 StripQuotes = strStrip
End Function


if Request.Form("cmdSave") = "Save" then
        ProdName = trim(Request.Form("ProdName"))
        ProdPhoto = trim(Request.Form("ProdPhoto"))
        ProdType = trim(Request.Form("ProdType"))
       
        if ProdName = "" then
                ProdName = " "
        end if
        if ProdPhoto = "" then
                ProdPhoto = " "
        end if
        if ProdType = "" then
                ProdType = " "
        end if
               
        sqlcmd = "Insert into ProdDisplay(ProdName, ProdPhoto, ProdType,) VALUES ("
        sqlcmd = sqlcmd & "'" & StripQuotes(ProdName) & "', "
        sqlcmd = sqlcmd & "'" & ProdPhoto & "', "
        sqlcmd = sqlcmd & "'" & ProdType & "', "
        dataCmd.CommandText = sqlcmd
        set upd = server.CreateObject("ADODB.Recordset")
        set upd = dataCmd.Execute
        Response.Redirect "updated.asp"
end if

 
%>

---

Welcome to VBForums :wave:

Thread moved to VB.Net (VB2002 and later) forum to ASP (pre .Net) forum

I don't see how that could possibly overwrite anything, or how it could run without giving an error - as there are a couple of significant syntax errors in your SQL statement. It should be like this:

Code:

---

        sqlcmd = "Insert into ProdDisplay(ProdName, ProdPhoto, ProdType) VALUES ("
        sqlcmd = sqlcmd & "'" & StripQuotes(ProdName) & "', "
        sqlcmd = sqlcmd & "'" & ProdPhoto & "', "
        sqlcmd = sqlcmd & "'" & ProdType & "') "

---

Also, the way you run it is not entirely correct - it will not return any records, so there is no point using a RecordSet to receive them. Instead of these two lines:

Code:

---

        set upd = server.CreateObject("ADODB.Recordset")
        set upd = dataCmd.Execute

---

..you should just have this:

Code:

---

        dataCmd.Execute

---

There is also an issue with you StripQuotes routine, as it will always ignore the first character after a ' (as you move all following items along by one character, but still increase position before checking the next). The easiest way to correct it is to change the direction of the loop, like this:

Code:

---

For i = Len(strStrip) To 1 Step -1

---

Note that if you are doing this to avoid errors caused by having the ' character in the data, there is a much better way to do that built into the Command object.