9 Replies - 25618 Views - Last Post: 28 October 2012 - 10:50 AM Rate Topic: -----

#1 chdboy   User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 135
  • Joined: 26-July 12

ExecuteNonQuery: Connection property has not been initialized[error]

Posted 20 October 2012 - 08:41 AM

I'm getting an error for this part of code With the first try the data has been inserted without

r = cmd.ExecuteNonQuery()
and then I put above line in my code it says

ExecuteNonQuerry:Connection property has not been initialized.
and getting to the end of the code
Employer.Show()
but the data is not inserted ?
and how to increment Empcode and Fileno in my coding to +1...If I set the property of Empcode and Filno in Database then while executing the insert statement it gives the error now they are nvarchar(50)both of them .?
con.ConnectionString = ("Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\Danial\Documents\Visual Studio 2010\Projects\Payroll_ESI_PF\Payroll_ESI_PF\Database\employee.mdf;Integrated Security=True;User Instance=True")
                con.Open()
                
                cmd.CommandText = "insert into Employee (Empcode,Fileno,Firstname,Lastname,Address,Contactno,dob,city,Emailid,Designation,Department,Esino,Pfno,Basicsallary,Doj,Dor,Pincode,Bankdetails,hraper,conv,otherded,loanamt,splalw,Bonus,Basicforpf) values ('" & Label2.Text & "','" & Label25.Text & "','" & TextBox1.Text & "','" & TextBox2.Text & "','" _
                    & TextBox3.Text & "','" & TextBox6.Text & "','" & DateAndTime.DateString & "','" & TextBox9.Text & _
                    "','" & TextBox10.Text & "','" & TextBox11.Text & "','" & TextBox12.Text & "','" & _
                    TextBox13.Text & "','" & TextBox14.Text & "','" & TextBox15.Text & "','" & DateAndTime.DateString & _
                    "','" & DateAndTime.DateString & "','" & TextBox8.Text & "','" & TextBox16.Text & "','" & _
                    TextBox17.Text & "','" & TextBox18.Text & "','" & TextBox19.Text & "','" & TextBox20.Text & "','" & _
                    TextBox21.Text & "','" & TextBox22.Text & "','" & TextBox23.Text & "')"

                r = cmd.ExecuteNonQuery()

                
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
Employer.Show()
            Me.Hide()


This post has been edited by chdboy: 20 October 2012 - 08:43 AM


Is This A Good Question/Topic? 0
  • +

Replies To: ExecuteNonQuery: Connection property has not been initialized[error]

#2 lucky3   User is offline

  • Friend lucky3 As IHelpable
  • member icon

Reputation: 233
  • View blog
  • Posts: 770
  • Joined: 19-October 11

Re: ExecuteNonQuery: Connection property has not been initialized[error]

Posted 20 October 2012 - 11:01 AM

Take a look at the example. If you use your connection somewhere else before, then it might be disposed, and not "alive" anymore.
Was This Post Helpful? 1
  • +
  • -

#3 zeeshanef   User is offline

  • D.I.C Head

Reputation: 18
  • View blog
  • Posts: 77
  • Joined: 14-April 12

Re: ExecuteNonQuery: Connection property has not been initialized[error]

Posted 21 October 2012 - 07:26 AM

you didn't tell the cmd object that, which open connection is to use!
just write this statement before executing command:
cmd.Connection = con


And you should use parameter in your query instead concatenation:
cmd.CommandText = "insert into Employee (Empcode) Values(@Empcode)
cmd.Parameters.AddWithValue("@Empcode", Label2.Text)

This post has been edited by zeeshanef: 21 October 2012 - 07:35 AM

Was This Post Helpful? 1
  • +
  • -

#4 chdboy   User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 135
  • Joined: 26-July 12

Re: ExecuteNonQuery: Connection property has not been initialized[error]

Posted 21 October 2012 - 08:41 AM

