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

Question

Hello I have an ASP.NET application that I am writing with C# syntax, bound to a SQL2008 server.

I am fairly new to ASP and C#, I was able to configure the SQL connection just fine, and was able to bind my dropdown lists with the content from one of my tables. However once I go to create the add parameters section in the button event when clicked I get the following error

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

I am so confused since my connection string works, it fails when I tried to add the parameters.

Here is the intialization that allowed me to bind the dropdowns

/*

SqlConnection sqlConnection = new SqlConnection();
        sqlConnection.ConnectionString = ConfigurationManager.ConnectionStrings["OneSecurityDB"].ToString();
        sqlConnection.Open();
        string sqlQuery = "SELECT * FROM SCSTrained";
        SqlCommand sqlCommand = new SqlCommand(sqlQuery, sqlConnection);
        sqlCommand.CommandType = CommandType.Text;
        SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);
        DataSet dataSet = new DataSet();
        sqlDataAdapter.Fill(dataSet);
        sqlConnection.Close();

*/

Here is the connection with parameters being added

/*

try
        {
            SqlConnection conn = new SqlConnection("OneSecurityDB");
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "INSERT INTO VisLog ([Date_Added],[Time_In],[Visitor_Name],[Company],[Philips_Escort],[Escort Email] VALUES (@Date_Added, @Time_In, @Visitor_Name, @Company, @Philips_Escort, @Escort Email)";
                cmd.Parameters.Add("@Date_Added", SqlDbType.DateTime).Value = tDate;
                cmd.Parameters.Add("@Time_In", SqlDbType.DateTime).Value = tIn;
                cmd.Parameters.Add("@Visitor", SqlDbType.VarChar).Value = vName;
                cmd.Parameters.Add("@Company", SqlDbType.VarChar).Value = vComapny;
                cmd.Parameters.Add("@Philips_Escort", SqlDbType.VarChar).Value = ddl3;
                cmd.Parameters.Add("@Escort_Email", SqlDbType.VarChar).Value = ddl4;
                conn.Open();
                cmd.ExecuteNonQuery();
                conn.Close();
                Response.Write("<script language=\"javascript\">alert(\"Database has been updated with your info, Thank You!\");</script>");
                success = true;
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }

*/

Any suggestions would be most helpful. Thanks

Brian

Answer 1

Hi Brian,

  In your insert query, Escort Email field in both places is not having an "_" in between but when you add parameter using Parameter.Add, its there. Just correct that, and i think this problem will get solved.
  And as a best practice, move this logic to a stored procedure, so that if there any exception, these information will not be shown to the public in any case. Stored procs will be more efficient that this. Just a Suggestion.

                cmd.CommandText = "INSERT INTO VisLog ([Date_Added],[Time_In],[Visitor_Name],
                    [Company],[Philips_Escort],[Escort_Email] 
                    VALUES (@Date_Added, @Time_In, @Visitor_Name, @Company, @Philips_Escort, @Escort_Email)";
                cmd.Parameters.Add("@Date_Added", SqlDbType.DateTime).Value = tDate;
                cmd.Parameters.Add("@Time_In", SqlDbType.DateTime).Value = tIn;
                cmd.Parameters.Add("@Visitor", SqlDbType.VarChar).Value = vName;
                cmd.Parameters.Add("@Company", SqlDbType.VarChar).Value = vComapny;
                cmd.Parameters.Add("@Philips_Escort", SqlDbType.VarChar).Value = ddl3;
                cmd.Parameters.Add("@Escort_Email", SqlDbType.VarChar).Value = ddl4;

---

-- Thanks Ajith R Nair

Answer 2

Thank you, I will try this later today and see if that fixes my issue.

Also thanks for stored produce tip, I will work on that as well.

---

Brian B. Software Engineer

Answer 3

That did not work. I have not created a stored procured yet, need to grab a book on SQL as I am new to it. Here is my ASP file with C# behind. I left out the Web.Config file due to server name and security.

ASP file

<%@ Page Title="Visitor Sign In" Language="C#" Debug="true" MasterPageFile="~/Site.master" AutoEventWireup="true"
    CodeFile="Sign In.aspx.cs" Inherits="Sign_In" %>
 
