Format of the initialization string does not conform to specification starting at index 0.

I am using some code to search radius and return local zipcodes from a fixed input text box.

The error:Format of the initialization string does not conform to specification starting at index 0.

continues to come up. I have checked the connection strings and even tested using the gridview connection and that works fine.

It seems whenever I am using the :Dim objConn As New SqlConnection("ConnectionString1") method, it wont work.

Have exhausted every possibility for errors. Help is appreciated.

Code Behind:
Imports System.Data.SqlClient
Imports System.Data

Partial Class GeoTest
    Inherits System.Web.UI.Page
    Sub GetInitialCoordinates() Handles btnZip.Click
        'This subroutine requires a Label control named lblStatus

        'Prepare to connect to db and execute stored procedure
        Dim objConn As New SqlConnection("ConnectionString1")
        Dim objCmd As New SqlCommand("sp_get_zip_code", objConn)
        objCmd.CommandType = CommandType.StoredProcedure

        'we need to supply the ZIP code as an input parameter to our stored procedure
        objCmd.Parameters.Add(New SqlParameter("zip_code", SqlDbType.Char, 5))
        objCmd.Parameters("zip_code").Value = tbZip.Text

        'sglMinLat = south, sglMaxLat = north, sglMinLon = west, sglMaxLon = east
        Dim sglMinLat As Single
        Dim sglMaxLat As Single
        Dim sglMinLon As Single
        Dim sglMaxLon As Single

        Try
            'open connection
            objConn.Open()
            'put results into datareader
            Dim objReader As SqlDataReader
            objReader = objCmd.ExecuteReader()
            If objReader.HasRows Then
                'if starting point found, calculate box points
                objReader.Read()
                sglMinLat = Rad2Deg(CalculateLatitudeCoordinate(Deg2Rad(objReader("latitude")), 3959, Deg2Rad(180), ddlDistance.SelectedValue))
                sglMaxLat = Rad2Deg(CalculateLatitudeCoordinate(Deg2Rad(objReader("latitude")), 3959, Deg2Rad(0), ddlDistance.SelectedValue))
                sglMinLon = Rad2Deg(CalculateLongitudeCoordinate(Deg2Rad(objReader("longitude")), Deg2Rad(objReader("latitude")), Deg2Rad(sglMinLat), 3959, Deg2Rad(270), ddlDistance.SelectedValue))
                sglMaxLon = Rad2Deg(CalculateLongitudeCoordinate(Deg2Rad(objReader("longitude")), Deg2Rad(objReader("latitude")), Deg2Rad(sglMinLat), 3959, Deg2Rad(90), ddlDistance.SelectedValue))

                'report starting point details to lblStatus
                Dim strOut As String
                strOut = "ZIP Code " & tbZip.Text & " is assigned to " & objReader("cityname") & ", " & objReader("statecode") & ".<br />"
                strOut &= "It is located at latitude " & objReader("latitude") & ", longitude " & objReader("longitude") & ".<br /><br />"
                strOut &= "At a distance of " & ddlDistance.SelectedValue & " miles, the search box coordinates are:<br />"
                strOut &= "Maximum latitude (North): " & sglMaxLat & "<br />"
                strOut &= "Miniumum latitude (South): " & sglMinLat & "<br />"
                strOut &= "Maximum longitude (East): " & sglMaxLon & "<br />"
                strOut &= "Minimum longitude (West): " & sglMinLon & "<br />"
                lblStatus.Text = strOut

                'populate gridview
                PopulateGridView(sglMinLat, sglMaxLat, sglMinLon, sglMaxLon, objReader("latitude"), objReader("longitude"))
            Else
                'starting point not found
                lblStatus.Text = "Error retrieving initial ZIP Code coordinates: No record found for " & tbZip.Text & "."
            End If
            objConn.Close()
            objCmd.Dispose()
            objConn.Dispose()
        Catch ex As Exception
            'technical problem running the query
            lblStatus.Text = "Error executing database query for initial coordinates: " & ex.Message
        End Try
    End Sub

    Sub PopulateGridView(ByVal sglMinLat As Single, ByVal sglMaxLat As Single, ByVal sglMinLon As Single, ByVal sglMaxLon As Single, ByVal sglStartLat As Single, ByVal sglStartLon As Single)
        sqlZip.SelectParameters("minlat").DefaultValue = sglMinLat
        sqlZip.SelectParameters("maxlat").DefaultValue = sglMaxLat
        sqlZip.SelectParameters("minlon").DefaultValue = sglMinLon
        sqlZip.SelectParameters("maxlon").DefaultValue = sglMaxLon
        sqlZip.SelectParameters("startlat").DefaultValue = sglStartLat
        sqlZip.SelectParameters("startlon").DefaultValue = sglStartLon

        gvZIP.DataBind()
    End Sub

    Function CalculateLatitudeCoordinate(ByVal sglLat1 As Single, ByVal intRadius As Integer, ByVal intBearing As Integer, ByVal intDistance As Integer) As Single
        Return Math.Asin(Math.Sin(sglLat1) * Math.Cos(intDistance / intRadius) + Math.Cos(sglLat1) * Math.Sin(intDistance / intRadius) * Math.Cos(intBearing))
    End Function

    Function CalculateLongitudeCoordinate(ByVal sglLon1 As Single, ByVal sglLat1 As Single, ByVal sglLat2 As Single, intRadius As Integer, ByVal intBearing As Integer, ByVal intDistance As Integer) As Single
        Return sglLon1 + Math.Atan2(Math.Sin(intBearing) * Math.Sin(intDistance / intRadius) * Math.Cos(sglLat1), Math.Cos(intDistance / intRadius) - Math.Sin(sglLat1) * Math.Sin(sglLat2))
    End Function

    Function Deg2Rad(ByVal sglDegrees As Single) As Single
        Return sglDegrees * (Math.PI / 180.0)
    End Function

    Function Rad2Deg(ByVal sglRadians As Single) As Single
        Return sglRadians * (180.0 / Math.PI)
    End Function

    Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load

    End Sub

    Protected Sub GetInitialCoordinates(sender As Object, e As System.EventArgs) Handles btnZip.Click

    End Sub
