Putting Data From DataTable/DataSet Into SQL Server Table?

May 5, 2005

Hello,

I created this DataTable, add rows of data to it, and then display the data on to a form via a repeater.

Dim ds As DataSet = New DataSetDim dtTableName As DataTable = New DataTable("dtTableName") dtTableName.Columns.Add("Description")dtTableName.Columns.Add("ItemNumber")dtTableName.Columns.Add("Quantity")dtTableName.Columns.Add("Price")ds.Tables.Add(dtTableName)

What I need to do now is create a table in SQL Server and update the database with the data I've collect in my dataset. How do I bind and update this data to a sql server table?

Thanks!James

View 1 Replies


ADVERTISEMENT

Dataset Not Putting Text In Right Place..is There A Better Control?

Jun 4, 2008

The dataset2 control doesn't seem to want to place the text in the right spot. I put it right after the 1st Dataset and the user comments wound up all the way down in the footer of the site. I want them to appear in the white area just underneath the article text.
Here's a link to one of my articles so you can see this. 
http://www.link-exchangers.com/view_full_article.aspx?aid=50
Down at the bottom left corner you'll see white space below the article. I'd like to have all the comments lined up going down the page. Is the dataset the best control for this task?

View 1 Replies View Related

Use DataTable/DataSet As Source To Report

Mar 31, 2008

Hi,

Is it possible to use datatable/dataset as source to a report in Reporting Services. That is possible in e.g. Crystal Reports. I can only find Report Server Model and XML as non-database sources.


Best regards
Lars

View 10 Replies View Related

DataSet Works... DataTable Doesn't... (ODBC)

Jun 21, 2007

I have a longstanding problem where Stored Procedures or complex T-SQL called from VB.NET will not populate a DataTable object, but will work fine with a DataSet.  For example:    'oConn is defined elsewhere...        Dim sErr as String = ""        Dim dt As New DataTable        If Not oConn Is Nothing Then            Try                Dim sSQL as String = "select 1"                Dim oCommand As New OdbcDataAdapter(sSQL, oConn)                oCommand.Fill(dt)            Catch ex As Exception                sErr = "Database Error: " & ex.Message            Finally                sqlCloseConnection(oConn)            End Try        End Ifthis works fine and my dt DataTable object gets one row.  However using this as the SQL:                Dim sSQL as String = "declare @foo table(mycol integer);insert @foo select 1;select mycol from @foo;"does not work.   It executes with no errors, but the DataTable has no rows.  Finally, if I replace the DataTable with:                Dim ds as DataSetI can then get the data in ds.Tables(0) no problem.So, if the results of the sql are a single result table being put at index 0 of a DataSet, why are they not being put in a single DataTable?When a sql is a simple select statement it always works directly to a DataTable.  Only when it's a SP or sql with some logic does it require the DataSet approach.  This is a reporting utility so I need to standardize the code though the sql will be dynamic. Any ideas?  

View 1 Replies View Related

Load Result Sets From Mysqldatareader Into Datatable Or Dataset

Nov 8, 2007

hi all.....
i select some records from my database using mysqldatareader.......but i want to load it's result sets into my datatable or dataset....
is it possible and how can i do it this way ?
thanks

View 1 Replies View Related

Putting Data Into Table B From Table A Without Using A Cursor

Aug 24, 2006

on SQL Server 2005, no SP.

Currently using a cursor in a stored procedure to retrieve data from one table and to put it into another table. like so:

declare @HTR money
declare @Uniqueid varchar(15)

declare cur_HTR cursor for
select uniqueid, sum(hours_to_resolve) as thtr from com_trail_helpdesk_module group by uniqueid

open cur_htr
fetch next from cur_HTR into @Uniqueid, @HTR

while @@fetch_status = 0 begin
update com_hpd_helpdesk_history
set total_hours_to_resolve = @HTR
where case_id_ = @Uniqueid
and dateasat = @dateasat
fetch next from cur_HTR into @Uniqueid, @HTR
end