<asp:Content ID="HeaderContent" runat="server" ContentPlaceHolderID="HeadContent">
</asp:Content>
<asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">
 
    <h2>
        Welcome Guest, to the Bothell Factory!
    </h2>
    <br />
    <form id="form1" action="Sign In.aspx" method="get">
 
 
        <div class="date" align="center">
            <label for="Today_Date">Date: </label>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                <asp:TextBox ID="tDate" runat="server" Text='<%=dateTime.Now.toShortdateString() %>' Columns="20" 
                    text-align="center">&nbsp</asp:TextBox>
        </div>
        
        <br />
   
        <div class="time_in" align="center">
            <label for="Time_In">Time In: </label>&nbsp;
                <asp:TextBox ID="tIn" runat="server" Columns="20"></asp:TextBox>
        </div>
 
        <br />
 
        
               
    <br />
       <h2>
        Please Sign In Below:
    </h2>
    <hr />
 
    <br />
    <table>
        <tr>
            <td align="left" class="style1">
            <asp:Label ID="label5" Text="Site Location:" runat="server"></asp:Label>
            </td>
            <td class="style6">
            <asp:DropDownList ID="ddl1" runat="server" Height="22">
            <asp:ListItem Text="" Value=""></asp:ListItem>
            <asp:ListItem Text="AED Reception" Value="Bothell Reception"></asp:ListItem>
            <asp:ListItem Text="AED Shipping" Value="Bothell Reception"></asp:ListItem>
            <asp:ListItem Text="Bothell Reception" Value="Bothell Reception"></asp:ListItem>
            <asp:ListItem Text="Bothell Security" Value="Bothell Security"></asp:ListItem>
            <asp:ListItem Text="Bothell Shipping" Value="Bothell Shipping"></asp:ListItem>
            <asp:ListItem Text="Administrator" Value="Administrator"></asp:ListItem>
            
            </asp:DropDownList>
            </td>
        </tr>
        </table>
        <br />
    <table>
        <tr>
            <td align="left" class="style1">
                <asp:Label ID="label1" runat="server" Text="Visitor Name:" ></asp:Label>
            </td>
            <td class="style1">
                <asp:TextBox ID="vName" runat="server" Columns="30" > </asp:TextBox>
            </td>
        </tr>
    </table>
    <br />
    <table>
        <tr>
            <td align="left" class="style2">
                <asp:Label ID="label2" runat="server" Text="Company With:" ></asp:Label>
            </td>
            <td class="style2">
                <asp:TextBox ID="vComapny" runat="server" Columns="30"> </asp:TextBox>
            </td>
        </tr>
    </table>
    <br />
 
    <table>
        <tr>
            <td align="left" class="style3">
                <asp:Label ID="label3" runat="server" Text="Philips Escort:" ></asp:Label>
            </td>
            <td align="left" class="style6">            
            <asp:DropDownList ID="ddl3" runat="server" AutoPostBack="True" DataTextField="Philips_Escort"
            DataValueField="ID" OnSelectedIndexChanged="ddl3_SelectedIndexChanged" Width="230" Height="22" ></asp:DropDownList>
            </td>             
       </tr>
    </table>
    <br />
    <table>
        <tr>
            <td align="left" class="style4">
                <asp:Label ID="label4" runat="server" Text="Escort Email:" ></asp:Label>
            </td>
            <td class="style6">
                 <asp:DropDownList ID="ddl4" runat="server" AutoPostBack="True" DataTextField="Escort_Email"
                     DataValueField="ID" OnSelectedIndexChanged="ddl4_SelectedIndexChanged" Height="22" ></asp:DropDownList>
            </td>
        </tr>
    </table>
    <br />
    
    <br />
    <asp:Button ID="button1" Text="Submit" runat="server" OnClick="button1_OnClick"  />
   
    
    <asp:SqlDataSource ID="SqlDataSource1" runat="server" DataSourceMode="DataSet" SelectCommand="SELECT * FROM [SCSTrained]"></asp:SqlDataSource> 
    </form>
   
        
    
</asp:Content>

Here is the C# file

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using System.Data.Sql;
 
 
public partial class Sign_In : System.Web.UI.Page
{
    private string _constr;
    private bool success;
    protected void Page_Load(object sender, EventArgs e)
    {
        tDate.Text = DateTime.Now.ToShortDateString();
        tIn.Text = DateTime.Now.ToString("hh:mm:ss tt");
 
        SqlConnection sqlConnection = new SqlConnection();
        sqlConnection.ConnectionString = ConfigurationManager.ConnectionStrings["OneSecurityDB"].ToString();
        sqlConnection.Open();
        string sqlQuery = "SELECT * FROM SCSTrained";
        SqlCommand sqlCommand = new SqlCommand(sqlQuery, sqlConnection);
        sqlCommand.CommandType = CommandType.Text;
        SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);
        DataSet dataSet = new DataSet();
        sqlDataAdapter.Fill(dataSet);
        sqlConnection.Close();
 
