Please help with database connection and querries that need to be run on sql server 2005 through vb.net
-
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:
Dim mySQLConnection As New SqlConnection( _ "Initial Catalog=DBName;Data Source=InstanceName;Integrated Security=SSPI;") mySQLConnection.Open() Dim mySQLTransaction As SqlTransaction = _ mySQLConnection.BeginTransaction() Dim mySQLCommand As SqlCommand = _ mySQLConnection.CreateCommand() mySQLCommand.CommandText = "SELECT * FROM StagingEmployer ORDER BY StagingDate" mySQLTransaction.Commit() mySQLConnection.Close()Now... how do i get theSelect * from StagingEmployer Order by StagingDatereturn values into a rowset that i can use??? Once there... how do i run a second query like for instance:Update StagingEmployer Set EndDate = GetDate() Where StagingDate < '2007/01/01'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
-
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:
Dim mySQLConnection As New SqlConnection( _ "Initial Catalog=DBName;Data Source=InstanceName;Integrated Security=SSPI;") mySQLConnection.Open() Dim mySQLTransaction As SqlTransaction = _ mySQLConnection.BeginTransaction() Dim mySQLCommand As SqlCommand = _ mySQLConnection.CreateCommand() mySQLCommand.CommandText = "SELECT * FROM StagingEmployer ORDER BY StagingDate" mySQLTransaction.Commit() mySQLConnection.Close()Now... how do i get theSelect * from StagingEmployer Order by StagingDatereturn values into a rowset that i can use??? Once there... how do i run a second query like for instance:Update StagingEmployer Set EndDate = GetDate() Where StagingDate < '2007/01/01'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
See: here i tried to run second query but no success with this:
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() While TestRowset.Read() If (Convert.ToString(TestRowset(35)) = "U") Then Dim sqlCommand2 As SqlCommand = New SqlCommand("UPDATE stagingEmployerTest SET" _ + " [Employer_Number] = '" + Convert.ToString(TestRowset(0)) _ + "', [Name] = '" + Convert.ToString(TestRowset(1)) _ + "', [Trading_Name] = '" + Convert.ToString(TestRowset(2)) _ + "', [Business_Number] = '" + Convert.ToString(TestRowset(3)) _ + "', [Company_Number] = '" + Convert.ToString(TestRowset(4)) _ + "', [Tax_File_Number] = '" + Convert.ToString(TestRowset(5)) _ + "', [Street_Address_1] = '" + Convert.ToString(TestRowset(6)) _ + "', [Street_Address_2] = '" + Convert.ToString(TestRowset(7)) _ + "', [Street_Address_3] = '" + Convert.ToString(TestRowset(8)) _ + "', [Street_Suburb] = '" + Convert.ToString(TestRowset(9)) _ + "', [Street_State] = '" + Convert.ToString(TestRowset(10)) _ + "', [Street_Postcode] = '" + Convert.ToString(TestRowset(11)) _ + "', [Street_Country] = '" + Convert.ToString(TestRowset(12)) _ + "', [Postal_Address_1] = '" + Convert.ToString(TestRowset(13)) _ + "', [Postal_Address_2] = '" + Convert.ToString(TestRowset(14)) _ + "', [Postal_Address_3] = '" + Convert.ToString(TestRowset(15)) _ + "', [Postal_Suburb] = '" + Convert.ToString(TestRowset(16)) _ + "', [Postal_State] = '" + Convert.ToString(TestRowset(17)) _ + "', [Postal_Postcode] = '" + Convert.ToString(TestRowset(18)) _ + "', [Postal_Country] = '" + Convert.ToString(TestRowset(19)) _ + "', [Contact_Name] = '" + Convert.ToString(TestRowset(20)) _ + "', [Contact_Title] = '" + Convert.ToString(TestRowset(21)) _ + "', [Contact_Salutation] = '" + Convert.ToString(TestRowset(22)) _ + "', [Phone] = -
See: here i tried to run second query but no success with this:
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() While TestRowset.Read() If (Convert.ToString(TestRowset(35)) = "U") Then Dim sqlCommand2 As SqlCommand = New SqlCommand("UPDATE stagingEmployerTest SET" _ + " [Employer_Number] = '" + Convert.ToString(TestRowset(0)) _ + "', [Name] = '" + Convert.ToString(TestRowset(1)) _ + "', [Trading_Name] = '" + Convert.ToString(TestRowset(2)) _ + "', [Business_Number] = '" + Convert.ToString(TestRowset(3)) _ + "', [Company_Number] = '" + Convert.ToString(TestRowset(4)) _ + "', [Tax_File_Number] = '" + Convert.ToString(TestRowset(5)) _ + "', [Street_Address_1] = '" + Convert.ToString(TestRowset(6)) _ + "', [Street_Address_2] = '" + Convert.ToString(TestRowset(7)) _ + "', [Street_Address_3] = '" + Convert.ToString(TestRowset(8)) _ + "', [Street_Suburb] = '" + Convert.ToString(TestRowset(9)) _ + "', [Street_State] = '" + Convert.ToString(TestRowset(10)) _ + "', [Street_Postcode] = '" + Convert.ToString(TestRowset(11)) _ + "', [Street_Country] = '" + Convert.ToString(TestRowset(12)) _ + "', [Postal_Address_1] = '" + Convert.ToString(TestRowset(13)) _ + "', [Postal_Address_2] = '" + Convert.ToString(TestRowset(14)) _ + "', [Postal_Address_3] = '" + Convert.ToString(TestRowset(15)) _ + "', [Postal_Suburb] = '" + Convert.ToString(TestRowset(16)) _ + "', [Postal_State] = '" + Convert.ToString(TestRowset(17)) _ + "', [Postal_Postcode] = '" + Convert.ToString(TestRowset(18)) _ + "', [Postal_Country] = '" + Convert.ToString(TestRowset(19)) _ + "', [Contact_Name] = '" + Convert.ToString(TestRowset(20)) _ + "', [Contact_Title] = '" + Convert.ToString(TestRowset(21)) _ + "', [Contact_Salutation] = '" + Convert.ToString(TestRowset(22)) _ + "', [Phone] =Okay, i see that it is advised to use the
ExecuteNonQuery()rather thanExecuteScalar()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
-
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:
Dim mySQLConnection As New SqlConnection( _ "Initial Catalog=DBName;Data Source=InstanceName;Integrated Security=SSPI;") mySQLConnection.Open() Dim mySQLTransaction As SqlTransaction = _ mySQLConnection.BeginTransaction() Dim mySQLCommand As SqlCommand = _ mySQLConnection.CreateCommand() mySQLCommand.CommandText = "SELECT * FROM StagingEmployer ORDER BY StagingDate" mySQLTransaction.Commit() mySQLConnection.Close()Now... how do i get theSelect * from StagingEmployer Order by StagingDatereturn values into a rowset that i can use??? Once there... how do i run a second query like for instance:Update StagingEmployer Set EndDate = GetDate() Where StagingDate < '2007/01/01'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
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
-
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