close cur_htr
deallocate cur_htr
...

This is taking about 45 minutes each time to do 21k records. Is there a faster, better way to do this?

View 10 Replies View Related

T-SQL (SS2K8) :: Hierarchical Update Dataset - Refresh Datatable After Delete

Feb 5, 2015

Imagine the following scenario: two tables (say, "requests" and "details") are joined in a 1:n relationship on MSSQL 2008. Both tables contain an ID (autoincrement field) and a timestamp field for proper concurrency management. Data access in the frontend is provided by a typed dataset in VS 2010. There are SPs on the server which select, update, insert or delete data in each of the two tables (so, 8 SPs alltogether: uspRequestsSelect, uspDetailsInsert etc.). These SPs are used for data access in the dataset. The GUI is a Windows form with 2 datagridviews, one for request datatable and one for the child-relation-based datatable FK_request_details. So, each request shows its details. The form works well so far.

Now, trouble strikes. A business rule says: "the first details row of a request (=row with lowest ID) always has a 0 in column "additional fee". For additional detail rows, this field has to be set to constant value 45". In short: the first detail row of each request is free, second and later details are charged 45 €.

So, I created a SP "uspRequestFeeManager", which recalculates all (!) detail rows of a request. This SP is called in uspDetailsInsert, uspDetailsUpdate and uspDetailsDelete, as each of this cases causes the additional fee to be recalculated for all rows (as rowcount can change). In Management Studio, this works as well!

But: as the uspRequestFeeManager changes data even for rows the user did NOT touch, there's a concurrency exception in my frontend in the following case:

In a request with 2 detail rows (first row has fee = 0, second = 45), the user deletes the row with fee = 0. Committing via TableAdapterManager calls uspDetailsDelete, which calls uspRequestFeeManager, which sets the remaining single details row to a fee value of 0 (which is correct!). This causes TableAdapterManager.UpdateAll to fail ("concurrency exception; delete command has handled 0 of 1 expected records"), as uspRequestFeeManager has "edited" a row which the user didn't touch, and thus updated its timestamp as well. So, the list is out of sync.

The uspRequestFeeManager looked like a good idea... but it seems not to be.

Approach would be: instead of calling the uspRequestFeeManager from within the SP, call it programmatically after TableAfterManager.UpdateAll, and after that, 're-fill' the details datatable with the updated data. But that would transfer business logic from server to client. I don't like that...

View 3 Replies View Related

Importing Data In Datatable Using SSIS Package Trigger On Insert Is Not Firing On That Table

Oct 6, 2007

Hi
I am Importing data in datatable using SSIS package . I made trigger on that table on insert. The trigger on insert is not firing on that table
Please help
Thanks
CP

View 1 Replies View Related

Problem In Using Sqlbulkcopy To Insert Data From Datatable(no Identity Column) Into Sql Server Table Having Identity Column

Jun 19, 2008

Hi,
I am having problem in bulk update of a sql server table haning identity column from a datatable( has no identity column) using sqlbulkcopy. I tried several approaches, but it does not show any error nor is the table getting updated. But the identity value seems to getting increased every time.
thanks.
varun

View 6 Replies View Related

SQL Server 2012 :: Putting Data Into XML Format

Oct 7, 2015

Need getting data into XML format as shown in the last code block.The datatypes and table structures are pretty much fixed, but I can re-hash data into another Temp Table, CTE, etc..This is a server running SQL Server 2012, but I'd guess any version that understands FOR XML PATH should be fine.

Source tables and data
if object_id('Tempdb..#Element1') is not null drop table #Element1;
create table #Element1 (
[Attr1] varchar(10)
,[Attr2] varchar(4)

[code]...

View 9 Replies View Related

Updating A Permanent Table From A Dataset Within The Data Flow

Feb 14, 2007

I have a dataset that was created via a source + lookups + derived columns.

I wish to take this dataset and treat is as a table within a sql statement so that I can update a permanent table with the a specific value within the temp dataset.

