Solved

Using SQL to update / edit records

Posted on 2001-06-24
15
159 Views
Last Modified: 2010-05-02
Hi,
Here is another problem - I know how to search for items and populate items, what I am having trouble with is adding new records, and updating existing ones with code, I can do this just fine with ADO control, but I wind up with a ton of them all over my forms, How do you "Bind" Code to a text box so you could do something like:

rs.addnew 'Blanking all text boxes for the new entries then
rs.update
Saving the data in the text boxes to the database, or
rs.update after altering the data in a textbox to save it.

Below is some of the code to search and populate textboxes - But I haven't been succesful after searching MSDN library and the net add or update with just code.


'Search Example
Dim Check
List1.Clear
List2.Clear
List3.Clear
List4.Clear
Dim strSQL As String
'If txtSKUNum.Text = txtLastSKU Then Exit Sub
'txtLastSKU = txtSKUNum.Text
strSQL = "SELECT ItemDesc, SellPrice,Category,Type,Image from NewItems " & _
           "Where ItemDesc like '" & "%" & Text1.Text_ & "%'"
On Error Resume Next
rs.Close
On Error GoTo 0
rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly

If Text1.Text = "" Then
MsgBox "Please enter a search term"
Else
Do Until rs.EOF = True
Check = rs!Itemdesc
List1.AddItem rs!Itemdesc
List2.AddItem Format(rs!SellPrice, "$####.##")
List3.AddItem rs!Category
List4.AddItem rs!Type
rs.MoveNext
Loop

If rs.EOF = True And Check = "" Then
MsgBox "Sorry No matches found"
End If
  End If
Set rs = Nothing
End Sub

All help is appreciated.
0
Comment
Question by:flosoft
15 Comments
 

Accepted Solution

by:
CLaW earned 200 total points
ID: 6223121
Alright, your close. I'm gonna use code here but it may not fit your situation. I have a connection already set up, a listbox control array 0-3, and a recordset named rs. I'm not gonna care about case

if you dont know what a listbox control array is, it is simply a listbox with its Index property set to 0, 1, 2, 3 and so on. you can refer to it like so: lstBox(0).clear or lstBox(1).clear. they must all have the same name to work. I do this so that for each will work, which is really handy when you have an abritrary number of them..

code:

Private Sub Form_Load()
if Populate ("rock", List1) then
 msgbox "Works"
else
 msgbox "None found"
end if
End Sub

Private Function Populate(SearchText As String, lstBox As Variant) As Boolean
Dim box As ListBox
Populate=true
Dim SQLSearch As String
Dim Count As Integer
SQLSearch = "SELECT ItemDesc, SellPrice,Category,Type,Image from NewItems Where ItemDesc like '" & "%" & SearchString & "%'"
' close it if you need to here...
rs.Open SQLSearch, cn, adOpenForwardOnly, adLockReadOnly
If rs.EOF And rs.bof Then ' Empty Recordset, nothing found!
   Populate=false ' return false
Else
   If Len(SearchText) < 1 Then ' test for empty search word
      Populate = False ' ' return false
   Else
     
   Do Until rs.EOF
      Count = Count + 1 '
      For Each box In lstBox ' all controls in array
         box.Clear
         If Count = 1 Then box.AddItem (rs!ItemDesc)
         If Count = 2 Then box.AddItem (Format(rs!SellPrice, "$###0.##"))
         If Count = 3 Then box.AddItem (rs!Catagory)
         If Count = 4 Then box.AddItem (rs!Type)
      Next box
      Count = 0
      rs.MoveNext
   Loop

   End If
End If
End Function
0
 

Expert Comment

by:CLaW
ID: 6223128
hmm, I see a few errors in my code, and I have a few notes

first, in form_load, "rock" is the search text. thought i'd make that clear.

the line after   Do Until rs.EOF is Count = Count + 1 '

that should be moved down into for each box in lstbox
my bad.
0
 

Expert Comment

by:CLaW
ID: 6223138
ahhh
more problems, haha

I forgot that you needed to 'bind' as well. there is no way really, except to update the boxes after every change. so lets say you add. you must either write a class that both adds the record and updates the listbox or add, then clear and re-request all data.

same with deleting and whatnot
0
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 

Author Comment

by:flosoft
ID: 6223155
Hmmm,
There isn't an easier way simular to the ADO control?
Something like:

Private Sub CmdAddNew_Click()
'This is assuming a connection has been made and a
'string 'pointing to the proper field been declared.
'Clear all textboxes
txtFirstName.Text = ""
txtLastName.Text = ""
'and so on
rs.Addnew
'You would then add the data Then do something like.
'Make sure data is entered
If txtFirstName.Text = "" Then
msgbox "All fields must be filled out"
Else
rs!FirstName = txtFirstName.Text
rs!LastName = txtLastName.Text
rs.Update
End If
End sub

I know that the code listed doesn't work, but is there anything simpler like this without a control? Or is it ok to have 6 controls on a form? It seems like control over kill eben though the Machine the program will be ran on can handle it with about 500MB Ram.

