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
%>
Re: Help Adding Records to Access DB
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:
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.