In sql this is what I am trying to do:

SELECT COUNT(*) AS Count_of_Employees, DEPT
FROM Employees
GROUP BY DEPT

UPDATE Departments
SET Number_of_Employees = Count_of_Employees
WHERE Dept = dept.

View 1 Replies View Related

Aggregating One Table And Putting Results In Another Table

Aug 15, 2012

I need to analyze the results from Table 1, count how many records there are where one field matches, and then put some of Table 1's information into Table 2 along with the count of the records that match. This is built to interface with another system, so a lot of the information, names, and relationships can't always be changed.

Table 1: tblResourceAllocation
Fields (Description):ID (Unique)LastName (Unique alphanumeric string for each employee, but one employee can have multiple records in this table)Project (Unique name, can have multiple entries in this table)Owner (Unique alphanumeric string for each owner, but one owner can have multiple records in this table)ResourceStartDate (datetime)ResourceEndDate (datetime)ServiceID [I](this is what I want to aggregate)Status_FC [I]

Table 2: tblGRFM
ID (unique)Pm (the tblResourceAllocation.Owner's actual full name, which I'll look up from an EmployeeData table and concatenate the FirstName and LastName)IdClient (from tblResourceAllocation.Project I will look up the Customer from the Projects table, then look up the Customer ID from the Customers table)US_State_Id (look up State from Projects table then ID from tblStateCodes)Project_name (same as tblResourceAllocation.Project)Status (same as tblResourceAllocation.Status_FC)Id_operlst (same as tblResourceAllocation.ServiceID)Start (earliest start date of relevant entries)end (latest end date of relevant entries)Nb_ress (total number of ResourceAllocation entries that match Id_operlst)Cadence (explanation below)

I need to calculate how many of each serviceID's are in table 1, and then store that calculation and the ServiceID (Id_operlst) in table 2I need to separate those calculations by Owner, IdClient, US_State_Id, and Project_Name
I.E. If there are 5 entries in table 1 with ServiceId = X, and Owner A is listed on 2 of them and Owner B is listed on 3, I want it displayed as [Owner A, X, 2] and [Owner B, X, 3] in table 2.

Same goes for the other fields, which obviously adds more complexityWithin those calculations I also need to calculate the Start and end date for table 2. Using the last example, if one entry of Owner A's service X has the early start date and the other entry has the later end date, those dates need to be displayed in the Start and end for table 2 (should I use Rank here?)

I need to create a string of numbers separated by semicolons that shows how many resources are working on a given week after the start date

Example:I have 3 resources on a projectResource 1: 12/31/2012-1/27/2013 (Week 1-4 of our calendar)Resource 2: 1/7/2013-1/27/2013 (Week 2-4)Resource 3: 1/7/2013-1/27/2013 (Week 2-4)tblGRFM.Start will equal 12/31/2012 and tblGRFM.end will equal 1/27/2013Cadence should be equal to "1;3;3;3" to indicate that one resource will be working the project the first week, and three resources will be working the project for the next three weeks.

View 2 Replies View Related

Putting Images In An Unpopulated Table.

Sep 1, 2005

Here's what I've got.

We have a table that needs to be populated with a Foreign Key and an image file that matches it. The image file is named the the person's SSN which matches the FK.

Is there a way I can use the name of the file (123456789.jpg) to import it into the database or is there a better way?

View 1 Replies View Related

Filling A DataTable From SqlQuery : If SqlQuery Returns Null Values Problem Ocurrs With DataTable

Sep 3, 2007

Filling a DataTable from SqlQuery : If SqlQuery returns some null values problem ocurrs with DataTable.
Is it possible using DataTable with some null values in it?
Thanks

View 2 Replies View Related

Can A DataTable Update An SQL Table?

Jul 24, 2007

Can you use a DataTable to update an SQL Table and can it be done in a batch UPDATE as opposed to incrementing through every row using a stored procedure similar to UPDATE SQLTable SET Position = @Position WHERE ID = @ID?

View 3 Replies View Related

