[Kaybin]

I am using this code that we wrote to add a product that is input by a webuser into an access db. The DB is already created and the code works fine other than it not creating a new row. I would like each time that the user clicks on the save button, for the db to create a new record on that table with the info instead of constantly rewriting the first record on top of itself. Any ideas, I am sure I am just missing something obvious and am drawing a blank.

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

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


sqlcmd = "Select * from ProdDisplay"
dataCmd.CommandText = sqlcmd
set rs = Server.CreateObject("ADODB.Recordset")
set rs = dataCmd.Execute

  
%>

[AdaHacker]

Kaybin, on 26 Aug, 2008 - 10:01 AM, said:

 sqlcmd = "Update ProdDisplay set ProdName = '" & trim(Request.Form("ProdName")) & "', ProdPhoto = '" & ProdPhoto & "', ProdType = '" & trim(Request.Form("ProdType")) & "'"

No offense intended, but this query suggests to me that you need to read some books or tutorials on SQL. Not only is it impossible to insert new records with this code, but what you have here will actually update every single record in the table when it is run. I can't imagine that's what you actually want.

For starters, in SQL you insert records via an INSERT query, not an UPDATE. The basic syntax is

INSERT INTO tablename (field1, field3, ...) 
                              VALUES ('value1', 'value2', ...);

As for updates, if you want to change just a single record, you need to specify a WHERE clause in your query. Otherwise, the update will affect all records in the table. A basic example is:

UPDATE tablename SET field1 = 'value1', field2 = 'value2', ... 
WHERE table_primary_key = 'primary_key_value';