Insert data from TataTable to a table in SQL

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
    - Probelm Quering Access Database Hi, i have a query that works normal from access query window but when i execute if from my code with cmd.executereader it give me error "The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect." the query is below, if someone is interested Thanks in advance!! SELECT a.MACH AS MACHINE, a.ST AS STATE, ROUND(a.DURATION1/(60*60), 2) AS DURATION, ROUND((a.DURATION1/b.DURATION2)*100, 2) AS PERCENTAGE, a.OCC AS OCCUR, a.YEAR, a.PNUM, a.PERIODSTART, a.PERIODEND FROM (SELECT MACHINE AS MACH, STATE AS ST, SUM(OCCUR) AS OCC, SUM(DURATION) AS DURATION1, DATEPART('yyyy', CURDAY) AS YEAR, DATEPART('m', CURDAY) AS PNUM, MIN(CURDAY) AS PERIODSTART, MAX(CURDAY) AS PERIODEND FROM test_DCXTMP WHERE CURDAY BETWEEN #2004-3-1# AND #2004-3-15# AND MACHINE = 'ARS' GROUP BY MACHINE, STATE, DATEPART('m', CURDAY), DATEPART('yyyy', CURDAY) ) as a INNER JOIN (SELECT MACHINE AS M, SUM(DURATION) AS DURATION2, DATEPART('yyyy', CURDAY) AS YEAR, DATEPART('m', CURDAY) AS PNUM FROM test_DCXTMP WHERE CURDAY BETWEEN #2004-3-1# AND #2004-3-15# AND MACHINE = 'ARS' GROUP BY MACHINE, DATEPART('m', CURDAY), DATEPART('yyyy', CURDAY) ) as b ON a.MACH = b.M AND a.YEAR = b.YEAR AND a.PNUM = b.PNUM ORDER BY a.YEAR, a.PNUM Tag: Insert data from TataTable to a table in SQL Tag: 93983
  - 2
    - updatestatus.skipcurrentrow I'm using the following code per kb313540 but i keep getting an error: a.. Check and update each row manually instead of using the AcceptChanges method on the DataSet. a.. Use the RowUpdated event of the DataAdapter object and skip the duplicate rows. If e.StatementType = StatementType.Insert Then e.Status = UpdateStatus.SkipCurrentRow a.. The error i'm getting is: expression does not produce a value (Visual Studio.Net 2003 has an line under "updatestatus"a.. Any ideas?? Thanks for your help!a.. Chris Thunella.. cthunell@pierceassociates.com Tag: Insert data from TataTable to a table in SQL Tag: 93982
  - 3
    - OleDb Insert Query Hi all, Having a lil problem with the OleDbConnection + Command. The whole thing is done im c# and uses Access datbase, it builds a SQL string from stuff that user typed in then calls OleDbCommand.ExecuteNonQuery with the folowing string. "INSERT INTO Trans (rID, [Number], Price, [Date], Month, Year, [Percent]) VALUES (6,1,5.95,18,8,2004,100)" at which time the try-catch wraping comes up with an error "Synatx error in INSERT INTO statement". I have tryed putting that string directly into MS Office Access and it executes perfectly......so wheres the problem? Thank you! Tag: Insert data from TataTable to a table in SQL Tag: 93981
  - 4
    - SQLDMO attachDB error with filenames including space character Hey everyone! I'm trying to use SQLDMO to programmatically attach a database. I have a problem when the file name includes a space character, for example: @"C:\Program Files\Microsoft SQL Server\MSSQL\Data\DM_0814_ABCTrucking.mdf" Here's a sample of code: string tmp = @"C:\Program Files\Microsoft SQL Server\MSSQL\Data\DM_0814_ABCTrucking.mdf" +"," +@"C:\Program Files\Microsoft SQL Server\MSSQL\Data\DM_0814_ABCTrucking_log.ldf"; SQLDMO.SQLServer sqlDMOConn = new SQLDMO.SQLServer(); sqlDMOConn.Connect("(local)", "sa", ""); sqlDMOConn.AttachDB( "DM_0814_ABCTrucking", tmp ); When I do this, the AttachDB call raised the following exception "Device Activation Error. The physical file name 'C:\Program' may be incorrect." I think what's happening is that SQLDMO doesn't like the space character in the filenames. How can I formulate the file name correctly? --steve Tag: Insert data from TataTable to a table in SQL Tag: 93980
  - 5
    - Mail problem Hi ! I have 2 textboxes that I use on my site to let people send mail to their friends for promotion. They fill in their name and their E-mail. Now the question..! This is how it looks, I use ordinary SMTP mail : objMM.To = tboxTipsEmail.Text objMM.From = tboxTipsName.Text Is this the right way to do it or must I have it like : objMM.To = "donald.duck@hotmail.com" objMM.From = tboxTipsName.Text It works without errormessages but the mail doesn´t arrive to "objMM.To = tboxTipsEmail.Text". OM "Myggan" *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! Tag: Insert data from TataTable to a table in SQL Tag: 93973
  - 6
    - Alter Table Add Column Trying to implement this for an Access.mdb file. It worked on development machine but failed on two user machines. Dim alter As OleDb.OleDbCommand daConn.Open() alter = New OleDb.OleDbCommand("ALTER TABLE BillableTimesheet ADD COLUMN Submitted BIT", daConn) Try alter.ExecuteNonQuery() Catch ex As Exception MessageBox.Show(ex.Message) End Try daConn.Close() Tag: Insert data from TataTable to a table in SQL Tag: 93969
  - 7
    - Help: SQL syntax error??? When I run an SQL statement with ADO.NET it gives me this error: System.Data.OleDb.OleDbException: Syntax error in INSERT INTO statement. at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr) at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteNonQuery() at DMSI.SqlHelper.ExecuteSql(String strSql, String strCnnString) in c:\inetpub\wwwroot\DMSI\SqlHelper.vb:line 9 When I copy/paste the SQL directly into an Access query and run it it runs PERFECT. Here's the SQL: INSERT INTO Employees(EmployeeId,TitleId,FirstName,MiddleInitial,LastName,Address,City,S tate,Zip,CellPhone,Fax,EmailAddress,UserName,Password,RegionalDirectorId) VALUES(8,3,'c','c','c','c','c','c','c','c','c','c','c','c',6); Ideas? Thanks! Tag: Insert data from TataTable to a table in SQL Tag: 93964
  - 8
    - Generic DataBase Tool I'm new to .Net and need to create a generic (free) way to update lookup tables in SQL Server (using C#) in ASP.Net pages. I found an article at: http://www.dotnetjunkies.com/Tutorial/0C968B48-44E8-4637-9F19-8490B0429881.dcik which explains how this can be done but does not supply enough example code my newbie self to go through in detail where I understand it. So, does anyone have some example code I could use to accomplish this or know where I can find something to get me started? Thanks, Jeff Tag: Insert data from TataTable to a table in SQL Tag: 93961
  - 9
    - Problems with extracting Data from DataRow Hello everyone, I have a DataSet in which I have extracted a row, within the row there is a column called "Text". This is taken from a table within SQL 2000 whose column has a data type of "text". When I try to retrieve the value using row["Text"].ToString() It retrieves a blank string. However when I use row[3].ToString() the command works. I cannot understand why using row["Text"] does not, I have looked at the row variable through the watch and column 3 has a "_columnName" equal to "Text". When I try the other columns using the field name to extract the value works. However it does not with the column named "Text", does anyone have any idea why as I am completely stumped. Many Thanks in advance for your answers Phil Tag: Insert data from TataTable to a table in SQL Tag: 93956
  - 10
    - Cannot Initialize SSPI Package I had the same problem, suddenly my ASP.NET will stop working connecting to SQL Server (Especially after the security updates from Microsoft (automatic)). I installed SP3A, things are back again normal. Tag: Insert data from TataTable to a table in SQL Tag: 93953
  - 11
    - command object hi, iam using vb.net code this is my stored procedure in sql server. create proc t1 (@q as int) as select * from test where id=@q GO ------ i am using vb.net code below like this Dim s As SqlClient.SqlDataReader Me.SqlCommand1.Parameters("@RETURN_VALUE").Direction = ParameterDirection.Output Me.SqlCommand1.Parameters("@q").Value = 1 s = SqlCommand1.ExecuteReader Do Until s.Read MsgBox(s(0)) Loop iam wondering how come iam not able to display msgbox, even though the data is there. any changes in the code need or any changes in the stored procedure. where should i change... thanks Tag: Insert data from TataTable to a table in SQL Tag: 93951
  - 12
    - Parameter Field Order Does anyone have any expreience with the order of fields in a OleDb INSERT command being significant. It seems that db.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand("INSERT INTO StagesProcessing (ptrOrder,StageDate,fromStage,toStage) VALUES (@p_ptrOrder,@p_StageDate,@p_fromStage,@p_toStage)",cn); will work but db.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand("INSERT INTO StagesProcessing (ptrOrder,fromStage,toStage,StageDate) VALUES (@p_ptrOrder,@p_fromStage,@p_toStage,@p_StageDate)",cn); will not work? ptrOrder is a GUID fromStage is a TEXT toStage is a TEXT StageDate is a DATETIME the database is Access 2002 I've never had any problems using dates before. Thanks Tag: Insert data from TataTable to a table in SQL Tag: 93950
  - 13
    - Selecting table in typed dataset Hello. I've populated a typed dataset and now I only want to bind one of the tables inside to datagrid - how is this accomplished? My code: private void FillDataGridTDS() { BLTask blTask = new BLTask(); DSTaskSchedules dsTaskSchedules; blTask.GetAllTasks(out dsTaskSchedules); // dsTaskSchedules is holding all my tables with relation etc. And now i only want to use one of these tables as a datasource. dgTasks.DataSource = dsTaskSchedules; dgTasks.DataBind(); } This is a snippet from my presentationlayer. And I dont want to set the datamember of the datagrid in this layer. So it's to be done in the BLL :O) The BLL: public void GetAllTasks(out DSTaskSchedules dsTaskSchedules) { dsTaskSchedules = null; DATask daTask = new DATask(); daTask.PopulateTDSTaskSchedules(out dsTaskSchedules); dsTaskSchedules.DataSetName // dsTaskSchedules is holding all my tables with relation etc. And now i only want to use one of these tables as a datasource. } Kind regards Tag: Insert data from TataTable to a table in SQL Tag: 93943
  - 14
    - 836874 hotfix / problem under windows xp Hi, one of my applications has the problem described in KB article 836874. As the application is running under Win2000, WinXP and Win2003 and the patches are only available for Win2000 and Win2003 (according to German MS Support sources) I cannot solve the problem for WinXP systems. Does anyone know if there is a workaround: applying the Win2000 oder Win2003 patch ? Appreciating any help. Thanks in advance, Frank Tag: Insert data from TataTable to a table in SQL Tag: 93942
  - 15
    - How to make the binding active? have a form with a TabControl with two TabPages and a TextBox control on each TabPage. Each of the TextBoxes are bound to a datasource like TextBox1.DataBindings.Add("Text", MyObject, "FirstName"). The problem is that the binding of the TextBox on the *second* TabPage is not active immediately (IsBinding = False), it only becomes active once the second TabPage is clicked?? This means that if I have a button on the form that does a MsgBox(TextBox2.Text) the Text is nothing until I click on the second TabPage. Any help on this question would be appreciated! Thanks, Thomas Tag: Insert data from TataTable to a table in SQL Tag: 93940
  - 16
    - Help with SQL problem: match where other record is flagged I need help with a SQL problem. This is "problem" as in like math problem. (And no, this isn't for class, it's a contract that I've lost a lot of time on by spending it pulling my hair out.) I have a database with one catch-all table, something like as follows: string Username string Key string SubKey bool/bit BoolValue int IntValue A unique record is determined by Username + Key + SubKey. I have two sets of many records. The first set looks like this: [username], MyBoolValueRecord, [subkey], [1/0], [null] The second set looks like this: [username], MyIntValueRecord, [subkey], [null], [#number#] The problem that I'm having is that I need the second set to be directly corresponding the first set, so that I only retrieve the [subkey] + [username] values of [key:MyIntValue] where [username] + [key:MyBoolValue] + [subkey] is 1/true, and ignore the [subkey]+[username] values of [key:MyIntValue] where [username] + [key:MyBoolValue] + [subkey] is 0/false. Since I have to refer to separate records of the same table, I cannot use JOIN (can I?). Surely this can still be done with a single SQL query? I can settle with a stored procedure if I have to, but I'd prefer not. I've tried WHERE ... IN (SELECT...), but that only supports one field match and I need both [username] and [subkey] to match. Sorry if this is hard to follow ... now you know my plight!! Thanks, Jon Tag: Insert data from TataTable to a table in SQL Tag: 93924
  - 17
    - DataSet Select or Dataview Filter gives max performance???? Hello all.. i've got 2 options 2 filter my data in a dataset....either user Select method of the dataset or create a dataview and then apply the filter criteria....Which gives the maximum performance...can anybody help?? smiles, Laiju Tag: Insert data from TataTable to a table in SQL Tag: 93923
  - 18
    - RowFilter - HowTo? Hi, I have a DataGrid bound a DataSet containing a Number of tables. The DataGrid contains a singles DataTableStyle mapped to one of the tables. I would like to be able to dynamically constrain the DataTableStyle to a subset of the rows in the DataSet. Can RowFilter be used to do this is how? TIA Steve Tag: Insert data from TataTable to a table in SQL Tag: 93922
  - 19
    - how to loop through a data table I use data adapter to fill up data set. How can I count the no. of record, which matches a specific condition, of a data table in a data set? And how can I loop through the data table? Thanks. Tag: Insert data from TataTable to a table in SQL Tag: 93917
  - 20
    - Merge() confusion hello folks, I have one dataset that I have modified (update, insert, delete). I also have another dataset that I just reload fresh from the database. Both datasets come from the same table. The idea with merge is to merge the modified dataset and new dataset together with preserving the changes made to the old one. When I do that, i got duplicate result instead of the merged one. What did I do wrong? -Agung Tag: Insert data from TataTable to a table in SQL Tag: 93916
  - 21
    - System.invalidOperationException I have 2 data adapters, 1 connection , and one data set. I use the same connection for all this data adapters. I'm using an access database and windows forms when I change from one record to other on the table JOBS it triggers one event. In this event, I have a code like System.Windows.Forms.BindingManagerBase bc; bc = this.BindingContext[this.ds1, "jobs"]; this.ds1.WorkEmp.Clear(); if (bc.Count>0) { System.Data.DataRow drv; drv = this.ds1.jobs.Rows[bc.Position]; this.daWorkEmp.SelectCommand.Parameters["njob"].Value=Convert.ToInt16(drv["njob"]); try { this.daWorkEmp.Fill(this.ds1.WorkEmp); // ERROR HAPPENS HERE } catch (Exception e3) { MessageBox.Show(e3.ToString()); return ; } } I got this message "System.invalidOperationException: there is already an open DataReader associated with this connection which must closed first" WorkEmp.SelectCommand is like SELECT b.firstname, b.lastname, a.empid, a.njob, b.empid AS Expr1 FROM WorkOn a INNER JOIN employee b ON a.empid = b.empid WHERE (a.njob = ?) I'm missing something??? I use the same logic in other modules and it works. I'm not using any DataReader!!! i'm not opening or closing by code the connection!! TIA Tag: Insert data from TataTable to a table in SQL Tag: 93914
  - 22
    - DataView.Sort Dilemma - A bit long I have a dataset contining sorted data in mutilple tables from which I am selecting rows to action. I use the Find method of the DefaultView from a specific Table to get the position of a row in the table. I then set the position of the BindingContext to action the row. eg. Table.DatafaultView.Sort = "ColumnName" pos=Table.DatafaultView.Find(key) BindingContext(DataSet,Table).Position = pos This works fine to switch between existing rows. When I add a row to the DataSet (without updating the DataAdpter, because I don't want to save the changes at this point) the above method returns incorrect rows. eg. If the existing data looks like this pos Key ------------ 0 123 1 234 2 345 and I then add the record 111 pos=Table.DatafaultView.Find(111) will return 0 as it should. BindingContext(DataSet,Table).Position = 0 will bind to 123 pos=Table.DatafaultView.Find(123) will return 1 BindingContext(DataSet,Table).Position = 1 will bind to 234 So it appears that the data in the DataSet looks like pos Key ------------ 0 123 1 234 2 345 3 111 Is there something I need to do, after an Table.Rows.Add(row), to the DefaultView or DataSet to have it rebuild its sort index? TIA Steve Tag: Insert data from TataTable to a table in SQL Tag: 93913
  - 23
    - Ideas on storing and retriving files in a Windows Application I'm looking for some ideas, I have a project to complete that has a requirement to store just about ANY type of document. Most common documents include Word, Excel, HTML, images, pdf, media files, etc. I was thinking of storing these into SQL Server 2000 as image type but my delimma lays with retrieval. I was thinking of using a Microsoft Web Browser control to display the item to the user. This works ok for 2 reasons, 1. The browser will open the appropiatte application to display the file and 2. I can just use the Navigate(2) method to display the item. The problem with this I don't want the requirement of storing the item to a file (filestream) before displaying (Navigate needs somewhere to go). I was thinking of memoryStream but I see no good examples (other than image files) on how to do this, espcially on how to handle large files. Is this a good approach? Or should I just stick with files stored on the server? THANKS -- Ken Beard MCSD, MCP Tag: Insert data from TataTable to a table in SQL Tag: 93905
  - 24
    - How call crosstab query in Access I have a crosstab query that I've created in MS Access. How do I run the query and stick the results into a datatable using ado.net? Can I reference the query just like a regular table? Tag: Insert data from TataTable to a table in SQL Tag: 93902
  - 25
    - DataSet Losing Data when Column Name Contains Colon Hi, Somebody else has posted something in regards to this before, but that was back in 2002. You can see his post at: http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&threadm=0f0c01c233f2%24988a7c30%24a4e62ecf%40tkmsftngxa06&rnum=4&prev=/groups%3Fhl%3Den%26lr%3D%26ie%3DUTF-8%26q%3Ddataset%2Bcolon%2B.net Microsoft has also made mention of this same problem. You can see Microsoft's FIX at: http://support.microsoft.com/default.aspx?scid=kb;en-us;818962 The FIX was posted in late 2003, but I have not seen any resolution to the problem. The problem must still exist since I am experiencing it today (2004). So, my problem is this: I have a dataset that gets populated by calling multiple stored procedures. The dataset is successfully populated. The stored procedure returns column names that contain colons (":"). When I do WriteXml(fileName, WriteSchema), I the dataset is saved successfully. When I load the file back, using ReadXml(fileName, ReadSchema), the dataset (its datatables, rather) have appropriate structures. The problem, though, is that the datatables that have column names that contain colons, result in the column being completely populated with DBNULL. I need help finding a work around to this. Any help is greatly appreciated. Keep in mind that I cannot change the stored procedure (in order to return appropriate column names). I hope I was clear as to what my problem is. Again, any help is greatly appreciated. Regards. Jacques Tag: Insert data from TataTable to a table in SQL Tag: 93900
- Next
  - 1
    - know the MappingName of a clicked Column in a Parent-Child DataGrid Hi, I have in my VB.NET-application a DataGrid with two DataTables in with a Parent-Child-relationship defined. When the users clicks on a call in the DataGrid, i want to know the MappingName of the Column. This is easy with only one table, but with 2 tables defined I'm not able to know on which Table the User is currently looking (the parent of the child), so I don't know if I should look at the Parent-tablestyle or the Child-tablestyle for my mappingname. does anybody has any suggestions how to know on which Table the user is lookin,g or how to find the MappingName without any need of the TableStyle or soething else? Thanks a lot in advance! Pieter Tag: Insert data from TataTable to a table in SQL Tag: 93895
  - 2
    - NEWBIE - SQL SUBSTR Function in asp.net Greetings, I have a quick question here. Can I use the SQL SUBSTR() function in my SQL statements in an asp.net page? I am getting an error when I try. Can anybody point me to a list of functions that are recognized and valid for SQL statements in asp.net pages? Thank you, -Dave Tag: Insert data from TataTable to a table in SQL Tag: 93890
  - 3
    - did debugging with sql server sp3 ever get fixed? When I installed SP3 on SQL 2000, I lost the ability to debug stored procedures from Visual Studio - single stepping into the stored procedure. I spent a day trying to fix it maybe 8 months ago - and gave up. Are there any tech notes that actually work to restore the ability to debug when SQL Server is on the same machine as Visual Studio? -- Thanks in advance, Les Caudle Tag: Insert data from TataTable to a table in SQL Tag: 93887
  - 4
    - SqlDataReader.GetChar ?? exist or not Hello, If this exists http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatasqlclientsqldatareaderclassgetchartopic.asp Why doesn't it show in intellisense in VS.NET 1.1 and throw errors. I have a stored procedure returning a char(1) datatype.... How should I pull this datafield from my DataReader, I know I could use dr.GetSqlString(i) but shouldn't there be something for a char or fixed length? Thanks, Matt Tag: Insert data from TataTable to a table in SQL Tag: 93886
  - 5
    - How to do this with SQL Hi! This is not clearly ADO.NET question, but I need this with VB.NET and ADO.NET anyway. It is possible to make SQL-query like ... SELECT 0 AS IntegerField, '' AS StringField ... ... to get field which really don't exists in database, but how to get this kind of "virtual field" as boolean type (true or false)? I need this as an aid field for user's temporary marking use with real database fields when form is loaded in SELECT SQL-query with ADO.NET. -- Thanks in advance! Mika Tag: Insert data from TataTable to a table in SQL Tag: 93882
  - 6
    - Expression Columns with Dates I was wondering if someone could enlighten me about using date/time variables in column expressions. I am trying to setup a timesheet style form. The datatable that holds the information is filled by code, no database interaction. I'm aware of the DateDiff function in SQL, but I can't get it to work in an expression column. Is there another function that I should be using, or do I have to do a work-around in code? Tag: Insert data from TataTable to a table in SQL Tag: 93881
  - 7
    - How to get distinct list of a column from a DataTable Hi; I am trying to do some calc for a DataTable populated from database. The DataTable has composite key e.g.ID, Date. My question is: What is the best and effective way to get the distinct list of ID from DataTable just like SQL "select distinct ID from table"? Thank you for your help! Yi Tag: Insert data from TataTable to a table in SQL Tag: 93873
  - 8
    - set parameter value fail? Hello, everyone: I have a store procedure just like: select name, id from user where( id in (@id)) When I try to set the parameter (@id) value with multiple id, I always get error, could anyone tell me how to set the value correctly? what I have done is: com1.parameters["@id"].value="1,2,3,"; but the type of this parameter is int. thanks for your help. haiwen Tag: Insert data from TataTable to a table in SQL Tag: 93864
  - 9
    - Connection to dBase file but not using .dbf extentions Hey all, Was wondering if there is another parameter to pass along with the rest of the connection string to have Jet look for a dBase connection using something other than .dbf? Here's my conn string for reference... "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & cDataPath & ";Extended Properties=dBASE IV;" We just happen to use the extension .DAT for all of our database files. Thanks in Advance for the help. Andy Tag: Insert data from TataTable to a table in SQL Tag: 93862
  - 10
    - DataSet - Forcing Values in IDENTITY fields There are numerous posts and articles on retrieving the IDENTITY values after inserts from Dataset. However, is there a way to force a value of an identity column through Dataset? The objective is to retrieve bulk rows from a source database and insert into a target database keeping the values of Identity columns intact. Thanks in advance! Tag: Insert data from TataTable to a table in SQL Tag: 93861
  - 11
    - Adding new records when databinding and using data relation win forms / data form wizard Hi, I have a dataset with two tables and a relation between them. I have controls (textboxes, check boxes etc) on a windows form bound to those data tables. Editing existing data and navigating between records works fine. However when I try to add a new record, only the master table is updated to the dataset (and eventually to the database). I struggled with this problem for a while and then decided to check what VS.NET data form wizard would generate in a similar situation. So I did the following actions: 1) added dataform to windows app .Net project 2) connected to Northwind database (SQL server) 3) created a new dataset 4) selected Orders and Order Details tables 5) created a datarelation between Orders and Order Details -tables ( Orders being the parent and Order Details the child). 6) chose all columns to be displayed from both tables 7) selected 'single record in individual controls' The generated form works fine when editing existing data or navigating. However when trying to add a new record, the master portion of the order (ie. individual text boxes) has to be saved prior to saving any Order Details rows. If the master portion isn't saved, "ForeignKeyConstraint 'x' requires the child key values (eg. 11088) to exist in the parent table. Do you want to correct the value?" message is displayed. ['x'= name of the datarelation in step 5]. So it's not possible to fill in all the necessary fields and then just hit 'update' to save the new order. Interestingly the OrderID is populated correctly in the datagrid with or without saving the master portion?! So how should the wizard generated code be amended to allow users to save an order by hitting update button just once? Any thoughts and/or pointers would be appreciated. Thanking in advance! Tag: Insert data from TataTable to a table in SQL Tag: 93860
  - 12
    - Strongly Typed Dataset Table - Indexed Find returns Nothing I have a strongly typed dataset and use the "FindBy.." function to search for a particular record based on the Primary Key. Generally, this works fine, but every once in a while, the find function fails and will not work anymore. In other words, when searching for a particular record, the FindBy function returns nothing even though the record definitely still exists. I have included some items from the Watch List below: _dstMain.FuturesContracts.Rows.Count 428 Integer DirectCast(_dstMain.FuturesContracts.Rows(273),FuturesDataset.FuturesContractsRow).FuturesCode "NG " String DirectCast(_dstMain.FuturesContracts.Rows(273),FuturesDataset.FuturesContractsRow).FuturesContract "U04 " String _dstMain.FuturesContracts.FindByFuturesCodeFuturesContract("NG ", "U04 ") Nothing As you can see, the dataset has 428 rows and row 273 contains the values "NG " for FuturesCode and "U04 " for FuturesContract. The function FindByFuturesCodeFuturesContract returns Nothing even though I am searching for a row that exists. (I also tried searching with the trailing spaces trimmed, but that did not help.) This table is being updated very often, though rows never get deleted or added. I also noted that the table in question seems to have 10 indexes. When the failure occurs, it seems to have 12 indexes, many with errors. I don't know where these extra indexes are coming from but I assume that they are part of the problem. Any help would be greatly appreciated. Thanks. Dave. Tag: Insert data from TataTable to a table in SQL Tag: 93854
  - 13
    - Binding datagrid with datset from DLL I am binding a datagrid to a dataset. The data displays fine, BUT if I do anything that causes the grid to repaint a few times (like scrolling the columns or even just moving the window so that part of the grid is off the screen), then the entire application hangs. I am binding like this: theGrid.DataSource = AppObject1.ds.Tables[0].DefaultView ; where AppObject1 is instantiated from a DLL like this: AppObject1 = (IAppClass)assembly.CreateInstance("MyNameSpace.AppClass1") I tried creating a copy of the dataset in the main program using ds.Copy(). When I bind to this new dataset, everything works without hanging. Is there a problem with binding a dataset that was created in a separate component? How can I workaround the problem without having to copy the entire dataset? Thanks, Yitz Tag: Insert data from TataTable to a table in SQL Tag: 93853
  - 14
    - DataReader questions Can a DataReader contain multiple result sets? I know a dataset can but I haven't seen the same statements regarding a DataReader? Also, how do you loop through multiple tables in a result set, whether its a dataset or data reader? Thanks! Norton Tag: Insert data from TataTable to a table in SQL Tag: 93849
  - 15
    - CacheSize Property Hi, I used to set the value of CacheSize property for Recordset in my VB apps(ADO 2.7). What property now I can use instead of CacheSize in ADO.NET? Any help would be greatly appreciated. Amin Tag: Insert data from TataTable to a table in SQL Tag: 93848
  - 16
    - Microsoft's DNP for Oracle. HELP!! (sorry for the x-post) Hello all, I am currently working on a project that uses MS's .net provider for Oracle. Now, here is where it gets WEIRD!!! for some reason when we run it in VS.NET's IDE it is fine, a previous build deployed (xcopy) onto a test PC is fine. However; when we have recently but a new build on it doesn't work. The only thing (and I ain't buying it) that my work mate says it is down to is that the path of the EXE has brackets in it!!! When we remove the brackets it is fine (something ain't right)!!!! Has anyone had a similar scenario or a explanation for this bizarre behaviour???? Regards Alan Seunarayan Tag: Insert data from TataTable to a table in SQL Tag: 93842
  - 17
    - How to read Constraints (Primary key, FK,etc) over any table. Hi, Iam trying hard to read the constraints over any table using System.Data obects in C#. Can anyone suggest the method for retreivint the constraints on any table . Moreover when I try reading tableschema(as XMLfile) it gives the coumn name and type ,but it do not give me the column length. How I can get the length of coumn? Vinay C Tag: Insert data from TataTable to a table in SQL Tag: 93839
  - 18
    - masterdetail need help ! Hi all I have a form with 2 datagrids (master-detail) and a combobox to create a filter for the masterdatagrid. The master-detail worked fine when i used a dataset like this : drelRelation = New DataRelation _ ("drelMasterDetail", dsMasterDetail.Tables("tblMaster").Columns("ID"), _ dsMasterDetail.Tables("tblDetail").Columns("MasterID")) dsMasterDetail.Relations.Add(drelRelation) dtgMaster.SetDataBinding(dsMasterDetail, "tblMaster") dtgDetail.SetDataBinding(dsMasterDetail, "tblMaster.drelMasterDetail") BUT now I wanna use the combobox for filtering, so I thought I could use the DataView and the RowFilter-property but then i can't get my master-detail part right????? anyone any idea? or is there a better way for this? I'm still a newbie Thanx in Advance Tag: Insert data from TataTable to a table in SQL Tag: 93837
  - 19
    - Crystal Report - Object reference not set to an instance of an obj I can't find anything on this.. anyone else have it? I looked on the crystal site but it lists the problem only for asp. I'm not usin asp, but winforms. I write the reports in CR for .NET and save them as .rpt files. Then i reference them in my app. I built a form usin the viewer, pass in all the crap, build and deploy... I start the app up and hit the form to bring up the report. That part works great. I can see the report just fine, data is there and all, and i can export it or whatever. BUT if i click on the print icon of the viewer it brings up the print options and U click ok, then... Object reference not set to an instance of an object What obj ref? It's the viewers print function... and it has the report.... Any ideas? Tag: Insert data from TataTable to a table in SQL Tag: 93834
  - 20
    - WQL DataProvider for ADO.Net Hi, WQL DataProvider is an ADO.Net data provider, so you can use it just like usual MS SQL DataProvider, but for WQL (WMI + SQL = WQL). This DataProvider supports SELECT, ASSOCIATORS and REFERENCES queries. Doesn't support event queries. http://www.yogageneration.com/~mvkazit/demo/WQLDataProvider/index.html -- Thanks, Tag: Insert data from TataTable to a table in SQL Tag: 93831
  - 21
    - KB article 830118 "Timeout Expired" - Cause? Hello, I have just had a bad experience upgrading a Windows Forms app from vb.net 2002 -> vb.net 2003. I experienced the problem as detailed in KB article 830118. According to the article, this behaviour is by design. Can someone please explain to me what the design goal was, and what is going on "under the hood" to cause this? Will it be resolved in future service packs and/or .net versions? Thanks. Tag: Insert data from TataTable to a table in SQL Tag: 93826
  - 22
    - Renumbering MS db ID? I have a small MS db which I update daily with new info. I've noticed that the ID numbers are increasing sequentially. How can I reset them back to 1 when I purge my data prior to adding in the new stuff? Here is how I'm currently deleting the info from my db (TSUpdate): Private Sub ClearMyData() ' Open Connection and Clear Data OleDbConnection1.Open() Dim MyCommand As New OleDbCommand("DELETE FROM TSUpdate", OleDbConnection1) MyCommand.ExecuteNonQuery() OleDbConnection1.Close() MyCommand.Dispose() End Sub Thanks in advance! Bruce Tag: Insert data from TataTable to a table in SQL Tag: 93824
  - 23
    - number of connection Hi How can I know how many connections is estabilished with database in SQL Server? Thanks Konrad Tag: Insert data from TataTable to a table in SQL Tag: 93820
  - 24
    - Problems in reading excel data Hello All I'm having some problems in reading excel data. The connection string is like this connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=" connectionString += "\"Excel 8.0;HDR={1};IMEX=1;ReadOnly=0;\""; It's not reading data which has an F appended to it (with any Alpohabet as suffix), like in 49844F or 32535X which is in coln called STOCK_NO. Any other numeric data, like 49929 is being read fine. This coln is being read into a column of type string and it's coming in as empty string since the resultant DataSet's xml is looking like this <MASTER> <RECD_DTE>2004-07-06T00:00:00.0000000-05:00</RECD_DTE> <STOCK_NO /> <MANUF_PRICE>42565</MANUF_PRICE> <INV_AMT>29960</INV_AMT> <LOW_PRICE>29661</LOW_PRICE> <SAVINGS>12904</SAVINGS> </MASTER> <MASTER> <RECD_DTE>2004-07-02T00:00:00.0000000-05:00</RECD_DTE> <STOCK_NO>49929</STOCK_NO> <MANUF_PRICE>47140</MANUF_PRICE> <INV_AMT>40212</INV_AMT> <LOW_PRICE>37810</LOW_PRICE> <SAVINGS>9330</SAVINGS> </MASTER> Any help is greatly appreciated. thanks Sunit Tag: Insert data from TataTable to a table in SQL Tag: 93815
  - 25
    - ADO.net update problem with VB and MS Access Greetings all, This is a repost. I had to put this down for a while, but now I have to get it running. I am using an Access Database to store question information for a questionnaire application. I am using VB.net as the front end, and ADO.net to retrieve the information from the database. My data is bring displayed in a DataGrid bound to a dataset. All of my calls to the database are to parameter queries. The problem that I am having is with the update command. When I call the update method for my data adapter, and pass a dataset containing just the changed records, only one record shows the change. I know that parameter queries work because data is being returned and update, just not in the quantities that I want. From what I have read, the update method in ADO.NET should be able to process a batch change, but the only specific information I have been able to find concerns SQL Server and not MS Access. The only information I have been able to find regarding the update method and MS Access were for single records. Does ADO.net send each update request separately, waiting for conformations between requests, or does it send all of the changes at once? I know, if you write the queries properly, SQL server can process the data in a lump sum, but I do not believe Access has that ability. Below is a copy of the offending code. Any help on this would be greatly appreciated. Thanks in advance, Edison Private Sub SaveData() Dim lRetVal As Long Dim oQuestions As Questions = New Questions Dim oDS_Changes As DataSet = New DataSet Dim sMsg As String 'Stop current edits 'Me.BindingContext(m_oDS).EndCurrentEdit() 'Grab all changed rows oDS_Changes = m_oDS.GetChanges 'Check to see if any changes have been made If (Not (oDS_Changes) Is Nothing) Then sMsg = "Are you sure you want to save these " & _ oDS_Changes.Tables(0).Rows.Count() & " rows to the database?" _ lRetVal = MsgBox(sMsg, Microsoft.VisualBasic.MsgBoxStyle.Question + _ Microsoft.VisualBasic.MsgBoxStyle.YesNo, "Save Records") Select Case lRetVal Case vbYes Try ' Save all changes oQuestions.SaveData(oDS_Changes) m_oDS.AcceptChanges() Catch updateException As System.Exception Throw updateException Catch e As Exception Throw e End Try Case vbNo 'Do nothing End Select End If End Sub Public Function SaveData(ByVal oDS As DataSet) As String Dim strMsg As String Dim lngRecsAffected As Long Try If (Not (oDS) Is Nothing) Then 'open connection to Database m_oCn.Open() 'attempt to update the data source da.Update(oDS, strClassName) End If Catch updateException As System.Exception Throw updateException Finally 'Close the Database Connection m_oCn.Close() End Try End Function Dim cmdSel As OleDbCommand Dim cmdIns As OleDbCommand Dim cmdUpd As OleDbCommand Dim cmdDel As OleDbCommand Dim strSQL As String 'Setup Select Command strSQL = "QryQuestion" cmdSel = New OleDbCommand(strSQL, m_oCn) cmdSel.CommandType = CommandType.StoredProcedure 'Setup Insert Command strSQL = "addqryQuestion" cmdIns = New OleDbCommand(strSQL, m_oCn) cmdIns.CommandType = CommandType.StoredProcedure cmdIns.Parameters.Add("QuestionText", OleDbType.LongVarWChar, 255, "Question_Text") cmdIns.Parameters.Add("CorrectAnswer", OleDbType.BigInt, 1, "Correct_Answer") cmdIns.Parameters.Add("QuestionNumber", OleDbType.BigInt, 1, "Question_Number") 'Setup Update Command strSQL = "updqryQuestion" cmdUpd = New OleDbCommand(strSQL, m_oCn) cmdUpd.CommandType = CommandType.StoredProcedure cmdUpd.Parameters.Add(New OleDbParameter("@QuestionNumber", OleDbType.BigInt, 50, "Question_Number")) cmdUpd.Parameters.Add(New OleDbParameter("@QuestionText", OleDbType.LongVarWChar, 255, "Question_Text")) cmdUpd.Parameters.Add(New OleDbParameter("@CorrectAnswer", OleDbType.BigInt, 50, "Correct_Answer")) cmdUpd.Parameters.Add(New OleDbParameter("@QuestionID", OleDbType.BigInt, 50, "Question_ID")).SourceVersion = DataRowVersion.Original 'Setup Delete Command strSQL = "delqryQuestion" cmdDel = New OleDbCommand(strSQL, m_oCn) cmdDel.CommandType = CommandType.StoredProcedure cmdDel.Parameters.Add("QuestionID", OleDbType.BigInt, 50, "Question_ID") initalizeDataAdapter(cmdSel, cmdIns, cmdUpd, cmdDel) End Sub Protected Sub initalizeDataAdapter(ByVal cmdSel As OleDbCommand, ByVal cmdIns As OleDbCommand, ByVal cmdUpd As OleDbCommand, ByVal cmdDel As OleDbCommand) 'Create and Setup Data Adapter da = New OleDbDataAdapter 'Setup Select Command da.SelectCommand = cmdSel 'Setup Insert Command da.InsertCommand = cmdIns 'Setup Update Command da.UpdateCommand = cmdUpd 'Setup Delete Command da.DeleteCommand = cmdDel 'Destroy connection object ' m_oCn.Close() End Sub Tag: Insert data from TataTable to a table in SQL Tag: 93812

I am trying to insert all the data in DataTable to a table to the SQL
Server. I looked around the internet and found ways to insert row by row.
Rather than doing this way, I want to simply "copy" data to a table in the
SQL Server. If possible, it would be convenient if the table is also
created.

I cannot use Select Into because I don't think I can use that statement from
DataTable. If you know a good way to copy table, can you let me know?

Thanks!

Top