1

I have been searching for a couple hours, and found several questions, but none of them really explained this in a way I can understand.

I'm programming a game similar to Rock Paper Sissors, except with many more selections, and the possiblity of a tie. I had originally hardcoded all of the possible outcomes, then decided to try a database so I can learn and practice sql as well.

Problem is, I can't figure out for the life of me how to connect to my local database, now that I have set it up and filled it through Visual Studio 2010. I can connect to it through Server Explorer just fine, and I can see it in Solution Explorer. I've tried several things, along the lines of:

Private sqlConn As New SqlConnection("Data Source=(local)|DataDirectory|\Outcomes.sdf;database=Outcomes;Integrated Security=true")

Private sqlConn As New SqlConnection("Data Source=.\SQLEXPRESS; Integrated Security=true")

Dim sqlConn As SqlConnection
sqlConn = New SqlConnection("DataSource=..\..\Outcomes.sdf")

I am relatively new to sql, but know enough through tinkering to build a sql statement and get me the result I want. But I've never connected to a database before. I've looked on MSDN and tried several things I saw on there (everything that looked like what I needed, really) but it still hasn't worked.

If I can connect, I already have my statement set, and have tested it through the database itself. Any help would be wonderful, especially if it's explained in a way I can understand it and use it for later.

Also, if it helps and isn't noticed through my tried code, my db name is Outcomes. I don't know if that is needed or will help, but just in case.

Improve this question

2 Answers 2

Reset to default
2

Please visit connection strings here...

It also would have been helpful to know what type of DBMS you are using as well. I noticed you have an .sdf database file, not a DBMS (For ex: MySql, SQL or Oracle). Therefore, your connection string is really going to depend on what you want.

Your issue is here by the way...

 Private sqlConn As New SqlConnection("Data Source=(local)|DataDirectory|\Outcomes.sdf;database=Outcomes;Integrated Security=true")

*You cant use the SqlConnection you have because its not supported with the use of .sdf files.

Instead you have to use: System.Data.SqlServerCe 'This is for compact edition

If you would like to know more about this please see here.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Ah, iPhone will do that to you. I'm searching for the information now, though. Thank you for the link.
Please see updated answer, I am sure it is more detailed now that I am actually at a PC...
That did it. I'm new to all this, so I didn't know there was a difference in how to code it between the two. Perfect answer. =)
Glad I could help, just trying to answer the question on the phone wasn't the best I guess.
1

Kendra,

Here are the logical Steps you will need to follow to access the database programmatically:

Note: I'm assumming you have the proper SQLExpress | SQL Server Database setup whether local or remote the methods below are identical except for the connection string information.

1) Import the Sql AdoNet Namespace so you can use the proper SQL Server Client Objects & Methods;

a) Imports System.Data.SqlClient

2) Establish a Connection to the database with the ADO Connection Object:

' Create your ADO Connection Object:

 Private myConn As SqlConnection
    myConn = New SqlConnection("Initial Catalog=OutComes;" & _
                "Data Source=localhost;Integrated Security=SSPI;")

Note: This connection string uses integrated security from your windows machine. you could also use standard security where you would need to enter your username and password credentials. Its your choice.

3) Setup Your ADO Command Object to Define your data retrieval query:

'Create a Command object.
      Private myCmd As SqlCommand
       myCmd = myConn.CreateCommand
        myCmd.CommandText = "SELECT FirstName, LastName FROM Employees"

       'Open the connection.
        myConn.Open()

Note: Subsitute CommandText string for your actual query based upon your own database schema.

4) Read, Fetch, Display Data using the SQLDataReader Object:

 Private results As String
   Private myReader As SqlDataReader
  myReader = myCmd.ExecuteReader()

  'Traverse the DataSet and Display in GUi for Example:
   Do While myReader.Read()
       results = results & myReader.GetString(0) & vbTab & _ 
                myReader.GetString(1) & vbLf
    Loop
    'Display results.
    MsgBox(results)

5) Gracefully Close all Objects Used:

' Close the reader and the database connection.
    myReader.Close()
    myConn.Close()

Note - You'll need to consult microsoft for further connection string formats, since I don't have enough info. But this should clarify the actual big picture steps for you.

Regards, Scott

Improve this answer

1 Comment

Thanks for the answer, but my problem actually stemmed from the fact I'm using SQL Server Compact, which doesn't use SqlClient. But I'm sure this will help in the future when I go to use an actual server. =)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.