how can I update database ? 1

allyeric · Mar 16, 2000

Database is in Access 2000, I am using ADO. I can open the recordset, but can't seem to be able to add new records. Here is some of my code, perhaps someone can point me in the right direction: 
 
strSQL = "SELECT * FROM Emp_Passwords" 
Set Con = New ADODB.Connection 
Con.Open "DSN=Training;UID=;PWD=;" 
Set rs = Con.Execute(strSQL) 
 
 
With rs 
.AddNew 
!EmpPassword = arrPassword(Index) 
!Emp_No = EmpNo(Index) 
.Update 
End With

Karl Blessing · Mar 16, 2000

use a sql query 
like you did when you selected it 
"update IPTimer set timed = '%s', mode = '0' where IP = '%s'" 
 
where %s would be replaced with whatever value you wanted 
same thing with adding, or inserting 
"insert into Table set (Field1, Field2) values ('f1', 'f2')" 
that kina thing, if you need to know more on using SQL strings to do almost all your database opening and changing task, check your SQL Server, or Acess help file for SQL Query. 
Karl <a href=mailto:kb244@bellsouth.net>kb244@bellsouth.net</a> <a href=

http://kb244.8m.com>

</a> Experienced in , or have messed with : VC++, Borland C++ Builder, VB-Dos, VB1 thru VB6, Delphi 3 pro, Borland C++ 3(DOS), Borland C++ 4.5, HTML, ASP(somewhat), QBasic(hehe, yea it was 4.5 too, least i didnt start with COBOL)

allyeric · Mar 16, 2000

thanks karl, but after I set my sql, do I do an Execute again?

Karl Blessing · Mar 16, 2000

yes, but if you do a select sql command "select ...." you need to close it 
but insert, delete, update, anything that does it right away, autmatically closes itself 
also another helpful thing if you didnt know already is like 
select * from table where SearchableField = '1' 
it'll only throw records int ther where their feild = 1 
helps me when i need to remove, something or check to see if a groupe of certain types of records exist, so on. Karl <a href=mailto:kb244@bellsouth.net>kb244@bellsouth.net</a> <a href=

http://kb244.8m.com>

</a> Experienced in , or have messed with : VC++, Borland C++ Builder, VB-Dos, VB1 thru VB6, Delphi 3 pro, Borland C++ 3(DOS), Borland C++ 4.5, HTML, ASP(somewhat), QBasic(hehe, yea it was 4.5 too, least i didnt start with COBOL)

allyeric · Mar 17, 2000

ok, I tried that, and now I am getting an error saying "too few parameters. Expected 2" 
 
Heres the code I added: 
 
strSQL2 = "UPDATE Emp_Passwords set Emp_Password = " & arrPassword(Index) _ 
& ", mode = '0' Where No_Employe =" & Val(EmpNo(Index)) 
Set rs = Con.Execute(strSQL2) 
 
I have this inside a loop, that shouldn't matter though, right?

Karl Blessing · Mar 17, 2000

depends tho you might just have the index a lil off, if the actual sql command gerated an error, you wouldnt 
get a syntax message, much more rather a critical COM type error. so I'm probally guessing that command that uses the index 
is the prob. 
also you may require 3 parameters if its a connection object 
cnn1.Execute strSQLRestore, , adExecuteNoRecords 
kinda like that, check your MSDN help, or goto msdn.microsoft.com 
i'll look more into it later today. Karl <a href=mailto:kb244@bellsouth.net>kb244@bellsouth.net</a> <a href=

http://kb244.8m.com>

</a> Experienced in , or have messed with : VC++, Borland C++ Builder, VB-Dos, VB1 thru VB6, Delphi 3 pro, Borland C++ 3(DOS), Borland C++ 4.5, HTML, ASP(somewhat), QBasic(hehe, yea it was 4.5 too, least i didnt start with COBOL)

allyeric · Mar 17, 2000

I am not getting any more error messages, but its not adding the records to my database

Karl Blessing · Mar 17, 2000

are you trying to Add, or update, an existing record, use Insert into if yer trying to add a brand new record 
 
"insert into Table set (table1, table2, table3) values (value1, 'value2', value3)" Karl <a href=mailto:kb244@bellsouth.net>kb244@bellsouth.net</a> <a href=

http://kb244.8m.com>

</a> Experienced in , or have messed with : VC++, Borland C++ Builder, VB-Dos, VB1 thru VB6, Delphi 3 pro, Borland C++ 3(DOS), Borland C++ 4.5, HTML, ASP(somewhat), QBasic(hehe, yea it was 4.5 too, least i didnt start with COBOL)

allyeric · Mar 17, 2000

I decided to try Insert Into - but says I can't insert a record, as one of the fields I am inserting into is linked to another table - how can I do this? There is a value already in the second table, I don't want to change this value, just have it link to first table. 
 
 
strSQL4 = "insert into Emp_Passwords values (' & arrPassword(Index)" _ 
& "', '" & Val(EmpNo(Index)) & "', " & examcode & "')" 
 
Set rs = Con.Execute(strSQL4) 
 
 
the value in examcode is a primary key in Exam_Table

Karl Blessing · Mar 17, 2000