        if (!IsPostBack)
        {
            ddl3.DataSource = dataSet.Tables[0];
            ddl3.DataBind();
            ddl3.DataTextField = "Philips_Escort";
            ddl3.DataValueField = "ID";
 
            ddl4.DataSource = dataSet.Tables[0];
            ddl4.DataBind();
            ddl4.DataTextField = "Escort_Email";
            ddl4.DataValueField = "ID";
        }
 
    }
 
    protected void SqlDataSource1_Selecting(object sender, EventArgs e)
    {
    }
 
    protected void ddl3_SelectedIndexChanged(object sender, EventArgs e)
    {
        ddl4.Text = ddl3.SelectedItem.Value;
    }
 
    protected void ddl4_SelectedIndexChanged(object sender, EventArgs e)
    {
        ddl3.Text = ddl4.SelectedItem.Value;
    }
 
    protected void button1_OnClick(object sender, EventArgs e)
    {
        if (ddl1.Text == "")
        {
            Response.Write("<script langauge=\"javascript\">alert(\"You must select a Location!\");</script>");
        }
        else if (vName.Text == "")
        {
            Response.Write("<script langauge=\"javascript\">alert(\"You did not supply a valid name for Visitor!\");</script>");
        }
        else if (vComapny.Text == "")
        {
            Response.Write("<script language=\"javascript\">alert(\"Visitor Company has been left blank!\");</script>");
        }
        else if (ddl3.Text == "" | ddl4.Text == "")
        {
            Response.Write("<script language=\"javascript\">alert(\"Philips Escort and Escort email not supplied!\");</script>");
        }
        else
        {
            button1.Enabled = true;
        }
 
        
        /* String to update tables on SQL server*/
 
        try
        {
            SqlConnection conn = new SqlConnection("OneSecurityDB");
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "INSERT INTO VisLog ([Date_Added],[Time_In],[Visitor_Name],[Company],[Philips_Escort],[Escort_Email] VALUES (@Date_Added, @Time_In, @Visitor_name, @Company, @Philips_Escort, @Escort_Email)";
                cmd.Parameters.Add("@Date_Added", SqlDbType.DateTime).Value = tDate.Text;
                cmd.Parameters.Add("@Time_In", SqlDbType.DateTime).Value = tIn.Text;
                cmd.Parameters.Add("@Visitor_name", SqlDbType.VarChar).Value = vName.Text;
                cmd.Parameters.Add("@Company", SqlDbType.VarChar).Value = vComapny.Text;
                cmd.Parameters.Add("@Philips_Escort", SqlDbType.VarChar).Value = ddl3.Text;
                cmd.Parameters.Add("@Escort_Email", SqlDbType.VarChar).Value = ddl4.Text;
                conn.Open();
                cmd.ExecuteNonQuery();
                conn.Close();
                Response.Write("<script language=\"javascript\">alert(\"Database has been updated with your info, Thank You!\");</script>");
                success = true;
            }
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message); 
        }
              
    }
}
 
   

Thanks for any help on this.

---

Brian B. Software Engineer

Answer 4

Stored Procedure created:

CREATE PROCEDURE dbo.usp_InsertVisLog

  @DateAdded datetime

, @TimeIn datetime

, @VisitorName varchar(50)

, @Company varchar(50)

, @PhilipsEscort varchar(50)

, @EscortEmail varchar(50)

AS

BEGIN

       SET NOCOUNT ON;

 

       INSERT INTO [OneSecurity].[dbo].[VisLog]

                        ([Date_Added]

                        ,[Time_In]

                        ,[Visitor_Name]

                        ,[Company]

                        ,[Philips_Escort]

                        ,[Escort_Email]

                        )

              VALUES

                        (

                         @DateAdded

                        ,@TimeIn

                        ,@VisitorName

                        ,@Company

                        ,@PhilipsEscort

                        ,@EscortEmail

                        )

 

END

Here is the C#:

SqlConnection oConn = new SqlConnection("OneSecurityDB");
        SqlCommand oCmd = new SqlCommand("usp_InsertVisLog", oConn);
        oCmd.Parameters.Add("@Date_Added", SqlDbType.DateTime).Value = tDate.Text;
        oCmd.Parameters.Add("@Time_In", SqlDbType.DateTime).Value = tIn.Text;
        oCmd.Parameters.Add("@Visitor_name", SqlDbType.VarChar).Value = vName.Text;
        oCmd.Parameters.Add("@Company", SqlDbType.VarChar).Value = vComapny.Text;
        oCmd.Parameters.Add("@Philips_Escort", SqlDbType.VarChar).Value = ddl3.Text;
        oCmd.Parameters.Add("@Escort_Email", SqlDbType.VarChar).Value = ddl4.Text;
        Response.Write("<script language=\"javascript\">alert(\"Database has been updated with your info, Thank You!\");</script>");
        success = true;

Still get the same error:

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

---

Brian B. Software Engineer

Answer 5

Forum can be closed, correcting my parameters and its all fixed.

try

        {

            using (SqlCommand cmd = new SqlCommand())

            {

                cmd.Connection = new SqlConnection(ConfigurationManager.ConnectionStrings["OneSecurityDB"].ConnectionString);

                cmd.CommandText = "usp_InsertVisLog";

                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add("@DateAdded", SqlDbType.DateTime).Value = tDate.Text;

                cmd.Parameters.Add("@TimeIn", SqlDbType.DateTime).Value = tIn.Text;

                cmd.Parameters.Add("@Visitorname", SqlDbType.VarChar).Value = vName.Text;

                cmd.Parameters.Add("@Company", SqlDbType.VarChar).Value = vComapny.Text;

                cmd.Parameters.Add("@PhilipsEscort", SqlDbType.VarChar).Value = ddl3.Text;

                cmd.Parameters.Add("@EscortEmail", SqlDbType.VarChar).Value = ddl4.Text;

                cmd.Connection.Open();

                cmd.ExecuteNonQuery();

                cmd.Connection.Close();

            }

        }

        catch (Exception ex)

        {

            throw ex;

        }

---

Brian B. Software Engineer