variables in update query
variables in update query
variables in update query
variables in update query
variables in update query
variables in update query variables in update query variables in update query variables in update query variables in update query variables in update query variables in update query variables in update query
variables in update query variables in update query
variables in update query
Go Back  Xtreme Visual Basic Talk > > > variables in update query


Reply
 
Thread Tools Display Modes
  #1  
Old 10-02-2016, 11:07 AM
starmanMike starmanMike is offline
Junior Contributor
 
Join Date: Oct 2005
Location: near Norwich, UK
Posts: 287
Default variables in update query


Hi,
Having solved one problem, I have another!
How do I get variables from text boxes into an update query? Plus, a 'check box' variable? One string variable is OK, no probs, but I want to update 6 fields with their new values. Say the variables from the form are strv1, strv2 etc. Would I have to include these all in one long (and complicated) SQL query string along the lines of
"update charts set firstcol=strv1...,secondcol=strv2,... (plus where clause)"
The problem comes from knowing how to enclose each variable name with single quotes (or whatever) and also how to refer to the state of the check box.
Any help would be hugely appreciated.
Reply With Quote
  #2  
Old 10-05-2016, 02:22 AM
Dennis DVR's Avatar
Dennis DVRvariables in update query Dennis DVR is offline
Back in the Game

Forum Leader
* Expert *
 
Join Date: Nov 2003
Location: Manila Philippines
Posts: 3,576
Default

Hi Mike,

Seeing your previous post it seems to me that you are using ADO Control. It would be nice if you will just use ADODB Connection object for your update. Data Control is very much limited and is very obsolete.

Basically what you need is string concatenation.
Code:
"update tabletoupdate set field1='" & text1.text & "',field2='" & chkBox.Value & "' where field3='" & valuetosearchhere & "'"
For the checkbox value if you only have check and uncheck state I believe you can directly access its value and pass it to the appropriate field like my example above.

I didn't test the query above, but I believe it will get you started.
__________________
Avatar by Lebb

[Posting Guidelines] [Standards & Practices Tutorial] [Participate here effectively]
Our knowledge can only be finite, while our ignorance must necessarily be infinite. Karl Popper
Reply With Quote
  #3  
Old 10-05-2016, 09:48 AM
starmanMike starmanMike is offline
Junior Contributor
 
Join Date: Oct 2005
Location: near Norwich, UK
Posts: 287
Default

Hi, and thanks very much for your help. At least I didn't get any error messages, and the string is obviously correct (I just tested with two variables rather than all 6)... but what has happened is that the update has inserted the information (correctly) whilst leaving the original unchanged, rather than 'overwriting' it with the values in the 'update' string.
Your version is clearly good, but mine isn't! So, is 'update' the keyword I should be using at all?
I am using the ADODB connection object now!
Reply With Quote
  #4  
Old 10-07-2016, 02:17 AM
Dennis DVR's Avatar
Dennis DVRvariables in update query Dennis DVR is offline
Back in the Game

Forum Leader
* Expert *
 
Join Date: Nov 2003
Location: Manila Philippines
Posts: 3,576
Default

Update is for replacing the value of the existing one, but make sure that you are not updating the value of your primary key. Insert is for adding new records.
So if you just want to change the value of existing record you should use update.

Can you post your code as it currently stand? and please indicate your primary key field.
What database are you using btw?

Regards,
Dennis
__________________
Avatar by Lebb

[Posting Guidelines] [Standards & Practices Tutorial] [Participate here effectively]
Our knowledge can only be finite, while our ignorance must necessarily be infinite. Karl Popper
Reply With Quote
  #5  
Old 10-07-2016, 09:06 AM
starmanMike starmanMike is offline
Junior Contributor
 
Join Date: Oct 2005
Location: near Norwich, UK
Posts: 287
Default

Hi Dennis,
Thanks again - I've just had a look and think I've spotted a possible cause, but for information's sake, I am using an .mdb (access 97) DB, with no primary key, though there is a field which must be - and is - unique. So bear with me - and if everything is OK after all, thanks anyway (but I may have to get back to you!)