End Class

Open in new window


ASP.net Code:
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="GeoTest.aspx.vb" Inherits="GeoTest" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <p>
    Select all ZIP Codes within 
 
    <asp:DropDownList runat="server" ID="ddlDistance">
        <asp:ListItem Selected="True">5</asp:ListItem>
        <asp:ListItem>10</asp:ListItem>
        <asp:ListItem>25</asp:ListItem>
        <asp:ListItem>50</asp:ListItem>
        <asp:ListItem>100</asp:ListItem>
    </asp:DropDownList>
 
    miles of ZIP Code
 
    <asp:TextBox runat="server" ID="tbZip" Columns="5" />
    <asp:RequiredFieldValidator
        runat="server"
        ID="rfvZip"
        ControlToValidate="tbZip"
        ErrorMessage="Please provide a ZIP Code"
        CssClass="warning"
        Display="Dynamic"
    />
    <asp:RegularExpressionValidator
        runat="server"
        ID="revZip"
        ControlToValidate="tbZip"
        ValidationExpression="^[0-9]{5}$"
        ErrorMessage="Please enter a valid five-digit ZIP Code"
        CssClass="warning"
        Display="Dynamic"
    />
 
    <asp:Button runat="server" ID="btnZip" Text="Get ZIP Codes" />
</p>
 
<p><asp:Label runat="server" ID="lblStatus" Text="Status messages will appear here" /></p>
 
<asp:GridView
    runat="server"
    ID="gvZIP"
    DataSourceID = "sqlZip"
    AutoGenerateColumns="false"
    AllowSorting="true"
    AllowPaging = "true"
    PageSize = "20"
    HeaderStyle-BackColor="Yellow"
    HeaderStyle-Font-Bold="true"
    HeaderStyle-HorizontalAlign="Center"
    AlternatingRowStyle-BackColor="WhiteSmoke"
    CellPadding="5"
