Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

passing data to SQL table

Posted on 2004-09-23
9
Medium Priority
?
278 Views
Last Modified: 2011-09-20


Hi all,

i am extremely new to programming and have ben asked to build a screenscraper for some web data and then pass it to a SQL database from a progfram in C# - so far i have managed to get the sceen to scrape using some varying parameters but i am wondering how you pass strings across to a SQL 2k backend - i have created the connection to the database and have attached the code below to show what is working so far. Any help is much appreciated. This data also updates evry half hour so it needs to be added into the table and then rerun again without duplicating the data.


using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.IO;
using System.Net;
using System.Data.SqlClient;



namespace ScreenScrape
{
      /// <summary>
      /// Summary description for Form1.
      /// </summary>
      public class Form1 : System.Windows.Forms.Form
      {
            private System.Windows.Forms.Button btnGo;
            private System.Windows.Forms.Button btnStop;
            private System.Windows.Forms.TextBox textBox1;
            private System.Windows.Forms.Timer timer2;
            private System.ComponentModel.IContainer components;

            private System.Windows.Forms.Label label1;
            private System.Windows.Forms.Label label2;
            private System.Windows.Forms.Label label3;
            private System.Windows.Forms.Label label4;
            private System.Windows.Forms.Label label5;
            private System.Windows.Forms.TextBox txtSession;
            private System.Windows.Forms.TextBox txtFpnStart;
            private System.Windows.Forms.TextBox txtFpnFinish;
            private System.Windows.Forms.Label label6;
            private System.Windows.Forms.Label label8;
            private System.Windows.Forms.Label label9;
            private System.Windows.Forms.Label label7;
            private System.Windows.Forms.TextBox txtMelStart;
            private System.Windows.Forms.TextBox txtMelFinish;
            private System.Windows.Forms.TextBox txtTimeA;
            private System.Windows.Forms.TextBox txtTimeB;
            private System.Windows.Forms.Label label10;
            private System.Windows.Forms.TextBox txtDate1;
            private System.Windows.Forms.TextBox txtDate2;
            private System.Windows.Forms.Button button1;

            int myCounter = 0;


            public Form1()
            {
                  //
                  // Required for Windows Form Designer support
                  //
                  InitializeComponent();

                  //
                  // TODO: Add any constructor code after InitializeComponent call
                  //
                  

            }
            

            /// <summary>
            /// Clean up any resources being used.
            /// </summary>
            protected override void Dispose( bool disposing )
            {
                  if( disposing )
                  {
                        if (components != null)
                        {
                              components.Dispose();
                        }
                  }
                  base.Dispose( disposing );
            }

