10 Replies - 816 Views - Last Post: 21 April 2016 - 06:20 AM Rate Topic: -----

#1 mnm458   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 85
  • Joined: 06-December 15

Format of the initialization string does not conform to specification

Posted 21 April 2016 - 05:07 AM

Imports System.Data.SqlClient
Imports System.Data
Imports System.Text
Imports System.Windows.Forms

Public Class Form1
    Public i As String = Environment.MachineName.ToString()
    Public conn As New SqlConnection("server=192.168.1.6,1433; database=Students_MAM12A; User ID=sa; Password=123")
   

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        Cursor.Current = Cursors.WaitCursor
        Try
            If conn.State = ConnectionState.Closed Then
                conn.Open()
                Cursor.Current = Cursors.Default
                MsgBox("connected")
            End If

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
        conn.Close()
    End Sub

    Private Sub Button3_Click(sender As System.Object, e As System.EventArgs) Handles Button3.Click
        Dim connectionString As String = conn.ToString()
        Dim sql As String = "SELECT * FROM Students_MAM12A"
        Dim connection As SqlConnection = New SqlConnection(connectionString)
        Dim dataadapter As SqlDataAdapter = New SqlDataAdapter(sql, conn.ToString())
        Dim ds As DataSet = New DataSet()
        connection.Open()
        dataadapter.Fill(ds, "FirstName")
        connection.Close()
        DataGridView1.DataSource = ds


    End Sub
End Class



here is my code, i have no idea why is this error occuring:

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

the error is on this line:

dim connection as sqlconnection = New sqlconnection (connectionstring)

i am using SQL server 2014.

Is This A Good Question/Topic? 0
  • +

Replies To: Format of the initialization string does not conform to specification

#2 maceysoftware   User is offline

  • Foo
  • member icon

Reputation: 353
  • View blog
  • Posts: 1,533
  • Joined: 07-September 13

Re: Format of the initialization string does not conform to specification

Posted 21 April 2016 - 05:16 AM

because conn.tostring doesn't output the connection string.

have a look at your variable names, perhaps rename some of them so you actually know what they are:

Right at the top you have:
Public conn As New SqlConnection("server=192.168.1.6,1433; database=Students_MAM12A; User ID=sa; Password=123")



just output what the .tostring actually gives and see if that is what you want.

Really you want:
con.ConnectionString


However i am puzzled as to why your storing the connection string in a sqlconnection object, when your creating a new instance of the sqlconnection object every time you are actually attempting to connect to the database to do anything (other to connect).

surely a connection string is crying out to just be stored as a string variable, that way you can reuse that connection string variable throughout your form.
Was This Post Helpful? 1
  • +
  • -

#3 mnm458   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 85
  • Joined: 06-December 15

Re: Format of the initialization string does not conform to specification

Posted 21 April 2016 - 05:20 AM

View Postmaceysoftware, on 21 April 2016 - 05:16 AM, said:

because conn.tostring doesn't output the connection string.

have a look at your variable names, perhaps rename some of them so you actually know what they are:

Right at the top you have:
Public conn As New SqlConnection("server=192.168.1.6,1433; database=Students_MAM12A; User ID=sa; Password=123")



just output what the .tostring actually gives and see if that is what you want.

Really you want:
con.ConnectionString


However i am puzzled as to why your storing the connection string in a sqlconnection object, when your creating a new instance of the sqlconnection object every time you are actually attempting to connect to the database to do anything (other to connect).

surely a connection string is crying out to just be stored as a string variable, that way you can reuse that connection string variable throughout your form.


i looked up a tutorial and followed it. i dont really know what's going on... :( can you help with some explanation and code? :)

I am a noob. it would be great if you could share you skills with me :)
Was This Post Helpful? 0
  • +
  • -

#4 maceysoftware   User is offline

  • Foo
  • member icon

Reputation: 353
  • View blog
  • Posts: 1,533
  • Joined: 07-September 13

Re: Format of the initialization string does not conform to specification

Posted 21 April 2016 - 05:27 AM

I have just left some comments on your code, what tutorial are you looking at?

