ResultSetOptions Enumeration ?
Jul 18, 2007
While using SQLCE 3.0, I need to insert ~20000 records. I using the following code :
Code Snippet
cmd.CommandText = "M_Reclass";
cmd.CommandType = CommandType.TableDirect;
SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Updatable | ResultSetOptions.Scrollable);
SqlCeUpdatableRecord rec = rs.CreateRecord();
while ((strInput = srFile.ReadLine()) != null)
{
if (strInput.Trim().Equals(""))
continue;
intCount = intCount + 1;
arInfo = fileIO.SplitRow(strInput, fldSep);
try
{
// GKH 2007/07/17 : Perform check, but sacrifice speed
//if (arInfo.Length >= 5)
//{
rec.SetString(0, arInfo[0]);
rec.SetString(1, arInfo[1]);
rec.SetString(2, arInfo[2]);
rec.SetString(3, arInfo[3]);
rec.SetString(4, arInfo[4]);
rs.Insert(rec);
ProgressText("Processing " + Para.RECLASS_IMP_FILE + ": " + intCount + " records(s)");
//}
}
catch (Exception ex)
{
throw new Exception("Record " + intCount + " cannot be imported : ", ex);
}
}
I did a search on MSDN, I found the option available are:
Code Snippet
Member name
Description
Insensitive
The ResultSet does not detect changes made to the data source.
None
No ResultSet options are specified.
Scrollable
The ResultSet can be scrolled both forward and backward.
Sensitive
The ResultSet detects changes made to the data source.
Updatable
The ResultSet allows updates.
SqlCeResultSet.Scrollable Property : True if the ResultSet is scrollable; otherwise, false.
SqlCeResultSet.Updatable Property : True if the values in the record can be modified; otherwise, false;
SqlCeResultSet.Sensitivity Property :
The sensitivity of the ResultSet indicates whether the ResultSet is aware of changes to the data source. A ResultSet that is sensitive is aware of changes; an insensitive ResultSet is unaware of changes. If no sensitivity is set, the ResultSet is asensitive and will use the optimal configuration based on other settings.The default value is asensitive.
Since I just insert only, does it mean I no need "Scrollable" ( i think insert is mere forward scroll)?
But what about "Updatable" ? which one from SIUD (Select/Insert/Update/Delete) need to use this "Updatable"?
View 3 Replies
Oct 19, 2007
Hello
When I call ExecuteResultSet(SqlServerCe.ResultSetOptions.Scrollable) I am getting the following error when the data type is Numeric(18, 4):
Expression evaluation caused an overflow. [ Name of function (if known) = ]
The numbers involved are not that big and work fine when ExecuteReader() or ExecuteResultSet(SqlServerCe.ResultSetOptions.None) are called on the same SQL.
Any ideas? Thanks in advance!
Cheers,
Dave
Code:
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Dim errorDescription As String = String.Empty
Dim numericNumber As String = String.Empty
Try
Using sqlCE As New System.Data.SqlServerCe.SqlCeConnection("Data Source = '" & My.Application.Info.DirectoryPath & "MyDatabase.sdf';")
sqlCE.Open()
Dim sqlCECommand As SqlServerCe.SqlCeCommand = sqlCE.CreateCommand()
sqlCECommand.CommandText = "SELECT SUM(MT.TPM_Measure1) AS CurrentAmount FROM BUS_Table MT"
System.Diagnostics.Debug.WriteLine(sqlCECommand.CommandText)
Dim reader As System.Data.IDataReader = Nothing
If RadioButton1.Checked Then
reader = sqlCECommand.ExecuteReader() 'Works fine
ElseIf RadioButton2.Checked Then
reader = sqlCECommand.ExecuteResultSet(SqlServerCe.ResultSetOptions.None) 'Works fine
Else
reader = sqlCECommand.ExecuteResultSet(SqlServerCe.ResultSetOptions.Scrollable) 'Causes the error!
End If
If reader.Read() Then
numericNumber = reader(0).ToString()
End If
reader.Close()
reader.Dispose()
End Using
Catch ex As Exception
errorDescription = ex.Message
Finally
Me.lblError.Text = errorDescription
Me.lblNumeric.Text = numericNumber
End Try
End Sub
TPM_Measure1 datatype is Numeric(18,4)
When the above query works the value is: 4053723.6300
View 18 Replies
View Related
May 31, 2006
Is there "enumerate values" in SQL server? I know that VB.Net has enumeration. If there is enumeration in SQL server, how to do that?
Thanks.
View 11 Replies
View Related
Jun 5, 2006
I planned to write a VB.NET SMO app enumerating all tables in a given database, all fields in each table, the properties (length, NULL, PK, etc) of each field, and finally store the result in a table. I think that should be quite doable.
But I noticed that the ForEach Loop editor has a SMO enumerator. (I don't know if it can be used to do exactly what I want.) By clicking on the options in the ForEach Loop editor I can get the Enumerator as follows:
Database[@Name='AdventureWorks']/Table[@Name='AWBuildVersion' and @Schema='dbo']/SMOEnumObj[@Name='Columns']/SMOEnumType[@Name='Names']
but it's not clear to me how to work with this thing! ( It must return a collection of items that I access through a variable.) Anyone know of an example illustrating SMO enumeration?
TIA,
Barkingdog
View 1 Replies
View Related
Apr 10, 2008
Hi. I am using SQL 2000 and DTS package to transfer data between two databases. In my DTS package, I need to create a enumeration task with a enumeration data task. However when I tried to add enumeration values, I cannot find any ways to create new values by right click to select from drop down menu or use Control + New in the enumeration properties. Could anyone point out how I can add new values in DTS Enumeration type.
Thanks
View 1 Replies
View Related
Feb 27, 2008
I am trying to move files from one directory to anonther using the For Each Loop Container and a File System Task. However, on the FIRST iteration of the ForEach Loop the variable that I am setting to the 0 index of the For Each Loop is returning me a valid directory with no file ( path only ) from who knows where ????
On the second iteration of the For Each Loop everything works as expected and I get the full path and file name and it iterates through all the files in the directory o.k.
So I have had to put a Hack to skip the fist iteration of the loop and then execute the File Task on the second iteration but I dont see anyone else doing this.
Where in the heck is it getting this odd directory from on the fist iteration ( its not my User varible because I am initializing it to \nowhereofile ). What am I doing wrong?
View 3 Replies
View Related
Apr 7, 2008
Hi,
This is my save procedure.
Please check and give me some advice.looping is need or not?
i get error "Collection was modified; enumeration operation may not execute"
plz help me.
-------------------------------------------------------------------------------- Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
Dim con As SqlConnectionDim cmd As SqlCommand
Dim da As SqlDataAdapter
Dim userid As String
Try
userid = Request.QueryString("userid")con = New SqlConnection(ConfigurationSettings.AppSettings("strcon"))
' con.ConnectionString = ConfigurationSettings.AppSettings("strcon")
con.Open()cmd = New SqlCommand("dbo.sp_AddAns", con)
cmd.CommandType = CommandType.StoredProcedure
For Each Item As DataListItem In dlAQ.ItemsDim paramid As New SqlParameter("@id", SqlDbType.Int, 4)
paramid.Value = userid.Trim
cmd.Parameters.Add(paramid)Dim paramans As New SqlParameter("@proansw", SqlDbType.NVarChar, 50)
Dim txtbox As New TextBoxtxtbox = CType(Item.FindControl("txtAns"), TextBox)
paramans.Value = txtbox.Text.Trim
cmd.Parameters.Add(paramans)
Dim paramprodesc As New SqlParameter("@prodesc", SqlDbType.NVarChar, 50)
Dim lbldesc1 As New Labellbldesc1 = CType(Item.FindControl("lbldesc"), Label)
paramprodesc.Value = lbldesc1.Text.Trim
cmd.Parameters.Add(paramprodesc)
Dim paramproid As New SqlParameter("@proid", SqlDbType.Int, 4)
Dim lblproid1 As New Labellblproid1 = CType(Item.FindControl("lblproid"), Label)
paramproid.Value = lblproid1.Text.Trim
cmd.Parameters.Add(paramproid)Dim paramreso As New SqlParameter("@proreso", SqlDbType.NVarChar, 50)
Dim lblreso1 As New Labellblreso1 = CType(Item.FindControl("lblreso"), Label)
paramreso.Value = lblreso1.Text.Trim
cmd.Parameters.Add(paramreso)Dim paramchk As New SqlParameter("@chk", SqlDbType.Int, 4)
paramchk.Value = "2"
cmd.Parameters.Add(paramchk)
'Dim rowaffected As Integer
cmd.ExecuteNonQuery()
bindData()
Next Item
View 1 Replies
View Related
Jul 14, 2007
OK, a new package, with a Foreach container enumerating CSV files in a directory.
I create the container pointing it at the directory and retrieving the fully qualified name, and create a variable (called 'CSVFiles') with a package scope, but no value.
Inside the container is a bulk insert task. The destination db/table is set, and the input flat file connection manager for the CSV files is defined with the connection string set to the variable created above.
As it iterates through the files, the variable is correctly set to the next file in the directory (I put a message box in the stream to display the file name/variable). It resembles 'C: empLocation1.csv'.
But when it gets to the bulk insert, I get this error message:
[Bulk Insert Task] Error: The specified connection "CSVFiles" is either not valid, or points to an invalid object. To continue, specify a valid connection.
What's going on here? Can I not use a bulk insert task in the container? Or some other parameter needs to be set?
SQL Server 9.00.3159
View 4 Replies
View Related