oh hehe its 
"insert into IPTimer (IP, timed, mode) values ('value1', 'value2', '0')" 
like that, into table (Fields) values (fieldvalues) 
to add a brandnew record Karl <a href=mailto:kb244@bellsouth.net>kb244@bellsouth.net</a> <a href=

http://kb244.8m.com>

</a> Experienced in , or have messed with : VC++, Borland C++ Builder, VB-Dos, VB1 thru VB6, Delphi 3 pro, Borland C++ 3(DOS), Borland C++ 4.5, HTML, ASP(somewhat), QBasic(hehe, yea it was 4.5 too, least i didnt start with COBOL)

allyeric · Mar 17, 2000

I posted that last one too soon lol ok, heres my new query string - the first two values I want to add to Emp_passwords, the last value, is already entered into Exam_Table, but if I leave that parameter off my SQl statement, I get an error, 1 parameter needed 
 
strSQL4 = "insert into table set(Emp_Passwords, Emp_Passowrds, Exam_Table) _ 
values (' & arrPassword(Index)" & "', '" & Val(EmpNo(Index)) & "' ," & examcode & &quot

"

allyeric · Mar 17, 2000

lol I am going to stop posting so fast - I will retry with your last suggestion

)

allyeric · Mar 17, 2000

ok, I tried, but still won't add a record because the table is linked to another - any more suggestions? Heres my SQL command, just so you can double check it

) 
 
strSQL4 = "insert into Emp_Passwords(Emp_Password, No_Employe, Exam_No) _ 
values (' & arrPassword(Index)" & "', '" & Val(EmpNo(Index)) & "' ," & examcode & &quot

"

Karl Blessing · Mar 17, 2000

hmm, interesting, when you insert a record, all fields must be accounted for unless in Access the field is marked as not required, and as far as inserting joint fields, i am really uncertain about that. But I'll be leaving work at about 12:15(early i know, but i have an appointment, then i am home the rest of the day), I will concentrate on your problem once i get home. and see if yuo can acheive the same task without trying to join the tables, oh and try putting a space bettween that table name and the first ( 
oh also , i dont know if you didnt notice it yourself, but your arrPassword(index) is inside the quotation, 
you have 
"insert into Emp_Passwords(Emp_Password, No_Employe, Exam_No) _ 
values (' & arrPassword(Index)" & "', '" & Val(EmpNo(Index)) & "' ," & examcode & &quot

" 
 
should be 
"insert into Emp_Passwords (Emp_Password, No_Employe, Exam_No) _ 
values ('" & arrPassword(Index) & "', '" & Val(EmpNo(Index)) & "' ," & examcode & &quot

" 
 
there that should do it Karl <a href=mailto:kb244@bellsouth.net>kb244@bellsouth.net</a> <a href=

http://kb244.8m.com>

</a> Experienced in , or have messed with : VC++, Borland C++ Builder, VB-Dos, VB1 thru VB6, Delphi 3 pro, Borland C++ 3(DOS), Borland C++ 4.5, HTML, ASP(somewhat), QBasic(hehe, yea it was 4.5 too, least i didnt start with COBOL)

allyeric · Mar 17, 2000

fixed that, but still getting the same error message: 
 
-2147217900 (80040e14) 
[Microsoft][ODBC Microsoft Access Driver] You cannot add or change a record because a related record is required in table 'Exam_Table'

Karl Blessing · Mar 17, 2000

did you check to make sure the Table actually exist in the database, and if yer putting in a _ cuz theres a space there, thats not the same the tables are case snetative, and you shouldnt be putting spaces into the table name, and make sure they are listed in the System DSN on the ODBC32 in your control pannel. Karl <a href=mailto:kb244@bellsouth.net>kb244@bellsouth.net</a> <a href=

http://kb244.8m.com>

</a> Experienced in , or have messed with : VC++, Borland C++ Builder, VB-Dos, VB1 thru VB6, Delphi 3 pro, Borland C++ 3(DOS), Borland C++ 4.5, HTML, ASP(somewhat), QBasic(hehe, yea it was 4.5 too, least i didnt start with COBOL)

allyeric · Mar 17, 2000

yes, the table does exist, and I put the _ because thats how I named the field in the table. And yes, it is listed in the System DSN. I think the problem may be the way my relationships are on the database. I am going to take the weekend to look it over, perhaps I can figure out where I went wrong. Again, thanks so much for all your help.

)

edderic · Mar 25, 2000

Run-time error: '-2147217900 (80040e14)' 
[Microsoft][ODBC SQL Server Driver][SQL Server] Cannot open a cursor on 
a stored procedure that has anything other than a single select 
statement in it. 
 
Look at : 
 
<A HREF="

http://support.microsoft.com/support/kb/articles/Q182/9/29.ASP?LNG=ENG&SA=MSDN&FR=1"

TARGET="_new">

http://support.microsoft.com/support/kb/articles/Q182/9/29.ASP?LNG=ENG&SA=MSDN&FR=1</A>

Eric De Decker <a href=mailto:vbg.be@vbgroup.nl>vbg.be@vbgroup.nl</a> <a href=

http://communities.msn.be/VisualBasicForum>Dutch

Visual Basic Forum</a>

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

how can I update database ? 1

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Similar threads

Log in

Part and Inventory Search

Sponsor