Public Class Form1

    ' give useful names, i doesn't mean anything and when it is used is normally used to store a Integer value, like in a loop
    ' for i as integer = 0 to 10
    Public i As String = Environment.MachineName.ToString() ' Also machineName is already a string, you don't need a tostring on a string.

    ' Already meantioned about this 
    Private connectionString As String = "server=192.168.1.6,1433; database=Students_MAM12A; User ID=sa; Password=123"
    Public conn As New SqlConnection("server=192.168.1.6,1433; database=Students_MAM12A; User ID=sa; Password=123")

    ' your controls should have useful names.
    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        Cursor.Current = Cursors.WaitCursor
        Try
            If conn.State = ConnectionState.Closed Then
                conn.Open()
                Cursor.Current = Cursors.Default
                MsgBox("connected") ' you should avoid using msgbox it is just left over form VB6 
            End If

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
        conn.Close()
    End Sub

    Private Sub Button3_Click(sender As System.Object, e As System.EventArgs) Handles Button3.Click
        ' ok so here you are attempting to get the connection string, which fails, which causes other issues later on
        ' if you actually stored the connectionString as a string like i showed you further up this issue kind of goes away
        Dim connectionString As String = conn.ToString()
        Dim sql As String = "SELECT * FROM Students_MAM12A"

        ' ok so your creating a conection and a adapter however your never disposing of them, really you should
        Dim connection As SqlConnection = New SqlConnection(connectionString)
        Dim dataadapter As SqlDataAdapter = New SqlDataAdapter(sql, conn.ToString())
        Dim ds As DataSet = New DataSet()
        connection.Open()
        dataadapter.Fill(ds, "FirstName")
        connection.Close()
        DataGridView1.DataSource = ds

        ' dispose like this:
        dataadapter.Dispose()
        connection.Dispose()
        ' remember to dispose in the reverse order than what you created.

        ' another way of doing it would be:
        'Dim ds As DataSet = New DataSet()

        'Using connection As New SqlConnection(connectionString)
        '    Using dataadapter As New SqlDataAdapter(sql, conn.ToString())
        '        connection.Open()
        '        dataadapter.Fill(ds, "FirstName")
        '    End Using
        'End Using

        'DataGridView1.DataSource = ds

        ' Notice that you don't have to go '= new sqlconnection' but rather build the whole statement together with a 'as new' however that is just personal preference however it is less keystrokes
        ' in my second instance i have also removed the connection.close, this is because when the program reaches the end using it will dispose of the connection, this will close the connection
        ' for us. i know some people perfer to manually close the connection so feel free to keep using that. 

    End Sub
End Class

Was This Post Helpful? 1
  • +
  • -

#5 mnm458   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 85
  • Joined: 06-December 15

Re: Format of the initialization string does not conform to specification

Posted 21 April 2016 - 05:33 AM

View Postmaceysoftware, on 21 April 2016 - 05:27 AM, said:

I have just left some comments on your code, what tutorial are you looking at?

Public Class Form1

    ' give useful names, i doesn't mean anything and when it is used is normally used to store a Integer value, like in a loop
    ' for i as integer = 0 to 10
    Public i As String = Environment.MachineName.ToString() ' Also machineName is already a string, you don't need a tostring on a string.

    ' Already meantioned about this 
    Private connectionString As String = "server=192.168.1.6,1433; database=Students_MAM12A; User ID=sa; Password=123"
    Public conn As New SqlConnection("server=192.168.1.6,1433; database=Students_MAM12A; User ID=sa; Password=123")

    ' your controls should have useful names.
    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        Cursor.Current = Cursors.WaitCursor
        Try
            If conn.State = ConnectionState.Closed Then
                conn.Open()
                Cursor.Current = Cursors.Default
                MsgBox("connected") ' you should avoid using msgbox it is just left over form VB6 
            End If

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
        conn.Close()
    End Sub

    Private Sub Button3_Click(sender As System.Object, e As System.EventArgs) Handles Button3.Click
        ' ok so here you are attempting to get the connection string, which fails, which causes other issues later on
        ' if you actually stored the connectionString as a string like i showed you further up this issue kind of goes away
        Dim connectionString As String = conn.ToString()
        Dim sql As String = "SELECT * FROM Students_MAM12A"

        ' ok so your creating a conection and a adapter however your never disposing of them, really you should
        Dim connection As SqlConnection = New SqlConnection(connectionString)
        Dim dataadapter As SqlDataAdapter = New SqlDataAdapter(sql, conn.ToString())
        Dim ds As DataSet = New DataSet()
        connection.Open()
        dataadapter.Fill(ds, "FirstName")
        connection.Close()
        DataGridView1.DataSource = ds

        ' dispose like this:
        dataadapter.Dispose()
        connection.Dispose()
        ' remember to dispose in the reverse order than what you created.

        ' another way of doing it would be:
        'Dim ds As DataSet = New DataSet()

        'Using connection As New SqlConnection(connectionString)
        '    Using dataadapter As New SqlDataAdapter(sql, conn.ToString())
        '        connection.Open()
        '        dataadapter.Fill(ds, "FirstName")
        '    End Using
        'End Using

        'DataGridView1.DataSource = ds

        ' Notice that you don't have to go '= new sqlconnection' but rather build the whole statement together with a 'as new' however that is just personal preference however it is less keystrokes
        ' in my second instance i have also removed the connection.close, this is because when the program reaches the end using it will dispose of the connection, this will close the connection
        ' for us. i know some people perfer to manually close the connection so feel free to keep using that. 

    End Sub
