Synch between Excel CVS <-> SQLCE

TheMSsForum.com: The Microsoft Software Forum

The MSS Forum ‹ Ado
- Archive
  - Biz
  - MCSE
  - CRM
  - Drivers
  - Framework
  - ADO
  - ASP
  - Compact
  - Forms
  - Dotnet
  - C#
  - VB
  - FontpageGen
  - Excel
  - WorkSheet
  - Exchange
  - Setup
  - Fox
  - Fontpage
  - ASP
  - IIS
  - Entourage
  - Money
  - Messanger
  - PocketPC
  - Powerpoint
  - Project
  - Publisher
  - Excel
  - VB
  - Security
  - Portal
  - Services
  - SQLServerDev
  - SVCS
  - SQLServer
  - VB
  - VC
  - MFC
  - ExcelGen

- Previous
 - 1
 - Datarelation between different column types Hi, I am trying to create a new datarelation between columns in two tables. The data types of the columns are Integer and Double, and when I try and create the data relation I get an invalid constraint exception thrown that says: Parent columns and child columns don't have type- matching columns' Is there anyway of getting around this issue, other than changing the types of the columns? I am linking to the data using an MS Access database (the data is an ODBC source) and so I can't change the type! Even though the type of the column that MS Access has linked to is Double, it does in fact only contain integer values. Many thanks, Jason. Tag: Synch between Excel CVS <-> SQLCE Tag: 115061
 - 2
 - DataTable bug http://support.microsoft.com/d efault.aspx?scid=kb;en-us;8917 42 Is there any new information for the DataTable Select Sort-Filter bug ? Will moving to the 2.0 Framework help ? Are there any specific workarounds ? thanks in advance Shyamal Tag: Synch between Excel CVS <-> SQLCE Tag: 115060
 - 3
 - SQLDataAdapter not working with multi-row delete Hello there.. the basic question is this - are there known issues with a sqldataadapter update method not processing all of the deletes and / or updates listed in the modified table? Now - the long version so you know the background... ok - the predicament of the night! I have a windows app that is designed to run in a disconnected manner to a sql server hosted on an Intranet. Basically, I press a button - which loads all of the data off of the sql server in the database into a Dataset. From there, I load each table in the dataset into an indivudal DataTable - worked a little better with MDI forms. The pp contains multiple tabs - which have a datagrid on them. The datagrid loads data pulled from a DataView that is loaded by filling it from the DataTable that was previously defined. Now, when I want to update the SQl server database, I do the following. 1. Detect the change on the DataTable using the following code where m_dtsystems is a DataTable. Dim changeadd As DataTable = m_dtsystems.GetChanges(DataRowState.Added) Dim changedelete As DataTable = m_dtsystems.GetChanges(DataRowState.Deleted) Dim changemodified As DataTable = m_dtsystems.GetChanges(DataRowState.Modified) ' try statement is wrapped in UpdateTable procedure for easier use Try If Not (changeadd Is Nothing) Then MsgBox("Add detected") UpdateTable(changeadd) changeadd.Dispose() End If If Not (changedelete Is Nothing) Then MsgBox("Delete detected") UpdateTable(changedelete) changedelete.Dispose() End If If Not (changemodified Is Nothing) Then MsgBox("Edit detected") UpdateTable(changemodified) changemodified.Dispose() End If catch ex as exception end try 2. Depending on the value of one of the tables, I then, as you see, pass the newly created DataTable (the one with modifications) to a function called UpdateTable. This function is responsible for passing the changed data to the webservice and the code is as follows: Private Sub UpdateTable(ByVal dttable As DataTable) Try Dim myds As New DataSet myds.Merge(dttable) Dim ws As New localhost.SQLWorks Dim mycount As Integer mycount = ws.UpdateTable(myds) MsgBox("Successful Save to Web Database! ... " & mycount & " records affected!") Catch ex As Exception MsgBox("Error Saving to Web Database! - " & ex.ToString) End Try End Sub 3. This method calls the UpdateTable web method of my web service which creates a new DataSet, takes the supplied DataTable and merges into the dataset and then updates the sql database via the adapter using the modified Dataset. The code: <WebMethod()> Public Function UpdateTable(ByVal ds As DataSet) As Integer Dim rowcount As Integer = 0 Dim tablename As String = ds.Tables(0).TableName.ToString Try ' dataadapter opens and closes connection automatically Dim connection As New SqlConnection("Initial catalog=sqldev01;" & "Data Source=(local); User ID=testuser; password=test321") Dim query As String = "SELECT * from " & tablename Dim dbadapter As SqlDataAdapter = New SqlDataAdapter(query, connection) Dim cmdbldr As New SqlCommandBuilder(dbadapter) dbadapter.InsertCommand = cmdbldr.GetInsertCommand dbadapter.DeleteCommand = cmdbldr.GetDeleteCommand dbadapter.UpdateCommand = cmdbldr.GetUpdateCommand rowcount = dbadapter.Update(ds, tablename) UpdateTable = rowcount Catch ex As Exception UpdateTable = 21000 End Try End Function *** THE PROBLEM*** This works great for all tables and all basic modifications to the tables. The problem I have is when I attempt to delete or edit more than one record in the DataTable the is stored on the client app. It throws an error! It will allow me to Add as many records as I want and remove a single record. I can also edit up to three records at a time. But I can't delete, say 4 records at a time. The updateTable web service exception gets thrown. I know this is long winded - but can ANYONE help me get through this. Been on it for some 12 hours strasight. thanks in advance. -mike Tag: Synch between Excel CVS <-> SQLCE Tag: 115058
 - 4
 - SQLDataAdapter not working with multi-row delete Hello there.. the basic question is this - are there known issues with a sqldataadapter update method not processing all of the deletes and / or updates listed in the modified table? Now - the long version so you know the background... ok - the predicament of the night! I have a windows app that is designed to run in a disconnected manner to a sql server hosted on an Intranet. Basically, I press a button - which loads all of the data off of the sql server in the database into a Dataset. From there, I load each table in the dataset into an indivudal DataTable - worked a little better with MDI forms. The pp contains multiple tabs - which have a datagrid on them. The datagrid loads data pulled from a DataView that is loaded by filling it from the DataTable that was previously defined. Now, when I want to update the SQl server database, I do the following. 1. Detect the change on the DataTable using the following code where m_dtsystems is a DataTable. Dim changeadd As DataTable = m_dtsystems.GetChanges(DataRowState.Added) Dim changedelete As DataTable = m_dtsystems.GetChanges(DataRowState.Deleted) Dim changemodified As DataTable = m_dtsystems.GetChanges(DataRowState.Modified) ' try statement is wrapped in UpdateTable procedure for easier use Try If Not (changeadd Is Nothing) Then MsgBox("Add detected") UpdateTable(changeadd) changeadd.Dispose() End If If Not (changedelete Is Nothing) Then MsgBox("Delete detected") UpdateTable(changedelete) changedelete.Dispose() End If If Not (changemodified Is Nothing) Then MsgBox("Edit detected") UpdateTable(changemodified) changemodified.Dispose() End If catch ex as exception end try 2. Depending on the value of one of the tables, I then, as you see, pass the newly created DataTable (the one with modifications) to a function called UpdateTable. This function is responsible for passing the changed data to the webservice and the code is as follows: Private Sub UpdateTable(ByVal dttable As DataTable) Try Dim myds As New DataSet myds.Merge(dttable) Dim ws As New localhost.SQLWorks Dim mycount As Integer mycount = ws.UpdateTable(myds) MsgBox("Successful Save to Web Database! ... " & mycount & " records affected!") Catch ex As Exception MsgBox("Error Saving to Web Database! - " & ex.ToString) End Try End Sub 3. This method calls the UpdateTable web method of my web service which creates a new DataSet, takes the supplied DataTable and merges into the dataset and then updates the sql database via the adapter using the modified Dataset. The code: <WebMethod()> Public Function UpdateTable(ByVal ds As DataSet) As Integer Dim rowcount As Integer = 0 Dim tablename As String = ds.Tables(0).TableName.ToString Try ' dataadapter opens and closes connection automatically Dim connection As New SqlConnection("Initial catalog=sqldev01;" & "Data Source=(local); User ID=testuser; password=test321") Dim query As String = "SELECT * from " & tablename Dim dbadapter As SqlDataAdapter = New SqlDataAdapter(query, connection) Dim cmdbldr As New SqlCommandBuilder(dbadapter) dbadapter.InsertCommand = cmdbldr.GetInsertCommand dbadapter.DeleteCommand = cmdbldr.GetDeleteCommand dbadapter.UpdateCommand = cmdbldr.GetUpdateCommand rowcount = dbadapter.Update(ds, tablename) UpdateTable = rowcount Catch ex As Exception UpdateTable = 21000 End Try End Function *** THE PROBLEM*** This works great for all tables and all basic modifications to the tables. The problem I have is when I attempt to delete or edit more than one record in the DataTable the is stored on the client app. It throws an error! It will allow me to Add as many records as I want and remove a single record. I can also edit up to three records at a time. But I can't delete, say 4 records at a time. The updateTable web service exception gets thrown. I know this is long winded - but can ANYONE help me get through this. Been on it for some 12 hours strasight. thanks in advance. -mike Tag: Synch between Excel CVS <-> SQLCE Tag: 115057
 - 5
 - RDS not populating AND not giving an error. I have seen other posts like this, but could not find one that included resolution. I am testing the following on a Windows2000 client box with IIS installed. Working in Visual Interdev 6.0. I have added the following code to my ASP page: <OBJECT CLASSID="clsid:BD96C556-65A3-11D0-983A-00C04FC29E33" ID="dsoDATATABLE" WIDTH="0" HEIGHT="0"> <PARAM NAME="Connect" VALUE="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydb.mdb;"> <PARAM NAME="SQL" VALUE="SELECT * FROM tbleData"> </OBJECT> <DIV>HI</DIV> <DIV datasrc="#dsoDATATABLE" datafld="DataID"></DIV> The connection string works when I use it in Server.CreateObject(ADODB...etc) What happens when I load the web page is that the page loads with no errors. However, nothing is displayed in the DIV section and I am sure the table has data in it. Does anyone know what might be causing this? Thanks! Tag: Synch between Excel CVS <-> SQLCE Tag: 115050
 - 6
 - Shopping Cart not updating until refresh - Access 2K backend This is my first shopping cart i have written and i have a problem when adding items that already exists in the cart. When a user adds to cart, they automatically redirected to the cart, where they can update quantities. If the item already exists in the cart, i want to update the quantity by adding 1 to the quantity. The code all works fine when i step thru the code, however when running it without debugging.. the quantity does not is not updated until the cart page is refreshed.. I can only think that the record in the cart table has not been updated, by the time that the cart is being read to load the cart page. How can i get round this problem? Here is my code: Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load 'Put user code to initialize the page here If Page.IsPostBack = False Then GetShoppingCartList() End If End Sub Sub GetShoppingCartList() Dim cart As ChoicesYours.WebModules.Commerce.ShoppingCart = _ New ChoicesYours.WebModules.Commerce.ShoppingCart _ (System.Configuration.ConfigurationSettings.AppSettings("ConnectionString")) ' Obtain current user's shopping cart id Dim cartId As String = cart.GetShoppingCartId() ' If no items, hide details and display message If cart.GetItemCount(cartId) = 0 Then 'DetailsPanel.Visible = False lblError.Text = "There are currently no items in your shopping cart." Else ' Databind Gridcontrol with Shopping Cart Items Dim rdr As OleDbDataReader rdr = cart.GetItems(cartId) MyList.DataSource = rdr MyList.DataBind() rdr.Close() 'Update Total Price Label 'lblTotal.Text = String.Format("{0:c}", cart.GetTotal(cartId)) End If End Sub Public Sub btnUpdateCart_Click(ByVal sender As System.Object, _ ByVal e As System.Web.UI.ImageClickEventArgs) Handles btnUpdateCart.Click UpdateShoppingCartDatabase() GetShoppingCartList() End Sub Sub UpdateShoppingCartDatabase() Dim cart As ChoicesYours.WebModules.Commerce.ShoppingCart = _ New ChoicesYours.WebModules.Commerce.ShoppingCart _ (System.Configuration.ConfigurationSettings.AppSettings("ConnectionString")) ' Obtain current user's shopping cart id Dim cartId As String = cart.GetShoppingCartId() ' Iterate through all rows within shopping cart list Dim i As Integer For i = 0 To MyList.Items.Count - 1 ' Obtain references to row's controls Dim quantityTxt As TextBox = CType(MyList.Items(i).FindControl("Quantity"), TextBox) Dim remove As CheckBox = CType(MyList.Items(i).FindControl("Remove"), CheckBox) ' Wrap in try/catch block to catch errors in the event that someone types in ' an invalid value for quantity Dim quantity As Integer Try quantity = CInt(quantityTxt.Text) ' If the quantity field is changed or delete is checked If quantity <> CInt(MyList.DataKeys(i)) Or remove.Checked = True Then Dim lblProductID As Label = CType(MyList.Items(i).FindControl("ProductID"), Label) Dim lblProductDetailID As Label = CType(MyList.Items(i).FindControl("ProductDetailID"), Label) If quantity = 0 Or remove.Checked = True Then cart.RemoveItem(cartId, CInt(lblProductID.Text), CInt(lblProductDetailID.Text)) Else cart.UpdateItem(cartId, CInt(lblProductID.Text), CInt(lblProductDetailID.Text), _ quantity) End If End If Catch ex As Exception Throw New Lilipro.WebModules.AppException _ ("Error occured in ShoppingCartASPXVB.UpdateShoppingCartDatabase ", ex) End Try Next End Sub Tag: Synch between Excel CVS <-> SQLCE Tag: 115048
 - 7
 - datarow question I'm trying to create an instance of a data row, set it's cells, and pass this to a function for update. Well, so far this is not working a i'm sure most of you will know why. What's the correct way to do this. the dataset exists. I guess i need to know how to create the row? Dim dr As dsUser.usrRow dr.usrFName = Me.txtFName.Text dr.usrLName = Me.txtLname.Text -- thanks (as always) some day i''m gona pay this forum back for all the help i''m getting kes Tag: Synch between Excel CVS <-> SQLCE Tag: 115047
 - 8
 - Datagrid annoyance Hello all: This is a new one on me... I have a datagrid, bound to a disconnected datatable (actually a dataview flaked off of that table), to which my user is able to add new records. 1) When I click on the * for a new row, it takes a long time for the new row to appear. 2) Any value I enter into any column gets changed back to DBNull as soon as I click or tab off of that column. I have created many an editable datagrid, in exactly the same manner as I am doing now, but have never had this problem, and after much poking about I must admit I have no idea what the hell is going on. The columns are defined as readonly=false both at the datatable level and at the gridcolumnstyle level. The grid itself has readonly=false. I explicitly set AllowEdit and AllowNew to true for the dataview. The dataview sees that I'm trying to add a row at runtime, but of course, the various data items contain dbnull when I am looking at it at debug time. Ideas? Thanks much, zdrakec Tag: Synch between Excel CVS <-> SQLCE Tag: 115039
 - 9
 - Insert into Table from Manually Built DataTable Hello All, I have an application where I manually build two DataTables (i.e., I don't query a database table to populate the DataTables). I have to do this manual creation of the DataTables. These DataTables have the same structure as their associated database tables. Once I have the manual DataTables created with all the required data, I'd like to then insert the entire DataTable into its associated database table, without having to loop through each record of the DataTable and perform an insert for each row. Does that make sense? Would anyone know if it's possible to take a manually built DataTable and insert all the data from it into a database table in one swoop? And, if so, how? Thanks. Tag: Synch between Excel CVS <-> SQLCE Tag: 115037
 - 10
 - commandbehavior.CloseConnection quick question does the cmd.ExecuteScalar() auto close the connection the same as RxecuteReader(commandbehavior.CloseConnection)? -- thanks (as always) some day i''m gona pay this forum back for all the help i''m getting kes Tag: Synch between Excel CVS <-> SQLCE Tag: 115032
 - 11
 - DataGrid mismatch between grid and scrollbar slider position I have a (small) multi-table database application which allows edits, print reports, and has two scrolling screen displays which show information merged from multiple tables. I'm having trouble with one of the scrolling displays which is implemented as a DataGrid with several custom DataGridColumnStyles. When the Form containing the DataGrid is made visible the initial rows of the DataGrid's DataView source display as one might expect. Unfortunately, the scroll bar is positioned at the bottom of its track, and as soon as it is touched with the mouse the displayed portion of the DataGrid "jumps" down to match the slider's position. Using the debugger to dig into the DataGrid just before it displays, I find that its CurrentRowIndex is (as one might expect) 0, and the VertScrollBar's Value is around 142 for a table of 153 rows. The DataGrid's exterior does seem to correctly reflect its internal state... I just can't figure out how it could wind up in this state. Originally I didn't see this problem. The DataGrid always showed up with both the grid and the scrollbar at the top of the DataView. Trouble is, <grin> it wasn't correct -- it wasn't keeping in step with changes to the database. My current approach creates a DataGrid display by issuing a fairly complex SELECT to re-fill the underlying DataTable each time the Form containg the DataGrid becomes visible. I add a couple of extra DataColumns, fill them, and return a DataView based on the result. I then point the DataGrid at the new DataView; the code looks something like the following: DataView dv_dg; private void FormXX__VisibleChanged(... if ( ((Form)sender).Visible == true ) { dv_dg = GetNewDataView(); dataGrid1.SuspendLayout(); dataGrid1.SetDataBinding(dv_dg,""); dataGrid1.ResumeLayout(false); //nope dataGrid1.Update(); //nope dataGrid1.DataSource = dv_dg; //nope dataGrid1.CurrentRowIndex = 0; //nope dataGrid1.Refresh(); ... (The "//nope" lines are unsuccessful things I've tried in an attempt to get the scroll bar slider to position correctly) So... does anyone know what it is that controls where the VertScrollBar slider winds up when you point a DataGrid at a new DataSource? It's true that I could create my own DataGrid class and forcibly stomp on the slider, and if I con't come up with any other ideas that's what I'll have to do. However, I'm a little uncomfortable doing this without understanding why the problem is occurring: it feels too much like restoring power by screwing a burnt-out fuse down on a penny. It's a workaround, and you never know when the underlying cause might come back to bite you in some sensitive portion of your anatomy. <grin> Has anyone ever seen anything like this before? Does anyone have any idea what might make a DataGrid behave this way? Any suggestions will be welcomed. Frank McKenney, McKenney Associates Richmond, Virginia / (804) 320-4887 Munged E-mail: frank uscore mckenney ayut minds pring dawt cahm (y'all) -- Q: What are the benefits of speaking to your fans via e-mail? DNA: It's quicker, easier, and involves less licking. -- Douglas Adams / The Salmon of Doubt -- Tag: Synch between Excel CVS <-> SQLCE Tag: 115030
 - 12
 - Convert EML to MSG Hi everyone, I have a problem about email message format converting. I want to write a program in VB.NET to convert the EML format email message to MSG format, does anyone knows how to do it? If you know it you can send me a message to ckjonny5@hotmail.com Thanks at all. Jonny Tag: Synch between Excel CVS <-> SQLCE Tag: 115029
 - 13
 - How to pass OleDbParameter object across domain? I did not get any response from remoting newsgroup. So, I am posting here to see if anyone has an answer. Sorry for the cross-post.- Prodip I am sure many of you have encountered similar problem when you try to pass an OleDbParameter object from client to Remote Service. You will get a nice message saying - Because of security restrictions, the type System.Runtime.Remoting.ObjRef cannot be accessed. This has to do with the Serialization of the OleDbParameter object. When the Remote client try to make the call to Service method, it fails. How do we fix this? Microsoft has some documentation on ObjRef and Remoting - http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetsec/html/THCMCh21.asp In the above article it says- If the object passed as a parameter derives from System.MarshalByRefObject, it is passed by reference. In this case, the object requires a URL to support call backs to the client. It is possible for the client URL to be spoofed, which can result in a call back to an alternate computer. Well, OleDbParameter meets this criteria because it implements MarshalByRefObject. I did not quite understand about the URL part. I will appreciate any help you can offer. Client config file: <?xml version="1.0" encoding="utf-8" ?> <configuration> <system.runtime.remoting> <application name="test"> <channels> <channel ref="tcp" port="0" useDefaultCredentials="true"> <channelSinkProviders> <clientProviders> <formatter ref="soap" /> <formatter ref="binary" ></formatter> </clientProviders> <serverProviders> <formatter ref="soap" typeFilterLevel="Full" /> <formatter ref="binary" typeFilterLevel="Full" /> </serverProviders> </channelSinkProviders> </channel> </channels> </application> </system.runtime.remoting> </configuration> Thanks, Prodip www.aspnet4you.com Tag: Synch between Excel CVS <-> SQLCE Tag: 115026
 - 14
 - WinForm datagrid I have a datagrid on a Windows form and I'm using a table within a DataSetto populate the datagrid. That works just fine. I know I have to use an update and insert command with the DataAdapter for updating the database. Can someone please point me to a good resource for this? Thanks, DougS Tag: Synch between Excel CVS <-> SQLCE Tag: 115024
 - 15
 - Dataset Constraints - again Last week sometime I posted the message below but I, unfortunately, have not yet received an answer to the problem. The question has to do with how to intercept a constraint viloation message in code and give the user a different message. Someone responed by saying that I should use a try catch statement, which is obvious, but the question is, where do I place it? Where do I place such a try catch or some other code to catch a constraint violation before VS shows the default message. The original question follows: A question please. Lets say I have a grid with its datasource as a dataset with three columns in it; UserID (an auto generated ID), LastName and FirstName. Additionally, lets say I want to restrict the user from entering two records with the same last name and first name. I have created a constraint for LastName FirstName in the dataset but I would like a different message than the default message I get when the constraint is violated. Is there a way for me to do this? Tag: Synch between Excel CVS <-> SQLCE Tag: 115019
 - 16
 - Howto bind a date to a textbox from a strongly typed dataset Hello, I am trying to bind a date to a textbox. The date will be null initially and may need to stay null. How is this done? Is there a way to tell the textbox how to handle null values like in the datagrid? Thanks, Tag: Synch between Excel CVS <-> SQLCE Tag: 115013
 - 17
 - How to use SqlParameter with Update Query Hi, I have to update my table using SqlParameter. Below is my code for update. cmdText = "update docs set Content = @content where DirName='" + DirName.Replace("'","''") + "' and LeafName='Hidden.txt'" SqlCommand cmd = new SqlCommand(); cmd.CommandText = cmdText; cmd.Connection = con; SqlParameter objParam = new SqlParameter("@content", SqlDbType.Image, binFile.Length); objParam.Direction = ParameterDirection.Input; objParam.Value = binFile; cmd.Parameters.Add(objParam); int i = cmd.ExecuteNonQuery() when i execute this code it's returing i valu -1.(No records affected). What is the problem in my code. If anbody knows please let me know to solve it. Thanks, Vinoth vinoth@gsdindia.com Tag: Synch between Excel CVS <-> SQLCE Tag: 114999
 - 18
 - Problem accessing SQL Server using Windows Authentication Hi, I'm facing an while issue accessing SQL Server using Windows Authentication. There is one webpart (Sharepoint) which gets connected to a database and displays some data. There is one service account allocated - with Windows Authentication enabled. Now when the user loads the webpage, wepart tries to access using the connection string with Windows authentication. Webpart is trying to access the SQL Server DB with the logged in user's windows account - which does not have access to the DB. Is it possible to access the Database for the logged in user using another Windows Authenticated service account. Is it possible to execute one block of code with another service acccount (another windows credentials). Thanks in Adavance Suhas Tag: Synch between Excel CVS <-> SQLCE Tag: 114997
 - 19
 - Optimistic Concurrency - DataRow.Column Error Property Not Set I'm implementing an optimistic concurrency model within a VB.net application which makes use of a SQL Server 2000 database. I'm able to trap concurrency errors caused by an Update stored procedure, and can easily work with the DataRows which have errors, but cannot figure out why the ColumnError properties are not set. Using the DataRow.GetColumnsInError or DataRow.GetColumnError(column) methods return ziltch. The DataRow simply has a "concurrency error, the update effected 0 records" error. I test for concurrency issues by including all editable fields within the WHERE clause of the UPDATE stored procedure. I'm also using a DataAdapter to call the appropriate Select, Insert, Update, and Delete stored procedures. My DataAdaptor is set to continue updates when errors are encountered. I guess that I don't understand which object is responsible for setting the column error property, and when it's supposed to happen. Any help would be appreciated. I can work around this by comparing row version values, but knowing that there is an easier way is eating away at me. - CPatrick_530 Tag: Synch between Excel CVS <-> SQLCE Tag: 114988
 - 20
 - Error when updating database using the dataadapter.update method I get an Error when i try to update a row in a table using the dataadapter.update method. The table in this case is only two columns, the 1st col is the key (bucket_status) and the 2nd column is a string (Comments) and can be null. this is a parent table to another child table, my data base contains 7 tables that are all related. when a change is done to the data in the datagrid, i extract the changed records from the dataset, then i pass the new dataset (with the changed rows) to the dataadapter.update method. When i try to change the value of the comments column, i get the following error: "[DB2/LINUX390] SQL0418N A statement contains a use of a parameter marker that is not valid. SQLSTATE=42610" The SQL code for the dataadapter was auto generated and it looks like this: UPDATE dbname.Bucket_tbl SET BUCKET_STATUS = ?, COMMENTS = ? WHERE (BUCKET_STATUS = ?) AND (COMMENTS = ? OR ? IS NULL AND COMMENTS IS NULL) my code is fairly simple: daBucketStatus.Update(dsChanges) This is a DB2 database and i am using an OleDb connection. * inserts and selects work ok, problem is with the update and delete methods. I can run the generated SQL code in the command window with the values substitued for the parms and it works ok, however, the update method always returns the above error?!! Please help! Thanks Riad Amro Tag: Synch between Excel CVS <-> SQLCE Tag: 114984
 - 21
 - MySql ado.net connector problem My host doesn't offer SqlServer, so I'm taking a swing at MySql and so far I'm getting a FileNotFoundException in my asp.net app when I try to instantiate a MySqlConnection object. I have added a reference to the dll and including the namespace in my using directive. Here is the stack trace: [FileNotFoundException: The system cannot find the file specified.] [FileNotFoundException: File or assembly name 'MySql.Data, Version=1.0.4.20163, Culture=neutral, PublicKeyToken=c5687fc88969c44d', or one of its dependencies, was not found.] TheHudsonDrags.BackEnd_Components.CDAL..ctor() in e:\files\web sites\thehudsondrags\web_root\backend_components\dal.cs:23 TheHudsonDrags.Controls.News.Page_Load(Object sender, EventArgs e) in e:\files\web sites\thehudsondrags\web_root\controls\news.ascx.cs:22 System.Web.UI.Control.OnLoad(EventArgs e) +102 System.Web.UI.Control.LoadRecursive() +45 System.Web.UI.Control.LoadRecursive() +130 System.Web.UI.Control.LoadRecursive() +130 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +952 I really don't see the problem. I have added the reference, so how does it not know where it is? Confusing. Any help appreciated, Steve Tag: Synch between Excel CVS <-> SQLCE Tag: 114983
 - 22
 - dataset Performence Issue We've heard reports that the Dataset object in the .NET 1.1 frameworks tends to slow down dramatically after it reaches a certain size, around 70MB or so, regardless of the amount of RAM installed on the machine. We haven't experienced this ourselves yet, but can see the day when our Dataset will comprise of hundreds of megabytes of data. Questions: 1 Is this a well-known issue? 2 Is this a general performance problem, or just a problem with certain methods? The Merge method comes to mind, which we use quite a bit in our code indirectly. 3 Is there any way to work around this limitation? 4 Does the NET 2.0 framework address this issue? Tag: Synch between Excel CVS <-> SQLCE Tag: 114981
 - 23
 - Strange Error when adding to access mdb file ok.... this is a strange error and it only happens in 2 instances 1) it happens when the program is first compiled. a restart of the program solves the problem 2) and when its out of order .... eg ( 12 Mad dog, 34 may and bbb) where bbb is displayed twice. this bbb is displayed twice in both instances. here is the code: Debug.WriteLine("miAddLCProject_Click (BEFORE) : " + this.dsProjects.Tables["projects"].Rows.Count.ToString()); System.Data.DataRow newRow = this.dsProjects.Tables["projects"].NewRow(); newRow["projectName"] = curProject.ProjectName.ToString(); this.dsProjects.Tables["projects"].Rows.Add(newRow); this.daProjects.Update(this.dsProjects, "projects"); this.daProjects.Fill(this.dsProjects, "projects"); Debug.WriteLine("miAddLCProject_Click (AFTER) : " + this.dsProjects.Tables["projects"].Rows.Count.ToString()); and when the error occurs, the debug lines output say 2 and 4 any ideas what could be causing this error.... Brent Tag: Synch between Excel CVS <-> SQLCE Tag: 114977
 - 24
 - DataReader.Read() Ends program Hi all, I have an odd problem, which I traced back to a line of code. I have a record set coming back into a DataReader (OdbcDataReader in my case). I can .Read() once, but when the loop starts again and the .Read() is executed a second time, the application just ends. There are NO exceptions, logs or anything, its as if the .Read method called Application.Exit(). Any ideas? Thanks Andy Tag: Synch between Excel CVS <-> SQLCE Tag: 114974
 - 25
 - URGENT: Oracle: String buffer too small I have a typed dataset which contains data I want to send to the database. I created an OracleDataAdapter and have set the correspond OracleCommands. In oracle there are the required procedures. When I call 'Update', I always get the error 'String buffer too small' . I already set the size of all parameters that correspond to a VARCHAR2 variable, to eg.50. But that was not the solution. Can anybody give me some more help or tips that could solve the problem? Some more info; Oracle 9.2.0.6 on the server Oracle 9.2.0.4.1 on the client. Using ODP.NET Thanks, Kris Tag: Synch between Excel CVS <-> SQLCE Tag: 114963
