mports
System.Data.SqlClient Imports
System.Data Public
Class Form9 Dim cnn1 As New SqlConnection Private Sub Form5_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load 'Compute top-level project folder and use it as a prefix for 'the primary data file Dim int1 As Integer = InStr(My.Application.Info.DirectoryPath, "bin\") Dim strPath As String = Microsoft.VisualBasic.Left(My.Application.Info.DirectoryPath, int1 - 1) Dim pdbfph As String = strPath & "northwnd.mdf" Dim cst As String = "Data Source=.\sqlexpress;" & _ "Integrated Security=SSPI;" & _ "AttachDBFileName=" & pdbfph cnn1.ConnectionString = cst
End Sub Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click 'Create a command to create a table Dim cmd1 As New SqlCommand cmd1.CommandText =
"CREATE TABLE FromExcel (" & _ "FirstName nvarchar(15), " & _ "LastName nvarchar(20), " & _ "PersonID int Not Null)" cmd1.Connection = cnn1
'Invoke the command Try cnn1.Open()
cmd1.ExecuteNonQuery()
MessageBox.Show(
"Command succeeded.", "Outcome", _ MessageBoxButtons.OK, MessageBoxIcon.Information)
Catch ex As Exception MessageBox.Show(ex.Message)
Finally cnn1.Close()
End Try End Sub Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click 'Create a command to drop a table Dim cmd1 As New SqlCommand cmd1.CommandText =
"DROP TABLE FromExcel" cmd1.Connection = cnn1
'Invoke the command Try cnn1.Open()
cmd1.ExecuteNonQuery()
MessageBox.Show(
"Command succeeded.", "Outcome", _ MessageBoxButtons.OK, MessageBoxIcon.Information)
Catch ex As Exception MessageBox.Show(ex.Message)
Finally cnn1.Close()
End Try End Sub Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click 'Declare FromExcel Data Table and RowForExcel DataRow Dim FromExcel As New DataTable Dim RowForExcel As DataRow FromExcel.Columns.Add(
"FirstName", GetType(SqlTypes.SqlString)) FromExcel.Columns.Add(
"LastName", GetType(SqlTypes.SqlString)) FromExcel.Columns.Add(
"PersonID", GetType(SqlTypes.SqlInt32)) 'Create TextFieldParser for CSV file from spreadsheet Dim crd1 As Microsoft.VisualBasic.FileIO.TextFieldParser Dim strPath As String = _ Microsoft.VisualBasic.Left( _
My.Application.Info.DirectoryPath, _ InStr(
My.Application.Info.DirectoryPath, "bin\") - 1) crd1 =
My.Computer.FileSystem.OpenTextFieldParser _ (
My.Computer.FileSystem.CombinePath(strPath, "Book1.csv")) crd1.TextFieldType = Microsoft.VisualBasic.FileIO.FieldType.Delimited
crd1.Delimiters =
New String() {","} 'Loop through rows of CSV file and populate 'RowForExcel DataRow for adding to FromExcel 'Rows collection Dim currentRow As String() Do Until crd1.EndOfData Try currentRow = crd1.ReadFields()
Dim currentField As String Dim int1 As Integer = 1 RowForExcel = FromExcel.NewRow
For Each currentField In currentRow Select Case int1 Case 1 RowForExcel(
"FirstName") = currentField Case 2 RowForExcel(
"LastName") = currentField Case 3 RowForExcel(
"PersonID") = CInt(currentField) End Select int1 += 1
Next int1 = 1
FromExcel.Rows.Add(RowForExcel)
RowForExcel = FromExcel.NewRow
Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException MsgBox(
"Line " & ex.Message & _ "is not valid and will be skipped.") End Try Loop 'Invoke the WriteToServer method fo the sqc1 SqlBulkCopy 'object to populate FromExcel table in the database with 'the FromExcel DataTable in the project Try cnn1.Open()
Using sqc1 As SqlBulkCopy = New SqlBulkCopy(cnn1) sqc1.DestinationTableName =
"dbo.FromExcel" sqc1.WriteToServer(FromExcel)
End Using Catch ex As Exception MessageBox.Show(ex.Message)
Finally cnn1.Close()
End Try 'Read the FromExcel table and display results in 'a message box Dim strQuery As String = "SELECT * " & _ "FROM dbo.FromExcel " Dim str1 As String = "" Dim cmd1 As New SqlCommand(strQuery, cnn1) cnn1.Open()
Dim rdr1 As SqlDataReader rdr1 = cmd1.ExecuteReader()
Try While rdr1.Read() str1 += rdr1.GetString(0) &
", " & _ rdr1.GetString(1) &
", " & _ rdr1.GetSqlInt32(2).ToString & ControlChars.CrLf
End While Finally rdr1.Close()
cnn1.Close()
End Try MessageBox.Show(str1,
"FromExcel") End Sub End
Class ////////////////////////////////////////////////////////////////////////////// I got the following error messages:
SecurityException was unhandled
Request for the permission of type 'System. Security. Permissions.FileIOPermission,mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'failed that is pointing to the code statement "Dim int1 As Integer = InStr (My.Application.Info.DirectoryPath, "bin\")."
Troubleshooting tips:
Store application data in isolated storage.
When deploying an Office solution, check to make sure you have fulfilled all necessary requirments.
Use a certificate to obtain the required permission(s).
If an assembly implementing the custom security references other assemblies, add the referenced assemblies to the full trust assembly list.
Get general help for the exception.
I am a new Microsoft VS.NET Express user to do the SQL Server 2005 Express and VB 2005 Express programming by using the example of a tutorial book. Please help and tell me what is wrong in my SQLCLR sep-up and project coding and how to correct the problem.
Many Thanks in advance,
Scott Chang
Scott Chang wrote: