|
Sorry, me again...
I thought of something.
How would one go about loading data from a select statement... like this one:
<br />
Dim connectionString As String = "Initial Catalog=DBName;Data Source=InstanceName;Integrated Security=SSPI;"<br />
Dim cn As SqlConnection = New SqlConnection(connectionString)<br />
Dim sqlCommand As SqlCommand = New SqlCommand("SELECT * FROM StagingEmployer ORDER BY StagingDate", cn)<br />
cn.Open()<br />
Dim TestRowset As SqlDataReader = sqlCommand.ExecuteReader()<br />
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
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
Here is what i have and it is working fine now
<br />
Dim DS As System.Data.DataSet<br />
Dim MyCommand As System.Data.OleDb.OleDbDataAdapter<br />
Dim MyConnection As System.Data.OleDb.OleDbConnection<br />
Dim connectionString As String = "Initial Catalog=DBName;Data Source=ServerName;Integrated Security=SSPI;"<br />
Dim cn As SqlConnection = New SqlConnection(connectionString)<br />
Dim strSqlCommand As SqlCommand = Nothing<br />
<br />
' Set Up the Connection String.<br />
MyConnection = New System.Data.OleDb.OleDbConnection( _<br />
"provider=SQLOLEDB; database=DBName; " & _<br />
"Integrated Security=SSPI; server=ServerName;")<br />
<br />
' Set Up the Command to be executed.<br />
MyCommand = New System.Data.OleDb.OleDbDataAdapter( _<br />
"SELECT * FROM StagingGeneric ORDER BY StagingDate", MyConnection)<br />
<br />
' Fill the DataSet with the returned Values from the Database.<br />
DS = New System.Data.DataSet()<br />
MyCommand.Fill(DS, "StagingGeneric")<br />
<br />
' Close The Connection To The DataBase.<br />
MyConnection.Close()<br />
<br />
' Get Data Table<br />
Dim dt As DataTable = DS.Tables("StagingGeneric")<br />
<br />
' For Each Row In The Data Row Check What Action To Follow<br />
For Each row As DataRow In dt.Rows<br />
'MsgBox(Convert.ToString(row(3)))<br />
If Convert.ToString(row(3)) = "U" Then<br />
Dim LastContribDate As String = "''"<br />
If (Len(Convert.ToString(row(1))) > 9) Then<br />
LastContribDate = Left(Convert.ToString(row(1)), 10)<br />
End If<br />
Dim StageDate As String = "''"<br />
If (Len(Convert.ToString(row(4))) > 9) Then<br />
StageDate = Left(Convert.ToString(row(4)), 10)<br />
End If<br />
cn.Open()<br />
strSqlCommand = New SqlCommand("UPDATE stagingGenericTest SET" _<br />
+ " [Member_Number] = '" + Convert.ToString(row(0)) _<br />
+ "', [LastContributionDate] = " + LastContribDate _<br />
+ ", [PaymentStatus] = '" + Convert.ToString(row(2)) _<br />
+ "', [Action] = '" + Convert.ToString(row(3)) _<br />
+ "', [StagingDate] = " + StageDate _<br />
+ " WHERE [Member_Number] = '" + Convert.ToString(row(0)) + "'", cn)<br />
strSqlCommand.ExecuteNonQuery()<br />
cn.Close()<br />
ElseIf Convert.ToString(row(3)) = "I" Then<br />
Dim LastContribDate As String = "''"<br />
If (Len(Convert.ToString(row(1))) > 9) Then<br />
LastContribDate = Left(Convert.ToString(row(1)), 10)<br />
End If<br />
Dim StageDate As String = "''"<br />
If (Len(Convert.ToString(row(4))) > 9) Then<br />
StageDate = Left(Convert.ToString(row(4)), 10)<br />
End If<br />
cn.Open()<br />
strSqlCommand = New SqlCommand("INSERT INTO stagingGenericTest" _<br />
+ " ([Member_Number] " _<br />
+ ", [LastContributionDate] " _<br />
+ ", [PaymentStatus] " _<br />
+ ", [Action] " _<br />
+ ", [StagingDate])" _<br />
+ " VALUES ('" + Convert.ToString(row(0)) _<br />
+ "', " + LastContribDate _<br />
+ ", '" + Convert.ToString(row(2)) _<br />
+ "', '" + Convert.ToString(row(3)) _<br />
+ "', " + StageDate _<br />
+ ")", cn)<br />
strSqlCommand.ExecuteNonQuery()<br />
cn.Close()<br />
ElseIf Convert.ToString(row(3)) = "D" Then<br />
cn.Open()<br />
strSqlCommand = New SqlCommand("DELETE FROM stagingGenericTest" _<br />
+ " WHERE [Member_Number] = '" + Convert.ToString(row(0)) + "'", cn)<br />
strSqlCommand.ExecuteNonQuery()<br />
cn.Close()<br />
End If<br />
Next<br />
Not bad for a days work 
"Many of life's failures are people who did not realize how close they were to success when they gave up." Thomas A. Edison
|
|
|
|
|
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
|
|
|
|
|
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...[^]
|
|
|
|
|
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
|
|
|
|
|
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.
|
|
|
|
|
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...
|
|
|
|
|
Hi all,
Please help me on the migration task from vb to that of vb.net
for that code is written in vb 6.0 and i want to it in vb.net .
visual studion 2005 environment.
Thanks
|
|
|
|
|
That's a very generic question covering an awful lot of ground. To start with, just open the VB6 project in Visual Studio 2005. Do not double click the projects .VBP file. You have to open Open the Project with VS2005 already started. This will automatically launch the upgrade wizard which will TRY AND DO ITS BEST to convert the code to VB.NET. Since there are lots of changes between VB6 and VB.NET code, chances are you WILL have errors and need to correct the code by hand to get it working.
|
|
|
|
|
Hi,
Please help me...
I am struggling to run more then one query on sql server 2005... i get errors and i dont really know how to get going.
Here is what i have tried now:
<br />
Dim mySQLConnection As New SqlConnection( _<br />
"Initial Catalog=DBName;Data Source=InstanceName;Integrated Security=SSPI;")<br />
mySQLConnection.Open()<br />
<br />
Dim mySQLTransaction As SqlTransaction = _<br />
mySQLConnection.BeginTransaction()<br />
<br />
Dim mySQLCommand As SqlCommand = _<br />
mySQLConnection.CreateCommand()<br />
<br />
mySQLCommand.CommandText = "SELECT * FROM StagingEmployer ORDER BY StagingDate"<br />
<br />
mySQLTransaction.Commit()<br />
<br />
mySQLConnection.Close()<br />
Now... how do i get the Select * from StagingEmployer Order by StagingDate return values into a rowset that i can use???
Once there... how do i run a second query like for instance:
<br />
Update StagingEmployer<br />
Set EndDate = GetDate()<br />
Where StagingDate < '2007/01/01'<br />
Sorry... i have tried it but not like this... i can post my previouse attempt but i will do this in 2nd post... it is a bit large.
"Many of life's failures are people who did not realize how close they were to success when they gave up." Thomas A. Edison
|
|
|
|
|
<br />
Dim connectionString As String = "Initial Catalog=DBName;Data Source=InstanceName;Integrated Security=SSPI;"<br />
Dim cn As SqlConnection = New SqlConnection(connectionString)<br />
Dim sqlCommand As SqlCommand = New SqlCommand("SELECT * FROM StagingEmployer ORDER BY StagingDate", cn)<br />
cn.Open()<br />
Dim TestRowset As SqlDataReader = sqlCommand.ExecuteReader()<br />
<br />
While TestRowset.Read()<br />
If (Convert.ToString(TestRowset(35)) = "U") Then<br />
Dim sqlCommand2 As SqlCommand = New SqlCommand("UPDATE stagingEmployerTest SET" _<br />
+ " [Employer_Number] = '" + Convert.ToString(TestRowset(0)) _<br />
+ "', [Name] = '" + Convert.ToString(TestRowset(1)) _<br />
+ "', [Trading_Name] = '" + Convert.ToString(TestRowset(2)) _<br />
+ "', [Business_Number] = '" + Convert.ToString(TestRowset(3)) _<br />
+ "', [Company_Number] = '" + Convert.ToString(TestRowset(4)) _<br />
+ "', [Tax_File_Number] = '" + Convert.ToString(TestRowset(5)) _<br />
+ "', [Street_Address_1] = '" + Convert.ToString(TestRowset(6)) _<br />
+ "', [Street_Address_2] = '" + Convert.ToString(TestRowset(7)) _<br />
+ "', [Street_Address_3] = '" + Convert.ToString(TestRowset(8)) _<br />
+ "', [Street_Suburb] = '" + Convert.ToString(TestRowset(9)) _<br />
+ "', [Street_State] = '" + Convert.ToString(TestRowset(10)) _<br />
+ "', [Street_Postcode] = '" + Convert.ToString(TestRowset(11)) _<br />
+ "', [Street_Country] = '" + Convert.ToString(TestRowset(12)) _<br />
+ "', [Postal_Address_1] = '" + Convert.ToString(TestRowset(13)) _<br />
+ "', [Postal_Address_2] = '" + Convert.ToString(TestRowset(14)) _<br />
+ "', [Postal_Address_3] = '" + Convert.ToString(TestRowset(15)) _<br />
+ "', [Postal_Suburb] = '" + Convert.ToString(TestRowset(16)) _<br />
+ "', [Postal_State] = '" + Convert.ToString(TestRowset(17)) _<br />
+ "', [Postal_Postcode] = '" + Convert.ToString(TestRowset(18)) _<br />
+ "', [Postal_Country] = '" + Convert.ToString(TestRowset(19)) _<br />
+ "', [Contact_Name] = '" + Convert.ToString(TestRowset(20)) _<br />
+ "', [Contact_Title] = '" + Convert.ToString(TestRowset(21)) _<br />
+ "', [Contact_Salutation] = '" + Convert.ToString(TestRowset(22)) _<br />
+ "', [Phone] = '" + Convert.ToString(TestRowset(23)) _<br />
+ "', [Fax] = '" + Convert.ToString(TestRowset(24)) _<br />
+ "', [Email] = '" + Convert.ToString(TestRowset(25)) _<br />
+ "', [Broker] = '" + Convert.ToString(TestRowset(26)) _<br />
+ "', [Underwriter] = '" + Convert.ToString(TestRowset(27)) _<br />
+ "', [Diplomatic_Indemnity] = '" + Convert.ToString(TestRowset(28)) _<br />
+ "', [Status] = '" + Convert.ToString(TestRowset(29)) _<br />
+ "', [Image] = '" + Convert.ToString(TestRowset(30)) _<br />
+ "', [Created] = '" + Convert.ToString(TestRowset(31)) _<br />
+ "', [Created_By] = '" + Convert.ToString(TestRowset(32)) _<br />
+ "', [Modified] = '" + Convert.ToString(TestRowset(33)) _<br />
+ "', [Modified_By] = '" + Convert.ToString(TestRowset(34)) _<br />
+ "', [Action] = '" + Convert.ToString(TestRowset(35)) _<br />
+ "', [StagingDate] = '" + Convert.ToString(TestRowset(36)) + "'" _<br />
+ " WHERE [Employer_Number] = '" + Convert.ToString(TestRowset(0)) + "'", cn)<br />
sqlCommand2.ExecuteScalar()<br />
End If<br />
MsgBox("Result :" + Convert.ToString(TestRowset(35)))<br />
End While<br />
In this example the first Query is the Select * from StagingEmployer Order By StagingDate (this one works fine) the second Query is the Update... (not working) i posted this section before but without the ,cn in the 2nd query. Sorry.
This doesnt work???
I thought it should and i am not sure why and how to get this going? Sorry for the big post.
"Many of life's failures are people who did not realize how close they were to success when they gave up." Thomas A. Edison
|
|
|
|
|
Okay, i see that it is advised to use the ExecuteNonQuery() rather than ExecuteScalar() in my second query (the update Query)... but it is still not working 
It gives an error : There is already an open DataReader associated with this Command which must be closed first.
I think i know what it means but i don't know how to fix it... The TestRowset is still open (i think that is why the error comes up) but i can't close it right??? because it needs to read it to be able to go to next record and next record and so 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
|
|
|
|
|
Beside your error I give you the tip to exlude the database connections and the SQL code OUTSIDE your code. SQL code belons in stored procedures and db connections in a ini-file or registry. This enables changes outside your code if there are db changes so a normal user/db-admin can modify it.
Greetings from Germany
|
|
|
|
|
okay, thanks... i will keep that in mind... i will change it when i get this working
"Many of life's failures are people who did not realize how close they were to success when they gave up." Thomas A. Edison
|
|
|
|
|
hi guys,,
i want my application to run from my network in partial trust mode and also i want to access data with oledbpoviders but i get security exception if i am in partial trst mode unless i configured the intranet zone to full trust that what i dont want, is there any way that i can run this application in partial trust mode, how do i create custom permission set like stuff ,, thanks in advance
..
hello
|
|
|
|
|
this is rather complicated stuff (more administrator like )
Normaly the best way is to give your application a strong name and then use the .net MMC plugin to configure the security context for the signed assembly.
|
|
|
|
|
can u please explain in a brief detail or give me some helping link ..
thanks in advance
hello
|
|
|
|
|
To use the .net config tool just go to the command prompt and enter:
"mscorcfg.msc"
You will see a nice GUI and hopefully find the infos you need.
To sign a assembly you can use sn.exe to generate a public/private-key that you can use in your application
More help with google:
http://www.google.com/search?q=code+security+.net
This one seems a good start:
http://msdn.microsoft.com/msdnmag/issues/01/02/CAS/
As I said: this stuff is rather nasty - to fully understand you might want to buy a book (there are several books - just search for security and .net - I think any of these will have one or more chapters concerning CAS)
|
|
|
|
|
how can i convert dataset to datatable or how can i copy dataset to datatable?
thanks in advance!
jyn
modified on Wednesday, January 09, 2008 7:41:18 PM
|
|
|
|
|
jhyn wrote: how can i convert dataset to datatable
DataTable dt = ds.Table[0];
jhyn wrote: how can i copy dataset to datatable?
DataSet ds = new DataSet();<br />
ds.Tables.Add(dt);
|
|
|
|
|
A DataSet is a collection of DataTable objects with any DataRelation objects connecting the tables. There's no such thing as converting a DataSet to a DataTable. The table you want is already a member of the DataSet.
|
|
|
|
|
hi,
i have two question:
1-
i move from form to another by function show() and hide() ,how can backtracking to first form or refresh first form ?
2-
how can delete row from sql server 2005 after 24 hours ?
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
