Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Visual Basic > VB 6 Visual Basic 6 > VB How-To
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
VB How-To Ask your "How do I do this with VB?" questions in this forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the VB How-To section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #51 (permalink)  
Old August 1st, 2005, 02:12 AM
Authorized User
 
Join Date: Jul 2005
Location: , , .
Posts: 40
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hey Raghavendra Mudugal,

Oh yea i remember coming across ADO Command Objects... currently i am struggling with handling two seperate tables.



Private Sub Command21_Click()
Rem insert
rst.Open "select * from qns_table", cn, adOpenDynamic, adLockOptimistic
If Not rst.EOF Then
    rst1.Open "select qnsID from autonumber", cn, adOpenDynamic, adLockOptimistic
    rst.AddNew
    rst!qnsID = "qns" & Right("0000" & CStr(CInt(rst1!qnsID) + 1), 4)
    rst!question = Me.Text17
    rst.Update
    rst1.Close
    rst1.Open "update autonumber set ansID = ansID +1", cn, adOpenDynamic, adLockOptmistic
End If

rst.Close

rst2.Open "select * from ans_table", cn, adOpenDynamic, adLockOptimistic
If Not rst.EOF Then
    rst1.Open "select ansID from autonumber", cn, adOpenDynamic, adLockOptimistic
    rst2.AddNew
    rst2!ansID = "ans" & Right("0000" & CStr(CInt(rst1!ansID) + 1), 4)
    rst2!answer = Me.Text19
    rst2.Update
    rst1.Close
    rst1.Open "update autonumber set qnsID = qnsID +1", cn, adOpenDynamic, adLockOptimistic
End If

rst2.Close


MsgBox "Data Inserted"
End Sub



i have created two columns for autonumber (qnsID & qnsID), initially when i started on only 'qns_table'... it worked fine but right now it doesn't seem so. sorry about this.
thank you.

Astro
Reply With Quote
  #52 (permalink)  
Old August 1st, 2005, 03:24 AM
Friend of Wrox
Points: 687, Level: 9
Points: 687, Level: 9 Points: 687, Level: 9 Points: 687, Level: 9
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Dec 2004
Location: Chennai, Tamilnadu, India.
Posts: 221
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hello Astro

check the column names. you said " have created two columns for autonumber (qnsID & qnsID)" both are qnsID's. Check for the spellings and tell me what the error
that you are seeing while execution.

I guess you have problem with column name. you are trying to update qnsID both the
times.

Its mainly, i guess you code needs revision.

Please check and let me know the error that it gives.

Hope this helps.

With Regards,
Raghavendra Mudugal
Reply With Quote
  #53 (permalink)  
Old August 1st, 2005, 03:34 AM
Authorized User
 
Join Date: Jul 2005
Location: , , .
Posts: 40
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hey Raghavendra Mudugal,

Ah sorry, it was qnsID & ansID... i'll run through again and let you know the error.
Thanks!

Astro

Reply With Quote
  #54 (permalink)  
Old August 1st, 2005, 03:42 AM
Friend of Wrox
Points: 687, Level: 9
Points: 687, Level: 9 Points: 687, Level: 9 Points: 687, Level: 9
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Dec 2004
Location: Chennai, Tamilnadu, India.
Posts: 221
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hello CheeWee

Here is the code for updating two tables.

rst.Open "select A.cName, A.cAddress, B.cName, B.cAddress from cust as A, cust1 as B where A.cid = 'qns0001'", cn, adOpenDynamic, adLockOptimistic
If Not rst.EOF Then
    rst.Fields(0) = "1212"
    rst.Fields(1) = "1212"
    rst.Fields(2) = "1212"
    rst.Fields(3) = "1212"
    rst.Update

End If
rst.Close

It depends upon the SQL statement how you hold the data, but both tables should
have a common column name that links both table. So we can get that particular
row to update it.

Hope this helps.

With Regards,
Raghavendra Mudugal
Reply With Quote
  #55 (permalink)  
Old August 1st, 2005, 09:31 PM
Authorized User
 
Join Date: Jul 2005
Location: , , .
Posts: 40
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hey Raghavendra Mudugal,

I am still having some trouble with handling 2 seperate 'inserting into table' with one click event.
I have tried duplicating the codes (line 3 to 12) with another If Then loop except everything ties with ans_table (instead of qns_table),
the other approach was repeating every line of the code (line 3 to 11), using seperate record sets.


1 Private Sub Command21_Click()


