Keskustelu: Ohjelmointikysymykset: VBA: Kotisivun tallennus

Imports System.Configuration

Public Partial Class MainForm

   Dim config As Configuration = _
   ConfigurationManager.OpenExeConfiguration( _
   ConfigurationUserLevel.None)
   Dim strUri As String

   Public Sub New()

      Me.InitializeComponent()

   End Sub

   Sub MainFormLoad(sender As Object, e As EventArgs)

      If config.AppSettings.Settings.Item( _
      "Nykyinensivu").Value = "" Then
         config.AppSettings.Settings.Item( _
         "Nykyinensivu").Value = "https://www.ohjelmointiputka.net" 'esim.
         config.Save(ConfigurationSaveMode.Modified)
      End If

      WebBrowser1.Url = New Uri( _
      config.AppSettings.Settings.Item("Nykyinensivu").Value)

   End Sub

   Sub WebBrowser1DocumentCompleted(sender As Object, _
   e As WebBrowserDocumentCompletedEventArgs)
      config.AppSettings.Settings.Item( _
      "Nykyinensivu").Value = WebBrowser1.Url.ToString
      config.Save(ConfigurationSaveMode.Modified)
   End Sub

End Class

'SqlServer testitietokata & taulun rakenne

DataBase
Name: LoginBase

Table
Name: LoginData

Columns:

Name: num (PrimaryKey)
Data Type: int
Identity Specification: Yes
(Is Identity): Yes
Identity Increment: 1
Identity Seed: 1
Allow Nulls: False

Name: username
Data Type: varchar(25)
Allow Nulls: False

Name: password
Data Type: varchar(16)
Allow Nulls: False
Name: setting
Data Type: varchar(200)
Allow Nulls: True

Imports System.Data
Imports System.Data.SqlClient

Public Partial Class MainForm

   Private conn As New SqlConnection()
   Private  IsChanged As Boolean = False

   Public Sub New()

      Me.InitializeComponent()

   End Sub

   Sub MainForm_Load(sender As Object, e As EventArgs)

      For i = 1 To 2
         Dim ctl As TextBox = _
         CType(Me.Controls("textBox" & CStr(i)),TextBox)
         ctl.MaxLength = 16
         ctl.PasswordChar = CType("*", Char)
         ctl.Visible = False
      Next

      button1.Text = "Muuta"
      button2.Text = "Testi"

      Dim frmLogin As New LoginForm
      Me.Hide
      frmLogin.ShowDialog
      Me.Show

      conn.ConnectionString = _
      "Data Source=.\SQLEXPRESS;" + _
      "Initial Catalog=LoginBase;Trusted_Connection=Yes"

   End Sub

   Sub Button1_Click(sender As Object, e As EventArgs)

      Dim ctl As TextBox = Nothing

      If button1.Text  = "Muuta" Then

         For i As Integer = 2 To 1 Step -1
            ctl = CType(Me.Controls("textBox" & CStr(i)), TextBox)
            ctl.Visible = True
         Next

         button1.Text = "OK": ctl.Focus
         ctl = Nothing: Exit Sub

      ElseIf button1.Text = "OK" Then

         Dim tagStr As String = String.Empty

         For i As Integer = 1 To 2

            ctl =  CType(Me.Controls("textBox" & CStr(i)), TextBox)
            If ctl.Text = String.Empty
               ctl.Focus: ctl = Nothing: Exit Sub
            End If

            If tagStr <> String.Empty And ctl.Text <> tagStr Then

               MsgBox("Salasana ja salasanan varmistus eivät täsmää!")
               For j As Integer = 2 To 1 Step -1
                  ctl =  CType(Me.Controls("textBox" & CStr(j)), TextBox)
                  ctl.Clear
               Next j

               ctl.Focus: ctl = Nothing: Exit Sub

            End If

            tagStr = ctl.Text

         Next i

         conn.Open

         Dim query As String = _
         "SELECT * FROM [LoginData] where username='" _
         + LoginData(0) + "' And password='" + LoginData(1) + "'"
         Dim da As SqlDataAdapter = New SqlDataAdapter(query , conn)
         Dim ds As New DataSet: da.Fill(ds, "LoginData")
         Dim cb As SqlCommandBuilder = New SqlCommandBuilder(da)
         ds.Tables("LoginData").Rows(0)(2) = textBox1.Text
         da.Fill(ds, "LoginData"): da.Update(ds, "LoginData")
         LoginData = New String(){ _
         CType(ds.Tables("LoginData").Rows(0)(1), String) , _
         CType(ds.Tables("LoginData").Rows(0)(2), String)}

         ds = Nothing: da = Nothing: cb = Nothing

         conn.Close

         For i As Integer = 1 To 2
            ctl.Clear
            ctl = CType(Me.Controls("textBox" & CStr(i)), TextBox)
            ctl.Visible = False
         Next

         ctl = Nothing: button1.Text = "Muuta"

      End If

   End Sub

   Sub Button2_Click(sender As Object, e As EventArgs)

      Dim msg As Integer = _
      MsgBox("Muutetaanko asetusta", _
      vbYesNo, "Nykyinen asetus on: " + MyAppSetting)

      If msg = 6 Then
         MyAppSetting = InputBox("Uusi arvo: ", "Asetukset")
         IsChanged = True
      End If

   End Sub

   Private m_Logindata() As String
   Public Property LoginData As String()

      Get
         LoginData = m_Logindata
      End Get

      Set(value As String())
         m_Logindata = value
      End Set

   End Property

   Private m_MyAppSettingData As String
   Public Property MyAppSetting As String

      Get
          MyAppSetting = m_MyAppSettingData
      End Get

      Set(value As String)
         m_MyAppSettingData = value
      End Set

   End Property

   Sub MainForm_FormClosing(sender As Object, e As FormClosingEventArgs)

      If IsChanged Then

         conn.Open

         Dim query As String = _
         "SELECT * FROM [LoginData] where username='" _
         + LoginData(0) + "' And password='" + LoginData(1) + "'"
         Dim da As SqlDataAdapter = New SqlDataAdapter(query , conn)
         Dim ds As New DataSet: da.Fill(ds, "LoginData")
         Dim cb As SqlCommandBuilder = New SqlCommandBuilder(da)
         ds.Tables("LoginData").Rows(0)(3) = MyAppSetting
         da.Fill(ds, "LoginData"): da.Update(ds, "LoginData")
         ds = Nothing: da = Nothing: cb = Nothing

         conn.Close

      End If

      conn = Nothing

   End Sub

