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!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

how can I update database ? 1

Status
Not open for further replies.
allyeric

allyeric

Programmer
Mar 14, 2000
104
0
0
CA
Visit site
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:<br>
<br>
strSQL = &quot;SELECT * FROM Emp_Passwords&quot;<br>
Set Con = New ADODB.Connection<br>
Con.Open &quot;DSN=Training;UID=;PWD=;&quot;<br>
Set rs = Con.Execute(strSQL)<br>
<br>
<br>
With rs<br>
.AddNew<br>
!EmpPassword = arrPassword(Index)<br>
!Emp_No = EmpNo(Index)<br>
.Update<br>
End With<br>

 
Sort by date Sort by votes
use a sql query<br>
like you did when you selected it<br>
&quot;update IPTimer set timed = '%s', mode = '0' where IP = '%s'&quot;<br>
<br>
where %s would be replaced with whatever value you wanted<br>
same thing with adding, or inserting<br>
&quot;insert into Table set (Field1, Field2) values ('f1', 'f2')&quot;<br>
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.<br>
<p>Karl<br><a href=mailto:kb244@bellsouth.net>kb244@bellsouth.net</a><br><a href= </a><br>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)
 
Upvote 0 Downvote
yes, but if you do a select sql command &quot;select ....&quot; you need to close it<br>
but insert, delete, update, anything that does it right away, autmatically closes itself<br>
also another helpful thing if you didnt know already is like<br>
select * from table where SearchableField = '1'<br>
it'll only throw records int ther where their feild = 1<br>
helps me when i need to remove, something or check to see if a groupe of certain types of records exist, so on. <p>Karl<br><a href=mailto:kb244@bellsouth.net>kb244@bellsouth.net</a><br><a href= </a><br>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)
 
Upvote 0 Downvote
ok, I tried that, and now I am getting an error saying &quot;too few parameters. Expected 2&quot;<br>
<br>
Heres the code I added:<br>
<br>
strSQL2 = &quot;UPDATE Emp_Passwords set Emp_Password = &quot; & arrPassword(Index) _<br>
& &quot;, mode = '0' Where No_Employe =&quot; & Val(EmpNo(Index))<br>
Set rs = Con.Execute(strSQL2)<br>
<br>
I have this inside a loop, that shouldn't matter though, right?<br>
<br>

 
Upvote 0 Downvote
depends tho you might just have the index a lil off, if the actual sql command gerated an error, you wouldnt<br>
get a syntax message, much more rather a critical COM type error. so I'm probally guessing that command that uses the index<br>
is the prob.<br>
also you may require 3 parameters if its a connection object<br>
cnn1.Execute strSQLRestore, , adExecuteNoRecords<br>
kinda like that, check your MSDN help, or goto msdn.microsoft.com<br>
i'll look more into it later today. <p>Karl<br><a href=mailto:kb244@bellsouth.net>kb244@bellsouth.net</a><br><a href= </a><br>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)
 
Upvote 0 Downvote
I am not getting any more error messages, but its not adding the records to my database
 
Upvote 0 Downvote
are you trying to Add, or update, an existing record, use Insert into if yer trying to add a brand new record<br>
<br>
&quot;insert into Table set (table1, table2, table3) values (value1, 'value2', value3)&quot; <p>Karl<br><a href=mailto:kb244@bellsouth.net>kb244@bellsouth.net</a><br><a href= </a><br>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)
 
Upvote 0 Downvote
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.<br>
<br>
<br>
strSQL4 = &quot;insert into Emp_Passwords values (' & arrPassword(Index)&quot; _<br>
& &quot;', '&quot; & Val(EmpNo(Index)) & &quot;', &quot; & examcode & &quot;')&quot;<br>
<br>
Set rs = Con.Execute(strSQL4)<br>
<br>
<br>
the value in examcode is a primary key in Exam_Table<br>

 
Upvote 0 Downvote
oh hehe its <br>
&quot;insert into IPTimer (IP, timed, mode) values ('value1', 'value2', '0')&quot;<br>
like that, into table (Fields) values (fieldvalues)<br>
to add a brandnew record <p>Karl<br><a href=mailto:kb244@bellsouth.net>kb244@bellsouth.net</a><br><a href= </a><br>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)
 
Upvote 0 Downvote
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<br>
<br>
strSQL4 = &quot;insert into table set(Emp_Passwords, Emp_Passowrds, Exam_Table) _ <br>
values (' & arrPassword(Index)&quot; & &quot;', '&quot; & Val(EmpNo(Index)) & &quot;' ,&quot; & examcode & &quot;)&quot;
 
Upvote 0 Downvote
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 :eek:) <br>
<br>
strSQL4 = &quot;insert into Emp_Passwords(Emp_Password, No_Employe, Exam_No) _ <br>
values (' & arrPassword(Index)&quot; & &quot;', '&quot; & Val(EmpNo(Index)) & &quot;' ,&quot; & examcode & &quot;)&quot;<br>
<br>

 
Upvote 0 Downvote
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 (<br>
oh also , i dont know if you didnt notice it yourself, but your arrPassword(index) is inside the quotation, <br>
you have<br>
&quot;insert into Emp_Passwords(Emp_Password, No_Employe, Exam_No) _ <br>
values (' & arrPassword(Index)&quot; & &quot;', '&quot; & Val(EmpNo(Index)) & &quot;' ,&quot; & examcode & &quot;)&quot;<br>
<br>
should be<br>
&quot;insert into Emp_Passwords (Emp_Password, No_Employe, Exam_No) _ <br>
values ('&quot; & arrPassword(Index) & &quot;', '&quot; & Val(EmpNo(Index)) & &quot;' ,&quot; & examcode & &quot;)&quot;<br>
<br>
there that should do it <p>Karl<br><a href=mailto:kb244@bellsouth.net>kb244@bellsouth.net</a><br><a href= </a><br>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)
 
Upvote 0 Downvote
fixed that, but still getting the same error message:<br>
<br>
-2147217900 (80040e14)<br>
[Microsoft][ODBC Microsoft Access Driver] You cannot add or change a record because a related record is required in table 'Exam_Table'<br>
<br>

 
Upvote 0 Downvote
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. <p>Karl<br><a href=mailto:kb244@bellsouth.net>kb244@bellsouth.net</a><br><a href= </a><br>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)
 
Upvote 0 Downvote
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. :eek:)<br>

 
Upvote 0 Downvote
Run-time error: '-2147217900 (80040e14)'<br>
[Microsoft][ODBC SQL Server Driver][SQL Server] Cannot open a cursor on<br>
a stored procedure that has anything other than a single select<br>
statement in it. <br>
<br>
Look at :<br>
<br>
<A HREF=" TARGET="_new"> <p>Eric De Decker<br><a href=mailto:vbg.be@vbgroup.nl>vbg.be@vbgroup.nl</a><br><a href= Visual Basic Forum</a><br>
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

sal21
  • Locked
  • Question
UPDATE DAO RECORDSET 1
Replies
11
Views
217
strongm
strongm
2009luca
  • Locked
  • Question
graph with access database - im on vb6 2
Replies
10
Views
350
strongm
strongm
2009luca
  • Locked
  • Question
i dont view any icon durin node add 1
Replies
3
Views
155
2009luca
2009luca
sal21
  • Locked
  • Question
how to read the coordinates in from this file
Replies
3
Views
191
Ken01
Ken01
Ken01
  • Locked
  • Question
I have a program, developed in VB6
Replies
6
Views
303
Ken01
Ken01

Part and Inventory Search

Sponsor

Back
Top