Imports System.Data
Imports System.Data.SqlClient
Partial Class _Default
    Inherits System.Web.UI.Page
    Dim constr As String = "workstation ID = pvfc_db26.mssql.somee.com;packet size=4096;user id=pvfc_login;pwd=pvfc_login;data source=pvfc_db26.mssql.somee.com;persist security info=False;initial catalog=pvfc_db26;TrustServerCertificate=True"
    Private con As New SqlConnection(constr)

    Protected Sub connect() Handles submit_reg.Click

        Dim stateLook As New Dictionary(Of String, String) From {
            {"Gainesville", "FL"},
            {"Plano", "TX"},
            {"Albany", "NY"},
            {"Carteret", "NJ"},
            {"Sacramento", "CA"},
            {"Boulder", "CO"},
            {"Seattle", "WA"},
            {"Santa Clara", "CA"},
            {"Clearwater", "FL"},
            {"Seminole", "FL"},
            {"Prospect Park", "NJ"},
            {"Battle Creek", "MI"},
            {"Carlisle", "PA"},
            {"Kaneohe", "HI"},
            {"Ogden", "UT"}
        }

        'Findign the max id, so id+1
        Dim id_num As Integer
        Dim id_cmd As New SqlCommand
        id_cmd.CommandText = "Select max(Customer_Id) FROM customer_T"

        Try
            con.Open()
            id_cmd.Connection = con
            id_num = id_cmd.ExecuteScalar() + 1
        Catch ex As Exception
            Response.Write("Error: " & ex.Message & " " & id_cmd.CommandText)
        Finally
            con.Close()
        End Try


        Dim cmd As New SqlCommand
        cmd.CommandText = "INSERT INTO customer_T VALUES ("
        cmd.CommandText &= id_num & ", '"
        cmd.CommandText &= cust_name.Text & "', '"
        cmd.CommandText &= cust_addr.Text & "', '"
        cmd.CommandText &= cust_city.Text & "', '"
        cmd.CommandText &= stateLook(cust_city.Text) & "', '"
        cmd.CommandText &= cust_postal.Text & "');"

        Try
            con.Open()
            cmd.Connection = con
            Response.Write(cmd.ExecuteNonQuery())
        Catch ex As Exception
            Response.Write("Error: " & ex.Message & " " & cmd.CommandText)
        Finally
            con.Close()
        End Try



    End Sub

    Protected Sub Page_Load() Handles MyBase.Load
        Dim constr As String = "workstation ID = pvfc_db26.mssql.somee.com;packet size=4096;user id=pvfc_login;pwd=pvfc_login;data source=pvfc_db26.mssql.somee.com;persist security info=False;initial catalog=pvfc_db26;TrustServerCertificate=True"

        Dim con As New SqlConnection
        con.ConnectionString = constr
        Dim connected As Boolean = False

        Try
            con.Open()
            connected = True
        Catch ex As Exception
            Response.Write(ex.Message)
        End Try

        If Not connected Then
            content.Visible = False

        ElseIf Me.IsPostBack = False Then
            cust_city.Items.Add("Gainesville")
            cust_city.Items.Add("Plano")
            cust_city.Items.Add("Albany")
            cust_city.Items.Add("Carteret")
            cust_city.Items.Add("Sacramento")
            cust_city.Items.Add("Boulder")
            cust_city.Items.Add("Seattle")
            cust_city.Items.Add("Santa Clara")
            cust_city.Items.Add("Clearwater")
            cust_city.Items.Add("Seminole")
            cust_city.Items.Add("Prospect Park")
            cust_city.Items.Add("Battle Creek")
            cust_city.Items.Add("Carlisle")
            cust_city.Items.Add("Kaneohe")
            cust_city.Items.Add("Ogden")
        End If
    End Sub

End Class