Help Adding Records to Access DB

[AlphaKaybin]

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.

<%@ 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

  
%>

[si_the_geek]

Welcome to VBForums

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:

	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:

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

..you should just have this:

	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:

 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.