VB.Net CF App Falls Over On SqlServerCe ExecuteNonQuery Under WM5 But Not PC2003
Jun 1, 2006
I have a VB.net 2005 application that uses both System.Data.SqlClient and System.Data.SqlServerCe which has worked on PC2003 handhelds but falls over in Windows Mobile 5. It seems to be when I have used a SqlClient connection and then use ExecuteNonQuery on the local sdf file - the application just bombs out - no error message, nothing.
Weirdly, if I am connected to a CE database through Query Analyzer 3.0 at the same time as running this application, everything works fine. Do I need to 'initialize' something that Query Analyzer seems to be doing in order to make this work?
Most grateful for any ideas.
Thanks
Chris
View 5 Replies
ADVERTISEMENT
Mar 18, 2008
Hi,
we just migrated to SqlServerMobile 3.5. My first experience is that Version 3.5 is much slower than 3.1.
I upgraded and compacted an existing Database. Is it recommended to create a new database, instead?
Has anybody else the feeling that Version 3.5 is slower?
Are there any settings to speed up the database.
Many thanks,
Stefan
View 2 Replies
View Related
May 10, 2006
Hi,
I am trying to make a mobile application work, but I get the following error. The operating system on Pocket PC is Microsoft® Windows Mobile„˘ 2003 Second Edition. Any ideas? Thanks in advance.
The followings are the error and my codes:
System.TypeLoadException was unhandled
Message="Could not load type 'System.Data.SqlServerCe.SqlCeDataAdapter' from assembly 'System.Data.SqlServerCe, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845DCD8080CC91'."
StackTrace:
at SQLMobile.Form1.Form1_Load()
at System.Windows.Forms.Form.OnLoad()
at System.Windows.Forms.Form._SetVisibleNotify()
at System.Windows.Forms.Control.set_Visible()
at System.Windows.Forms.Application.Run()
at SQLMobile.Form1.Main()
Codes:
Private Sub FillGrid()
Dim filename As New String _
("Program FilesSQLMobilesqlmobile.sdf")
Dim conn As New SqlCeConnection("Data Source=" + filename)
Dim selectCmd As SqlCeCommand = conn.CreateCommand()
selectCmd.CommandText = "select Destination from flightdata"
Dim adp As New SqlCeDataAdapter(selectCmd)
Dim ds As New DataSet()
adp.Fill(ds)
DataGrid1.DataSource = ds
End sub
View 1 Replies
View Related
Nov 20, 2013
Aim - > Count how many id falls within the appropriate months
Currently the “CreatedDate” Column is in the following format “2013-02-26T10:59:26.000Z”
My query is :
SELECT
[Id],
[CreatedDate]
FROM [FDMS].[Dan].[Stg_SF_Opportunities]
E.g.
Id CreatedDate
1 2013-02-04T10:59:26.000Z
2 2013-02-10T10:59:26.000Z
3 2013-02-21T10:59:26.000Z
4 2013-02-26T10:59:26.000Z
4 2013-03-01T10:59:26.000Z
Desired results:
Month Count
Jan 0
Feb 4
March 1
April 0
Etc etc
View 4 Replies
View Related
May 13, 2015
I have
Issues | Category | Date
I1 | A | 1/1/2015
I2 | A | 2/2/2015
I3 | B | 2/1/2015
I4 | C | 3/3/2015
I5 | B | 4/3/2015
I6 | A | 5/4/2015
I want to convert to
A | B | C
JAN 1 | 1 | 0
FEB 1 | 0 | 0
MAR 0 | 1 | 1
APR 1 | 0 | 0
Where numbers under neath A, B, C are the count that falls in the particular month.
View 3 Replies
View Related
May 12, 2006
I'm using MS SQL 2000. I developing a shopping cart where on the admin side when we are inserting or updating the products, there is a chance that a single product can fall into two categories therefore i'm giving the users a option of CheckBoxList control for the categories. The checkboxlist control is Database Databinded with categories.
When a user selects couple of checkboxes, i'm storing both the categories seperated with a comma in a single field of database but then when i want retrieve the all products of a particular category, this approach wont work. Is their any way around for this??
Even though i use CheckboxList control is their any way to retrieve products of a particular category?? Can i store each checkbox text in a different row with the same product ID's in a different table and all the product info in a different table ?? I hope i made myself clear.
Thanks for your help and time in advance.
View 1 Replies
View Related
Jan 3, 2014
I'm trying to find if any part of a date range in my table between orig_start_rent and stop_rent falls within a period I specify in two variables: startPeriod and endPeriod
For example if I specify 2013-11-01 as startPeriod and 2013-11-30 as endPeriod, then if any part of the date range between orig_start_rent and stop_rent(stop-rent can be null if hire is open) falls within that period, I want that to be picked up and assigned the value of 1 in my case statement for OnHire. My code is not picking everything up however - using the example above, a record with orig_start_rent of 2013-05-23 and stop_rent of 2013-11-18 is being assigned 0 when it should be 1. My code here:
declare @startPeriod as smalldatetime
declare @endPeriod as smalldatetime
set @startPeriod = '2013-11-01'
set @endPeriod = '2013-11-30';
select dticket, orig_start_rent, stop_rent, case when orig_start_rent >= @startPeriod and orig_start_rent <= @endPeriod then 1 when orig_start_rent < @endPeriod and stop_rent is null then 1 else 0 end [OnHire] from deltickitem
View 5 Replies
View Related
Sep 13, 2006
Hello
I have a script which checks the disk space and when it falls a certain size , it mails the dba mail box.
I would like to know how I can change it , as a percentage calculation.
For example when the free space is less than 20% of the total space on the drive I should be receiving a mail.
The script I have is :
declare @MB_Free int
create table #FreeSpace(
Drive char(1),
MB_Free int)
insert into #FreeSpace exec xp_fixeddrives
-- Free Space on F drive Less than Threshold
if @MB_Free < 4096
exec master.dbo.xp_sendmail
@recipients ='dvaddi@domain.edu',
@subject ='SERVER X - Fresh Space Issue on D Drive',
@message = 'Free space on D Drive
has dropped below 2 gig'
drop table #freespace
Thanks
View 1 Replies
View Related
Aug 29, 2014
I need to extract a price from a package solution table that was current on a certain date.
Columns are:
Product_Reference int,
Change_Date int, -- Note that this is in yyyymmdd format
Price decimal
So for one items you would get
Product_ReferenceChange_DatePrice
100014200401281.59
100014200605131.75
100014200802121.99
100014200906252.35
100014201002242.50
100014201107151.10
100014201205151.15
The challenge is to return the price of the item on 20070906. In this example 1.75.
I've tried joining it to itself
SELECT DISTINCT p1.[Product_Reference]
,p1.[Change_Date]
,p1.[Price]
FROM PHistory p1
INNER JOIN PHistory p2
ON p1.Product_Reference = p2.Product_Reference
WHERE p1.Product_Reference = 100014
AND p1.Change_Date >= 20070906
and p2.Change_Date < 20070906
But it returns the price above and below that date.
View 3 Replies
View Related
Jan 20, 2008
Dear;
I got a problem executenonquery in asp.net 2.0. Below as my Code:
1 Dim conn As New SqlConnection(tmpconn)2 Dim cmd1 As New SqlCommand("SP_RPTFABTRANSFER_DYEING_PREV", conn)3 4 cmd1.CommandType = CommandType.StoredProcedure5 cmd1.CommandTimeout = 9006 cmd1.Parameters.Add(New SqlParameter("@aSTDATE", SqlDbType.VarChar, 10))7 cmd1.Parameters.Add(New SqlParameter("@aEDDATE", SqlDbType.VarChar, 10))8 cmd1.Parameters.Add(New SqlParameter("@aBUYERID", SqlDbType.VarChar, 10)) 9 cmd1.Parameters.Add(New SqlParameter("@aFACTORYID", SqlDbType.VarChar, 10))10 cmd1.Parameters.Add(New SqlParameter("@aFabGrpId", SqlDbType.VarChar, 10))11 cmd1.Parameters.Add(New SqlParameter("@aFABSUPPIDFROM", SqlDbType.VarChar, 10))12 cmd1.Parameters.Add(New SqlParameter("@aFABSUPPIDTO", SqlDbType.VarChar, 10))13 cmd1.Parameters.Add(New SqlParameter("@aUSERID", SqlDbType.VarChar, 20))14 cmd1.Parameters.Add(New SqlParameter("@aDelType", SqlDbType.VarChar, 20))15 16 cmd1.Parameters("@aSTDATE").Value = lstartdt17 cmd1.Parameters("@aEDDATE").Value = lenddt18 cmd1.Parameters("@aBUYERID").Value = Trim(dropBuyer.SelectedValue)19 cmd1.Parameters("@aFACTORYID").Value = Trim(dropFactory.SelectedValue)20 cmd1.Parameters("@aFabGrpId").Value = lFabGrp21 cmd1.Parameters("@aFABSUPPIDFROM").Value = fabFrom22 cmd1.Parameters("@aFABSUPPIDTO").Value = fabTo23 cmd1.Parameters("@aUSERID").Value = Session("UID").ToString24 cmd1.Parameters("@aDelType").Value = lDelType25 Try26 conn.Open()27 cmd1.ExecuteNonQuery()28 conn.Close()29 Catch ex As Exception30 lblerr.Visible = True31 lblerr.Text = ex.Message32 Finally33 conn.Close()34 End Try
Web.Config<add name="oldtextileConnectionString" connectionString="Data Source=xx.xx.xx.xx;Initial Catalog=ERP;Integrated Security=TRUE;Connection Lifetime=0;Min Pool Size =0;Max Pool Size=1000;Pooling=true;" providerName="System.Data.SqlClient"/>
This Code running on asp only 2min Execute time. But i try it on Asp.net 2.0 take a long time about 15min or request time out error. Could any one can give me some tips or hits? Help much appreciated. Thanks
View 5 Replies
View Related
Oct 4, 2006
Hello, Iam devloping a product for a mobile unit using Windows mobile database. (*.sdf)
Is it possible from other form applictions, such as ordernary Windows forms ?
Have a wonderful day!
View 1 Replies
View Related
Apr 11, 2007
hi,
i was facing problem in sqlceserver connection, the coding i had code as below, it's return an error message "Procedure Call or Argument is not valid". i was study this issue for 2 days, but i am still can't found out the cause of issue, can anyone please point out the mistake i had made? Thank.
Module Module1
Public Const local_DatabaseFile As String = "My Documents esting.sdf"
Public conn As SqlCeConnection
Dim cmd As SqlCeCommand
Public myReader As SqlCeDataReader
Public mySelectQuery As String
-----------------------------------------------------------------------
Form1
Function Check_Code()
Try
' On Error Resume Next
builtConnStr()
If conn.State = ConnectionState.Open Then
conn.Close()
End If
mySelectQuery = "SELECT * FROM Product " & _
"WHERE Item_Code = '" + txtCode.Text + "' "
conn.Open()
Dim myCommand As New SqlCeCommand(mySelectQuery, conn)
myReader = myCommand.ExecuteReader()
' Always call Read before accessing data.
If myReader.Read() Then
txtName.Text = myReader.GetString(1)
End If
myReader.Close()
conn.Close()
Catch ex As Exception
MsgBox(Err.Description)
End Try
End Function
------------------------------------------------------------------------------
Public Sub builtConnStr()
Try
Dim local_ConnString As String
local_ConnString = "Data Source= " & local_DatabaseFile
If conn Is Nothing Then
conn = New SqlCeConnection(local_ConnString)
conn.Open()
cmd = conn.CreateCommand()
End If
Catch ex As Exception
MsgBox(Err.Description)
End Try
End Sub
View 9 Replies
View Related
Sep 7, 2006
I hope you would help me in this problem. I use the code below for executenonquery command for mdb DB.But I do not know the changes I should made when Using SQL2005.-------------Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0; " & _ "Data Source=C:ASPNET20dataNorthwind.mdb" Dim dbConnection As New OleDbConnection(connectionString) dbConnection.Open() Dim commandString As String = "INSERT INTO Employees(FirstName, LastName) " & _ "Values(@FirstName, @LastName)" Dim dbCommand As New OleDbCommand(commandString, dbConnection) Dim firstNameParam As New OleDbParameter("@FirstName", OleDbType.VarChar, 10) firstNameParam.Value = txtFirstName.Text dbCommand.Parameters.Add(firstNameParam) Dim lastNameParam As New OleDbParameter("@LastName", OleDbType.VarChar, 20) LastNameParam.Value = txtLastName.Text dbCommand.Parameters.Add(LastNameParam) dbCommand.ExecuteNonQuery() dbConnection.Close()--------
View 2 Replies
View Related
Sep 11, 2006
Hi, I am trying to execute a nonquery as follows (look for bold):Dim connStringSQL As New SqlConnection("Data Source=...***...Trusted_Connection=False")'// Create the new OLEDB connection to Indexing ServiceDim connInd As New System.Data.OleDb.OleDbConnection(connStringInd)Dim commandInd As New System.Data.OleDb.OleDbDataAdapter(strQueryCombined, connInd)Dim commandSQL As New SqlCommand("GetAssetList2", connStringSQL)commandSQL.CommandType = Data.CommandType.StoredProcedureDim resultDS As New Data.DataSet()Dim resultDA As New SqlDataAdapter()'// Fill the dataset with valuescommandInd.Fill(resultDS)'// Get the XML values of the dataset to send to SQL server and run a new query...'// Return the number of resultsresultCount.Text = source.Count.ToStringresults.DataSource = sourceresults.DataBind()'// Record the searchcommandSQL = New SqlCommand("RecordSearch", connStringSQL)commandSQL.Parameters.Clear()commandSQL.Parameters.Add("@userName", Data.SqlDbType.VarChar, 50).Value = authUser.Text()commandSQL.Parameters.Add("@createdDateTime", Data.SqlDbType.DateTime).Value = DateTime.Now()commandSQL.Parameters.Add("@numRows", Data.SqlDbType.Int, 1000).Value = resultCount.TextcommandSQL.Parameters.Add("@searchString", Data.SqlDbType.VarChar, 1000).Value = searchText.TextconnStringSQL.Open()commandSQL.ExecuteNonQuery()connStringSQL.Close() The stored procedure looks like this:Use GTGAssetsDROP PROC dbo.RecordSearch;--New ProcedureGOCREATE PROC dbo.RecordSearch(@userName varchar(50),@createdDateTime DateTime,@numRows varchar(1000),@searchString varchar(1000))ASBEGINSET NOCOUNT ONINSERT INTO SearchLog (SearchString, CreatedByUser, CreatedDTTM, RowsReturned) VALUES (@searchString, @userName, @createdDateTime, @numRows)ENDGOAny ideas as to why this error is appearing? Incorrect syntax near 'RecordSearch'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near 'RecordSearch'.Source Error: Line 169: commandSQL.Parameters.Add("@searchString", Data.SqlDbType.VarChar, 1000).Value = searchText.Text
Line 170: connStringSQL.Open()
Line 171: commandSQL.ExecuteNonQuery()
Line 172: connStringSQL.Close()
Line 173: End IfMany thanks!James
View 5 Replies
View Related
Dec 9, 2006
When I try to insert a record with the ExecuteNonQuery command, I get the following error information. Any clues why? Thanks.
SSqlException was unhandled by user code...Message="Incorrect syntax near [output of one of my field names]."...[Item detail:] In order to evaluate an indexed property, the property must be qualified and the arguments must be explicitly supplied by the user.
My code:
Private objCmd As SqlCommandPrivate strConn As New SqlConnection(ConfigurationManager.AppSettings("conn"))...objCmd = New SqlCommand("INSERT INTO tblUsers (UserID,FName,LName,PrimLang1,Ctry,Phone)" & _"VALUES('" & strUser & "','" & strFName.Text & "','" & strLName.Text & "', '" & strLang.Text & "', '" & strCtry.Text & "', '" & strPhone.Text & "'" _, strConn)strConn.Open()objCmd.ExecuteNonQuery()
View 17 Replies
View Related
Dec 12, 2006
Hi,I am developing a small application where in I need to take a few data from the user and put it on the DB. I have stored procs for the same. I am getting an error when I execute the ExecuteNonQuery() command. the error is as follows:
System.InvalidCastException: Object must implement IConvertible. at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Intranet_New.leaveForm.btnSubmit_Click(Object sender, EventArgs e) in c:inetpubwwwrootintranet_newleaveform.aspx.cs:line 138
Snippet of code:
try
{
con = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["SqlCon"]);
cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "SP_InsertIntoLeave";
cmd.Parameters.Add("@empid", SqlDbType.Char, 20);
cmd.Parameters["@empid"].Value = txtEmplyId.Text;
cmd.Parameters.Add("@empName", SqlDbType.NVarChar, 50);
cmd.Parameters["@empName"].Value = txtName.Text;
cmd.Parameters.Add("@LeaveFrom", SqlDbType.DateTime);
string str_LeaveFrom = ddlDay.SelectedValue + "/" +ddlMonth.SelectedValue + "/" + ddlYear.SelectedValue;
DateTime LF = new DateTime();
LF = DateTime.Parse(str_LeaveFrom);
string LeaveFrom1 = (LF.ToShortDateString());
cmd.Parameters["@LeaveFrom"].Value = LeaveFrom1;
cmd.Parameters.Add("@LeaveTo", SqlDbType.DateTime);
string str_LeaveTo = ddltoDay.SelectedValue + "/" + ddltoMonth.SelectedValue + "/" + ddltoYear.SelectedValue;
DateTime LT = new DateTime();
LT = DateTime.Parse(str_LeaveTo);
string LeaveTo1 = (LT.ToShortDateString());
cmd.Parameters["@LeaveTo"].Value = LeaveTo1;
cmd.Parameters.Add("@TotalDays", SqlDbType.BigInt);
cmd.Parameters["@TotalDays"].Value = txtNoofDays.Text;
cmd.Parameters.Add("@TypeOfLeave", SqlDbType.NVarChar, 50);
cmd.Parameters["@TypeOfLeave"].Value = rbtnType.SelectedValue;
cmd.Parameters.Add("@ReasonOfLeave", SqlDbType.NVarChar, 1000);
cmd.Parameters["@ReasonOfLeave"].Value = txtReason;
con.Close();
con.Open();
cmd.ExecuteNonQuery();
con.Close();
Stored proc is as follows:
ALTER PROCEDURE dbo.SP_InsertIntoLeave
(
@empid as char(20), @empName as nvarchar(50), @totalLeave as decimal(9) = 12, @LeaveFrom as datetime,
@LeaveTo as datetime, @TotalDays as bigint, @TypeOfLeave as nvarchar(50), @ReasonOfLeave as nvarchar(1000),
@RemainigLeave as decimal(9)
)
/*
(
@parameter1 datatype = default value,
@parameter2 datatype OUTPUT
)
*/
AS
/* SET NOCOUNT ON */
INSERT INTO Leave_Table
(
emp_id, emp_Name, Total_Leave, Leave_From, Leave_To, Total_no_of_Days, Type_of_Leave, Reason_of_Leave,
Leave_Remaining
)
VALUES
(
@empid, @empName, @totalLeave, @LeaveFrom, @LeaveTo, @TotalDays, @TypeOfLeave, @ReasonOfLeave,
@RemainigLeave
)
RETURN
Thanks in Advance.
View 1 Replies
View Related
Jul 3, 2007
I have created a stored procedure that takes several parameters and ultimately does an INSERT on two tables. The sp returns with an integer indicating which is positive if one or more rows were added.
If I execute the SP by hand using the SQL Server Management Studio Express I get the proper results, the records are added to both tables and the return values are proper. One is an output parameter indicating the Identity value of the main record, the return value simply >0 if OK.
However, when I use C#, build my connection, command and its associated parameters making sure they match the SP then I get a malfunction.
The problem is that when I call ExecuteNonQuery the integer value it returns is -1 even though calling it from Mgmt. Studio gives a >0 result. Even though it returns -1 I can confirm that the records were added to BOTH tables and that the output parameter (The identity) given to me is also correct. However the return value is always -1.
I have no idea what is going wrong, Since I have SQL Express 2005 I do cannot do profiling :(. I really don't see why this goes wrong and I think using ExecuteScalar is not the best choice for this type of action.
View 3 Replies
View Related
Oct 22, 2007
what the max length that I can run query to sql Server?example:
View 2 Replies
View Related
Feb 14, 2008
HI
I am using ExecuteNonQuery to run an UPDATE statement but i keep getting the following error message: "ExecuteNonQuery: Connection property has not been initialized. "
this is my code can anyone see what is wrong?
DBCommand.CommandType = CommandType.TextDBCommand.CommandText = queryCourse
DBConnection.Open()
ExecuteNonQuery = DBCommand.ExecuteNonQuery()
DBCommand.ExecuteNonQuery()
DBConnection.Close()
help would be appreciated.
View 1 Replies
View Related
Apr 29, 2007
I have a DataAccess that has "cmd.ExecuteNonQuery" and another file SqlTableProfileProvider for a SandBox project(from ASP.NET) for a Custom TableProfileProvider. That also has a cmd.ExecuteNonQuery even thou these are 2 different files i get an error after debug.
Question is are they conflicting ? if so how can i fix this.
Code Snippets for both provided below the 2nd cmd.ExecuteNonQuery that is producing the Error is Commented in Orange...Thxs for the help Rattlerr
Incorrect syntax near ','.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near ','.
Source Error:
Line 454: cmd.CommandType = CommandType.Text;
Line 455:
Line 456: cmd.ExecuteNonQuery();
Line 457:
Line 458: // Need to close reader before we try to update
[SqlException (0x80131904): Incorrect syntax near ','.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +859322
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +736198
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1959
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +149
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +903
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +132
System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +415
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +135
Microsoft.Samples.SqlTableProfileProvider.SetPropertyValues(SettingsContext context, SettingsPropertyValueCollection collection) in d:Programming ProgramsXtremesystemsXtremesystemsxsApp_CodeSqlTableProfileProvider.cs:456
System.Configuration.SettingsBase.SaveCore() +379
System.Configuration.SettingsBase.Save() +77
System.Web.Profile.ProfileBase.SaveWithAssert() +31
System.Web.Profile.ProfileBase.Save() +63
System.Web.Profile.ProfileModule.OnLeave(Object source, EventArgs eventArgs) +2374047
System.Web.SyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +92
System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +64
DataAccess.cs::
Code Snippet
public abstract class DataAccess
{
private string _connectionString = "";
protected string ConnectionString
{
get { return _connectionString; }
set { _connectionString = value; }
}
private bool _enableCaching = true;
protected bool EnableCaching
{
get { return _enableCaching; }
set { _enableCaching = value; }
}
private int _cacheDuration = 0;
protected int CacheDuration
{
get { return _cacheDuration; }
set { _cacheDuration = value; }
}
protected Cache Cache
{
get { return HttpContext.Current.Cache; }
}
protected int ExecuteNonQuery(DbCommand cmd)
{
if (HttpContext.Current.User.Identity.Name.ToLower() == "sampleeditor")
{
foreach (DbParameter param in cmd.Parameters)
{
if (param.Direction == ParameterDirection.Output ||
param.Direction == ParameterDirection.ReturnValue)
{
switch (param.DbType)
{
case DbType.AnsiString:
case DbType.AnsiStringFixedLength:
case DbType.String:
case DbType.StringFixedLength:
case DbType.Xml:
param.Value = "";
break;
case DbType.Boolean:
param.Value = false;
break;
case DbType.Byte:
param.Value = byte.MinValue;
break;
case DbType.Date:
case DbType.DateTime:
param.Value = DateTime.MinValue;
break;
case DbType.Currency:
case DbType.Decimal:
param.Value = decimal.MinValue;
break;
case DbType.Guid:
param.Value = Guid.Empty;
break;
case DbType.Double:
case DbType.Int16:
case DbType.Int32:
case DbType.Int64:
param.Value = 0;
break;
default:
param.Value = null;
break;
}
}
}
return 1;
}
else
return cmd.ExecuteNonQuery();
}
protected IDataReader ExecuteReader(DbCommand cmd)
{
return ExecuteReader(cmd, CommandBehavior.Default);
}
protected IDataReader ExecuteReader(DbCommand cmd, CommandBehavior behavior)
{
return cmd.ExecuteReader(behavior);
}
protected object ExecuteScalar(DbCommand cmd)
{
return cmd.ExecuteScalar();
}
SecondFile::
Code Snippet
public override void SetPropertyValues(SettingsContext context, SettingsPropertyValueCollection collection) {
string username = (string)context["UserName"];
bool userIsAuthenticated = (bool)context["IsAuthenticated"];
if (username == null || username.Length < 1 || collection.Count < 1)
return;
SqlConnection conn = null;
SqlDataReader reader = null;
SqlCommand cmd = null;
try {
bool anyItemsToSave = false;
// First make sure we have at least one item to save
foreach (SettingsPropertyValue pp in collection) {
if (pp.IsDirty) {
if (!userIsAuthenticated) {
bool allowAnonymous = (bool)pp.Property.Attributes["AllowAnonymous"];
if (!allowAnonymous)
continue;
}
anyItemsToSave = true;
break;
}
}
if (!anyItemsToSave)
return;
conn = new SqlConnection(_sqlConnectionString);
conn.Open();
List<ProfileColumnData> columnData = new List<ProfileColumnData>(collection.Count);
foreach (SettingsPropertyValue pp in collection) {
if (!userIsAuthenticated) {
bool allowAnonymous = (bool)pp.Property.Attributes["AllowAnonymous"];
if (!allowAnonymous)
continue;
}
//Normal logic for original SQL provider
//if (!pp.IsDirty && pp.UsingDefaultValue) // Not fetched from DB and not written to
//Can eliminate unnecessary updates since we are using a table though
if (!pp.IsDirty)
continue;
string persistenceData = pp.Property.Attributes["CustomProviderData"] as string;
// If we can't find the table/column info we will ignore this data
if (String.IsNullOrEmpty(persistenceData)) {
// REVIEW: Perhaps we should throw instead?
continue;
}
string[] chunk = persistenceData.Split(new char[] { ';' });
if (chunk.Length != 2) {
// REVIEW: Perhaps we should throw instead?
continue;
}
string columnName = chunk[0];
// REVIEW: Should we ignore case?
SqlDbType datatype = (SqlDbType)Enum.Parse(typeof(SqlDbType), chunk[1], true);
object value = null;
// REVIEW: Is this handling null case correctly?
if (pp.Deserialized && pp.PropertyValue == null) { // is value null?
value = DBNull.Value;
}
else {
value = pp.PropertyValue;
}
// REVIEW: Might be able to ditch datatype
columnData.Add(new ProfileColumnData(columnName, pp, value, datatype));
}
// Figure out userid, if we don't find a userid, go ahead and create a user in the aspnetUsers table
Guid userId = Guid.Empty;
cmd = new SqlCommand("SELECT u.UserId FROM vw_aspnet_Users u WHERE u.ApplicationId = '" + AppId + "' AND u.UserName = LOWER(@Username)", conn);
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@Username",username);
try {
reader = cmd.ExecuteReader();
if (reader.Read()) {
userId = reader.GetGuid(0);
}
else {
reader.Close();
cmd.Dispose();
reader = null;
cmd = new SqlCommand("dbo.aspnet_Users_CreateUser", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ApplicationId", AppId);
cmd.Parameters.AddWithValue("@UserName", username);
cmd.Parameters.AddWithValue("@IsUserAnonymous", !userIsAuthenticated);
cmd.Parameters.AddWithValue("@LastActivityDate", DateTime.UtcNow);
cmd.Parameters.Add(CreateOutputParam("@UserId", SqlDbType.UniqueIdentifier, 16));
cmd.ExecuteNonQuery();
userId = (Guid)cmd.Parameters["@userid"].Value;
}
}
finally {
if (reader != null) {
reader.Close();
reader = null;
}
cmd.Dispose();
}
// Figure out if the row already exists in the table and use appropriate SELECT/UPDATE
cmd = new SqlCommand(String.Empty, conn);
StringBuilder sqlCommand = new StringBuilder("IF EXISTS (SELECT 1 FROM ").Append(_table);
sqlCommand.Append(" WHERE UserId = @UserId) ");
cmd.Parameters.AddWithValue("@UserId", userId);
// Build up strings used in the query
StringBuilder columnStr = new StringBuilder();
StringBuilder valueStr = new StringBuilder();
StringBuilder setStr = new StringBuilder();
int count = 0;
foreach (ProfileColumnData data in columnData) {
columnStr.Append(", ");
valueStr.Append(", ");
columnStr.Append(data.ColumnName);
string valueParam = "@Value" + count;
valueStr.Append(valueParam);
cmd.Parameters.AddWithValue(valueParam, data.Value);
// REVIEW: Can't update Timestamps?
if (data.DataType != SqlDbType.Timestamp) {
if (count > 0) {
setStr.Append(",");
}
setStr.Append(data.ColumnName);
setStr.Append("=");
setStr.Append(valueParam);
}
++count;
}
columnStr.Append(",LastUpdatedDate ");
valueStr.Append(",@LastUpdatedDate");
setStr.Append(",LastUpdatedDate=@LastUpdatedDate");
cmd.Parameters.AddWithValue("@LastUpdatedDate", DateTime.UtcNow);
sqlCommand.Append("BEGIN UPDATE ").Append(_table).Append(" SET ").Append(setStr.ToString());
sqlCommand.Append(" WHERE UserId = '").Append(userId).Append("'");
sqlCommand.Append("END ELSE BEGIN INSERT ").Append(_table).Append(" (UserId").Append(columnStr.ToString());
sqlCommand.Append(") VALUES ('").Append(userId).Append("'").Append(valueStr.ToString()).Append(") END");
cmd.CommandText = sqlCommand.ToString();
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery(); //THIS cmd.ExecuteNonQuery Produces the Error
// Need to close reader before we try to update
if (reader != null) {
reader.Close();
reader = null;
}
UpdateLastActivityDate(conn, userId);
}
finally {
if (reader != null)
reader.Close();
if (cmd != null)
cmd.Dispose();
if (conn != null)
conn.Close();
}
}
View 17 Replies
View Related
Apr 21, 2008
I have an interesting error that shows up on Windows Mobile 6.0 devices but does not appear to affect Windows Mobile 5.0 devices. The application runs in .NET CF 2.0 on SQL CE 3.0 using Merge Replication. When the application starts, there is an ExecuteNonQuery prior to calling the first synchronization. It appears that once the application has replicated that you can no longer create a connection to the database and run an ExecuteNonQuery successfully. If you have already created the SqlCeConnection and just open and close it as needed that you are able to run ExecuteNonQuery commands.
In Windows Monile 6.0; however, I eventually run out of memory, like there is a leak someplace. Does anyone have any ideas for how you would actually be able to dispose of the SqlCeConnection and recreate it each time? Does calling .Close() release all the native resources on a SqlCeConnection? I am trying out one more modification, I realized the SqlCeReplication object was not being disposed of and I'm guessing this probably also uses native resources; can anyone confirm this would also potentially cause a problem?
The application works but it is aggravating to have to softboot once in awhile to recover the memory, I'd like to move beyond it. Any ideas would be great!
View 2 Replies
View Related
Jan 29, 2004
Hello,
I'have this SyBase and SQL Server example update query:
UPDATE Table1 SET
Table1.Col1 = Table2.Col1 + Table2.Col2
Table1.Col2 = -1
FROM Table2
WHERE Table1.Id = Table2.Id
If i try to execute this query under SqlServerCE 2.0 i get this error message:
Native Error:(25501)
Description: There was an error parsing the query.
How can i write this query for running it under SqlServerCE 2.0?
Many Thanks in advance!!!
Mauro
View 1 Replies
View Related
Sep 4, 2007
I registered to redistribute the runtime for SQL Server Compact edition.
I have a desktop application which uses SDF files as the database.
In VS 2008 - when I set System.Data.SqlServerCe configured to Copy Local as TRUE - sqlserverce.dll does not get inserted to my bin directory. I am assuming if I manually put the DLL file into my bin directory (and deploy it) - the DLL file will automatically be used by my app?
View 4 Replies
View Related
Mar 6, 2008
Does anybody know if sqlserverce's license is freeware or shareware??
thanks a lot!
View 1 Replies
View Related
Mar 27, 2008
I try to get the value return from GetRandomPosition StoredProc, and it throws a statement saying incorrect syntax.
Error:
Line 1: Incorrect syntax near 'GetRandomPosition'.
Mark up:
myPuzzleCmd.Execute is used in Classic ASP and it works just fine.
I tried to subsitute with ExecuteReader / ExecuteScalar / ... etc, none of them fix it.
View 13 Replies
View Related
Aug 1, 2004
Hi all!
I basically need to get some records from a table and while looping through them i need to insert some records on other table.
I keep getting this error:
There is already an open DataReader associated with this connection which must be closed first.
The piece of code that I have is like this:
...
SqlCommand sqlCmd2 = new SqlCommand(sqlString2, dbConn);
sqlCmd2.Transaction = trans;
SqlDataReader dr = sqlCmd2.ExecuteReader(CommandBehavior.CloseConnection);
//loop through dr
while (dr.Read())
{
string sqlStr = "insert into prodQtyPrice (typeQtyId, prodId, typeId) values(28," + dr["prodId"] + "," + dr["typeId"] +")";
SqlCommand sqlCmd3 = new SqlCommand(sqlStr, dbConn);
//sqlCmd3.Transaction = trans;
sqlCmd3.ExecuteNonQuery();
}
...
Also I would like to have the insertions in the same transaction as the previous sql commands.
Thanks a million!
LAM
View 4 Replies
View Related
Aug 12, 2004
Hi
I have an asp.net [c#] page that queries a database which worked fine until I entered a new field called VSReferenceNumber.
The error message suggest there is something wrong with the sql string, but for the life of me I can't see why it is wrong.
I believe the problem is somewhere here:
SqlString += "VSReferenceNumber = '" + VSReferenceNumber.Replace("'", "''") + "', ";
Any help would be most appreciated.
Many thanks in advance
Regards
Miles
The Error is::
System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 'VSReferenceNumber'. at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at ASP.workingdatagrid2_aspx.myDataGrid_Update(Object Sender, DataGridCommandEventArgs e) in C:Inetpubwwwrootuapworkingdatagrid2.aspx:line 64
Here's the code::
<script runat="server">
//public string ConnString = "server=atw_data01;database=UAP;uid=atw_cheryl.theobald;pwd=nudibranch;";
public string ConnString = "Server=(local); user id=sa;password=;initial catalog = UAP;";
void BindData()
{
//-- Using the Try statement, we attempt to connect to our
//-- database, execute a SqlDataAdapter to store our data,
//-- populate a dataset and then bind that dataset
//-- to our DataGrid.
try
{
SqlConnection SqlConn = new SqlConnection(ConnString);
string SqlString = "SELECT [uapID], [DealershipName], [AutoExchangeClientID], [VSReferenceNumber] FROM uapForm";
SqlDataAdapter SqlComm = new SqlDataAdapter(SqlString, SqlConn);
DataSet customerData = new DataSet();
SqlComm.Fill(customerData, "uapForm");
myDataGrid.DataSource = customerData;
myDataGrid.DataBind();
SqlConn.Close();
SqlComm.Dispose();
SqlConn.Dispose();
}
//-- If we are not able to connect, display a friendly error
catch (Exception e)
{
ErrorLabel.Text = "Not able to connect to database. See description below: <P>";
ErrorLabel.Text += e.ToString();
}
}
void myDataGrid_Update (object Sender, DataGridCommandEventArgs e)
{
//-- Take the data from each textbox in our editable item
//-- and assign that text to a string variable
string uapID = Convert.ToString(e.Item.Cells[0].Text);
string DealershipName = ((TextBox) e.Item.Cells[1].Controls[0]).Text;
string AutoExchangeClientID = ((TextBox) e.Item.Cells[2].Controls[0]).Text;
string VSReferenceNumber = ((TextBox) e.Item.Cells[3].Controls[0]).Text;
//-- Again, using the Try statement, attempt to connect to our database
//-- and make an update with the data from our datagrid
SqlConnection SqlConn = new SqlConnection(ConnString);
try
{
SqlConn.Open();
string SqlString = "UPDATE uapForm ";
SqlString += "SET DealershipName = '" + DealershipName.Replace("'", "''") + "', ";
SqlString += "AutoExchangeClientID = '" + AutoExchangeClientID.Replace("'", "''") + "' ";
SqlString += "VSReferenceNumber = '" + VSReferenceNumber.Replace("'", "''") + "', ";
SqlString += " WHERE uapID = '" + uapID + "'";
SqlCommand SqlComm = new SqlCommand(SqlString, SqlConn);
SqlComm.ExecuteNonQuery();
SqlConn.Close();
SqlComm.Dispose();
SqlConn.Dispose();
}
//-- If for some reason we cannot connect, display a friendly error.
catch (Exception exc)
{
ErrorLabel.Text = "Not able to connect to database. <br>Please See description below:<P> <P>";
ErrorLabel.Text += exc.ToString();
}
//-- Remove the edit focus
myDataGrid.EditItemIndex = -1;
//-- Rebind our datagrid
BindData();
}
void myDataGrid_Cancel(object Sender, DataGridCommandEventArgs e)
{
//-- Remove the edit focus
myDataGrid.EditItemIndex = -1;
//-- Rebind our datagrid
BindData();
}
void myDataGrid_Edit(object Sender, DataGridCommandEventArgs e)
{
//-- Set the edit focus to the item that was selected
myDataGrid.EditItemIndex = (int) e.Item.ItemIndex;
//-- Rebind our datagrid
BindData();
}
void Page_Load (object Sender, EventArgs e)
{
//-- If the page is not posting back, bind our datagrid
if (!Page.IsPostBack)
{
BindData();
}
}
</script>
View 4 Replies
View Related
Apr 27, 2006
Hi,
I'm using .net 2.0 and I have created a stored procedure (sql server DB) which has one insert statement with 2 input parameters. When I execute this stored procedure using ExecuteNonQuery(), it always returning -1. I want to know how many records are effected. I want to know if there is any error. Help appreciated.
Thanks,
Brett
code looks like this:
SqlConnection sqlConn = new SqlConnection();
sqlConn.ConnectionString = "myString...";
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "usp_InsertApplicationInfo";
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter param1 = cmd.Parameters.Add("@vchrApplicationName", SqlDbType.VarChar);
param1.Direction = ParameterDirection.Input;
SqlParameter param2 = cmd.Parameters.Add("@vchrApplicationDescription", SqlDbType.VarChar);
param2.Direction = ParameterDirection.Input;
param1.Value = "prama1";
param2.Value = "test";
cmd.Connection = sqlConn;
sqlConn.Open();
int rows = cmd.ExecuteNonQuery();
View 2 Replies
View Related
Dec 13, 2006
I was trying an RDA Demo project. However, everytime I pull the data from Sql Server 2005 and then move on to add a row to the table using SqlCeCommand.ExecuteNonQuery(), the application fails catastrophically without giving any exception and simply exits!
When I have pulled the data once and this application has shut down (exited) I can open it again and then continue normally but I can't expect the application to PULL the data and then even add or update a row to that table. I can do select queries fine. Why would this happen?
I am disposing all the objects that I use in both the scenarios, or atleast I believe so.
Does any one have any idea?
View 3 Replies
View Related
May 18, 2007
Hy, again! I am at the begining of an application. I have some modules that insert, delete and update only one row at one table. My question is should I use dataadapter.Update() or ExecuteNonQuery(). I prefer ExecuteNonQuery because I want to build a class : DataLayer to implement my own InsertProcedure(), UpdateProcedure(),DeleteProcedure(). I want speed in my application, so which is the best: dataadapter.Update() OR ExecuteNonQuery(). Thank you!
View 5 Replies
View Related
Apr 10, 2008
I'm using VB.Net 2008 with SQL Compact 3.5. After trying forever, I finally got my app to open a retrieve a record from a table. Now, when I try to update the record, I get an error on the ExecuteNonQuery statement. The error says I am "attempting to read or write protected memory". The code works perfectly with an Access database.
Here is the code I am using:
Dim objConnection As Data.OleDb.OleDbConnection
Dim objCommand As Data.OleDb.OleDbCommand
Dim sConnection As String
Dim sTableName As String
Try
'Set Table Name
sTableName = "Settings"
'Build SQL and Connection strings
'sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mstrDataPath
sConnection = "Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=" & mstrDataPath
objConnection = New Data.OleDb.OleDbConnection(sConnection)
'Create the UpdateCommand
objCommand = New Data.OleDb.OleDbCommand("UPDATE " & sTableName & " SET " & _
"OwnerName = ?, " & _
"Address1 = ?, " & _
"Address2 = ?, " & _
"City = ?, " & _
"State = ?, " & _
"ZipCode = ?, " & _
"Phone = ?, " & _
"EmailAddress = ? ", objConnection)
objCommand.Parameters.Add(New Data.OleDb.OleDbParameter)
objCommand.Parameters(0).Value = mstrOwnerName
objCommand.Parameters.Add(New Data.OleDb.OleDbParameter)
objCommand.Parameters(1).Value = mstrAddress1
objCommand.Parameters.Add(New Data.OleDb.OleDbParameter)
objCommand.Parameters(2).Value = mstrAddress2
objCommand.Parameters.Add(New Data.OleDb.OleDbParameter)
objCommand.Parameters(3).Value = mstrCity
objCommand.Parameters.Add(New Data.OleDb.OleDbParameter)
objCommand.Parameters(4).Value = mstrState
objCommand.Parameters.Add(New Data.OleDb.OleDbParameter)
objCommand.Parameters(5).Value = mstrZipCode
objCommand.Parameters.Add(New Data.OleDb.OleDbParameter)
objCommand.Parameters(6).Value = mstrPhone
objCommand.Parameters.Add(New Data.OleDb.OleDbParameter)
objCommand.Parameters(7).Value = mstrEmailAddress
objConnection.Open()
objCommand.ExecuteNonQuery()
objConnection.Close()
objCommand.Dispose()
UpdateRecord = True
Catch objException As Exception
UpdateRecord = False
MessageBox.Show(objException.ToString, "Error")
End Try
View 21 Replies
View Related
Aug 16, 2006
I have developed a smart device application using SqlServerCE that replicates data to the backend server. I need to provide to the user a status of this replication process. I read about the "MergeSubscriberMonitor" in SQL Server but can't find it in SqlServerCe. Is this possible? Can someone please point me in the right direction?
View 6 Replies
View Related
Sep 21, 2007
I have successfully converted a MSVS 2005 Winforms Application into the MSVS 2008 beta 2. I am however having trouble getting the winmobile data tables to fill. The ODBC Paradox tables fill fine. When I converted the application I had to re create the win mobile datasource for the project. So I used the datasource wizard in 2008, it opened the winmobile5 database and told me that I would need to convert the database to 3.5. I let it overwrite the existing database and it created a new .xsd file for me for the project. When I try to load data into the database tables
this.proceduresTableAdapter.Fill(this.fB7MobileDBDataSet.Procedures);
I get the native error 28609
You are trying to access an older version of a SQL Server Compact 3.5 database. If this is a SQL Server CE 1.0 or 2.0 database, run upgrade.exe. If this is a SQL Server Compact 3.5 database, run Compact/Repair.
I have run Compact but not repair, and still get the same error.
1. What could be left in the project that ismaking it expect to open the 3.0 database instead of the 3.5 that was converted in the datasource wizard?(If I substitute an older database file I get the native error 25010 Invalid File Name. Check the database filename. )
2. Is there a way after you convert a project to then add a datasource to the project that would use the same xsd file and datasetDesigner.cs from before the conversion ?
Thanks Jon Stroh
View 2 Replies
View Related
