Connection not opening ...what to do

  • (2 Pages)
  • +
  • 1
  • 2

24 Replies - 1189 Views - Last Post: 02 October 2012 - 09:03 AM Rate Topic: -----

#16 chdboy  Icon User is offline

  • D.I.C Head

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

Re: Connection not opening ...what to do

Posted 26 September 2012 - 09:15 AM

it is the new error that pops up when I change in the code
("Server=localhost\SQLExpress;uid=sa;Data Source=C:\Users\danial\Documents\Visual Studio 2010\Projects\Addtodatabase\Addtodatabase\Database\Employee.sdf;Database=Employee.sdf; Trusted_Connection=Yes;")


Attached Image

This post has been edited by chdboy: 26 September 2012 - 09:16 AM

Was This Post Helpful? 0
  • +
  • -

#17 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1533
  • View blog
  • Posts: 4,919
  • Joined: 25-September 09

Re: Connection not opening ...what to do

Posted 26 September 2012 - 11:23 AM

OK, here is your problem. You are accessing a SqlServer Compact Edition file. Therefore, you won't use SQLClient.

You need to first Add a reference to your project for SQLServerCE

Next you will add at the top of your code
Imports System.Data.SQLServerCE


So an example to your datafile would be

Dim con as New SqlCeConnection("Data Source=C:\Users\danial\Documents\Visual Studio 2010\Projects\Addtodatabase\Addtodatabase\Database\Employee.sdf")
con.Open
dim Sql as string = "INSERT INTO YourTable (Field1, Field2) VALUES ('Value1', 'Value2')
dim cmd as New SqlCeCommand(Sql, con)
cmd.ExecuteNonQuery
con.close() 'Not this will put the x on your sdf file in your database explorer.

Notice that instead of SqlClient.SqlConnection or SqlClient.SqlCommand you now need to use SqlCeConnection because you are using a Compact Edition datafile not an SQLServer database

This post has been edited by CharlieMay: 26 September 2012 - 11:25 AM

Was This Post Helpful? 0
  • +
  • -

#18 November-06  Icon User is offline

  • D.I.C Regular

Reputation: 45
  • View blog
  • Posts: 376
  • Joined: 04-January 11

Re: Connection not opening ...what to do

Posted 26 September 2012 - 06:12 PM

Do you have access to the database? Does the database have username password?

Try to delete the existing connection, then recreate it. Maybe there was something wrong with the way you connected it.
Was This Post Helpful? 0
  • +
  • -

#19 chdboy  Icon User is offline

  • D.I.C Head

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

Re: Connection not opening ...what to do

Posted 02 October 2012 - 05:49 AM

View PostCharlieMay, on 26 September 2012 - 11:23 AM, said:

OK, here is your problem. You are accessing a SqlServer Compact Edition file. Therefore, you won't use SQLClient.

You need to first Add a reference to your project for SQLServerCE

Next you will add at the top of your code
Imports System.Data.SQLServerCE


So an example to your datafile would be

Dim con as New SqlCeConnection("Data Source=C:\Users\danial\Documents\Visual Studio 2010\Projects\Addtodatabase\Addtodatabase\Database\Employee.sdf")
con.Open
dim Sql as string = "INSERT INTO YourTable (Field1, Field2) VALUES ('Value1', 'Value2')
dim cmd as New SqlCeCommand(Sql, con)
cmd.ExecuteNonQuery
con.close() 'Not this will put the x on your sdf file in your database explorer.

Notice that instead of SqlClient.SqlConnection or SqlClient.SqlCommand you now need to use SqlCeConnection because you are using a Compact Edition datafile not an SQLServer database

WOW this works like a magic ...I cannot thank you enough for this :) You are really a genius would you like to be my mentor?I would love to have you as a mentor.

I have to put cmd.ExecuteNonQuery into try and catch bcoz it was throwing an exception so I thought it is better to catch the exception .

but now The DB data is not showing hehe...working out

This post has been edited by chdboy: 02 October 2012 - 05:51 AM

Was This Post Helpful? 0
  • +
  • -

#20 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1533
  • View blog
  • Posts: 4,919
  • Joined: 25-September 09

Re: Connection not opening ...what to do

Posted 02 October 2012 - 06:22 AM

Yes,
You should always use Try...Catch in areas prone to exceptions. Glad I could help.
Was This Post Helpful? 0
  • +
  • -

#21 rgfirefly24  Icon User is offline

  • D.I.C Lover
  • member icon


Reputation: 260
  • View blog
  • Posts: 1,419
  • Joined: 07-April 08

Re: Connection not opening ...what to do

Posted 02 October 2012 - 06:30 AM

Although, I would add that putting something in a try catch because it throws an error doesn't magically fix the error. If your sql statement is throwing an error then you need to figure out what the error is, and fix it. The try catch is only meant to gracefully handle the errors so your program doesn't crash.

On that, your EmpData table, how many columns does it have? The reason I ask this is because if there is more then 1 column, you need to either specify which columns you are inserting data into, or have an item in the VALUES section for each of the columns.

This post has been edited by rgfirefly24: 02 October 2012 - 06:32 AM

Was This Post Helpful? 1
  • +
  • -

#22 chdboy  Icon User is offline

  • D.I.C Head

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

Re: Connection not opening ...what to do

Posted 02 October 2012 - 07:52 AM

View Postrgfirefly24, on 02 October 2012 - 06:30 AM, said:

Although, I would add that putting something in a try catch because it throws an error doesn't magically fix the error. If your sql statement is throwing an error then you need to figure out what the error is, and fix it. The try catch is only meant to gracefully handle the errors so your program doesn't crash.

On that, your EmpData table, how many columns does it have? The reason I ask this is because if there is more then 1 column, you need to either specify which columns you are inserting data into, or have an item in the VALUES section for each of the columns.

Right now I have one column in my database and that is Firstname and I was hoping to add some more
Was This Post Helpful? 0
  • +
  • -

#23 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1533
  • View blog
  • Posts: 4,919
  • Joined: 25-September 09

Re: Connection not opening ...what to do

Posted 02 October 2012 - 08:07 AM

can you post the exception you're getting?
Was This Post Helpful? 0
  • +
  • -

#24 chdboy  Icon User is offline

  • D.I.C Head

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

Re: Connection not opening ...what to do

Posted 02 October 2012 - 08:58 AM

before catch and try it was giving me out of the bound exception and after the data has been entered on cmd.ExecuteNonQuery() line
Was This Post Helpful? 0
  • +
  • -

#25 rgfirefly24  Icon User is offline

  • D.I.C Lover
  • member icon


Reputation: 260
  • View blog
  • Posts: 1,419
  • Joined: 07-April 08

Re: Connection not opening ...what to do

Posted 02 October 2012 - 09:03 AM

ok, Can you re-post the relevant code with your changes, also WHAT is throwing the out of bounds exception? I've never known an INSERT statement to throw that type of error.
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2