Contents of binsert_param_trans.aspx:
<%@ Page Language="VB" %> <%@ import Namespace="System.Data" %> <%@ import Namespace="System.Data.SqlClient" %> <script runat="server"> Sub Page_Load(sender As System.Object, e As System.EventArgs) Dim bid_array () as Integer = { 126, 127, 197, 128, 109, 119, 179, 216, 217, 218, 311, 406, 512, 526, 527} Dim bloc_array () as String = { "A", "A", "A", "A", "A", "A", "A", "A", "A", "A", "B", "B", "B", "B", "B"} Dim fn_array () as String = {"Joe","Joan","John","Millie","Bob" , "Bill", "Fred","Alice", "Annie", "Carol", "Will","Ethel","Burdine","Wilson","Vera"} Dim ln_array () as String = {"Doe", "Doe", "Doe", "Smith","Smythe","Jones","Perry", "Post","Taylor","Rainey","Carlson","Cabot", "Ward","Watson","Huff"} Dim et as String Dim SQLString0 as String Dim SQLString1 as String Dim SQLString2 as String Dim i as Integer Dim error_count as Integer error_count=0 results.InnerHtml="" SQLString0="DROP TABLE badge2" SQLString1="CREATE TABLE badge2 (bid int NOT NULL, bloc char(1), blastname varchar(20), bfirstname varchar(20),primary key (bid))" SQLString2="INSERT INTO badge2 (bid,bloc,bfirstname,blastname) VALUES (@bid,@bloc,@fn,@ln)" Dim myConnection = New SqlConnection("server=AUCKLAND;database=gl001;uid=gl001;pwd=11111;") myConnection.Open() Dim myCommand = New SqlCommand("", myConnection) Dim myTransaction As SqlTransaction Try myCommand.CommandText = SQLString0 myCommand.ExecuteNonQuery() results.InnerHtml="Step A. Dropped table badge2 OK<P>" myCommand.CommandText = SQLString1 myCommand.ExecuteNonQuery() results.InnerHtml=results.InnerHtml+"Step B. Create table badge2 OK<P>" Catch exc as exception error_out.InnerHtml = "<br><b>*** Error while Creating badge table</b>.<br>" + et+"<br>"+exc.Message + "<br>" + exc.Source error_count=error_count+1 End Try if error_count=0 then '*** i.e., the drop/create worked ' '*** Start the transaction ' '*** Note, we begin the transaction on the connection ' AND, we have to ALSO assign the transaction to the command object ' ' myTransaction = myConnection.BeginTransaction() myCommand.Transaction=myTransaction results.InnerHtml=results.InnerHtml+"<P>Step C. Begin the Transaction<P>Start the bulk inserts:" for i=0 to 14 Try myCommand.CommandText=SQLString2 myCommand.Parameters.AddWithValue( "@bid" , bid_array(i) ) myCommand.Parameters.AddWithValue( "@bloc" , bloc_array(i) ) myCommand.Parameters.AddWithValue( "@fn" , fn_array(i) ) myCommand.Parameters.AddWithValue( "@ln" , ln_array(i) ) myCommand.ExecuteNonQuery() myCommand.Parameters.Clear() results.InnerHtml=results.InnerHtml+"<br>Inserted row"+Cstr(i)+" OK Values:" results.InnerHtml=results.InnerHtml+" bid="+Cstr(bid_array(i)) results.InnerHtml=results.InnerHtml+" bloc="+bloc_array(i) results.InnerHtml=results.InnerHtml+" fn="+fn_array(i) results.InnerHtml=results.InnerHtml+" ln="+ln_array(i) Catch exc as exception error_out.InnerHtml = error_out.InnerHtml+"<br><b>Part B *** Error while Inserting Records into badge2 table i=</b>"+Cstr(i)+"<br>" + exc.Message + "<br>" + exc.Source error_count=error_count+1 End Try next if error_count=0 then myTransaction.Commit() results.InnerHtml=results.InnerHtml+"<p>Step D. No errors detected. Commit the transaction." else myTransaction.Rollback() results.InnerHtml=results.InnerHtml+"<p>Step D. "+Cstr(error_count)+" errors detected. Rolled back the transaction." end if end if myConnection.Close() end sub </script> <html> <head> </head> <body> <center> <table border="1"> <tr> <td> <img src="captsm.gif" /></td> <td align="center" valign="middle" bgcolor="#aaaaaa"><font face="COMIC SANS MS"><font size="4"> <br><b>Bulk Insert in VB.NET <br>With Parameters and Transaction Control <br> </font> </td> </tr> </table> </center> <form id="Form1" method="post" runat="server"> <p><font color="#000000"> <div id="results" runat="server"></div> </font> <p><font color="#ff0000"> <div id="error_out" runat="server"></div> </font> </form> </body> </html>
***** end of file *** printed: 131 lines on: 11/23/2009 6:06:58 AM