End Class

Imports System.Data
Imports System.Data.SqlClient

Public Partial Class LoginForm

   Private conn As New SqlConnection()
   Private Logged As Boolean = False

   Public Sub New()

      Me.InitializeComponent()

   End Sub

   Sub LoginForm_Load(sender As Object, e As EventArgs)

      textBox1.MaxLength = 25
      textBox2.MaxLength = 16
      textBox2.PasswordChar = CType("*", Char)
      button1.Text = "OK"

      conn.ConnectionString = _
      "Data Source=.\SQLEXPRESS;" + _
      "Initial Catalog=LoginBase;" + _
      "Trusted_Connection=Yes"

      conn.Open

   End Sub

   Sub Button1_Click(sender As Object, e As EventArgs)

      If textBox1.Text = String.Empty Then
         textBox1.Focus: Exit Sub
      ElseIf textBox2.Text = String.Empty
         textBox2.Focus: Exit Sub
      End If

      Static cnt As Integer
      cnt += 1

      Dim query As String = _
      "SELECT username, password,  FROM [LoginData] where username='" _
      + textBox1.Text + "' And password='" + textBox2.Text + "'"
      Dim da As SqlDataAdapter = New SqlDataAdapter(query , conn)
      Dim ds As New DataSet: da.Fill(ds, "LoginData")

      If ds.Tables("LoginData").Rows.Count = 1 Then

         MainForm.LoginData = New String(){ _
         CType(ds.Tables("LoginData").Rows(0)(0), String) , _
         CType(ds.Tables("LoginData").Rows(0)(1), String)}
         MainForm.MyAppSetting = _
         CType(ds.Tables("LoginData").Rows(0)(2), String)
         Logged = True

      End If

      ds = Nothing: da = Nothing

      If Not Logged And cnt < 3 Then
         textBox1.Clear: textBox2.Clear
         MsgBox("käyttäjätunnus/salasana on väärä!")
         textBox1.Focus: Exit Sub
      ElseIf Not Logged And cnt = 3 Then
         textBox1.Clear: textBox2.Clear
         MsgBox("käyttäjätunnus/salasana on annettu kolmasti väärin" + _
               " - Ohjelma suljetaan...")
      End If

      Me.Close

   End Sub

   Sub LoginForm_FormClosin(sender As Object, e As FormClosingEventArgs)

      conn.Close
      If Not Logged Then End

   End Sub

End Class