SQL Server CE questions

[k3n51mm]

I have the following code (adapted from a GotDotNet article) to add a record to a table in an embedded SQL CE database that will be distributed as a part of our application:

If dlgAddMRS.ShowDialog = Windows.Forms.DialogResult.OK Then
Dim objConn As SqlCeConnection
objConn = New SqlCeConnection(My.Settings.dbConnStr)
Dim cmdSQLce As SqlCeCommand = objConn.CreateCommand()
cmdSQLce.Parameters.Add(New SqlCeParameter("MrsName", SqlDbType.NVarChar, 300))
cmdSQLce.Parameters("MrsName").Value = dlgAddMRS.txtMrsName.Text
cmdSQLce.Parameters.Add(New SqlCeParameter("StateID", SqlDbType.TinyInt))
cmdSQLce.Parameters("StateID").Value = 15
cmdSQLce.Parameters.Add(New SqlCeParameter("DistrictID", SqlDbType.Int))
cmdSQLce.Parameters("DistrictID").Value = 15
cmdSQLce.Parameters.Add(New SqlCeParameter("PropertyID", SqlDbType.Int))
cmdSQLce.Parameters("PropertyID").Value = 15
cmdSQLce.CommandText = "Insert Into MRS(MrsName, StateID, DistrictID, PropertyID) Values (?,?,?,?)"
Try
objConn.Open()
cmdSQLce.ExecuteNonQuery()
Catch exc As Exception
MsgBox(exc.ToString)
End Try
End If

Looking at the database table after executing this routine, no record is created. You can see the Try...Catch statement, which does not throw an error so I can't tell why it's not working.

QUESTIONS
1) Why doesn't the above code work?

2) I'm opening the table inside Visual Studio using the Server Explorer to view whether the record is created or not. Is this how I'm supposed to check it?

3) When trying to develop against this database, it always has to be checked in before we can run the debugger, which among other things is a hassle. Is there any way to make life easier in this regard? We're using SourceSafe integrated with VS 2005.

 

[mbb]

You're not alone!

I'm still learning about SQL Compact and would never have considered using parameters.

Why not just make your command like this:

string.format("Insert Into MRS(MrsName, StateID, DistrictID, PropertyID) Values ({0},{1},{2},{3})", ... )

where ... are the specific variables.

You can use the server explorer to verify the contents of the database. I'm using SQL Server Management Studio and typing in a select statement.

Personally I don't include the database in the project at all. Amongst other things Visual Studio locks the .sdf which is a pain in the butt. I use external scripts to create the database in management studio or with an application I have written.

 

[JohnH]

I'm still learning about SQL Compact and would never have considered using parameters.

Why not just make your command like this:

string.format("Insert Into MRS(MrsName, StateID, DistrictID, PropertyID) Values ({0},{1},{2},{3})", ... )

The Ins and OUTs of Parameterized Queries... - Visual Basic .NET Forums

 

[mbb]

The Ins and OUTs of Parameterized Queries... - Visual Basic .NET Forums

Hey thanks. I suspect parametised queries are faster? I'll get to play with this at the weekend.

Anyway my suggestion may help, if only for debugging ... that is work back from:

1. Hard coded sql
2. An sql built from formatted strings
3. A fully parametised sql.

Although if an exception is being raised it should be showing the problem there.

I am as you might have guessed, a dinosaur from the Informix days! Rooooarr!