Help with creating new row in MDB

VB Script for input from webpage into access db

Page 1 of 1

1 Replies - 1505 Views - Last Post: 26 August 2008 - 10:39 AM Rate Topic: -----

#1 Kaybin  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 26-August 08

Help with creating new row in MDB

Post icon  Posted 26 August 2008 - 08:01 AM

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

  
%>



Is This A Good Question/Topic? 0
  • +

Replies To: Help with creating new row in MDB

#2 AdaHacker  Icon User is offline

  • Resident Curmudgeon

Reputation: 452
  • View blog
  • Posts: 811
  • Joined: 17-June 08

Re: Help with creating new row in MDB

Posted 26 August 2008 - 10:39 AM

View PostKaybin, 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';

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1