Putting Table Structure Into Word Using Procedure

Jan 22, 2006

Hi there

I have nearly 1000 table. I need all the table structure in word document. Is it possible to take table structure using Stored Procedure. If it is How to do that.

Thanx in advance

by
yasi

View 8 Replies View Related

Fetching Data In A Datatable

Apr 21, 2006

hello, i'm using asp.net 2 with VB, i have a table in my db named 'exams' and i have 6 columns in this table one for question number and one for the question and 3 columns for 3 answers and a last column for the right answer, now in my page i want to show 7 questions and  each  question has 3 answers that i can choose between them i want the question appear in datatable and the answers in radiolist i have a little code for that but it shows an error it says "no row at position 1" so hope u guys can help : Dim con As New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|aspnetdb.mdf;Integrated Security=True;User Instance=True")        Dim a As New ArrayList(8)        Dim i As New Integer        i = 0        Dim d As New DataSet()        Dim cmd As New SqlDataAdapter("select top(7) question from exams", con)        cmd.Fill(d, "exams")        a.Add(d.Tables.Item(0, 1))        i = i + 1        Dim ta As New DataTable        ta.Rows(i).Item(0).text = d.Tables(0).Rows(0)(i)and by the way this code is for the questions i still dont have the code for the answers that will appear in a radiolist hope u can help .thanks

View 3 Replies View Related

Create SQL Table From Excel Or DataTable?

Jul 24, 2007