End Class


YOU ARE A CHAMP! i cant emphasize enough how much this is helping me. i did what you suggested before. the connection string is fine now, but i am getting this error on this line:

dataadapter.Fill(ds, "FirstName")
error: Invalid object name
Firstname is the name of a column in StudentsMAM_12 database Students table
Was This Post Helpful? 0
  • +
  • -

#6 PeterH   User is offline

  • D.I.C Regular

Reputation: 58
  • View blog
  • Posts: 282
  • Joined: 03-September 09

Re: Format of the initialization string does not conform to specification

Posted 21 April 2016 - 05:43 AM

This should be your 'Table' name. As a follow on, look into using Command Parameters
Was This Post Helpful? 0
  • +
  • -

#7 mnm458   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 85
  • Joined: 06-December 15

Re: Format of the initialization string does not conform to specification

Posted 21 April 2016 - 05:50 AM

View PostPeterH, on 21 April 2016 - 05:43 AM, said:

This should be your 'Table' name. As a follow on, look into using Command Parameters

my table name is dbo.StudentsTBL
should i write dbo... cause when i just wrote StudentsTBL, i got the same error:

"Invalid object name 'Students_MAM12A'."

View Postmnm458, on 21 April 2016 - 05:46 AM, said:

View PostPeterH, on 21 April 2016 - 05:43 AM, said:

This should be your 'Table' name. As a follow on, look into using Command Parameters

my table name is dbo.StudentsTBL
should i write dbo... cause when i just wrote StudentsTBL, i got the same error:

"Invalid object name 'Students_MAM12A'."

'Students_MAM12A' is the name of the database in SQL server 2014
Was This Post Helpful? 0
  • +
  • -

#8 maceysoftware   User is offline

  • Foo
  • member icon

Reputation: 353
  • View blog
  • Posts: 1,533
  • Joined: 07-September 13

Re: Format of the initialization string does not conform to specification

Posted 21 April 2016 - 05:53 AM

PeterH, why are you talking about command parameters at this stage?

Yes he needs to be aware of them however his code doesn't have any concatenated sql queries so if anything by stating he need to look into him it could just cause confusion?

mmm458 When you get onto inserting updating and deleting records, or even searching for individual records, you will need to look into stored procedures.

The reason for having stored procedures is to stop SQL injection, so to stop using from basically writing a delete statement in your username textbox for example.

If your ever finding yourself writing SQL commands in your code like: "SELECT * FROM SHARED_USERS_T WHERE Username = " & TextboxUsername.text

Then it is time to start looking at parameters.

I have knocked together a simple example of how I would normally do database transaction, it may be helpful, sadly I don't use adapters so I don't know how these works. Hopefully you will find this helpful.