OK - a little apology, there was an ADODC data control (mainly because it works!) so I deleted it and entered the above code, called from the form_load in a call setupgrid() line:
Code:
Private Sub setupgrid()
Dim myconn As ADODB.Connection, rs As ADODB.Recordset
    
    Set myconn = New ADODB.Connection
    myconn.ConnectionString = "Provider=Microsoft.Jet.oledb.4.0;data source=" & App.Path & "\starcharts.mdb;"
    myconn.Open
    Set rs = myconn.Execute("select * from charts")
    Me.DataGrid1.DataSource = rs
    Set rs = Nothing
    myconn.Close
    Set myconn = Nothing
End Sub
When I know all is working properly, I shall transfer the code to link to the database, i.e., the first 4-5 lines above, to a code module. But for now, I want the individual queries in their 'own places' so to speak.
When I run this, I get an error message saying 'method or data member not found' at the line that defines the grid's data source. Either that, or something about not being bookmarkable. The database has been defined, the sql query executed (well, read anyway), so why is there a problem with the grid? I also deleted its former ADODC data source from the properties window.
from perusing this issue on the net, a lot of people seem to have had the same problem.

Last edited by starmanMike; 10-07-2016 at 04:57 PM.
Reply With Quote
  #6  
Old 10-08-2016, 08:17 AM
dilettante's Avatar
dilettantevariables in update query dilettante is offline
Underclocked lifestyle

Forum Leader
* Guru *
 
Join Date: Feb 2005
Location: Michigan, USA
Posts: 4,524
Default

There is so much wrong going on in this thread it is difficult to know where to begin.

But to focus on your immediate problem, your rs is completely inadequate. As it says in the documentation which comes with every legal copy of VB6:

Quote:
Execute Method (ADO Connection)
...
The returned Recordset object is always a read-only, forward-only cursor. If you need a Recordset object with more functionality, first create a Recordset object with the desired property settings, then use the Recordset object's Open method to execute the query and return the desired cursor type.
Reply With Quote
  #7  
Old 10-08-2016, 01:26 PM
starmanMike starmanMike is offline
Junior Contributor
 
Join Date: Oct 2005
Location: near Norwich, UK
Posts: 287
Default

Hi,
Speaking about 'legal copies', I wasn't aware of all this at the time, but I got it from a bloke down the pub (honestly!) who had just left his job as a VB programmer. I did wonder why the installation CD had a wooden leg and a parrot on its shoulder that kept saying 'pieces of eight'. As you may have divined by now, no I don't have built-in help.
If this all seems flippant, what I mean is that I acquired the VB CD in good faith several years back, and didn't realise that the lack of help files was in any way problematic as regards provenance.

Last edited by starmanMike; 10-09-2016 at 08:53 AM.
Reply With Quote
  #8  
Old 10-09-2016, 10:06 AM
dilettante's Avatar
dilettantevariables in update query dilettante is offline
Underclocked lifestyle

Forum Leader
* Guru *
 
Join Date: Feb 2005
Location: Michigan, USA
Posts: 4,524
Default

Yes, the EULA states that specifically. Legal transfer of license requires turning over everything, possibly even the box it came in. If you did not get the MSDN CDs with it then you have a pirated copy. Your intentions aren't the point.
Reply With Quote
  #9  
Old 10-09-2016, 11:14 AM
starmanMike starmanMike is offline
Junior Contributor
 
Join Date: Oct 2005
Location: near Norwich, UK
Posts: 287
Default

Thanks for that. Yes, I realise that now, but didn't at the time.
Reply With Quote
Reply

Tags
update, variables, variable, query, check, box, string, secondcol=strv2, clause, firstcol=strv1, set, lines, appreciated, sql, charts, complicated, hugely, refer, enclose, knowing, single, quotes, boxes, text, solved


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
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

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
variables in update query
variables in update query
variables in update query variables in update query
variables in update query
variables in update query
variables in update query variables in update query variables in update query variables in update query variables in update query variables in update query variables in update query
variables in update query
variables in update query
 
variables in update query
variables in update query
 
-->