- Next
 - 1
 - Merge and Get Changes returns nothing I have a simple case of two datasets each containing a table with the same schema. When I merge the two datasets, the empty tables in daataset1 get filled with the rows from dataset2, all fine so far. When I try and get the changes of the tables in dataset1 no changes are returned. Any ideas? ''merge the server table with the local table ds_Server.Merge(ds_Local) ds_Server.Table(0-x) all have rows now, but ds_Server.Table(0-x).GetChanges returns nothing. Tag: Synch between Excel CVS <-> SQLCE Tag: 114957
 - 2
 - Oracle 8i, OleDB driver and Batch Queries Hello Is it possible to execute Batch Queries with Oracle and the driver OleDB. I don't managed to do it. My ConnectionString : ..value="Provider=OraOLEDB.Oracle; Data Source=MYBASE; User ID=USER; Password=PASSWORD" Here is my code, --- This part is running, there is only one request. Dim mySQL As String = "SELECT CHAMP1? CHAMP2 FROM MATABLE" _ & "WHERE MATABLE.IDE = ?" Dim myOleDbCmd as New OleDbCommand (mySQL,myOleDbConn) myOleDbCmd.Parameters.Add(New OleDb.OleDbParameter("ide", intIde)) Dim myReader As OleDbDataReader = myOleDbCmd.ExecuteReader() But if i want to try that, Dim mySQL As String = "SELECT CHAMP1? CHAMP2 FROM MATABLE" _ & "WHERE MATABLE.IDE = ?;SELECT COUNT(*) FROM MATABLE" I get error ORA-0911 invalid Caracter. The problem seem to be the caracter ; Thanks a lot for your help fabrice Tag: Synch between Excel CVS <-> SQLCE Tag: 114956
 - 3
 - reading text file using OLEDB into dataset Hi I'm using OLEDB to fill a dataset from CSV file. it works great - too great. the dataset recognizes some text fields as integers. for example the date 02/08/05 is transformed to 20805. I want the dataset to read all data as strings. I can't use schema files because the CSV files are generated at runtime with different names and column number, and I can't manipulate the files I read. any ideas? Thanks Richard Tag: Synch between Excel CVS <-> SQLCE Tag: 114954
 - 4
 - move all data access to component(s)? I'm thinking about moving all my data access to components that will return a series of record sets, data readers, scaler. The pages will create an instance of a record set to be filled by functions. Updates will be functions with datarows or recordsets as parameters. Am i going too far? Will this slow down that app or make it unnecessarly big? i like some advice on this (I still new at this, but i'm getting there) thanks kes Tag: Synch between Excel CVS <-> SQLCE Tag: 114948
 - 5
 - Copy ms access tables from one DB to an other using ado.net in vb. Hi everyone, I need to know how to copy a ms access table from database A to database B using ado.net in vb.net. What classes of the OLEDB namespaces will allow me to do this? Can you please provide a code sample? Thanks for your help. Tag: Synch between Excel CVS <-> SQLCE Tag: 114944
 - 6
 - display data from Oracle database with another code page Hi! I have database with ISO-8859-2 code page set (POLISH_POLAND.EE8ISO8859P2) I write a WinForms application which uses those data (System.Data.OracleClient) How can I force my application to display national chracters in proper way (i want use windows-1250 code page)? Pawel Tag: Synch between Excel CVS <-> SQLCE Tag: 114942
 - 7
 - Problem in Context connection of CLR Trigger Hi, I'm using June CTP Visual Studio 2005. I have Created CLR Object. In my CLR Method i have opened a connection like below public partial class Triggers { //In my CLR Method i have Opened the connection like below [Microsoft.SqlServer.Server.SqlTrigger(Name="ClrTrigger", Target="Triplets",Event="FOR INSERT")] public static void CLRTrigger() { Triggers objTriggers = new Triggers(); objTriggers.Connection(true); ... ..... Test(); // Here i'm calling one more method Test(); .... .... objTriggers.Connection(false); } In this test method i'm Executing the command. Public void Test() { .... SqlCommand cmd = new SqlCommand(); string cmdText = "select dirname from Docs where id=45"; cmd.CommandText = cmdText; cmd.Connection = con; try { sdr = cmd.ExecuteReader(); } catch (Exception exp) { WriteLog("R E ::: "+exp.Message); } ...... } //Connection open and Close method public void Connection(bool status) { if (status) { con = new SqlConnection("context connection = true"); con.Open(); } else { con.Close(); } } } I'm getting the error of Connection already in use. I have properly opened the connection in begin of method and Closed in end of method. Inside the execution of method if i called some method means then it can also use the same connection. Then Why its giving this error. Please anybody knows solution to my problem let me know to solve it. Thanks, Vinoth vinoth@gsdindia.com Tag: Synch between Excel CVS <-> SQLCE Tag: 114940
 - 8
 - sqltransaction and updates to multiple databases I'm trying to use sqltransaction for updates across multiple databases but can't quite figure out how to do it. Since the sqlconnection will be connected to a single database, it would appear that I would need multiple sqltransactions, which would keep from having true rollback and commit for the updates. A vb.net example of what I'm trying to do would be as follows. This is simply trying to copy transactions from a live database to a historical database then remove the transaction from the live database. I know there are other ways to do this but it gives an example of my problem: Dim sqlConnection1, sqlConnection2 As New SqlConnection() sqlConnection1.ConnectionString = "Persist Security Info=False; Integrated Security=SSPI;database=livedatabase; server=mySQLServer;Connect Timeout=30" sqlConnection2.ConnectionString = "Persist Security Info=False; Integrated Security=SSPI;database=historydatabase; server=mySQLServer;Connect Timeout=30" Dim SqlTrans1, SqlTrans2 As SqlTransaction 'Copy to history and remove from current SqlTrans1 = sqlConnection1.BeginTransaction SqlTrans2 = sqlConnection2.BeginTransaction Try insert commands to history database go here (commands have .transaction property set to SqlTrans2) Try delete commands from live database go here (commands have .transaction property set to SqlTrans1) Catch e As Exception SqlTrans1.Rollback() SqlTrans2.Rollback() End Try Catch e As Exception SqlTrans2.Rollback() End Try 'commit routine Try SqlTrans2.Commit() Try SqlTrans1.Commit() Catch e As Exception SqlTrans1.Rollback() End Try Catch e As Exception SqlTrans1.Rollback() SqllTrans2.Rollback() End Try The problem with the above fragment is that if the Commit to the remove from the live database (SqlTrans1.Commit) fails, I've already performed the commit to the write to the history database (SqlTrans2.Commit) so I'm not able to roll the history database back to the previous state. Am I failing to understand something about the behavior of sqltransaction? Tag: Synch between Excel CVS <-> SQLCE Tag: 114919
 - 9
 - NEW CHANCE TO WIN MONEY This is a multi-part message in MIME format --=_NextPart_2rfkindysadvnqw3nerasdf Content-Type: text/plain Content-Transfer-Encoding: quoted-printable --=_NextPart_2rfkindysadvnqw3nerasdf Content-Type: text/html Content-Transfer-Encoding: Binary <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> <meta name="GENERATOR" content="Microsoft FrontPage Express 2.0"> <title>Ceci n'</title> </head> <body bgcolor="#000000">   <div align="center"><center> <table border="0" bgcolor="#FFFFFF"> <tr> <td>READY TO BE RICH...</td> </tr> </table> </center></div>   <div align="center"><center> <table border="0" bgcolor="#FFFFFF"> <tr> <td> </td> <td>Dear Friends: Greetings: I am a retired attorney. A few years ago a man came to me with a letter. He asked me to verify the fact that this was legal to do. I told him I would review it and get back to him. When I first read the letter my client brought me, I thought it was some "off-the-wall" idea to make money. A week and a half later we met in my office to discuss the issue. I told him the letter he originally brought me was not 100% legal. My client then asked me to alter it to make it perfectly legal. I asked him to make one small change in the letter. I was still curious about the letter, so he explained to me how it works. I thought it seemed like a long shot, so I decided against participating. But before my client left, I asked him to keep me updated on his results. About two months later, he called me to tell me he had received over $800,000 in cash.I didn't believe him, so he asked me to try this idea and find out for myself. I thought about it for a couple of days and decided I really didn't have anything to lose, so I asked him for a copy of the letters. I followed the instructions exactly, mailed 200 copies, and sure enough, the money started coming in! It arrived slowly at first, but coming. I kept a precise record of the earnings, and in the end, it totaled $978,493! I could hardly believe it. I met with my friend for lunch to find out exactly how it worked. He told me there are quite a few similar letters around, but this one is different because there are six names at the end of the letter, not five like some others. This fact alone results in your name being in far more returns. The other fact was the help I gave him, making sure the whole thing was legal, since no one wants to take the risk of doing something illegal. By now you are surely curious to know what small changes to make. If you sent a letter like this one out, in order to be completely legal, you must actually sell something in order to receive a dollar in return. So when you send a dollar to each of the names on the list, you must include these words, "PLEASE PUT ME ON YOUR MAILING LIST" and include your name and address. This is the key to the program. The item you will receive for the dollar you sent to the six people below is the letter. At the time I first tried this idea, I was earning a good living as a lawyer. But everyone in the legal profession will tell you there is a lot of stress that comes with the job. I told myself if things worked out, I would retired from my practice and play golf. I decided to try the letter again, but this time I sent 500 copies. Three months later , I had totaled $2,341,178! Here are a few reasons a person might give for not trying this program: Some people think they can never make a lot of money with anything this simple. Some are afraid they will be ridiculed for trying Some dream of large sums of money, but do nothing to actually achieve it. Some are just plain lazy. Some are afraid of losing their investment. They think this program is designed to beat them out of a few dollars. The system works if you will just try it. But you must follow the simple instructions exactly, and in less than three months, you will receive $800,000 GUARANTEED! Keep what you are doing to yourself for awhile. Many will tell you it won`t work and will try to talk you out of your dreams. Let them know of your success after it works. LETTERS FROM PARTICIPANTS IN THIS PROGRAM: :My name is David Rhodes. In 1992 my car was repossessed and bill collectors were housing my. I was laid off and my unemployment ran out. In October of 1992, I received a letter telling me how to earn $800,000 anytime I wanted. Of course, I was skeptical. But because I was so desperate and virtually had nothing to lose, I gave it a try. In January 1993, my family and I went on a 10-day cruise. The next month I bought a brand new Mercedes with cash! I am currently building a home in Virginia and I will never have to work again. This money program really works perfectly every time. I have never failed to receive less than $500,000. This is a legitimate, money-making opportunity. It does not require you to sell anything or to come in contact with people. And , best of all, you only leave the house to mail the letters. If you have always believed that someday you would get the lucky break, then simply follow the instructions and make dreams come true. Larry McMahon, Norfolk, VA Six months ago, I received this letter and ignored it. Five more came within a period of time and I ignored them also. I was tempted, but I was convinced that they were just a Hoax. After three weeks of deliberating, I decided to give it a try ( not expecting much ). Two weeks went by and nothing happened. The fourth week was unbelievable! I cant say I received $800,000 but I received $400,000. For the first time in years, I am debt free. I am doing this again, only this time starting with 500 post. I strongly recommend that you follow the instructions exactly as outlined in this letter. INSTRUCTIONS 1. Immediately send $1.00 to each of the six people on the list at the end of this letter. Wrap the dollar bill in a note saying " Please add me to your mailing list" and include your name and address. 2. Copy this letter. You do not have to type it 200 times. Simply place your cursor at the top of the page, hold it and drag it all the way down to the end of the letter. Then click on "edit" and select "copy". Now open up a notepad file on your computer and put the cursor at the top of the page in the notepad, click on 'edit' and then select 'paste' it will copy the letter for you onto your computer. 3. Remove the name next to the #1 on the list and move the rest of the names up one position (#2 becomes #1, #3 becomes #2, etc.....) Then place your name in the #6 position. Then save it, make sure it is saved as a txt. file. Cover money by paper fully by which it can not been seen in light , because postal employees do some cheat some times. 4. When you have completed the instructions, take this letter and then go to (Google,Yahoo,...) and type in (Making Money Message board,or post message,...)and start posting your copy to 200 message boards,or more this is only the minimum, you can post as much as you like...The more copies you send the better the results. Keep a copy of this letter so you can use it a second time. Post it out again in six months, but Post it with the addresses you receive with each dollar. It will work better the second time. NOTE: This service is 100% legal - (Refer to title 18 section 1302 of the U.S. Postal & lottery laws) How does it work? When you send out 200 Posts, it is estimated that at least 15 people will respond and send you a $1.00. ($15.00) Those 15 will Post 200 Posts each and 225 people send you $1.00 ($225.00) Those 225 people Post 200 Posts each and 3,375 people send you $1.00 ($3,375.00) Those 3,375 post 200 posts each and 759,375 people send you $1.00 ($759,375.00) At this point your name drops off the list, but so far you have received $813,615.00. Here is the list to Send 1.G.H.L.N Thorntree Rd1600 Muncie,IN 47304-9710 USA 2. K. Hastie 459 Pinetree Cres. Cambridge ON N3H 4x4 CANADA 3. M.Motut.Anora Dr.2377 Abbotsford, BC v2s 5p3 CANADA 4. M. Baun, 8451 Cabin Creek Drv. Apt. G, Indianapolis IN 46237 5. Mr Angel VILLAR PEREZ Calle Castellon nº2 - 4F 47012 VALLADOLID SPAIN 6. Kyle Pate P.O.Box 2046 - Great Falls, MT59403-2046 USA </td> </tr> </table> </center></div> </body> </html> --=_NextPart_2rfkindysadvnqw3nerasdf-- Tag: Synch between Excel CVS <-> SQLCE Tag: 114917
 - 10
 - Store and retrieve an array of double into image field(sql server) I have a single dimensional array of double values which I want to store into an image field of sql server database. I couldn't find any sample code to do that, the sample code everywhere talks about reading and writing to a file. I want to know how to store and retrieve an array of double from the image field using vb.net. I would appreciate any inputs on this. Thanks Tag: Synch between Excel CVS <-> SQLCE Tag: 114916
 - 11
 - How to do a simple update command with ado.net? I am trying to execute a simple SQL Update command on an Access database. I have an OleDBConnection (gDatabaseConn) that has successfully been executing select commands, but I cannot get an update command to work. Here is the code in question: Dim cmdUpdate As New OleDb.OleDbCommand("", gDatabaseConn) sSQL = "UPDATE NoteTable SET AcctID = '" & Account.sAcctID sSQL = sSQL & "' WHERE AcctNo = '" & Account.sAccountNo & "'" cmdUpdate.CommandText = sSQL cmdUpdate.ExecuteNonQuery() The call to ExecuteNonQuery throws an exception with the message "No value given for one or more required parameters". Both of the variables used in the string have values. Is ADO.NET capable of running a simple SQL command like this, or do I need to investigate OleDBParameter objects? This way would be preferrable, since I am not using DataTables or DataRows. Tag: Synch between Excel CVS <-> SQLCE Tag: 114912
 - 12
 - New column added to old table causes ORA-00904 I added a new column to an old table, and any time I try to add it to a select, I get an ORA-00904 error. I'm using the MS OracleDataProvider for DotNet, and Oracle 9.2.0.4.0. Experimenting has proven that any new column on any table produces the same error. I have renamed the column, dropped and re-added, and tried various simple names and a variety of datatypes. Has anyone else ever seen this problem? Tag: Synch between Excel CVS <-> SQLCE Tag: 114911
 - 13
 - dataset sort I want to use two SQL statements: Dim strSQLLCMR As String = "SELECT DTG, POO FROM LCMR WHERE (Confirmed = true and Friendly = False) ORDER BY DTG" Dim strSQLQ36 As String = "SELECT DTG, POO FROM Q36 WHERE (Confirmed = true and Friendly = False) ORDER BY DTG" to fill one table in a dataset. Then I want to sort that dataset by the field DTG. Is this possible? The reason for this is that I then want to loop through the table and determine records that have a Date/Time group (DTG) within 5 minutes of each other. Any suggestions welcome. Thanks, Thomas -- Posted via NewsDemon.com - Premium Uncensored Newsgroup Service ------->>>>>>http://www.NewsDemon.com<<<<<<------ Unlimited Access, Anonymous Accounts, Uncensored Broadband Access Tag: Synch between Excel CVS <-> SQLCE Tag: 114889
 - 14
 - Problems with array<Byte>^ Hi everyone! I was playing a little with VC++ 2005 Express for evaluation purposes (and out of curiosity) when I stumbled over some problems. I have a set of binary data in a postgresql-table. I managed to insert the data via the npgsql-data-provider. I can also read string and numeric values, but when it comes to reading the data out of the db and writing it to disk I fail. I tried the following (I did this on another computer and type from mind here so don't bother typos plz): String^ statement = "SELECT data FROM jpegimages WHERE \"ID\" = " + Convert::ToString(newObject->GetID()) + ";"; NpgsqlCommand^ command = gcnew NpgsqlCommand(statement, this->conn); NpgsqlDataReader^ reader = command->ExecuteReader(); // I know there is only one row... reader->Read(); if (reader[0] != IntPrt::Zero) { array<Byte>^ bytes = (array<Byte>^)(reader[0]); bufferedWriter->Write(bytes); ... The code compiles without errors but when it is run I get "Cast not valid" Exceptions at the "array<Bytes.."-line. There isn't much info in the help about stuff like this. The docs for the postgres-data-provider are written against c# (which I didn't intent to use in this special case) where one can use a simple "byte[]"-construct. Any help is highly appreicated. regards... ...Dennis Tag: Synch between Excel CVS <-> SQLCE Tag: 114885
 - 15
 - ADO.net book, resources Hello All, I am new to ADO.net and C#, I have been trying to understand both but having problems. Can anyone please guide me to a good ADO.net book using C# that I can buy, I have checked books on Amazon but have not been able to make a decision as to which one should I buy. In addition a good online resource that I should use to work with ADO.net would be great, for now although there are lot of resources, but have not been able to find one that I can get most of the information from, generally have to search for each thing on Google and then spend hours to find answer to something simple as declaring a null column in the prepared statement. Thanks a lot. Imran. Tag: Synch between Excel CVS <-> SQLCE Tag: 114813
 - 16
 - parameter not supplied error , using parameters.add Hello All, I have a table with few fields that allow null, I use the following code to add entries strQuery = "insert into tblChannelItems (nChannelID,sItemTitle,sItemLink,sItemDesc,sItemAuthor,sItemCategory,sItemComments,sItemEnclosure,sItemPubDate) values (@nChannelID,@sItemTitle,@sItemLink,@sItemDesc,@sItemAuthor,@sItemCategory,@sItemComments,@sItemEnclosure,@sItemPubDate);"; strQuery = strQuery + " SELECT nItemID from tblChannelItems where nItemID=SCOPE_IDENTITY();"; SqlCommand objCmd = new SqlCommand(strQuery, this.conn); SqlParameter nChannelID = objCmd.Parameters.Add("@nChannelID", SqlDbType.Int); SqlParameter sItemTitle = objCmd.Parameters.Add("@sItemTitle", SqlDbType.NVarChar); SqlParameter sItemLink = objCmd.Parameters.Add("@sItemLink", SqlDbType.NVarChar); SqlParameter sItemDesc = objCmd.Parameters.Add("@sItemDesc", SqlDbType.Text); SqlParameter sItemAuthor = objCmd.Parameters.Add("@sItemAuthor", SqlDbType.NVarChar); SqlParameter sItemCategory = objCmd.Parameters.Add("@sItemCategory", SqlDbType.NVarChar); SqlParameter sItemComments = objCmd.Parameters.Add("@sItemComments", SqlDbType.NVarChar); SqlParameter sItemEnclosure = objCmd.Parameters.Add("@sItemEnclosure", SqlDbType.NVarChar); SqlParameter sItemPubDate = objCmd.Parameters.Add("@sItemPubDate", SqlDbType.DateTime); nChannelID.Value = objStItem.nChannelID; sItemTitle.Value = objStItem.sItemTitle; sItemLink.Value = objStItem.sItemLink; sItemDesc.Value = objStItem.sItemDesc; sItemAuthor.Value = objStItem.sItemAuthor; sItemCategory.Value = objStItem.sItemCategory; sItemComments.Value = objStItem.sItemComments; sItemEnclosure.Value = objStItem.sItemEnclosure; sItemPubDate.Value = objStItem.sItemPubDate; nItemID = (int)objCmd.ExecuteScalar(); but I get the error of {"Prepared statement '(@nChannelID int,@sItemTitle nvarchar(33),@sItemLink nvarchar(50' expects parameter @sItemAuthor, which was not supplied."} Seems although the author column is optional but still somehow since I dont specify the parameters as optional in my prepared statement so I get this error, can anyone please guide me how I can set these parameters as optional , so I dont get this error? Imran. Tag: Synch between Excel CVS <-> SQLCE Tag: 114793
 - 17
 - Controlling a primary key column Hello: I have an application with a data base. i insert information by the application in the data base, and i have to control that the user doesn't give a value for the primary key column that already exists. I would want to know what happens in that case, when you try to insert a new row in the table with a value for the primary key that already exists, an exception is raised? nothing happens? it happens an updated? Thanks for the help. Bye. Tag: Synch between Excel CVS <-> SQLCE Tag: 114768
 - 18
 - ADODB.DLL - Error Object Reference not set to an instance of an object Hello, I have the following problem on certain servers, when trying to access ADO objects I get the following error message 'Object Reference not set to an instance of an object', on certain servers ADODB.DLL is in the GAC and on other servers its in the application folder. On certain server it works when installed in the application folder, and on ther servers it does not, if I install it in the GAC then it works, why does this happen? I've seen the following artcles KB321688 KB810098 Please can some explain why it works on some servers when not installed in the GAC. Thanks, Tag: Synch between Excel CVS <-> SQLCE Tag: 114691
 - 19
 - Bulk insert Hi, I need to insert nearly more than 6000 records in a single shot. For that I need to get the data from tree view (windows app) control, Im reading through recursive mannner to get parent & childs and im forming an xml file then sending to backend. here to compose the xml file from tree view control it is taking 25 secs time and at back end I returned and Stored procedure for bulk insert. Create PROC insertversion ( @VersionData ntext ,@Update bit ) AS DECLARE @hDoc int EXEC sp_xml_preparedocument @hDoc OUTPUT,@VersionData IF @Update = 1 BEGIN INSERT INTO Version_Map (version_id, question_id, parent_question_id, disp_sequence_nbr, created_by, created_date, updated_by, updated_date) SELECT * FROM OPENXML(@hDoc, 'ROOT/QUESTIONS/QUESTION',1) WITH ( VERSION_ID int , QUESTION_ID INT, PARENT_QUESTION_ID int, SEQNO int, created_by varchar(20), created_date datetime, updated_by varchar(20), updated_date datetime) XMLVersion END ELSE BEGIN INSERT INTO Version_Map (version_id, question_id, parent_question_id, disp_sequence_nbr, created_by, created_date) SELECT * FROM OPENXML(@hDoc, 'ROOT/QUESTIONS/QUESTION',1) WITH ( VERSION_ID int , QUESTION_ID INT, PARENT_QUESTION_ID int, SEQNO int, created_by varchar(20), created_date datetime) XMLVersion END EXEC sp_xml_removedocument @hDoc GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO Here Im using the OpenXml method... for bulk insert this is taking 50 secs.. Total insert opration it is taking 84 secs to do this operation... but Im looking a solution to this operation with in 10 secs.. plz help me .... thanks .... Tag: Synch between Excel CVS <-> SQLCE Tag: 114681
 - 20
 - Incorrect syntax near the keyword 'DEFAULT' Hello Experts! I am attempting to use the OleDbCommand.ExecuteScaler() function within my ASP.NET C# web page to perform a simple validation, but receive the following error: "Incorrect syntax near the keyword 'DEFAULT'" The form has 2 fields on it, called tb_username and tb_password. (see code snippet below). The SQL Server table is called _users and has a number of columns, 2 of which I am reading: username and password. I've searched the net for any clues on this but so far none of the items I've found were relavent to what I was doing. Can anyone shed any light on this? Many thanks! === here is my code ==== private bool ValidateLogin() { // set the connection string oleDbConnection_login.ConnectionString = C_Constants.g_sDSN; // add parameters to the query oleDbCommand_login.Parameters.Add("@user",System.Data.OleDb.OleDbType.VarWChar,25,tb_username.Text); oleDbCommand_login.Parameters.Add("@pass",System.Data.OleDb.OleDbType.VarWChar,25,tb_password.Text); oleDbCommand_login.CommandText = "Select username from _users whereusername=? AND password=?"; // open the connection oleDbConnection_login.Open(); // execute the query oleDbCommand_login.Connection = oleDbConnection_login; object objResults = oleDbCommand_login.ExecuteScalar(); <--- errormessage occurs here // close the connection oleDbConnection_login.Close(); if (objResults==null) { return false; } return true;} Tag: Synch between Excel CVS <-> SQLCE Tag: 114675
 - 21
 - Distinct Values from a Datatable guys, Has anyone tried this before? i have a dataset and rather than doing an addtional trip to the server with a DISTINCT sql query - i'd like to just loop thru the existing dataset in memory. i assign that dataset (ds) to a datatable, declare a datarow and then use the select property to get the distinct employee id. i keep getting "Syntax error: Missing operand after 'EmplId' operator." and i've pretty much been out on the web looking for a solution to this. any help is appreciated. snippet of the code is below. thanks rik Dim oTable As DataTable = ds.Tables(0) Dim myRow As DataRow For Each myRow In oTable.Select("DISTINCT EmplId") cboEmplList2.Items.Add(myRow.Item(0)) Next ********************************************************************** Sent via Fuzzy Software @ http://www.fuzzysoftware.com/ Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources... Tag: Synch between Excel CVS <-> SQLCE Tag: 114666
 - 22
 - Saving a dataview to a new dataset in C# Hi, I am hoping I can get some help with a small problem I have run into using C#. U have an XML file that I load into a Dataset and then display this in a Datagrid. No problems doing this at all. I then use a Dataview to filter this view using a keyword and I have no problems with this either. What I would like to do is to save this Dataview to a new Dataset so I can save the reslts to a new file but here is where I am having the problem. A Dataview is typically not written back to a Dataset so I'm not sure what would be the best way to do this. I found a code snippet after looking for a day but I have errors with it and I do not know why. I have created my main Dataset called 'ds' and after filtering it and displaying it I clone 'ds' to a new Dataset and then try to import the rows back into my new data set. The code I am using to do this is posted below.... //*************************************************** DataSet ds2 = ds.Clone(); for(int iTable = 0; iTable < ds.Tables.Count; iTable++) { for(int iRow = 0; iRow < ds.Tables[iTable].DefaultView.Count; Row++) { ds2.Tables[iTable].ImportRow(ds.Tables[iTable].DefaultView[iRow].Row); } } ds2.WriteXml(@"c:\data.xml"); //************************************************* My problem comes in on the this line... for(int iRow = 0; iRow < ds.Tables[iTable].DefaultView.Count; iRow++) The error that is displayed is as follows.... "An unhandled exception of type 'System.Data.InvalidConstraintException' occurred in system.data.dll Additional information: ForeignKeyConstraint Computer_ComputerHardware requires the child key values (3) to exist in the parent table." On this part of the code three matches are found & displayed so I'm not sure if it is important. The error occurs on the 'iRow++' part pf the statement but I don't know why. I can't set breakpoints in C# nor can I see where the error is because I can't step through the code. Any idea of this would happen? I'm really stumped on this, below is a snippet of my XML document so you know what I am using in this code. Thanks for any help, Dennis //***************************************** <Office_Inventory> <Computer> <RoomNumber>G2017</RoomNumber> <ComputerNumber>G201701</ComputerNumber> <InServiceDate>2003-03-15</InServiceDate> <ComputerHardware> <Printer>HP DeskJet 500</Printer> <Scanner>HP Scan 100</Scanner> <CD-DVD>40X CD-ROM</CD-DVD> <Monitor>Acer 67L</Monitor> </ComputerHardware> <ComputerSoftware> <OS>Windows 2000</OS> <Office>Office 2003 Professional</Office> <Programming>Visual Studio 2003 Profressional</Programming> <Utilities>Norton AntiVirus 2005</Utilities> </ComputerSoftware> </Computer> </Office_Inventory> Tag: Synch between Excel CVS <-> SQLCE Tag: 114664
 - 23
 - Use datareader to get a value for UserNo Hi, I'm relatively new to ADO, so forgive me if I am going about this in the wrong way. I have a login.aspx page, with a textbox 'usernameTxtBx' I would like to extract a value from a single table in an SQL database. The database has two columns of interest usrNo and usrName, they are in the same table. I would like to set the value from usrNo as an int where the value in the usrName column matches that entered in the usernameTxtBx. I have attempted to use a datareader in c#. Here's the relevent code: // Declare conn from Web.Config SqlConnection conn = new SqlConnection(ConfigurationSettings.AppSettings["strConn"]); conn.Open(); // Set username as a string string Username = usernameTxtBx.Text; //find usrNo SqlCommand cmd = new SqlCommand("SELECT usrNo FROM tblUser WHERE usrName = 'Username'", conn); SqlDataReader dr = cmd.ExecuteReader(); dr.Read(); int UserNo = (int)dr["UserNo"]; conn.Close(); This gives the following error: 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.IndexOutOfRangeException: UserNo In debugging the value of UserNo appears to get set to 0 regardless of the login used, where i would expect the value to equal 1 or 3 or similar integer. Any ideas what i'm doing wrong? am I even on the right track?? Thanks Tag: Synch between Excel CVS <-> SQLCE Tag: 114662
 - 24
 - Clearing dataset tables where they have relations I'm using a Public dataset in the module for all data throughout the project. For most forms, this is great, no need to instantiate a new object, just clear the datatables and reload as needed. However, an issue arises where I have data relations in a form. Reloading the data on those forms requires clearing the relation and the datatables. What I've done in the past is reset the dataset and clear the relations. This also works fine -- except for the obvious, that being that it kills off any data in the dataset. So, I've tried to just clear the tables and then clear the dataset relations. This causes an exception on the 2nd load of data ("The row doesn't belong to the same DataSet as this relation."). How do I resolve this -- without creating a new dataset for each form where I have a data relation? ********************************************************* TECHNIQUE A (this works well, except clears the dataset entirely) ds.Reset() ds.Relations.Clear() TECHNIQUE B (this causes an exception on 2nd load of employee data) If ds.Tables.Contains("dtEmployees") Then ds.Tables("dtEmployees").Clear() End If If ds.Tables.Contains("dtEmpCategoryMatches") Then ds.Tables("dtEmpCategoryMatches").Clear() End If ds.Relations.Clear() Tag: Synch between Excel CVS <-> SQLCE Tag: 114655
 - 25
 - Can rows be ordered in a datatable? I have a DataTable that is bound to a DataGrid that displays a number of selections from a product menu: Item1 quantity price totalAmount Item2 quantity price totalAmount TAX - - - - - - - - - - totalAmount TOTAL - - - - - - - - totalAmount Each time the user makes a selection, the DataTable is updated and re-bound to the DataGrid. The problem is I need to keep the TAX and TOTAL rows as the last rows in the DataTable so they appear at the bottom of the DataGrid. But the only way I know how to do this is to delete the rows every time the user makes a selection from the product menu, then recalculate the order, and lastly reinsert the rows in the DataTable (otherwise the user's most recently selected product is the last row in the DataTable and appears at the bottom of the DataGrid). Is there a way to re-order the rows rather than deleting and re-inserting every time? Is there some way to tell the data table that the Total row should be the last row in the table, and the Tax row second-to-last? That way I could just overwrite the value in those rows rather than deleting and re-inserting after each time the users selects another item. Thanks in advance. Tag: Synch between Excel CVS <-> SQLCE Tag: 114653

Hi, I am wondering if there are better way to synch the data between Excel
CSV and SQLCE ?

Or manually create two buttom and allow user to pull and push is the best
way so far ?
So, everytime user want to update SQLCE data to the current CSV file. users
have to deleted old data, then insert all again.

Pls advise me. thank you very much

Top

Re: Synch between Excel CVS <-> SQLCE by W

W
Fri Aug 05 08:34:53 CDT 2005

How are you reading in the excel file? Are you using PocketExcel? Probably
the easiest way is to fill a datatable and use the AcceptChangesDuringFill
property to false on it, then point it to the destination and call Update on
the same datatable providing a valid insert command.
" AAA" <pk999@hotmail.com> wrote in message
news:ehOIXTMmFHA.3380@TK2MSFTNGP12.phx.gbl...
> Hi, I am wondering if there are better way to synch the data between Excel
> CSV and SQLCE ?
>
> Or manually create two buttom and allow user to pull and push is the best
> way so far ?
> So, everytime user want to update SQLCE data to the current CSV file.
> users have to deleted old data, then insert all again.
>
> Pls advise me. thank you very much
>
>

Top