Thanks for your time thus far.


0
 

Expert Comment

by:CLaW
ID: 6223172
I think i see what you mean

rs.open ' your open method
rs.AddNew ' creates empty record
rs.movelast ' make sure, better to be explicit
rs!record=NewData ' sets the local data to newdata
rs.Update ' sends data back to server
rs.close ' disconnect the recordset

0
 

Expert Comment

by:CLaW
ID: 6223180
thats about as simple as it gets, Having 10-20 controls is ok, 100-200 isn't (lots of overhead)


Sorry, i have a tendancy of hitting send to early, they should have an edit on here.
0
 

Expert Comment

by:CLaW
ID: 6223185
thats about as simple as it gets, Having 10-20 controls is ok, 100-200 isn't (lots of overhead)


Sorry, i have a tendancy of hitting send to early, they should have an edit on here.
0
 

Author Comment

by:flosoft
ID: 6223191
Hi,
Yea - That's exactly what I am trying to do - It isn't that complex a program, and I do Ok with the controls, but I can't get an easy scenario like the one we both listed above to work - I thought of another possibility, which is to compile each form into a program (A Point of sale Program this is) and possibly using controls, then using the shell command to launch each different data input form that way there won't be so many controls running at the same time, does this make any sense?
0
 

Expert Comment

by:CLaW
ID: 6223199
thats about as simple as it gets, Having 10-20 controls is ok, 100-200 isn't (lots of overhead)


Sorry, i have a tendancy of hitting send to early, they should have an edit on here.
0
 

Expert Comment

by:CLaW
ID: 6223201
i would recommend using code though, it performs better and if you design it like my first example, you can add boxes and fields very easy. controls require quite a bit of extra work to change.
0
 

Expert Comment

by:CLaW
ID: 6223220
i would recommend using code though, it performs better and if you design it like my first example, you can add boxes and fields very easy. controls require quite a bit of extra work to change.
0
 

Expert Comment

by:CLaW
ID: 6223226
odd, double-posing here. must be me pressing back button. they should be redirecting after a submit so that back-pushers dont double.
0
 
LVL 43

Expert Comment

by:TimCottee
ID: 6224053
First, a point of order CLaW, you are new to EE so your use of the "answer" option can be excused this once. Read the guidelines on comments vs answers and please don't post "answers" in future unless they are 100% definitive and do not in any way duplicate previous comments (I know the latter doesn't apply in this case).

flosoft, From what you say, I think perhaps that you are approaching things from the wrong direction. If you want to really bind the recordset to the textbox then all you need to do is set the datasource and datafield properties of the appropriate textbox to the recordset that you want to use. In exactly the same way as you do if you are using a data control. Having said that I would strongly advise you to avoid bound controls as they can make things more complicated than they need to be. In my opinion (and that of any others) it is far better to control things completely in code. The process can be understood using the following pseudo-code example:

Form_Load:
  Initialise recordset by select statement
  Call MoveFirst to get the first record

MoveFirst Function:
  CheckForChanges
  Recordset.MoveFirst
  Call ReadValues Function

MoveLast Function:
  CheckForChanges
  Recordset.MoveLast
  Call ReadValues Function

MoveNext Function:
  CheckForChanges
  Recordset.MoveNext
  Call ReadValues Function

MovePrev Function:
  CheckForChanges
  Recordset.MovePrevious
  Call ReadValues Function

CheckForChanges:
  Compare Field values with those contained in the display elements
  If there are changes then either save them or prompt the user to save or discard
  Call WriteValues function to perform update to table

ReadValues:
  Read values from record and assign to the appropriate display elements (textboxes, checkboxes etc)

WriteValues:
  If New record (based on a flag in the form)
    Recordset.AddItem
  Save the displayed values to the fields of the recordset
  Recordset.Update  

You also need buttons on the form to perform the necessary functions.

This is a very simplified way of looking at this sort of thing and ignores validtion of the entered values and so on.

To take it further, it is a good idea not to keep the recordset itself open for the whole time if you are not doing frequent moves/updates/inserts to the recordset. In an ideal world, all of the inserts/updates etc can be handled as stored procedures and called by your code only when needed.

Hope this helps a little.
0
 
LVL 3

Expert Comment

by:jrspano
ID: 6248704
- I thought of
another possibility, which is to compile each form into a program (A Point of sale Program this is)
and possibly using controls, then using the shell command to launch each different data input form that
way there won't be so many controls running at the same time, does this make any sense?

don't do this.  you add the overhead of a new program for each form and possibly in the future complicate getting data back and forth
0
 

Author Comment

by:flosoft
ID: 6254103
I believe I found another solution, After hitting and digging through the MSDN - I have figured out a few ways to join tables and update them through code as needed - This has also allowed me to remove all data controls from the project - It was as easy as I described above - I just used the wrong code.

First I had to open the connection not just read only , Then instead of the code above I use:

rs.Update "QTY", txtWhatever.Text

I am still having trouble with joins, relationships etc. but am plowing forward none the less.

I will keep this updated and post the code I use.
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now