July 25, 2017 at 12:46 pm
I'm currently working on a project in Visual Studio 2015 along with SQL server 2014, with the goal of connecting to a web interface and pulling data from that interface to be displayed in a central location.
I have successfully connected to the interface using HTTP WebRequest in VBA, but am really struggling with the SQL code side of things to query the data.
The table I need to pull from is called "Intervals" (minus the quotation marks). I know that the table has two rows in it, but am unsure of the number of columns. Is there a way for me to query this entire table since I don't know the entire structure of the table nor the names of the columns? How would I go about writing that query? Can I store those results directly to a VBA variable? Or do I need to store those results in a database before storing it to a variable? The Primary purpose of this project is to display data from several devices to a central location - Only real time data will be posted and there will never be a time that I need to refer back to historical data.
Thanks in advance and I hope I was clear enough on what I am looking to do. If you need me to elaborate on anything or check any of the XML on the web interface that I am pulling from, let me know and I will post that back.
July 25, 2017 at 12:51 pm
rowe12david - Tuesday, July 25, 2017 12:46 PMI'm currently working on a project in Visual Studio 2015 along with SQL server 2014, with the goal of connecting to a web interface and pulling data from that interface to be displayed in a central location.I have successfully connected to the interface using HTTP WebRequest in VBA, but am really struggling with the SQL code side of things to query the data.
The table I need to pull from is called "Intervals" (minus the quotation marks). I know that the table has two rows in it, but am unsure of the number of columns. Is there a way for me to query this entire table since I don't know the entire structure of the table nor the names of the columns? How would I go about writing that query? Can I store those results directly to a VBA variable? Or do I need to store those results in a database before storing it to a variable? The Primary purpose of this project is to display data from several devices to a central location - Only real time data will be posted and there will never be a time that I need to refer back to historical data.
Thanks in advance and I hope I was clear enough on what I am looking to do. If you need me to elaborate on anything or check any of the XML on the web interface that I am pulling from, let me know and I will post that back.
SELECT * from table
returns all data and all columns. As far as I can remember, you can process the resulting recordset in VBA without needing to store them anywhere else, but it's been a while since I did that.
July 25, 2017 at 2:14 pm
Sounds good, I appreciate the prompt reply. I will play around with this some and hopefully this will resolve my problem. Thanks again
July 27, 2017 at 1:15 pm
The way you worded your original post sounds odd. Why would you expect to run a query against a web service using SQL Server? If the web service doesn't support ODBC connectivity, you're not going to get a connection going there. However, if you already have VBA code that makes the connection, you could certainly write the data from the web service to a SQL Server database, and then query that database from within your VBA code using ADO. What you do with that data after querying it into a recordset is then entirely up to you.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 28, 2017 at 1:23 pm
sgmunson - Thursday, July 27, 2017 1:15 PMThe way you worded your original post sounds odd. Why would you expect to run a query against a web service using SQL Server? If the web service doesn't support ODBC connectivity, you're not going to get a connection going there. However, if you already have VBA code that makes the connection, you could certainly write the data from the web service to a SQL Server database, and then query that database from within your VBA code using ADO. What you do with that data after querying it into a recordset is then entirely up to you.
The device that I am connecting to has a web based interface, which has data accessible only by use of LESQL. I need to query the data from the interface, store it as a VB variable, then display it in a central connection. As of now, I still have not successfully achieved what I wanted to.
July 28, 2017 at 1:45 pm
rowe12david - Friday, July 28, 2017 1:23 PMsgmunson - Thursday, July 27, 2017 1:15 PMThe way you worded your original post sounds odd. Why would you expect to run a query against a web service using SQL Server? If the web service doesn't support ODBC connectivity, you're not going to get a connection going there. However, if you already have VBA code that makes the connection, you could certainly write the data from the web service to a SQL Server database, and then query that database from within your VBA code using ADO. What you do with that data after querying it into a recordset is then entirely up to you.The device that I am connecting to has a web based interface, which has data accessible only by use of LESQL. I need to query the data from the interface, store it as a VB variable, then display it in a central connection. As of now, I still have not successfully achieved what I wanted to.
So what happened when you tried SELECT * FROM TABLE? Did it execute? Did you get an error?
July 28, 2017 at 1:54 pm
Right now Im getting an error pointing to the SQL connection string and am working on trying to find the root cause of that (I currently have access to the web stream, but apparently I found out that an additional SQL connection string is required too). As of now, I haven't got the code to be able to run far enough to execute the SELECT * FROM INTERVALS statement.
July 28, 2017 at 2:03 pm
rowe12david - Friday, July 28, 2017 1:54 PMRight now Im getting an error pointing to the SQL connection string and am working on trying to find the root cause of that (I currently have access to the web stream, but apparently I found out that an additional SQL connection string is required too). As of now, I haven't got the code to be able to run far enough to execute the SELECT * FROM INTERVALS statement.
Are you able to get some support from the manufacturers of the 'device' (whatever it is)?
July 31, 2017 at 5:31 am
Phil Parkin - Friday, July 28, 2017 2:03 PMrowe12david - Friday, July 28, 2017 1:54 PMRight now Im getting an error pointing to the SQL connection string and am working on trying to find the root cause of that (I currently have access to the web stream, but apparently I found out that an additional SQL connection string is required too). As of now, I haven't got the code to be able to run far enough to execute the SELECT * FROM INTERVALS statement.Are you able to get some support from the manufacturers of the 'device' (whatever it is)?
Unfortunately I can't for some reason. I tried reaching out to a guy at the company, who was helping me before, and apparently he doesn't work there anymore. When I tried speaking to someone else, that said there wasn't anyone else there who could help.
I'll get back to playing with it some more today and see where I get.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply