Solved

Using SQL to update / edit records

Posted on 2001-06-24
15
156 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:flosoft
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:flosoft
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
- 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
Comment Utility
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
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…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

728 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

10 Experts available now in Live!

Get 1:1 Help Now