            #region Windows Form Designer generated code
            /// <summary>
            /// Required method for Designer support - do not modify
            /// the contents of this method with the code editor.
            /// </summary>
            private void InitializeComponent()
            {
                  this.components = new System.ComponentModel.Container();
                  this.btnGo = new System.Windows.Forms.Button();
                  this.btnStop = new System.Windows.Forms.Button();
                  this.textBox1 = new System.Windows.Forms.TextBox();
                  this.timer2 = new System.Windows.Forms.Timer(this.components);
                  this.label1 = new System.Windows.Forms.Label();
                  this.label2 = new System.Windows.Forms.Label();
                  this.label3 = new System.Windows.Forms.Label();
                  this.label4 = new System.Windows.Forms.Label();
                  this.label5 = new System.Windows.Forms.Label();
                  this.txtDate1 = new System.Windows.Forms.TextBox();
                  this.txtSession = new System.Windows.Forms.TextBox();
                  this.txtFpnStart = new System.Windows.Forms.TextBox();
                  this.txtFpnFinish = new System.Windows.Forms.TextBox();
                  this.txtMelStart = new System.Windows.Forms.TextBox();
                  this.txtMelFinish = new System.Windows.Forms.TextBox();
                  this.label8 = new System.Windows.Forms.Label();
                  this.label9 = new System.Windows.Forms.Label();
                  this.txtTimeA = new System.Windows.Forms.TextBox();
                  this.txtTimeB = new System.Windows.Forms.TextBox();
                  this.label6 = new System.Windows.Forms.Label();
                  this.label7 = new System.Windows.Forms.Label();
                  this.txtDate2 = new System.Windows.Forms.TextBox();
                  this.label10 = new System.Windows.Forms.Label();
                  this.button1 = new System.Windows.Forms.Button();
                  this.SuspendLayout();
                  //
                  // btnGo
                  //
                  this.btnGo.Location = new System.Drawing.Point(16, 56);
                  this.btnGo.Name = "btnGo";
                  this.btnGo.TabIndex = 0;
                  this.btnGo.Text = "Go";
                  this.btnGo.Click += new System.EventHandler(this.clickGo);
                  //
                  // btnStop
                  //
                  this.btnStop.Location = new System.Drawing.Point(16, 88);
                  this.btnStop.Name = "btnStop";
                  this.btnStop.TabIndex = 1;
                  this.btnStop.Text = "Stop";
                  this.btnStop.Click += new System.EventHandler(this.clickStop);
                  //
                  // textBox1
                  //
                  this.textBox1.Location = new System.Drawing.Point(32, 336);
                  this.textBox1.Multiline = true;
                  this.textBox1.Name = "textBox1";
                  this.textBox1.ReadOnly = true;
                  this.textBox1.Size = new System.Drawing.Size(368, 40);
                  this.textBox1.TabIndex = 2;
                  this.textBox1.Text = "";
                  //
                  // timer2
                  //
                  this.timer2.Interval = 5000;
                  this.timer2.Tick += new System.EventHandler(this.timerTick);
                  //
                  // label1
                  //
                  this.label1.Location = new System.Drawing.Point(8, 16);
                  this.label1.Name = "label1";
                  this.label1.Size = new System.Drawing.Size(552, 23);
                  this.label1.TabIndex = 3;
                  this.label1.Text = "BM_reports_scraper";
                  //
                  // label2
                  //
                  this.label2.Location = new System.Drawing.Point(112, 64);
                  this.label2.Name = "label2";
                  this.label2.TabIndex = 4;
                  this.label2.Text = "Date-start";
                  //
                  // label3
                  //
                  this.label3.CausesValidation = false;
                  this.label3.Location = new System.Drawing.Point(152, 97);
                  this.label3.Name = "label3";
                  this.label3.TabIndex = 5;
                  this.label3.Text = "half hour";
                  //
                  // label4
                  //
                  this.label4.Location = new System.Drawing.Point(152, 130);
                  this.label4.Name = "label4";
                  this.label4.TabIndex = 6;
                  this.label4.Text = "fpn-start";
                  //
                  // label5
                  //
                  this.label5.Location = new System.Drawing.Point(152, 163);
                  this.label5.Name = "label5";
                  this.label5.TabIndex = 7;
                  this.label5.Text = "fpn-finish";
                  //
                  // txtDate1
                  //
                  this.txtDate1.Location = new System.Drawing.Point(240, 64);
                  this.txtDate1.Name = "txtDate1";
                  this.txtDate1.Size = new System.Drawing.Size(88, 20);
                  this.txtDate1.TabIndex = 8;
                  this.txtDate1.Text = "";
                  this.txtDate1.TextChanged += new System.EventHandler(this.txtDate1_TextChanged);
                  //
                  // txtSession
                  //
                  this.txtSession.Location = new System.Drawing.Point(312, 96);
                  this.txtSession.Name = "txtSession";
                  this.txtSession.Size = new System.Drawing.Size(144, 20);
                  this.txtSession.TabIndex = 9;
                  this.txtSession.Text = "";
                  this.txtSession.TextChanged += new System.EventHandler(this.txtSession_TextChanged);
                  //
                  // txtFpnStart
                  //
                  this.txtFpnStart.Location = new System.Drawing.Point(312, 128);
                  this.txtFpnStart.Name = "txtFpnStart";
                  this.txtFpnStart.Size = new System.Drawing.Size(144, 20);
                  this.txtFpnStart.TabIndex = 10;
                  this.txtFpnStart.Text = "";
                  this.txtFpnStart.TextChanged += new System.EventHandler(this.txtFpnStart_TextChanged);
                  //
                  // txtFpnFinish
                  //
                  this.txtFpnFinish.Location = new System.Drawing.Point(312, 160);
                  this.txtFpnFinish.Name = "txtFpnFinish";
                  this.txtFpnFinish.Size = new System.Drawing.Size(144, 20);
                  this.txtFpnFinish.TabIndex = 11;
                  this.txtFpnFinish.Text = "";
                  this.txtFpnFinish.TextChanged += new System.EventHandler(this.txtFpnFinish_TextChanged);
                  //
                  // txtMelStart
                  //
                  this.txtMelStart.Location = new System.Drawing.Point(312, 200);
                  this.txtMelStart.Name = "txtMelStart";
                  this.txtMelStart.Size = new System.Drawing.Size(144, 20);
                  this.txtMelStart.TabIndex = 12;
                  this.txtMelStart.Text = "";
                  //
                  // txtMelFinish
                  //
                  this.txtMelFinish.Location = new System.Drawing.Point(312, 232);
                  this.txtMelFinish.Name = "txtMelFinish";
                  this.txtMelFinish.Size = new System.Drawing.Size(144, 20);
                  this.txtMelFinish.TabIndex = 13;
                  this.txtMelFinish.Text = "";
                  //
                  // label8
                  //
                  this.label8.Location = new System.Drawing.Point(152, 196);
                  this.label8.Name = "label8";
                  this.label8.TabIndex = 14;
                  this.label8.Text = "mel-start";
                  //
                  // label9
                  //
                  this.label9.Location = new System.Drawing.Point(152, 229);
                  this.label9.Name = "label9";
                  this.label9.TabIndex = 15;
                  this.label9.Text = "mel-finish";
                  this.label9.Click += new System.EventHandler(this.label9_Click);
                  //
                  // txtTimeA
                  //
                  this.txtTimeA.Location = new System.Drawing.Point(136, 288);
                  this.txtTimeA.Name = "txtTimeA";
                  this.txtTimeA.TabIndex = 16;
                  this.txtTimeA.Text = "";
                  this.txtTimeA.TextChanged += new System.EventHandler(this.TimeA_TextChanged);
                  //
                  // txtTimeB
                  //
                  this.txtTimeB.Location = new System.Drawing.Point(392, 288);
                  this.txtTimeB.Name = "txtTimeB";
                  this.txtTimeB.TabIndex = 17;
                  this.txtTimeB.Text = "";
                  //
                  // label6
                  //
                  this.label6.Location = new System.Drawing.Point(16, 288);
                  this.label6.Name = "label6";
                  this.label6.TabIndex = 18;
                  this.label6.Text = "Start time";
                  //
                  // label7
                  //
                  this.label7.Location = new System.Drawing.Point(264, 288);
                  this.label7.Name = "label7";
                  this.label7.TabIndex = 19;
                  this.label7.Text = "end time";
                  //
                  // txtDate2
                  //
                  this.txtDate2.Location = new System.Drawing.Point(472, 64);
                  this.txtDate2.Name = "txtDate2";
                  this.txtDate2.Size = new System.Drawing.Size(88, 20);
                  this.txtDate2.TabIndex = 20;
                  this.txtDate2.Text = "";
                  //
                  // label10
                  //
                  this.label10.Location = new System.Drawing.Point(352, 64);
                  this.label10.Name = "label10";
                  this.label10.TabIndex = 21;
                  this.label10.Text = "Date-end";
                  this.label10.Click += new System.EventHandler(this.label10_Click);
                  //
                  // button1
                  //
                  this.button1.Location = new System.Drawing.Point(16, 128);
                  this.button1.Name = "button1";
                  this.button1.TabIndex = 22;
                  this.button1.Text = "DB Form";
                  this.button1.Click += new System.EventHandler(this.button1_Click);
                  //
                  // Form1
                  //
                  this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
                  this.ClientSize = new System.Drawing.Size(576, 398);
                  this.Controls.AddRange(new System.Windows.Forms.Control[] {
                                                                                                              this.button1,
                                                                                                              this.label10,
                                                                                                              this.txtDate2,
                                                                                                              this.label7,
                                                                                                              this.label6,
                                                                                                              this.txtTimeB,
                                                                                                              this.txtTimeA,
                                                                                                              this.label9,
                                                                                                              this.label8,
                                                                                                              this.txtMelFinish,
                                                                                                              this.txtMelStart,
                                                                                                              this.txtFpnFinish,
                                                                                                              this.txtFpnStart,
                                                                                                              this.txtSession,
                                                                                                              this.txtDate1,
                                                                                                              this.label5,
                                                                                                              this.label4,
                                                                                                              this.label3,
                                                                                                              this.label2,
                                                                                                              this.label1,
                                                                                                              this.textBox1,
                                                                                                              this.btnStop,
                                                                                                              this.btnGo});
                  this.Name = "Form1";
                  this.Text = "Form1";
                  this.Load += new System.EventHandler(this.Form1_Load);
                  this.ResumeLayout(false);

            }
            #endregion