View Postzeeshanef, on 21 October 2012 - 07:26 AM, said:

you didn't tell the cmd object that, which open connection is to use!
just write this statement before executing command:
cmd.Connection = con


And you should use parameter in your query instead concatenation:
cmd.CommandText = "insert into Employee (Empcode) Values(@Empcode)
cmd.Parameters.AddWithValue("@Empcode", Label2.Text)



Yes I have used this method as you said
    Using connection As New SqlConnection("connection string here")
        Using command As New SqlCommand("INSERT INTO Employee (Empcode, Fileno, Firstname) VALUES (@Empcode, @Fileno, @Firstname)", _
                                        connection)
            command.Parameters.AddWithValue("@Empcode", as number)
            command.Parameters.AddWithValue("@Fileno", some number)
            command.Parameters.AddWithValue("@Firstname", some name)
     
            connection.Open()
     
            command.ExecuteNonQuery()
        End Using
    End Using


But it worked when the table has no Primary key assigned to it
otherwise it gives me this error while inserting another record to it

Violation of Primary key constraint 'PK_Employee'. cannot insert duplicate key in object 'dbo.Employee'


so I have to remove the primary key assigned to my table ...I hope that's not a risk?

View Postlucky3, on 20 October 2012 - 11:01 AM, said:

Take a look at the example. If you use your connection somewhere else before, then it might be disposed, and not "alive" anymore.

thanks ..you were also right I used what you said ..thanks for the help :)
Was This Post Helpful? 0
  • +
  • -

#5 Nemesis4895   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 46
  • Joined: 06-December 10

Re: ExecuteNonQuery: Connection property has not been initialized[error]

Posted 21 October 2012 - 11:46 AM

Violation of Primary key constraint 'PK_Employee'. cannot insert duplicate key in object 'dbo.Employee'


That error is due to an attempt to insert a value that already exists in a column defined as the primary key. When creating the table you can set your primary key to auto-increment with the IDENTITY keyword. If you create your table using PRIMARY KEY IDENTITY you won't need to specify a value for this column in your INSERT statement, SQL server will handle it on its own.
Was This Post Helpful? 0
  • +
  • -

#6 chdboy   User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 135
  • Joined: 26-July 12

Re: ExecuteNonQuery: Connection property has not been initialized[error]

Posted 24 October 2012 - 12:13 AM

View PostNemesis4895, on 21 October 2012 - 11:46 AM, said:

Violation of Primary key constraint 'PK_Employee'. cannot insert duplicate key in object 'dbo.Employee'


That error is due to an attempt to insert a value that already exists in a column defined as the primary key. When creating the table you can set your primary key to auto-increment with the IDENTITY keyword. If you create your table using PRIMARY KEY IDENTITY you won't need to specify a value for this column in your INSERT statement, SQL server will handle it on its own.

so with the above code and insert command as
command.Parameters.AddWithValue("@Empcode", comcode.Text)
                        command.Parameters.AddWithValue("@Fileno", filno.Text)


do I have to delete this statement from my code?
Was This Post Helpful? 0
  • +
  • -

#7 chdboy   User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 135
  • Joined: 26-July 12

Re: ExecuteNonQuery: Connection property has not been initialized[error]

Posted 24 October 2012 - 12:42 AM

and by doing that it says that it cannot insert null value into the table so the statement has been treminated I have deleted
("INSERT INTO Employee (Firstname) VALUES (@Empcode, @Fileno, @Firstname)", _
	                                    connection)'I have deleted the Empcode and fileno and next two command parameters
	        command.Parameters.AddWithValue("@Empcode", as number)
	        command.Parameters.AddWithValue("@Fileno", some number)

Was This Post Helpful? 0
  • +
  • -

#8 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 15433
  • View blog
  • Posts: 61,836
  • Joined: 12-June 08

Re: ExecuteNonQuery: Connection property has not been initialized[error]

Posted 24 October 2012 - 07:07 AM

