How to load record into a dataset???

Support123

Sorry, me again... I thought of something. How would one go about loading data from a select statement... like this one: Dim connectionString As String = "Initial Catalog=DBName;Data Source=InstanceName;Integrated Security=SSPI;" Dim cn As SqlConnection = New SqlConnection(connectionString) Dim sqlCommand As SqlCommand = New SqlCommand("SELECT * FROM StagingEmployer ORDER BY StagingDate", cn) cn.Open() Dim TestRowset As SqlDataReader = sqlCommand.ExecuteReader() Into a dataset. Then you can close the connection to the database... and work with the sorted data in the dataset??? I need help on this matter please. Thank you in advance :-)

"Many of life's failures are people who did not realize how close they were to success when they gave up." Thomas A. Edison

Support123

Okay, you wont beleive it but i figured it out... Now can someone please show me or point me to a link on how to read from a Dataset Once it has the records from the database in it???

"Many of life's failures are people who did not realize how close they were to success when they gave up." Thomas A. Edison

Support123

NEVER MIND... I got it all figured out :-)

"Many of life's failures are people who did not realize how close they were to success when they gave up." Thomas A. Edison

ChandraRam

Support123 wrote:

I got it all figured out

Great, but it would be really great if you could post your findings back here too... it will help others looking for something similar and also puts you in the position of giving something back to the community :)

Support123

Sorry... Here is what i have and it is working fine now :-) Dim DS As System.Data.DataSet Dim MyCommand As System.Data.OleDb.OleDbDataAdapter Dim MyConnection As System.Data.OleDb.OleDbConnection Dim connectionString As String = "Initial Catalog=DBName;Data Source=ServerName;Integrated Security=SSPI;" Dim cn As SqlConnection = New SqlConnection(connectionString) Dim strSqlCommand As SqlCommand = Nothing ' Set Up the Connection String. MyConnection = New System.Data.OleDb.OleDbConnection( _ "provider=SQLOLEDB; database=DBName; " & _ "Integrated Security=SSPI; server=ServerName;") ' Set Up the Command to be executed. MyCommand = New System.Data.OleDb.OleDbDataAdapter( _ "SELECT * FROM StagingGeneric ORDER BY StagingDate", MyConnection) ' Fill the DataSet with the returned Values from the Database. DS = New System.Data.DataSet() MyCommand.Fill(DS, "StagingGeneric") ' Close The Connection To The DataBase. MyConnection.Close() ' Get Data Table Dim dt As DataTable = DS.Tables("StagingGeneric") ' For Each Row In The Data Row Check What Action To Follow For Each row As DataRow In dt.Rows 'MsgBox(Convert.ToString(row(3))) If Convert.ToString(row(3)) = "U" Then Dim LastContribDate As String = "''" If (Len(Convert.ToString(row(1))) > 9) Then LastContribDate = Left(Convert.ToString(row(1)), 10) End If Dim StageDate As String = "''" If (Len(Convert.ToString(row(4))) > 9) Then StageDate = Left(Convert.ToString(row(4)), 10) End If cn.Open() strSqlCommand = New SqlCommand("UPDATE stagingGenericTest SET" _ + " [Member_Number] = '" + Convert.ToString(row(0)) _ + "', [LastContributionDate] = " + LastContribDate _ + ", [PaymentStatus] = '" + Convert.ToString(row(2)) _ + "', [Action] = '" + Convert.ToString(row(3)) _ + "', [StagingDate] = " + StageDate _ + " WHERE [Member_Number] = '" + Convert.ToString(row(0)) + "'", cn) strSqlCommand.ExecuteNonQuery() cn.Close() ElseIf Convert.ToString(row(3)) = "I" Then Dim LastContribDate As Stri

Support123

So now i can just change the connectionString to point to another server (i just used the same server to test on.) :)

"Many of life's failures are people who did not realize how close they were to success when they gave up." Thomas A. Edison

Dave Kreskowiak

May I STRONGLY suggest you read up on parameterized queries. Using string concantentation to build your UPDATE query is a severe security risk and makes for some very unstable code. Read this...[^]

A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007

ChandraRam

Support123 wrote:

Not bad for a days work

Yeah... but you don't want your DB hacked, do you? :) Read up on SQL Injection Attacks...

Support123

i am going to run this vb.net code in a ssis package (script task)... i will be running this package in a scheduled job on SQL... so i dont think the user will be able to add bits to the query... this is just to keep our db up to date with their live database. The user wont see the results of these queries as it does not return any values to them... i only updates our database if the staging tables (that are populated by means of triggers) have any values available. (if i am wrong please correct me) Thank you anyway :-)

"Many of life's failures are people who did not realize how close they were to success when they gave up." Thomas A. Edison

Dave Kreskowiak

Support123 wrote:

i am going to run this vb.net code in a ssis package (script task)...

Even more of a reason to secure the code.

Support123 wrote:

so i dont think the user will be able to add bits to the query...

All it takes is a screwed piece of data, that you're assuming is good, to screw up your database. You do know what "assume" really means, don't you?

Support123 wrote:

The user wont see the results of these queries as it does not return any values to them...

Well, the database returns data to someone at some time in the future. The users will eventually see the results.

A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007