>
    <Columns>
        <asp:BoundField HeaderText="City" DataField="cityname" SortExpression="cityname" />
        <asp:BoundField HeaderText="State" DataField="statecode" SortExpression="statecode" />
        <asp:BoundField HeaderText="ZIP Code" DataField="zipcode" SortExpression="zipcode" />
        <asp:BoundField HeaderText="Latitude" DataField="latitude" SortExpression="latitude" />
        <asp:BoundField HeaderText="Longitude" DataField="longitude" SortExpression="longitude" />
        <asp:BoundField HeaderText="Distance" DataField="distance" SortExpression="distance" />
    </Columns>
</asp:GridView>
 
<asp:SqlDataSource
    runat="server"
    ID="sqlZip"
    SelectCommand="CREATE PROCEDURE [dbo].[sp_get_zip_code]
     @zip_code CHAR(5)
AS
BEGIN
     -- SET NOCOUNT ON added to prevent extra result sets from
     -- interfering with SELECT statements.
     SET NOCOUNT ON;
 
    -- Insert statements for procedure here
     SELECT *
     FROM zipcodes
     WHERE zip = @zip_code
END
"
    ConnectionString="<%$ ConnectionStrings:ConnectionString1 %>"
>
    <SelectParameters>
        <asp:ControlParameter ControlID="tbZip" Name="zip_code" PropertyName="Text" />
    </SelectParameters>
</asp:SqlDataSource>


    </div>
    </form>
</body>
</html>

Open in new window


Web-Config:
<configuration>
  <connectionStrings>
    <add name="ConnectionString1" connectionString="Data Source=mssql1.gear.host;Persist Security Info=True;User ID=shane;Password=password"
      providerName="System.Data.SqlClient" />
    <add name="ConnectionString2" connectionString="Data Source=mssql1.gear.host;Persist Security Info=True;User ID=shane;Password=Zp2KMi8>xW6^"
      providerName="System.Data.SqlClient" />
  </connectionStrings>
  <system.web>
    <compilation debug="true" strict="false" explicit="true" targetFramework="4.0"/>
  </system.web>
</configuration>

Open in new window

SmummeryAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Carl TawnSystems and Integration DeveloperCommented:
I think the problem you are having is that you are passing the name of a connection string into the constructor for the SqlConnection object, rather than an actual connection string.

So, instead of:
Dim objConn As New SqlConnection("ConnectionString1")

Open in new window

You would need to use something more like the following to read the connection from the config file:
Dim connString As String = System.Configuration.ConfigurationManager.ConnectionString("ConnectionString1").ConnectionString
Dim objConn As New SqlConnection(connString)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SmummeryAuthor Commented:
Getting blue line error: Connectionstring is not a member of system.configuration.configurationmanager


I added this to my imports but no joy.

Thanks for your help,

Imports System.Data.SqlClient
Imports System.Data
Imports System.Configuration.ConfigurationManager

'Prepare to connect to db and execute stored procedure
        Dim connString As String = System.Configuration.ConfigurationManager.ConnectionString("ConnectionString1").ConnectionString
        Dim objConn As New SqlConnection(connString)
        Dim objCmd As New SqlCommand("sp_get_zip_code", objConn)
        objCmd.CommandType = CommandType.StoredProcedure
0
Carl TawnSystems and Integration DeveloperCommented:
Oops, missed an "s" off the end there. It should say:
Dim connString As String = System.Configuration.ConfigurationManager.ConnectionStrings("ConnectionString1").ConnectionString

Open in new window

You may also need to add a reference to the System.Configuration assembly, if you don't already have one.
0
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

SmummeryAuthor Commented:
That solved that issue but now I'm getting a "Notimplemented Exception"...
0
SmummeryAuthor Commented:
Private Function objCmd() As Object

        Throw New NotImplementedException

    End Function
0
SmummeryAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for Smummery's comment #a40689421

for the following reason:

I have decided to explore another option. Thanks
0
Carl TawnSystems and Integration DeveloperCommented:
The solution suggested did solve the original issue. The fact that another unrelated issue arose after that doesn't make the solution to the original issue any less valid or correct.
0
SmummeryAuthor Commented:
Sorry, I guess I should have allotted points.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.