ConnectionString problem

[MilantoMinsk]

I am using Visual Studio 2012 express and its SQL compact edition (CE) to make a database application. I did menage to add a database as a datasource, run a couple of SQL statements and displayed it in a DataGridView. However, I was relying on automatic database code and avoided to write any code, since I do not know it. But it seems it is inevitable. I got in trouble with the first line, as instructed on this link (Retrieving and Saving Data in Databases-VBForums).
The code is:

Using connection As New SqlConnection(Database1ConnectionString)

End Using

When I make a datasource, the Wizard gives me this as a connection string: ? Database1ConnectionString?. When I use it, i get error: ?Database1ConnectionString is not declared. It may be inaccessible due to its protection level.? I checked Project Designer (How to: Save and Edit Connection Strings) , but it all seems fine. Of course, I wrote at the beginning ?Imports System.Data.SqlClient?. I have saved the project and got written connectionstring to the config file. What am I doing wrong?

 

[jmcilhinney]

That bit of code you posted is mine. As I state in that thread, that code is for SQL Server and you will have to change the ADO.NET provider if you're using a different data source. Despite the name, SQL Server CE is a completely different product to SQL Server and thus requires its own ADO.NET provider. You'll have to add a reference in your project, if it doesn't already have one, to System.Data.SqlServerCe.dll. You can then create a System.Data.SqlServerCe.SqlCeConnection, which will accept a SQL Server CE connection string.

You then have to make sure that you have an appropriate connection string. Do you have one in your App.config file? Does it show up in the table on the Settings page of the project properties? If so, try using My.Settings.Database1ConnectionString.

 

[MilantoMinsk]

thanks, i worked like a charm!

however, i immediately came across another problem. this piece of code worked fine:

Using Connection As New SqlServerCe.SqlCeConnection(My.Settings.Database1ConnectionString)
Using Command As New SqlServerCe.SqlCeCommand("SELECT Name FROM ListOfProducts WHERE ID = 2", connection)
connection.Open()
Using reader As SqlServerCe.SqlCeDataReader = Command.ExecuteReader
Dim table As New DataTable
table.Load(reader)
DataGridView1.DataSource = table
End Using
End Using
End Using

 

[MilantoMinsk]

on the other hand, this slight modification with user defined ID gave me an error:


Dim a As Integer = CInt(TextBox1.Text)

Using Connection AsNew SqlServerCe.SqlCeConnection(My.Settings.Database1ConnectionString)
Using Command AsNew SqlServerCe.SqlCeCommand("SELECT Name FROM ListOfProducts WHERE (ID = @a)", connection)
connection.Open()
Using reader As SqlServerCe.SqlCeDataReader = Command.ExecuteReader
Dim table AsNewDataTable
table.Load(reader)
DataGridView1.DataSource = table
EndUsing
EndUsing
EndUsing

 

[jmcilhinney]

on the other hand, this slight modification with user defined ID gave me an error:

If you get an error, please tell us what it is rather than just that it occurred. Many times it's like looking for a needle in a haystack otherwise.

Dim a As Integer = CInt(TextBox1.Text)

Using Connection AsNew SqlServerCe.SqlCeConnection(My.Settings.Database1ConnectionString)
Using Command AsNew SqlServerCe.SqlCeCommand("SELECT Name FROM ListOfProducts WHERE (ID = @a)", connection)
connection.Open()
Using reader As SqlServerCe.SqlCeDataReader = Command.ExecuteReader
Dim table AsNewDataTable
table.Load(reader)
DataGridView1.DataSource = table
EndUsing
EndUsing
EndUsing

You have a parameter place-holder in your SQL code but you haven't added a parameter to your command. You need to add a parameter with a value so that that value can be substituted for the place-holder when the SQL is executed.

By the way, please post your code snippets as plain text inside formatting tags, i.e.

[xcode=vb]your code here[/xcode]

 

[MilantoMinsk]

You were right. The error message was "A parameter is missing. [parameter ordinal = 1]"

 

[MilantoMinsk]

These two lines of code did the job:

Command.Parameters.Add("@a", SqlDbType.Int)
Command.Parameters("@a").Value = a

 

[jmcilhinney]

These two lines of code did the job:

Command.Parameters.Add("@a", SqlDbType.Int)
Command.Parameters("@a").Value = a

If you already have a value when you create the parameter then you should call AddWithValue rather than calling Add and setting Value:

Command.Parameters.AddWithValue("@a", a)

The data type of the parameter is inferred from the value.