INSERT INTO Employee (Firstname) 
VALUES (@Empcode, @Fileno, @Firstname)

Look at that statement. You only name one column but try to shove in three values. You need to enumerate all the columns you want to interact with... and IN THE SAME ORDER provide values.
Was This Post Helpful? 0
  • +
  • -

#9 chdboy   User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 135
  • Joined: 26-July 12

Re: ExecuteNonQuery: Connection property has not been initialized[error]

Posted 24 October 2012 - 09:56 PM

View Postmodi123_1, on 24 October 2012 - 07:07 AM, said:

INSERT INTO Employee (Firstname) 
VALUES (@Empcode, @Fileno, @Firstname)

Look at that statement. You only name one column but try to shove in three values. You need to enumerate all the columns you want to interact with... and IN THE SAME ORDER provide values.

sorry but that above code is just an example

correct code is here

 Using connection As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\Danial\Documents\Visual Studio 2010\Projects\Payroll_ESI_PF\Payroll_ESI_PF\Database\employee.mdf;Integrated Security=True;User Instance=True")
                    Using command As New SqlCommand("INSERT INTO Employee (Firstname,Lastname,Gender,Address,Height,Weight,Contactno,Bloodgroup,dob,city,Emailid,Designation,Department,Esino,Pfno,Basicsallary,Doj,Dor,Pincode,Bankdetails,hraper,conv,otherded,loanamt,splalw,Bonus,Basicforpf) VALUES (@Firstname, @Lastname, @Gender, @Address, @Height, @Weight, @Contactno, @Bloodgroup, @dob, @city, @Emailid, @Designation, @Department, @Esino, @Pfno, @Basicsallary, @Doj, @Dor, @Pincode, @Bankdetails, @hraper, @conv, @otherded, @loanamt, @splalw, @Bonus, @Basicforpf)", _
                     connection) 
                        ' command.Parameters.AddWithValue("@Empcode", comcode.Text)
                        'command.Parameters.AddWithValue("@Fileno", filno.Text)
                        command.Parameters.AddWithValue("@Firstname", TextBox1.Text)
                        command.Parameters.AddWithValue("@Lastname", TextBox2.Text)
                        command.Parameters.AddWithValue("@Gender", TextBox24.Text)
                        command.Parameters.AddWithValue("@Address", TextBox3.Text)
                        command.Parameters.AddWithValue("@Height", TextBox4.Text)  and so on 

Do I have to insert Empcode and Filno befor Firstname in my insert command and in Values also ?bcoz in my database I have automatically incremented the values using IDENTITY keyword..
my concern is when user input the details in the form the value of first form will be 1 and 1 to the fields Empcode and fileno and in the second form it should also automatically pic up with 2 and 2 in both the fields and so on .
Was This Post Helpful? 0
  • +
  • -

#10 Nemesis4895   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 46
  • Joined: 06-December 10

Re: ExecuteNonQuery: Connection property has not been initialized[error]

Posted 28 October 2012 - 10:50 AM

No, if the columns are created with IDENTITY, you do not need to specify the column name, nor the column value in your INSERT statement.

Example Table:

CREATE TABLE [dbo].[Employees] ([EmpCode] INT PRIMARY KEY IDENTITY,[FirstName] VARCHAR(30) NOT NULL,[LastName] VARCHAR(50) NOT NULL)



Example Insert:

INSERT INTO [dbo].[Employees] ([FirstName],[LastName]) VALUES ('John','Smith')



Assuming the table is empty, your first row will appear as this

----------------------------------
| EmpCode | FirstName | LastName |
| 1 | John | Smith |
----------------------------------


Your concern is correct, if both the EmpCode and FileNo columns are auto-incremented with the same increment value (Default is 1) they will have the same value for every row. Even if they are created with different increment values, the ratio of Column A : Column B will still always be Value A : Value B, which only seems to provide unnecessary redundancy.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1