VBScript Code for a button to populate an Access table

[amal1973]

Hello developers
I have created an Access page that has 3 Combo Boxes, and one text field. What I am trying to do is when the user opens this page in a web browser; he should enter his name in the text box and choose the data from the combo boxes. After that, the user should click the submit button to populate a table with the data he entered and choused. Please help me write the code that should be on the click event for the submit button and tell me where to place it. It should be in VBScript.

Thanks a million
this is the SQL statment that i think should be embeaded in code!!??
UPDATE Survay_Master SET Survay_Master.Employee_A_Number = [Text4].[Value], Survay_Master.Excel = [DropDownList0].[Value], Survay_Master.Access = [DropDownList1].[Value], Survay_Master.years = [DropDownList2].[Value];

 

[onpnt]

This should give you the answers you need

http://www.asp101.com/samples/db_add.asp

provide tools to let people become their best.

 

[amal1973]

thank you for you replay ..
I am having some problems .. this is the code i wrote
first it tells me cant find Survey _master ..that is the table that sould be inserted with the new values

<script language=vbscript event=onclick for=button1>

Dim DB_CONNECTIONSTRING
DB_CONNECTIONSTRING = &quot;Provider=Microsoft.Jet.OLEDB.4.0;&quot; _
& &quot;Data Source=C:/Survey_Page.mdb;&quot;
<%
Dim objRecordset
Set objRecordset = server.CreateObject(&quot;ADODB.Recordset&quot

objRecordset.Open &quot;Survey_Master, DB_CONNECTIONSTRING, adOpenKeyset, adLockPessimistic, adCmdText
objRecordset.AddNew
objRecordset.Fields(&quot;Employee_A_Number&quot = CStr(Text4.Value)
objRecordset.Fields(&quot;Excell&quot = CStr(Dropdownlist0.Value)
objRecordset.Fields(&quot;Access&quot = CStr(Dropdownlist1.Value)
objRecordset.Fields(&quot;Years&quot = CStr(Dropdownlist2.Value)
objRecordset.Update
objRecordset.Close
Set objRecordset = Nothing

thank you

 

[BarkingFrog]

amal1973,

All you really have to do is execute your sql string using your connection object.

SET objConn = Server.CreateObject(&quot;ADODB.Connection&quot

Dim strSQL

strSQL = &quot;INSERT INTO Survay_Master(Employee_A_Number,Excel,Access,years) VALUES('&quot; & CStr(Text4.Value) & &quot;','&quot; & CStr(Dropdownlist0.Value) & &quot;','&quot; & CStr(Dropdownlist1.Value) & &quot;','&quot; & CStr(Dropdownlist2.Value) & &quot;'&quot;

objConn.execute strSQL

Works for me everytime.

Good Luck!

Dave

 

[amal1973]

It tells me object requird (server) and if i took the server out it will give me a message that operation is not allowed when the object is closed??!!
also if i may ask
where in the code do you wite this line
DB_CONNECTIONSTRING = &quot;Provider=Microsoft.Jet.OLEDB.4.0;&quot; _
& &quot;Data Source=C:/Survey_Page.mdb;&quot;
thank you

 

[BarkingFrog]

You are probably getting the object required error because you don't have a connection open. Look below and I show you where your connection string should go.

Try this first, before executing the code I posted earlier:

Dim objConn , DB_CONNECTIONSTRING

'Create connection Object
Set objConn = Server.CreateObject(&quot;ADODB.Connection&quot

objConn .ConnectionTimeout = 30

objConn .CommandTimeout = 30

objConn .CursorLocation = 3

DB_CONNECTIONSTRING = &quot;Provider=Microsoft.Jet.OLEDB.4.0;&quot; _
& &quot;Data Source=C:/Survey_Page.mdb;&quot;

objConn .Open DB_CONNECTIONSTRING

 

[amal1973]

Man. Thank you very much it did work perfectly J..
I also would like to ask two things related to this page..
If I had the word Server in front the Create Object, it will give me an error telling me Object required server, and when I take it out, it works…
SET objConn = Server.CreateObject(&quot;ADODB.Connection&quot

The second thing I am thinking about is when the user inserts an Employee Number it should be a primary key .how can I send a message to the user that has pressed the button telling him that this number that he is putting has been already inserted by someone else!! (Like form validation) and best regards

 

[amal1973]

i have tried to place this page on the web server and when accessed it give an error tell that you are trying to coonect to another domain?? why is that? what am i doing wrong ..

 

[BarkingFrog]

Check to be sure the database exists on the server that you moved your web page to.

Secondly, on the validation, you will need to do a postback to the page where the value that the user has chosen to enter is. Check to see if that value has been posted back, i.e. request.form(&quot;value&quot, then use a select statement using that value. If you get a recordcount of 1, then the value has been used and you can then re-display the page with text stating that the value has already been used and ask the user to pick another one.