Public Class Form1


    ' Ok so this is a cropped down version of what i would normally do, normally i wouldn't do any Database transactions on the code behind a button, but rather
    ' using a class to do this.

    ' However this is just an example

    ' Ok so first lets have a look at the controls on my form.
    ' there is two ButtonFetch which will get my data and display in my second controls DataGridViewUsers

    ' What do that should be declared up here (my preference)?
    ' Connection String
    ' SQL Command 
    ' Something to store the data in? a datatable perhaps?

    Private Const ConnectionString As String = "Connection String"
    ' if you were not hard coding your connection string then i would recommend having the format as a const and then storing the connection string string as a normal variable
    Private Const FetchSQL As String = "SELECT User_ID, Username, Forename, Surname FROM SHARED_USERS_T"

    ' why did we declare these as const you may ask, well that would be because they don't change, they are static data.

    Private _userTable As DataTable

    Private Sub ButtonFetch_Click(sender As System.Object, e As System.EventArgs) Handles ButtonFetch.Click
        ' So moving onto the fetch command what do i need to do here? 

        ' i need to connect to the database, fire a command, in this case a fetch command and then process the data

        ' Next you want to store the data in a dataset so:
        Dim completeData As DataSet

        Using connection As New System.Data.SqlClient.SqlConnection(ConnectionString)
            ' Sorry i don't use the adapters so i have no idea how they work, and as this code is going to be completely untested i won't attempt to.
            Using fetchCommand As New System.Data.SqlClient.SqlCommand(FetchSQL, connection)

                ' Open the connection, however lets have have some error handling around this.
                Try
                    connection.Open()
                Catch ex As Exception
                    ' you should come back and make the message more user friendly really, however this does for now
                    MessageBox.Show(ex.Message, "Connection Failed")
                End Try


                Try
                    ' Now we will want to execute out command on the database and read back the data:
                    Using reader As SqlClient.SqlDataReader = fetchCommand.ExecuteReader
                        ' we now want to load this data into the dataset
                        completeData.Load(reader, LoadOption.OverwriteChanges, "Users") ' this will load all the data from the first result table into a table in the dataset called users
                    End Using
                Catch ex As Exception
                    ' you should come back and make the message more user friendly really, however this does for now
                    MessageBox.Show(ex.Message, "Failed to retrieve data")
                End Try


            End Using
        End Using


        ' Now after everything has been disposed of i can checked to see if the table exists
        If completeData.Tables("Users") IsNot Nothing Then
            ' really the table name of users to me should of been a const too, this way if you change it in one place you change in all
            _userTable = completeData.Tables("Users") ' keep a reference to this datatable, incase you want to do anything further with it (you might not need to)
            DataGridViewUsers.DataSource = _userTable
        End If


    End Sub


End Class



Also there is no need to quote the previous comment, there is a reply button further on.

Remove the string, your command is only bringing back one result table therefore you can just pass through the datasource like so:

dataadapter.fill(ds)

Was This Post Helpful? 1
  • +
  • -

#9 PeterH   User is offline

  • D.I.C Regular

Reputation: 58
  • View blog
  • Posts: 282
  • Joined: 03-September 09

Re: Format of the initialization string does not conform to specification

Posted 21 April 2016 - 06:09 AM

maceysoftware, the OP has progressed from having NOTHING but a DB, to the current status, by simply following links I posted on a previous post made today. Therefore, I have simple suggested he reviews command parameters as a future step. I think he is capable enough to determine when they become applicable, my apologies if you found this intrusive.
Was This Post Helpful? 0
  • +
  • -

#10 mnm458   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 85
  • Joined: 06-December 15

Re: Format of the initialization string does not conform to specification

Posted 21 April 2016 - 06:13 AM

thanks a lot for spending time to help me! :D the explanation is veryy helpful :)

however i am getting an error: object not set to an instance of object.

on this line: If completeData.Tables("tbl") IsNot Nothing Then

i think because of : Dim completeData As DataSet
because we are not setting it to anything..

ok fixed, completeData = new Dataset()

Its working. thanks a lot guys!!!!!!
Was This Post Helpful? 0
  • +
  • -

#11 maceysoftware   User is offline

  • Foo
  • member icon

Reputation: 353
  • View blog
  • Posts: 1,533
  • Joined: 07-September 13

Re: Format of the initialization string does not conform to specification

Posted 21 April 2016 - 06:20 AM

Hello PeterH,

I didn't mean it as a bad why however when the thread has nothing to do with parameters you might confuse not only him but others who look at the thread in the future.

Also you never stated for a future step but rather said 'As a follow on, look into using Command Parameters' a follow on to what? this query to the database doesn't need command parameters.

I guessed you had been helping and that's where the comment had come from however I was just clearing up that there was nothing wrong with what he had done and he doesn't need to use command parameters in this case. I apologise if found this offensive.

mmm458

Glad you got it working.

This post has been edited by maceysoftware: 21 April 2016 - 06:20 AM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1