            /// <summary>
            /// The main entry point for the application.
            /// </summary>
            [STAThread]
            static void Main()
            {
                  Application.Run(new Form1());
                  
            }

            private string GetHtmlPage(string strURL)
            {
                  // the html retrieved from the page
                  string strResult;
                  
                  WebResponse objResponse;
                  WebRequest objRequest = System.Net.HttpWebRequest.Create(strURL);
                  objResponse = objRequest.GetResponse();
                  // the using keyword will automatically dispose the object
                  // once complete
                  using (StreamReader sr =
                                 new StreamReader(objResponse.GetResponseStream()))
                  {
                        strResult = sr.ReadToEnd();
                        // Close and clean up the StreamReader
                        sr.Close();
                  }
                  return strResult;
            }

            private void clickGo(object sender, System.EventArgs e)
            {
                  textBox1.Text = "starting...please wait...";
                  myCounter = 1;
                  timer2.Start();
            }

            private void clickStop(object sender, System.EventArgs e)
            {
                  textBox1.Text = "Stopped";
                  timer2.Stop();
            }

            private void timerTick(object sender, System.EventArgs e)
            {
                  string theUrl="http://www.bmreports.com/servlet/com.logica.neta.bwp_LatestBmDataServlet?param1=";

                  if(myCounter == 1) theUrl += "T_FERR-2";
                  if(myCounter == 2) theUrl += "T_FERR-3";
                  if(myCounter == 3) theUrl += "T_FERR-4";

                  theUrl += "&param2=&param3=";
                  
                  label1.Text = theUrl;

                  string htmlSrc = GetHtmlPage(theUrl);

                  textBox1.Text = htmlSrc;

                  if(htmlSrc.IndexOf("axis start sett date") > 0)
                  {
                        string MyDateA = htmlSrc.Substring((htmlSrc.IndexOf("series time 1") + 22), 10);
                        txtDate1.Text = MyDateA;
                  }
                  if(htmlSrc.IndexOf("axis start sett date") > 0)
                  {
                        string MyDateB = htmlSrc.Substring((htmlSrc.IndexOf("series time 1") + 39), 10);
                        txtDate2.Text = MyDateB;
                  }

                  if(htmlSrc.IndexOf("axis min period") > 0)
                  {
                        string Session = htmlSrc.Substring((htmlSrc.IndexOf("axis min period") + 24), 2);
                        txtSession.Text = Session;
                  }

                  if(htmlSrc.IndexOf("series level 1") > 0)
                  {
                        string FpnStart = htmlSrc.Substring((htmlSrc.IndexOf("series level 1") + 23), 7);
                        txtFpnStart.Text = FpnStart;
                  
                        if (htmlSrc.IndexOf("series level 1") > 0)
                        {
                              string FpnFinish = htmlSrc.Substring((htmlSrc.IndexOf("series level 1") + 31), 7);
                              txtFpnFinish.Text = FpnFinish;
                        }
                        if (htmlSrc.IndexOf("series level 3") > 0)
                        {
                              string MelStart = htmlSrc.Substring((htmlSrc.IndexOf("series level 3") + 23), 7);
                              txtMelStart.Text = MelStart;
                        }
                        if (htmlSrc.IndexOf("series level 3") > 0)
                        {
                              string MelFinish = htmlSrc.Substring((htmlSrc.IndexOf("series level 3") + 31), 7);
                              txtMelFinish.Text = MelFinish;
                        }
                        if (htmlSrc.IndexOf("series time 1") > 0)
                        {
                              string TimeA = htmlSrc.Substring((htmlSrc.IndexOf("series time 1") + 33), 5);
                              txtTimeA.Text = TimeA;
                        }
                        if (htmlSrc.IndexOf("series time 1") > 0)
                        {
                              string TimeB= htmlSrc.Substring((htmlSrc.IndexOf("series time 1") + 50), 5);
                              txtTimeB.Text = TimeB;
                        }
                        htmlSrc = "";
                        myCounter++;
                        if(myCounter > 3) myCounter = 1;
0
Comment
Question by:redmayne
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
9 Comments
 
LVL 3

Expert Comment

by:mAjKoL
ID: 12131361
Make a Stored Procedure in the SQL Server to insert data you need.
Than make a typed DataSet in VS. ( Create empty DataSet, Drag your stroed proc from Server Explorer and drop it on the Design window of your new DataSet)
Make a DataAdapter with your SP set to UPDATE.
than invoke DataAdapter's Update method passind the DataSet.

HTH
0
 
LVL 6

Expert Comment

by:Chester_M_Ragel
ID: 12131422
This is not a good programming practice. Anyway, I'll follow you. If you have database, datatable with field to have data, you can just do something like this to add data.

SqlCommand command = new SqlCommand(("Insert into [tablename] [(columnname)] values('"+yourString+"')"),connection);

Similarly you can update using the correct syntax.
0
 
LVL 3

Expert Comment

by:mAjKoL
ID: 12131477
And you think that inline queries and string concatanation are good ideas ?!?
Yes - you can skip the part with the DataSet, and you can invoke your SP from SQLCommand, but you have to use SQLParameter.
And this is the first time I hear that making SP-s isn't a good programming practise.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:redmayne
ID: 12131481
so from your answer would tthe following be a valid piece of code ?

SqlCommand command = new SqlCommand(("Insert [BMreports_data04][DateStart](MyDateA)"));
                        

0
 

Author Comment

by:redmayne
ID: 12131508
from the code i have above the strings i used are all there could someone give me an example of how to do this - like i said i am a compelte novice to programming - this is my first ever program!!!!!!!

as for SQL ibarely managed to create the table so any simplistic steps aer very welcome
0
 
LVL 3

Expert Comment

by:mAjKoL
ID: 12131600
If you cant't create a Stored Proc you can do sth like that:

SqlConnection conn = new SqlConnection("<your connection string>");
SqlCommand command = new SqlCommand("INSERT INTO [BMreports_data04](DateStart) VALUES (@myDateA)");
SqlParameter param = new SqlParameter("@myDateA", System.Data.SqlDbType.DateTime); //the second should be the type of the column in DB
param.Value = MyDateA;
command.Parameters.Add(param);
command.Connection = conn;
conn.Open();
command.ExecuteNonQuery();
command.Connection.Close();

You can add as many parameters as you need.
HTH
0
 

Author Comment

by:redmayne
ID: 12131618
i think that helps a lot
so by creating say 8 of these replacing MyDateA for B etc... then it should run and everytime it receives a paramater value for say Mydate A,B then it will place it in the correct column right?

if so thats spot on...
Andy
0
 
LVL 3

Expert Comment

by:mAjKoL
ID: 12131628
SqlCommand command = new SqlCommand("INSERT INTO [BMreports_data04](DateStart, OtherColumn, SomeOtherColumn) VALUES (@myDateA, @otherParam, @someOtherParam)");
if you want to insert more values to the same row, and than define 3 params, assign them a Value, an add them to the command
0
 
LVL 3

Accepted Solution

by:
mAjKoL earned 2000 total points
ID: 12131660
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

650 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