2 Rem insert
3 rst.Open "select * from qns_table", cn, adOpenDynamic, adLockOptimistic

4 If Not rst.EOF Then

5 rst1.Open "select qnsID from autonumber", cn, adOpenDynamic, adLockOptimistic
6 rst.AddNew
7 rst!qnsID = "qns" & Right("0000" & CStr(CInt(rst1!qnsID) + 1), 4)
8 rst!question = Me.Text17
9 rst.Update
10 rst1.Close
11 rst1.Open "update autonumber set qnsID = qnsID +1", cn, adOpenDynamic, adLockOptimistic

12 End If

13 rst.Close
14 MsgBox "Data Inserted"


15 End Sub



Unfortunately, boths ways did not work for me. Am i working at it in a wrong approach?
Would be nice if you could shed some light =) Sorry about this.

Thank You

Astro

Reply With Quote
  #56 (permalink)  
Old August 1st, 2005, 09:54 PM
Registered User
 
Join Date: Aug 2005
Location: , , Senegal.
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I cannot insert just only cName to a cust1 table, cname and caddress to cust table (already done in your example) Also,I don quite understand “rst.Fields(0) = "1212"… <The (0) and “1212” refer to> The SQL statement I could understand. My main is to have cName to cust1 table, cname and caddress to cust table, both table can insert and update the same time.. please guide me.. sorry for my poor English.. thank..

Reply With Quote
  #57 (permalink)  
Old August 2nd, 2005, 12:19 AM
Friend of Wrox
Points: 687, Level: 9
Points: 687, Level: 9 Points: 687, Level: 9 Points: 687, Level: 9
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Dec 2004
Location: Chennai, Tamilnadu, India.
Posts: 221
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hello Astro,

It will be better, when you get the error just post the error message and
the line number and the code of that line.

With Regards,
Raghavendra Mudugal
Reply With Quote
  #58 (permalink)  
Old August 2nd, 2005, 12:26 AM
Friend of Wrox
Points: 687, Level: 9
Points: 687, Level: 9 Points: 687, Level: 9 Points: 687, Level: 9
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Dec 2004
Location: Chennai, Tamilnadu, India.
Posts: 221
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hello Cheewee

Yes you can, i said you before, IT DEPENDS UPON ON THE SQL STATEMENT ON WHAT DATA
IT HOLDS. fields(0),fields(1)... are related to the column name in the sql state
ment which you open for the rscordset.

For example

rst.Open "select A.cName, A.cAddress, B.cName from cust as A, cust1 as B where A.cid = 'qns0001'", cn, adOpenDynamic, adLockOptimistic
If Not rst.EOF Then
    rst.Fields(0) = "1212" 'for table CUST (first column name in SQL statement)
    rst.Fields(1) = "1212" 'for table CUST (second column name in SQL statement)
    rst.Fields(2) = "1212" 'for table CUST1 (third column name in SQL statement)
    rst.Update

End If
rst.Close

Hope this makes you clear to you




With Regards,
Raghavendra Mudugal
Reply With Quote
  #59 (permalink)  
Old August 2nd, 2005, 09:39 PM
Authorized User
 
Join Date: Jul 2005
Location: , , .
Posts: 40
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hey Raghavendra_Mudugal!

Finally, it's up! well, not entirely but the inital part i worked on has completed! I'll try to upload it for you to see! Haha erm now i am completely clueless on how to allow a user retrieve all that data into a questionaire form with convenience. need some suggestion =)

Thank You
Astro

Reply With Quote
  #60 (permalink)  
Old August 3rd, 2005, 02:26 AM
Authorized User
 
Join Date: Jul 2005
Location: , , .
Posts: 40
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hey Raghavendra Mudugal,

Here is the url where i uploaded my project file. You gotta copy and paste the url into your web browser in order to be able to download it.
Sorry for the trouble. =)

http://www.angelfire.com/fang/fiftytwo/project.zip

Thank You
Astro
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Update query: Editing phone number fields? monacle Access 2 February 27th, 2007 08:55 AM
Combining Text Fields lizhaskin Access VBA 1 May 1st, 2006 06:15 AM
Calculating text fields jayman Classic ASP Basics 13 March 6th, 2006 05:27 AM
MySQL Text Fields MAtkins General .NET 0 May 27th, 2005 03:17 PM
variable number of fields in record manisha.anand Oracle 0 July 1st, 2004 05:00 PM



All times are GMT -4. The time now is 11:59 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.