Hello,I am trying to create a new table in SQL Server based on an excel sheet someone uploads to my site (ie No DTS, and I don't know the field names).  How can I easily do that?  Can I make a sql table based on a DataTable without going row-by-row?  Cause then I could go excel to datatable to sql table.Thanks a bunch, 

View 3 Replies View Related

Update/Insert DataTable Into SQL Table

Jan 31, 2008

I'm importing XML file into DataTable and need to Insert Data into SQL Table.
I'm not sure if its posible to take a DataTable with Data and insert into DataAdapter.  From there i wanted to update SQL using TableAdapter?
Any Tips?
Thanks,

View 4 Replies View Related

Putting Data From A Store Procedure Into A Cursor

Jun 21, 2004

The tile says it all... How to put into a Cursor the result of a procedure that selects certain rows.

View 1 Replies View Related

How To Save A Dataset To A Sql Server Table?

Sep 21, 2007

Scenario:  I have created a dataset from an excel file to display it on a screen. Now I would like to save this same dataset in a SQL Server table.
 Any ideas would be greatly appreciated.
 
Thanks

View 7 Replies View Related

Putting Multiple Xml Files Data Into Database In A Single Transaction

Sep 27, 2006

First of all i do not know whether this is the right form to ask the question Let me describe the scenario iam using Iam generating xml files at a particular place and sending them to a server  xml1|--------------------->dataset1------------------------------>adapter1.update(dataset1)xml2|----------------------->dataset2----------------------------->adapter2.update(dataset2)xml3|----------------------->dataset3------------------------------>adapter3.update(dataset3) all the three updates should happen in only one transaction if any one of the update fails then the transaction should rollbackcan anyone tell me a way to do iti am desperately in search of any ways to do it can anybody help please   

View 2 Replies View Related

Loading Data Into SQL Server From A SAS Dataset

Jul 20, 2005

Hi,SQL Server 2000 SP3Has anyone ever successfully loaded data into SQL Server from a SASdataset. I have tried using DTS and SAS OLE DB drivers but get thefollowing errorError Description:A provider specific error occurred (%1:%2)Context:Error calling GetRowSet to get DBSCHEMA_TABLES schema info. Yourprovider does not support all the schema rowsets required by DTS.It does seem to me to be a problem with the OLE DB providers but ifanyone has seen this issue with DTS previously , ler me know......Any help is appreciated....Thanks in advanceReg

View 1 Replies View Related

How To Transfer Data From One Dataset To Other Dataset

Apr 11, 2008

i have two datasets.one dataset have old data from some other database.second dataset have original data from sql server 2005 database.both database have same field having id as a primary key.i want to transfer all the data from first dataset to new dataset retaining the previous data but if old dataset have the same id(primary key) as in the new one then that row will not transfer.
but if the id(primary key) have changed values then the fields updated with that data.how can i do that.
 

View 4 Replies View Related

SQL 2012 :: Bulk Insert (or Another Way) To Table From Datatable From Inside Store Procedure

Nov 4, 2014

I passed .net datatable from a .net app to a store procedure. From this store procedure, how to code to bulk insert (or another way) to SQL table?

View 7 Replies View Related

SQL Server 2008 :: Populate One Dataset In SSRS Based On Results From Another Dataset Within Same Project?

May 26, 2015

I have a report with multiple datasets, the first of which pulls in data based on user entered parameters (sales date range and property use codes). Dataset1 pulls property id's and other sales data from a table (2014_COST) based on the user's parameters. I have set up another table (AUDITS) that I would like to use in dataset6. This table has 3 columns (Property ID's, Sales Price and Sales Date). I would like for dataset6 to pull the Property ID's that are NOT contained in the results from dataset1. In other words, I'd like the results of dataset6 to show me the property id's that are contained in the AUDITS table but which are not being pulled into dataset1. Both tables are in the same database.

View 0 Replies View Related

Adding Table To Dataset For SQL Server Mobile Causes VS 2005 To Lock Up.

Apr 20, 2006

I am running VS 2005 Professional Edition

Windows XP profession with Service Pack 2

SQL Server 2005 Developer Edition.

WHAT I HAVE DONE:

I have a database running in an instance of SQL Server.

I set this up for merge publication and then set up a SQL Server Mobile Edition Subscription to that publication.  After a few oversights I got everything working.  The Mobile database replicated just fine.  I went back verified all data was there.  Can make queries to it.

 

PROBLEM: 

I set up a new dataset to use tables from the SQL Server Mobile database.  If I drag one of the tables to the dataset, VS 2005 simply stops responding.  It is not using any processor. I click any place on the application and I get the Microsoft Visual Studio Delay Notification saying:

Microsoft Visual Studio is Busy.

Microsoft Visual Studio is waiting for an internal operation to complete.  If you regularly encounter this delay during normal usage, please report this problem to Microsoft.

Well...  It is more than just a delay.  The environment is not using any processor its just sitting here.  And I left it running for 2.5 hours...  so now this is becoming a big source of pain for me because I need to get that dataset working to finish my business logic.  The only option I have is to Kill the process.

Hopefully someone out there can help.

Additional Services running:

IIS (Whatever version comes with Windows XP Pro.  I think 5.1)

SQL Server Agent, SQL Server Integration Services, SQL Server Broswer and SQL Server FullTextSearch

UPDATE:  I am editing this post with an update.

I noticed that my other tables get added to the dataset just fine.  It is when I add one particular table that the entire visual studio simply stops and starts giving the delay notification.  I have no idea why this happens... nor do I see any noticeable difference between this table and the rest of them.  I went back and made sure that all columns types where directly supported by SQL Mobile Edition and they are.

 

View 14 Replies View Related

Putting Date Into SQL Server

Feb 2, 2006

I'm trying to put a date into a SQL Server table.  The database field type is "smalldatetime".  The variable dDate is type "date" and contains: 2/2/2006 (although I think Cdate actually converts it to: #2/2/2006#).  When I run the following code the date in the database is always ends up being: 1/1/1900.
Dim cmd As SqlCommand = New SqlCommand("INSERT INTO MyTable(MyDate) " & _                                 "VALUES (" & dDate & ")", SqlConn)daAppts.InsertCommand = cmddaAppts.InsertCommand.Connection = SqlConndaAppts.InsertCommand.ExecuteNonQuery()
Any other field types work fine, it's just dates that aren't working ????  Can someone please provide a code snippet showing me what I'm doing wrong??
 

View 3 Replies View Related

SQL Server 2012 :: Retrieving Dataset That Only Includes Latest Versions Of Data

Mar 28, 2014

In our Microsoft Dynamics Nav instance we have a Sales Header Archive table - into which copies of the Sales Header are placed, with 3 items forming the compound key:

Document Number
Version
Occurrence number

so if doc 1 is archived, then the records would be

Doc# | Version | Occurrence #
1 | 1 | 1

When a second copy is archived a new record is added:

Doc# | Version | Occurrence #
1 | 1 | 1
1 | 1 | 2

and then when maybe a 3rd version is archived a 3rd entry added

Doc# | Version | Occurrence #
1 | 1 | 1
1 | 1 | 2
1 | 2 | 2

This is for EACH document and I now need to retrieve the dataset which is the latest version of each document... but I'm drawing a blank!

If I

select [Doc#], max([Version]) as [V], max([Occurrence #]) from (table) group by [Doc#]

then I get the distinct list of docs, but I now need to use this list to select the records which match this criteria, from this table.

How do I select just these?

I thought (wrongly) that I could simply say:

Select * from Invoice Table where
Invoice.[Doc#], Invoice.[V], Invoice.[Occurrence #] in
(select [Doc#], max([Version]) as [V], max([Occurrence #]) from (table) group by [Doc#])

View 2 Replies View Related

SQL Server 2005 Reporting Svcs - How To Use Dataset 1 Info To Drive Nested Table Select

May 20, 2008

I've only been doing sql 2005 for a couple of months with minimal training so there's a lot I don't know.
What I'm trying to do is load a nested table (industry & customer totals) based on a value from the table it's nested in.
This is the relationship of the data.
I have at the highest group, an industry code, then a customer, then the part and then the fiscal year.
What I'm trying to accomplish is to get a group total (footer) for the (1) industry code and (2) the customer code. This footer would contain the fiscal years (ascending) and their monthly totals. I would like to take the industry code from table one and pass it to the select statement in the dataset that feeds the nested table. I've read this is not possible to load a dataset field into a parm but I've seen where some people know how to work around this. If you reply, please explain in simple terms. Thanks!


industry Customer Year OCT NOV DEC
001 - Signposts
M12345
Part 1
2006 5 6 2
2007 0 3 1


Part 2
2006 4 3 0
2007 1 0 7

Customer M12345 totals
2006 9 9 2
2007 1 3 8


M45678
Part 3
2007 8 4 7
2008 3 4 8


Part 4
2006 3 8 7
2007 5 6 6


Customer M45678 totals
2006 3 8 7
2007 13 10 13
2008 3 4 8
Industry 001 totals




2006 12 17 9
2007 14 13 21
2008 3 4 8

View 1 Replies View Related

Secruity Question, Putting SQL Server 2000 Or 2005 On Windows Server 2003 Running A Website

Jan 31, 2006

Where can I further educate myself on this subject?

Right off from the start I would assume that installing SQL Server 2000 or 2005 on Windows Server 2003 that is set up as a web server hosting a website would be against "best practices." Is my assumption right?

Common sense tells me to not to host a website on a pc that is also hosting my database.

View 4 Replies View Related

Putting SQL Server 05 Database On GoDaddy Server. Argh!!!

Apr 5, 2007

I have a simple Website and Database created in VWD exp.




I've just added a windows account to GoDaddy.com.


Unfortunately there is no way to port your database on to the GoDaddy server directly, but it says you must instead, recreate the database by copying and running SQL statements from a file called "personal-add.sql". (their "help" page: http://help.godaddy.com/article.php?article_id=695)


I can't find any such file. Can someone please let me know what I'm missing here?


Thanks.

View 4 Replies View Related

SQL Server 2012 :: Putting Multiple Values In The Variables?

Jan 28, 2015

how can i put multiple values in the variables.

for eg:

Declare @w_man as varchar
set @w_man = ('julial','BEVERLEYB', 'Lucy') and few more names.

I am getting syntax error(,)

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved