• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1950
  • Last Modified:

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

0
Smummery
Asked:
Smummery
  • 5
  • 3
2 Solutions
 
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
 
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
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now