Recordsets in CF VB.Net

  • Thread starter Thread starter Atley
  • Start date Start date
A

Atley

I am trying to add new records to a SQLCe table or edit records in the same
table. I could use insert or update statements, except that the table has
an ntext field that often holds text strings that contain carriage returns
and/or apostrophes. Any suggestions?

Any help is greatly appreciated...

Atley
 
In my DataBase Class I check (and try to correct) Strings with the following
code when creating the SQL-Statements :

if (dset_Table.Columns.DataType == typeof(System.String)) //
String
{
if ((i_ServerType == 1) || (i_ServerType == 2)) // Odbc, OleDb only -
not SqlCe
{ // We are using " in our Sql Statement, replace found " with ' so
that Sql will not fail
System.Text.RegularExpressions.Regex r1 = new
System.Text.RegularExpressions.Regex("\"");
s_DataType = r1.Replace(s_DataType,"\'"); // Replace possible "
with '
} // if ((i_ServerType == 1) || (i_ServerType == 2)) // Odbc, OleDb
only - not SqlCe
} // if (dset_Table.Columns.DataType == typeof(System.String))
// String
if ((dset_Table.Columns.DataType == typeof(System.DateTime)) &
(s_DataType == ""))
{
if ((i_ServerType == 1) || (i_ServerType == 2)) // Odbc, OleDb
s_DataType = "01.01.1900 00:00:00";
if (i_ServerType == 3) // SqlServer / SqlCe
s_DataType = "1900-01-01T00:00:00";
} // if ((dset_Table.Columns.DataType == typeof(System.DateTime)) &
(s_DataType == ""))
if ((i_ServerType == 1) || (i_ServerType == 2)) // Odbc, OleDb
{
s_DataType = '"'+s_DataType+'"';
} // if ((i_ServerType == 1) || (i_ServerType == 2)) // Odbc, OleDb
only - not SqlCe
if (i_ServerType == 3) // SqlServer / SqlCe
{ // SqlCe must have ' and not "
s_DataType = "'"+s_DataType+"'";
} // if (i_ServerType == 3) // SqlServer / SqlCe

Mark Johnson, Berlin Germany
(e-mail address removed)
 
I tried to use that, but it doesn't seem to work... It doesn't seem to fire
properly... I also can't find a complete example of it, the examples in the
help don't seem to work for me. Have you ever gotten it to work and if so
how?

Here is the code I used, it didn't do anything...

cmdAddSaveCustData.CommandText = "INSERT INTO CustPurch (MName, MDate,
MPurchased, MPrice, MLength, MMedia, MType, MRating, MDesc, MPerRating)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
'Set Command Parameters

cmdAddSaveCustData.Parameters.Add("MName", SqlDbType.NVarChar
cmdAddSaveCustData.Parameters.Add("MDate", SqlDbType.DateTime)
cmdAddSaveCustData.Parameters.Add("MPurchased", SqlDbType.Int)
cmdAddSaveCustData.Parameters.Add("MPrice", SqlDbType.Float)
cmdAddSaveCustData.Parameters.Add("MLength", SqlDbType.Int)
cmdAddSaveCustData.Parameters.Add("MMedia", SqlDbType.Int)
cmdAddSaveCustData.Parameters.Add("MType", SqlDbType.Int)
cmdAddSaveCustData.Parameters.Add("MRating", SqlDbType.Int)
cmdAddSaveCustData.Parameters.Add("MDesc", SqlDbType.NText)
cmdAddSaveCustData.Parameters.Add("MPerRating", SqlDbType.Int)'

cmdAddSaveCustData.Prepare()

cmdAddSaveCustData.Parameters("MName").Value = MName
cmdAddSaveCustData.Parameters("MDate").Value = MDate
cmdAddSaveCustData.Parameters("MPurchased").Value = MPurchased
cmdAddSaveCustData.Parameters("MPrice").Value = MPrice
cmdAddSaveCustData.Parameters("MLength").Value = MLength
cmdAddSaveCustData.Parameters("MMedia").Value = MMedia
cmdAddSaveCustData.Parameters("MType").Value = MType
cmdAddSaveCustData.Parameters("MRating").Value = MRating
cmdAddSaveCustData.Parameters("MDesc").Value = MDesc
cmdAddSaveCustData.Parameters("MPerRating").Value = MPerRating

' MsgBox(cmdAddSaveCustData.CommandText())

cmdAddSaveCustData.ExecuteNonQuery()


Thanks for your answer to my previous post.

Atley
 
This worked for me...Note d is nTex

Your problem might be that you use prepare before you assign the values

strSQL = "INSERT INTO StoreCall (a, b, c, d, e, f) " &
" VALUES (?,?,?,?,?,?)

sqlCMD = New SqlServerCe.SqlCeComman
With sqlCMD .CommandText = strSQ
.Connection = m_sqlC
.Parameters.Add("@a", SqlDbType.Int).Value = m_intStoreI
.Parameters.Add("@b", SqlDbType.DateTime).Value = dteStar
.Parameters.Add("@c", SqlDbType.DateTime).Value = dteEn
.Parameters.Add("@d", SqlDbType.NText).Value = strCommen
.Parameters.Add("@e", SqlDbType.Int).Value = intCycleI
.Parameters.Add("@f", SqlDbType.Bit).Value = blnIsDMCal
.Prepare(
.ExecuteNonQuery(
End With
 
Back
Top