|
This is cross posted in the Visual Basic / VB.NET and SQL / ADO / ADO.NET since I am not sure exactly where it goes. So... lets give this a shot. Background: I am using Access 2000 with extensive Visual Basic coding to handle much of the legwork and raw data manipulation. In the project I have a table with several columns (Say [Info1], [Info2], [Amount1], and [Amount2]). When I open the form it selects all the entries from the table where [Amount1] is less than [Amount2] (I call this a sub set of the table but I am sure there is some big word or funny acronym that is normally used). In the VB code for the form I use Form_FormName.RecordSet.Movefirst / Movenext / Movelast to move around the subset of data and modify it as needed based on what is in [Info1] and [Info2]. This all works fine, but I would like to take all of this data from the subset and put it into a new table in the same database. The new table already exist and has data in it, what I can’t figure out is how to open two different tables in a single form and place data from one table into the other table. It sounds simple, and probably is, but I just can’t seem to figure it out. If anyone could give me a hand or at least get me pointed in the right direction I would greatly appreciate it.
Thanks,
Mark
|
|
|
|
|
First of all, it sounds like you had a similar experience as I did - I entered the DB world after years of programming, and tried to do everything in Access using VBA. It worked, but not always efficiently. Eventually, I ran into problems where I was forced to learn about SQL and DBs. (If this isn't the case for you, excuse me.)
The point of all that is that what you are describing can easily be done with SQL.
First off, if you're looking to select all the records from Table1 where [Amount1] is less than [Amount2], and you don't need to display all the records from Table1, things will run much faster if you write a query in Access for this. If you use SQL view (the easiest for me to duplicate here), the SQL should look like this:
SELECT * FROM Table1 WHERE [Table1.Amount1] < [Table1.Amount2]
For this explanation, save this as DifferenceQuery.
If you only have a few records, you might not notice the speed difference. I am working in a medium sized database (30,000 records or so in the largest table, with 20+ fields in the table), and I quickly noticed the difference when creating my queries in Access rather than using ADO.
Then, in your program, to get the records you want from Table1, use ADO with SQL like:
SELECT [Info1], [Info2], [Amount1], [Amount2] FROM [DifferenceQuery]
This will return the same subset you currently acheive, only you don't have to programatically cycle through the records and select the ones where [Info1] < [Info2].
To place data from this subset into Table2, use ADO & SQL again:
dim info1 as string = "info1"
dim info2 as string = "info2"
dim amount1 as double = 1.1
dim amount2 as double = 2.0
dim sql as string
sql = "INSERT INTO [Table2] ([Info1], [Info2], [Amount1], [Amount2]) VALUES ('" & info1 & "', '" & info2 & "', " & amount1 & ", " & amount2 & ")"
The string concatenation can start to get confusing, but the above would (if I typed it right) produce the sql statement:
INSERT INTO [Table2] ([Info1], [Info2], [Amount1], [Amount2]) VALUES ('info1', 'info2', 1.1, 2.0)
Step through your subset and execute this SQL statement (substituting the appropriate values for your fields) and all your records will be added to table 2.
Hope this helps.
P.S.
I find w3school's SQL tutorials good reference, since I don't use all the SQL commands all the time: Link[^]
|
|
|
|
|
Thank you for your reply. I follow everything you said, and it seems so easy, but maybe I have missed something. The SQL statement which we saved as [DifferenceQuery] is just that, a Query. I have used these a lot but just recently have started looking at the actual SQL statement so I am a little behind. This ends up being saved in the Query section in Access. Where I get lost is the next statement...
"Then, in your program, to get the records you want from Table1, use ADO with SQL like:"
I follow what the line does, but WHERE do I put it? Does this go into a Form in the Form_Load section?
If you find a little time to expand on your info I would greatly appreciate it. Oh... and thanks for the link... I have already started reading.
|
|
|
|
|
i am an engg. student in cs.i want to develop a media on demand web application using vb.net language.
Details:
clients will be provided an gui through which they can select their choices, videos,music etc.options will be provided through which they will be allowed to become members,pay and watch videos , listen to songs etc.
based on their account balances,their permissions will be given.
the database backend is oracle.here, we will be maintaining the various tables. this is simple.
the problem is developing the frontend web application and doing the coding (handling events etc)on vb.net and asp.net as i am totally new to this platform.but i intend to learn that.so, i need help over here.
i have read many tutorials on vb.net.still,i am not able to start.so, if anyone out there has got some similar project already done, pliz help out.if the code is there, it would be of great help.
thanking u!!!!:
|
|
|
|
|
Hi,
If you go to this site:
http://www.wrox.com/WileyCDA/WroxTitle/productCd-0764543865,descCd-download_code.html[^]
You will be able to download code that accompanies an ASP.NET book.
I went through the book and it is pretty good. The sample code that you can download is a site called "The Phile" it is supposed to be a site of video/book/dvd enthusiasts to share information, data, messages, etc. I think it is pretty close, or at least a start for what you need.
It will be very frustrating (I think) without going through a begin to end book of some sort.
Good luck.
Thanks,
-Len Miller
"If I had eight hours to chop down a tree, I'd spend six sharpening my axe."
-Abraham Lincoln
|
|
|
|
|
I’m trying to get the lowlatitude, highlatidude and lowlongitude, highlongitude of an area given the zip code and the radius in miles.
This is what I’ve done but I think it is incorrect.
Anyone has a better calculation?
Thanks,
Martin
Sub SetRadius(ByVal iRadius, ByVal iZip)
Dim rs, SQL, iStartlat, iStartlong, LatRange, LongRange
SQL = "SELECT LATITUDE, LONGITUDE FROM ZIP_CODES WHERE ZIP = '" & iZip & "'"
Dim strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\PROJECT\zipbase.mdb"
Dim Conn = New OleDbConnection(strConn)
Conn.open()
Dim cmdSelect As New OleDbCommand(SQL, Conn)
Dim dr As OleDbDataReader
dr = cmdSelect.ExecuteReader()
While dr.Read()
iStartlat = dr(0)
iStartlong = dr(1)
End While
Conn.Close()
LatRange = iRadius / ((6076 / 5280) * 60)
LongRange = iRadius / (((Cos(CDbl(iStartlat * 3.141592653589 / 180)) * 6076) / 5280) * 60)
LowLatitude = iStartlat - LatRange
HighLatitude = iStartlat + LatRange
LowLongitude = iStartlong - LongRange
HighLongitude = iStartlong + LongRange
end sub
|
|
|
|
|
Ok, I’m not the seasoned programmer but you are at least talking in terms that I am very familiar with. If I read everything correctly, you have center latitude, center longitude, and a radius in miles. I am assuming the following; your Latitude and Longitude are in degree format, i.e. +/- 30.5 and your radius is in statute miles. Since I am use to doing all my calculation in Nautical Miles, I would convert the radius first:
RadiusNM = iRadius / 1.15077945 (1 NM = 1.15077945 statute mile)
Since there are 60 nautical miles in a degree, I would convert the RadiusNM into degree format so it is in the same format as my other data.
RadiusD = RadiusNM / 60
Now that the radius is in nautical terms in degree format, you can simple add and subtract it from your center latitude to get the High / Low latitudes.
HighLatitude = CenterLatitude + RadiusD
LowLatitude = Centerlatitude – RadiusD
For longitude you will need to multiply the distance of your radius in nautical miles by the COS of the center latitude.
HighLongitude = CenterLongitude + (RadiusD * COS(CenterLatitude))
LowLongitude = CenterLongitude - (RadiusD * COS(CenterLatitude))
|
|
|
|
|
Thank you for your reply. I tried your suggestion and I got the same result. I think that my zip code database is incorrect. Here is where I got the DB from : http://www.cfdynamics.com/zipbase/
If I try zip = 33182 and miles = 1 I get 71 zip codes. And if I try zip = 33182 and miles = 12 I also get 71 zip codes.
This is how I get the zip codes:
Private Sub getZip(ByVal LowLatitude As String, ByVal LowLongitude As String, ByVal HighLatitude As String, ByVal HighLongitude As String)
If (LowLongitude.Substring(3, 1) = ".") Then LowLongitude = "-0" + LowLongitude.Substring(1, LowLongitude.Length - 1)
If (HighLongitude.Substring(3, 1) = ".") Then HighLongitude = "-0" + HighLongitude.Substring(1, HighLongitude.Length - 1)
Dim sql = "Select ZIP FROM ZIP_CODES WHERE latitude > '+" + LowLatitude + "' and latitude < '+" + HighLatitude + "' and longitude < '" + LowLongitude + "' and longitude > '" + HighLongitude + "';"
Dim strConn As String
Dim strSQL As String
Dim Conn As OleDbConnection
Dim objDA As OleDbDataAdapter
Dim objDS As New DataSet()
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\PROJECT\zipbase.mdb"
Conn = New OleDbConnection(strConn)
Try
Conn.Open()
objDA = New OleDbDataAdapter(sql, Conn)
objDA.Fill(objDS, "ZIP_CODES")
Catch ex As Exception
MsgBox(ex.Message)
End Try
Conn.Close()
ComboBox1.DisplayMember = "ZIP"
ComboBox1.ValueMember = "ZIP"
ComboBox1.DataSource = objDS.Tables("ZIP_CODES")
MsgBox(ComboBox1.Items.Count.ToString) ‘display the # of zip found
End Sub
THANKS AGAIN,
MARTIN
|
|
|
|
|
Do you know where I can get another DB just like the one I'm using?
|
|
|
|
|
Have you stoped in the middle of this and checked to see what High / low Latitude and Longitude you are sending in the SQL statement. Since you know what the Latitude and Longitude of the zip is (33182 Lat 25.7802 & Long -80.4182), and you know what the distance is, you should know what the high and lows should be (High lat 25.7946 and low lat 25.76571). Seems pretty simple... BTW... Miami has 134 records that come up if you check for zip codes at zip-codes.com.
|
|
|
|
|
I did stop and checked the high and low Latitude/Longitude and all 4 look fine, but I get the same result with a radius of 1 mile and a radius of 12 miles. I think the DB I'm using is wrong. Do you know where I can find another zip code DB?
Thanks again,
Martin
|
|
|
|
|
Hey all,
Just wondering how to go about accessing and reading data from a remote (on a webserver) xml file. Thanks
|
|
|
|
|
Look for information on how to create xml web services. An xml web service is just another class library (dll) that resides in a remote web server. All the methods declared in the xml web service return xml formatted data.
After you create a web service dll and place it in a remote web server, you can create windows app and when you right click on "References" on solution explorer, select "Add Web Reference" and just set the reference to the xml service you created (similar to setting reference to a dll in your hard drive).
After you set a reference to your xml web service, you should be able to create an instance of any class in your web service and you should be able to call any methods that have [WebService] defined in front of the method. Note that you probably want to call methods asynchronously due to slow response over internet, so look for information in it. VS automatically creates methods that can be called asynchronously and you just pass a callback function to let you know when it's done.
|
|
|
|
|
Thanks for the help. I'll be looking into it. Cheers
|
|
|
|
|
Hi there,
I have problems with DSOFile 2.0 from Microsoft.
I have downloaded the component from the Microsoft Homepage and tried the sample (VB 6.0).
When I tried to change the document title of a document containing a title, everything worked fine.
When I tried to "create" the document title, I get an "access denied". With the same file within explorer I can set the document title and afterwards I can change it through DSOFile.
Is there any known restriction that I have to create the title manually to use DSOFile?
tia
Sebastian
|
|
|
|
|
You DO know this is a sample project to demonstrate how to use the IPropertySetStorage and IPropertyStorage interfaces, don't you? It's NOT production quality code, NOT supported, and NOT guaranteed to do anything!
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
Hi Dave,
yes I DO know that this is a sample, that it is not supported...
I think it should be ok to ask why a given sample does not work as expected because there are only a few methods so I dont have a chance to do something different...
I have not found any information on how to set the document title except this component and this is not working. Maybe I do something wrong but it would be nice if someone can tell me what to correct...
tia
Sebastian
|
|
|
|
|
I want to know that how to computer turn on Sleep and wake up mode using Visual Basic Program automatic.
|
|
|
|
|
I have two application. In the first one, I want to store values to the app.config file from my app textbox, how do I do in the app.config?
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<appSettings>
<add key="Server" value="" />
<add key="UserID" value="" />
<add key="Password" value="" />
</appSettings>
</configuration>
what should do in the value = "" ?
for example:
if the textbox1.text = "MyServerName", when the button click, then send the value auto sign to app.config value="MyServerName"...
After I install this app. in the target machine, install my second app. I need to read the three value from the app.config. Do I need to create another app.config for the second app or I can read from the same app.config? If I do can read from the same app.config, how should I code it in my second app?
Thanks All Your Help!
Lisa
|
|
|
|
|
|
how can I read tha app.config from other app. created? it created a menu.exe.config..how can I read it from another application?
Lisa
|
|
|
|
|
You cannot write a config file during runtim. You will want to serialize an object with your data to an XML file. You can do it like this:
1) Lets say you have an object called CustomSettings that has a bunch of properties that you want to save
2) You can set the appSettingsFullpath to something like:
appSettingsFullPath = String.Concat( _
Application.StartupPath, "\custom.settings.xml")
3) Then you can write your CustomSettings object out like this:
Dim writer As StreamWriter
' Write (serialize) XML app settings data
' Folder: <app executable="" bin="">
Try
writer = New StreamWriter(appSettingsFullPath)
Dim serializer As New XmlSerializer(GetType(CustomSettings))
serializer.Serialize(writer, Me.AppCustomSettings)
Catch ex As Exception
MessageBox.Show( _
String.Format("Cannot serialize XML settings object.{0}{0}{1}{0}{0}{2}" _
, Microsoft.VisualBasic.ControlChars.CrLf _
, ex.Message, ex.StackTrace) _
)
Finally
writer.Close()
End Try
4) You can de-serialize (read it) back into your object like this:
' Get (deserialize) XML app settings data
' Folder: <app executable="" bin="">
Try
Dim fileObj As File
If Not fileObj.Exists(appSettingsFullPath) Then
'** Decide what you need to do here
End If
fs = New FileStream(appSettingsFullPath, FileMode.Open)
Dim csSerializer As New XmlSerializer(GetType(CustomSettings))
'** you can add two event handlers below if you wish
' AddHandler csSerializer.UnknownNode, AddressOf csSerializer_UnknownNode
' AddHandler csSerializer.UnknownAttribute, AddressOf csSerializer_UnknownAttribute
Dim cs As SMS.CustomSettings
Me.AppCustomSettings = CType(csSerializer.Deserialize(fs), CustomSettings)
Catch ex As Exception
MessageBox.Show( _
String.Format("Cannot de-serialize XML settings object.{0}{0}{1}{0}{0}{2}" _
, Microsoft.VisualBasic.ControlChars.CrLf _
, ex.Message, ex.StackTrace) _
)
Finally
fs.Close()
End Try
I hope this helps!
Thanks,
-Len Miller
"If I had eight hours to chop down a tree, I'd spend six sharpening my axe."
-Abraham Lincoln
|
|
|
|
|
Hello all,
I am making a vb.net application which is a product to be distributed in CD with moneyback guarantee. Since the CD will be offered with a money back guarantee, there needs to be some type of copy protection on the CD or the ability to only use the CD on one specific computer etc. Otherwise the customer can purchase the CD, make a full functioning copy and ask for their money refunded. If i provide a serial number, Customer can make a cd copy and use the serial number even after claiming for refund. Please provide any suggestions either for preventing to make duplicate CD or provide any mechanism which should be used to make application to be run on one system only.
Thanks,
Amod Mishra
|
|
|
|
|
Ahh! The Copy Protection pipe dream again.
Ever notice you don't see people, of sound mind and body anyway, selling software with a money back guarantee? Ever wonder why?
No matter what copy protection scheme you come up with, it will always come down to a single IF statement to determine wheather your code will run or not. This IF statement can ALWAYS be hacked to get around your protection.
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
Dave Kreskowiak wrote:
it will always come down to a single IF statement to determine wheather your code will run or not
This brings a question to mind... I have just built a trial version of a PocketPC App that only allows the user to input two entries. How should I go about it to make it harder for someone to decompile my application and change the code and get a full version for free?
Thanks,
Mitch
My sig:
"And it is a professional faux pas to pay someone else to destroy your computer when you are perfectly capable of destroying it yourself." - Roger Wright
Get Perpendicular! (Hitachi Storage)
My CodeProject Blog
Most recent blog post: April 11
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
