Truncate MS Access Table From Visual Studio Using Code

Dec 24, 2014

I am building a Visual Sudio process that will Truncate and load data to an Access database (2010) from SQL Server (2012). I have one "Execute SQL Task" for one table (LungCancer_HF_Patients):

"DELETE LungCancer_HF_Patients.* FROM LungCancer_HF_Patients;" that parses and works.

I have another table (Report1):

"DELETE Report1.* FROM Report1;"

that does not parse with a message "The query failed to parse. Incorrect syntax near '*'."

Why the first one parses correctly and the other one does not?

View 2 Replies


ADVERTISEMENT

Help With Converting Code: VB Code In SQL Server 2000-&&>Visual Studio BI 2005

Jul 27, 2006

Hi all--I'm trying to convert a function which I inherited from a SQL Server 2000 DTS package to something usable in an SSIS package in SQL Server 2005. Given the original code here:
Function Main()
on error resume next
dim cn, i, rs, sSQL
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=sqloledb;Server=<server_name>;Database=<db_name>;User ID=<sysadmin_user>;Password=<password>"
set rs = CreateObject("ADODB.Recordset")
set rs = DTSGlobalVariables("SQLstring").value

for i = 1 to rs.RecordCount
sSQL = rs.Fields(0).value
cn.Execute sSQL, , 128 'adExecuteNoRecords option for faster execution
rs.MoveNext
Next

Main = DTSTaskExecResult_Success

End Function

This code was originally programmed in the SQL Server ActiveX Task type in a DTS package designed to take an open-ended number of SQL statements generated by another task as input, then execute each SQL statement sequentially. Upon this code's success, move on to the next step. (Of course, there was no additional documentation with this code. :-)

Based on other postings, I attempted to push this code into a Visual Studio BI 2005 Script Task with the following change:

public Sub Main()

...

Dts.TaskResult = Dts.Results.Success

End Class

I get the following error when I attempt to compile this:

Error 30209: Option Strict On requires all variable declarations to have an 'As' clause.

I am new to Visual Basic, so I'm on a learning curve here. From what I know of this script:
- The variables here violate the new Option Strict On requirement in VS 2005 to declare what type of object your variable is supposed to use.

- I need to explicitly declare each object, unless I turn off the Option Strict On (which didn't seem recommended, based on what I read).

Given this statement:

dim cn, i, rs, sSQL

I'm looking at "i" as type Integer; rs and sSQL are open-ended arrays, but can't quite figure out how to read the code here:

Set cn = CreateObject("ADODB.Connection")

cn.Open "Provider=sqloledb;Server=<server_name>;Database=<db_name>;User ID=<sysadmin_user>;Password=<password>"

set rs = CreateObject("ADODB.Recordset")

This code seems to create an instance of a COM component, then pass provider information and create the recordset being passed in by the previous task, but am not sure whether this syntax is correct for VS 2005 or what data type declaration to make here. Any ideas/help on how to rewrite this code would be greatly appreciated!

View 7 Replies View Related

How Can I Copy The Content Of MS Access Table In Database SQL Using Visual Studio Basic

Sep 26, 2007

Code Snippet

Hi there,

I'm struggeling for more than a week now with this problem, without a finding the solution.

I have two databases, MS Access and SQL Server 2005 Express Edition

Using a procedure in Visual Studio i would like to copy all the records from one table in MS Access into an existing table in SQL Server (the tables have the same name and the same layout)

I tried to prepare one Dataset to copy from Access into SQL Server but when i run the command 'DaSQL.Update(DsSQL, "Tabella") nothing happens (not even an exeption has been raised), looking during debug, the DataSet seems filled though...

Please could anyone explain what's wrong and / or is there a more quicker way to copy data from a table to another?


Note i woul have as a final goal to get data from an AS400 database by ODBC, manage it, and put it on SQL Server for a 'data mining' scope (eliminating the use of MS Access, not suited for FE-BE).

the procedure goes like this;


' Create a connection to the MS Access Database
Dim connectionToAccess As New OleDbConnection(DBConnectionAccString)
strsql = "SELECT * FROM [TABELLA]"
connectionToAccess.Open()
Dim DaAccess As New OleDbDataAdapter(strsql, connectionToAccess)

Dim DsAccess As New DataSet("ACCESS")
DaAccess.FillSchema(DsAccess, SchemaType.Source, "Tabella")
DaAccess.Fill(DsAccess, "Tabella")

' Create a connection to the SQL Database
Dim connectionToSQL As New SqlConnection(DBConnectionSQLString)
connectionToSQL.Open()
Dim DaSQL As New SqlDataAdapter(strsql, connectionToSQL)

Dim DsSQL As New DataSet("SQL")
DaSQL.FillSchema(DsSQL, SchemaType.Source, "Tabella")
DaSQL.Fill(DsAccess, "Tabella")

DaSQL.Update(DsSQL, "Tabella")

Note I tried also the following, withou a result;


DsSQL = DsAccess.Copy
DaSQL.Update(DsSQL, "Tabella")

Please is there someone who could respond !!???

View 6 Replies View Related

How To Trap The Results Of Constraints In SQL Server 2005from Visual Studio C# Code?

Jul 2, 2007

Hi all,
Suppose I have set a CHECK constraint to a column where Salary field is not permitted to be less than 1000 or greater than 10000.
In this situation, when I insert a new record with salary as 10, using a stored procedure from Visual Studio, how will I trap the error from C# Code?
Thanks
Tomy

View 2 Replies View Related

Sqlcommand And Visual Studio Net 2003 Code To Update A Record With A Click Of Button.

Sep 1, 2006

Hello to everyone,  I have this problem If I use this code when I try to update a record in my sql database It does not do anything. This is the code.  Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click        Dim myconn As New SqlConnection("Data Source=.;Initial Catalog=SistemaIntegral; user id=sa")        Dim mycmd As New SqlCommand("presup", myconn)        mycmd.CommandType = CommandType.StoredProceduremycmd.parameters.Add(New SqlParameters("@txtClave", SqlDbtype.int,4)).Value=txtClave.textmycmd.parameters.Add(New SqlParameters("@txtForig", SqlDbtype.varchar,50)).Value=txtFOrig.textmycmd.parameters.Add(New SqlParameters("@txtFmod", SqlDbtype.varchar,50)).Value=txtFmod.textmycmd.parameters.Add(New SqlParameters("@txtFeje", SqlDbtype.varchar,50)).Value=txtFeje.textmycmd.parameters.Add(New SqlParameters("@txtPorig", SqlDbtype.varchar,50)).Value=txtPorig.textmycmd.parameters.Add(New SqlParameters("@txPmod", SqlDbtype.varchar,50)).Value=txtPmod.textmycmd.parameters.Add(New SqlParameters("@txtPeje", SqlDbtype.varchar,50)).Value=txtPeje.text mycmd.parameters.Add(New SqlParameters("@txtClave", SqlDbtype.int,4)).Value=txtClave.text        myconn.Open()        mycmd.ExecuteNonQuery()        myconn.Close()        mycmd.Dispose()        myconn.Dispose()    End Sub  This is the code for the procedure called "presup" in SQL 2000 Server.CREATE PROCEDURE [presup](@txtClave [int],@txtForig [varchar](50),@txtFmod [varchar](50),@txtFeje [varchar](50),@txtPorig [varchar](50),@txtPmod [varchar](50),@txtPeje [varchar](50),@txtIdPresupuesto [int])as Update [Presupuesto]Set [IdClave]=@txtClave,[orig]=@txtForig,[moda]=@txtFmod,[ejer]=@txtFEje,[origr]=@txtPorig,[modar]=@txtPmod,[ejerr]=@txtPejeWhere ([IdPresupuesto]=@txtIdPresupuesto)GO When I click the Update button of my webform it does not do anything. Please Help me I am new to Visual Studio.Net 2003. (2 weeks ago I started using Visual Studio) 

View 1 Replies View Related

Cannot Access Data Source In Visual Studio 2012

May 25, 2015

I am not able to see the Data Source in Visual studio 2012. I have tried several things like 'Alt + shift + D' which is meant to bring up the datasource tab but it does not. I have also looked in view->Other windows but its not displayed there.

View 5 Replies View Related

Need For Multi-Threaded Visual Basic.NET To SQL Server Data Access Example Code

Feb 10, 2007

Of all the Visual Basic.NET data access books that I have purchased and all the Internet site example code that I have reviewed, none have had any good examples of multi-threaded VB.NET code doing data access.

I am trying to avoid the non-responsiveness in a VB app while a simple data retrieval from SQL Server 2005 is in progress.

If anyone knows of any book titles or web sites that have example code (good or not) of multi-threaded VB.NET applications doing data access against Microsoft SQL Server (7, 2000, or 2005) or even against Microsoft Access(TM), it would be very much appreciated if you could provide the book title or URL to point me in the right direction.

The more examples the better.

Thanks in advance.

View 5 Replies View Related

Can Visual Studio 2005 BI Co-exist With Visual Studio 2003?

Jan 19, 2006

I ask because I'm still looking for the source of interface problems I have in using the SSIS designer.

View 4 Replies View Related

What's The Accepted Way To Retrieve Records In A SQL Table With Null Values Using A Visual Studio 2005 Table Adapter?

Jan 21, 2008

I'm using an ObjectDataSource in Visual Studio to retrieve records from a SQL Server 2005 database.
 I have a very simple dilemma.  In a table I have fields FirstName, Surname, Address1, Address2, Address3 etc. None of these are mandatory fields.
It is quite common for the user not to enter data in Address2, Address3, so the values are <null> in the SQL table.
In Visual Studio 2005 I have an aspx form where users can pass search parameters to the ObjectDataSource and the results are returned according to the passed in parameters.
The WHERE clause in my Table Adapter is:WHERE (Address1 LIKE @Address1 + '%') AND (Address2 LIKE @Address2 + '%') AND   (Address3 LIKE @Address3 + '%') AND (FirstName LIKE @FirstName + '%') AND (Surname LIKE @Surname + '%')
If, for example, I simply want to search WHERE FirstName LIKE ‘R’, this does not return any results if the value of Address3 is <null>
My query is this: Could someone please show me the best way in Visual Studio 2005 to return records even if one of the Address fields is <null>.
For reference, I have tried: Address3 LIKE @Address3 + '%' OR IS NULLThis does work, however itsimply returns every instance where Address3 is <null>  (accounting for about 95% of the records in the database). Thanks in advance Simon
 

View 9 Replies View Related

Truncate Microsoft Access Table From SSIS

Mar 27, 2008

Does anyone know how to truncate a Mircrosoft Access table from an SSIS package?

View 7 Replies View Related

How To Delete A Table In Visual Studio 2005

Aug 21, 2007

I need to delete a database table from my database. I can not figure out how to do it though. I tried deleting individual columns but go an error message "Drop Failed for column 'TPListHistoryId ' (Microsoft.SqlServer.Smo)

View 6 Replies View Related

Data Access :: Cannot Truncate Table Because It Is Being Referenced By FOREIGN KEY Constraint

Nov 11, 2015

Am working on sql database table shifting to new database tables. Am getting the following error while truncating the tables:-

Cannot truncate table 'orderItem' because it is being referenced by a FOREIGN KEY constraint.How can i remove all the items in the table if i found this error.

View 7 Replies View Related

Integration Services :: Truncate Or Delete Access Table Using SSIS?

Sep 4, 2015

I have to truncate  access table before I insert records to access database table. 

I tried using Delete From Table_name  or Truncate table Table_NAME and I have used Microsoft Jet 4.0 OLE DB Provider. It does not seem to work. I read some post on forums. None of them seem to work while truncating or deleting records from one of the access database table.

Is there any easy way to truncate access database without using script component and VB scripts. I do not know how to Write VB scripts so trying to find alternatives.

[URL]

View 5 Replies View Related

Visual Studio 2005 - Reporting Services 2005 Access Import Error

Oct 22, 2007

I am trying to import Access reports using Visual Studio 2005 and Access 2007. SQL Server 2005 with Reporting Services is also installed. I select Reports in the Solution Explorer and then Import Reports... Microsoft Access and browse to my Access database. After I select the database I see it open briefly as if it is trying to import the reports but then Access quickly closes and a error pops up: "You already have the database open." I have checked and double checked and the database is not open. I have even tried several different databases and the same errror occurs. Any help would be greatly appreciated.

View 1 Replies View Related

Visual Studio Closes When Accessing The Columns Tab Of A Lookup Transformation Referencing SQL 7 Table

Jul 30, 2007

I'm creating a new Integration Services Project that copies data out of a SQL 7 server, transforms it, and places the data on a SQL 2005 (SP 2) Server. When defining a lookup transformation, if I specify an OLE DB Connection to my server running SQL 7 as the reference table, as soon as I click on the Colums tab, Visual Studio closes / crashes and dumps me to windows. I don't get an error message. If however I specify a connection to a server running SQL 8, or SQL 2005, no problems.

Is this supposed to happen?

My workstation is running Windows XP Pro SP2, Visual Studio 2005 Pro.

Microsoft SQL Server Integration Services Designer
Version 9.00.1399.00

The server that doesn't work for a reference table is running Windows 2000 Server SP4
SQL 7.00.623


Thanks for your help,
Kirk

View 6 Replies View Related

Table Visibility Not Functioning Correctly On Server, Works Fine In Visual Studio

Feb 13, 2007

I have a report problem. I'm using a parameter to dynamically control visibility for two tables. If the parameter is set to one value, I want to switch one of the tables to invisible, if the parameter is set to another, I want the other table to be invisible instead.

This all works fine in Visual Studio. When I publish it to my report server, the visibility controls no longer function and both tables always display. Any ideas here?

I'm running 2005, SP2 CTP.

View 4 Replies View Related

Visual Studio Database File And SQL Server Management Studio Express Question

Mar 17, 2007

I have a database in my "App_Data" folder of my visual studio project.  I can view it fine in Visual Studio's built-in tools for managing a database attached to a solution.  However i recently started playing around with the SQL Server Management Studio Express program.  When i attach my database to Management Studio, and try to run my program it crashes.  I think it might be a permissions error?!? When i detatch it and reattach it in visual studio it runs fine again.   Any suggestions? ThanksJason 

View 1 Replies View Related

MS Visual Studio 2005 Vs SQL Server Business Intelligence Development Studio

Apr 16, 2007

I recently installed the Evaluation Edition of SQL Server 2005 x64 and it appears that MS Visual Studio 2005 is installed in stead of SQL Server Business Intelligence Development Studio. When I choose new project the only template available is "Blank Solution". How do I get all the templates (i.e. Analysis Server Project, Integration Services Project, Report Model Project, Report Server Wizard project, etc.)?

Or would it be better to uninstall MS Visual Studio 2005 and attempt to reinstall BIDS?



View 4 Replies View Related

Visual Studio 2005 Standard And SQL Server Management Studio?

Sep 4, 2007

I am new to visual studio and I am still not sure of all its components and features.

I installed visual studio 2005 standard edition but cannot find SQL Server Management Studio?

I guess this must be because it is not included with Visual studio 2005 standard. Is it included with VS 2005 professional?

I want to add pictures of products to my shopping site using an SQL database and I’ve been told that SQL Server Management studio is required as it is a graphical tool.

How would I go about obtaining the SQL server management studio. There seems to be different versions of SQL server that it is confusing to know which one to purchase.

Will the SQL server 2005 version that comes with Visual studio standard be sufficient for me now right? I want to create a shopping site with hundreds, perhaps even thousands of products. I want to use an SQL server 2005 database. The database will include ‘dynamically generated’ product images if that is the correct terminology.

My goodness, it seems I still have so much to learn.

Thanks

View 1 Replies View Related

The Database Created Using Management Studio Cannot Be Connected To Visual Studio???help

May 13, 2008

 
 
I have created a database under management studio and i want it to be connected in visual studio but it failed
the error msgs said that the database can't be connected coz the database with same name exits but that is not true

View 2 Replies View Related

How Do I Get The Database That I Am Using In Visual Studio Into My SQL Server Management Studio?

Sep 12, 2007

How do i get the database that i am using in visual studio into my SQL server management studio?
i need to create some scripts to create stored procedures on a live server.

View 1 Replies View Related

Query Works SQL Server Studio Not Visual Studio

Feb 29, 2008

I have SSRS in Visual Studio. I created a query that works fine in SQL Server Management Studio, but when pasted into Visual Studio I get the error "An expression of non-boolean type specified in a context where a condition is expected, near '('.

Here is the query. Can anyone help on why this isn't working? Thanks.

SELECT CASE WHEN MONTH(dbo.MAS_CCS_ARN_InvHistoryHeader.SOTransDate) = MONTH(GETDATE()) AND YEAR(dbo.MAS_CCS_ARN_InvHistoryHeader.SOTransDate) = YEAR(GETDATE())
THEN dbo.MAS_CCS_ARO_InvHistoryDetail.SOExtChargeAmount ELSE 0 END AS CurrentMonth,
CASE WHEN SubString(dbo.MAS_CCS_GL_Account.Account,1,3) = '400' THEN 'ALEDO' ELSE ' ' END AS Location,
dbo.MAS_CCS_ARN_InvHistoryHeader.SOTransDate, dbo.MAS_CCS_ARN_InvHistoryHeader.InvoiceNumber,
dbo.MAS_CCS_AR1_CustomerMaster.CustomerName, dbo.MAS_CCS_ARO_InvHistoryDetail.DetailSeqNumber,
dbo.MAS_CCS_ARO_InvHistoryDetail.LineType, dbo.MAS_CCS_GL_Account.Account, dbo.MAS_CCS_ARO_InvHistoryDetail.SOExtChargeAmount
FROM dbo.MAS_CCS_AR1_CustomerMaster, dbo.MAS_CCS_ARN_InvHistoryHeader, dbo.MAS_CCS_ARO_InvHistoryDetail,
dbo.MAS_CCS_GL_Account
WHERE dbo.MAS_CCS_AR1_CustomerMaster.CustomerNumber = dbo.MAS_CCS_ARN_InvHistoryHeader.CustomerNumber AND
dbo.MAS_CCS_ARN_InvHistoryHeader.InvoiceNumber = dbo.MAS_CCS_ARO_InvHistoryDetail.InvoiceNumber AND
dbo.MAS_CCS_ARO_InvHistoryDetail.SOGLSalesAcct = dbo.MAS_CCS_GL_Account.AccountKey

View 1 Replies View Related

Is Management Studio Express Compatible With Visual Studio?

Sep 13, 2006

I have installed Visual Studio 2005 which includes SQL Server Express but not the Management Studio.

Can I install SQL Server Management Studio Express?

View 1 Replies View Related

How To Access By Code Table Returned By SqlDataSource1 ???

Jun 5, 2008

Hi to all,
I'm writting code that needs to pass data returned by a SqlDataSource object to another object (user control to graph data)
Query in SqlDataSource object is OK, I've seen data returned by it at a GridView Object but I need to pass that data to an user control that needs a reference to the table that has the data
... What would be the code that do that task ??
 I'm new to develop web applications... using Visual Web Developer Express 2008
Help in advance

View 1 Replies View Related

To Create Unique Table Names And Access Them In Code...

Jan 17, 2006

Hi all,My program is a central data processing application built in ASP.We have different companies that use different web pages on another webapplication (from different countries) to load some inventory data(merchandise - clothes).Here is my requirement: Load different types of data (in differentformats) into a common set of tables, to do this I have to firstfilter, do lookup's, use cross-reference tables on this data and thenload it into a couple of tables.Since data is so different everytime, I want to have one main storedprocedure in which I can build the table (I know the format of thisdata so I know what columns/types to create) into which I will feed thedata.After this I will create other stored procs/udf's that reference thistable (probably from the same main stored proc) 'cleanse' the databefore loading into the actual tables.How feasible is my approach? (if you can call it one :-) I do not wantto have seperate tables for each country, that will be too many and sothis plan...If not any other ideas will be really helpful.thanks much

View 3 Replies View Related

Can Never Access Database Table From Code After Copying To Another SQL Server...

Nov 29, 2007

I am not sure I understand the problem I am causing, but I am a beginner!

Here's the situation: I have a table located on MS SQL server database number 1. Said table, which we'll call WIDGET_PRICES, is accessed regularly by my existing source code and has no problems.

At some point, I decide to move operations to MS SQL database number 2 and do a very simple database copy of WIDGET_PRICES from database 1 to database 2 using the Microsoft SQL Server Management Studio.

The end result, inevitably, is that my source code can no longer access the very same table as it is located on the new database server. The code hasn't changed, it's still trying to access WIDGET_PRICES as always. And, from what I see on my screen through Management Studio, WIDGET_PRICES appears just fine.

An example error is the one I just got:



Microsoft OLE DB Provider for SQL Server error '80040e37'

Invalid object name 'YB_ITEMS'.

/yardbark/tampabay/header.asp, line 27




The only clue is that while my transferred tables often look named like "database1.WIDGET_PRICES on database 1, they wind up looking like database2.WIDGET_PRICES on dabase 2.




I include a little more detail and screenshots of the tables in questions at this web page.




Dave

View 3 Replies View Related

Advice On Importing Access Data Into MSSQL Table Using Code

Aug 2, 2004

Hi,

I'm about to embark on writing some code in perl or VBscript that automatically synchronises a constantly updated Access database with an MSSQL database.

I know MSSQL has an import tool built into Enterprise manager but I'm wondering if theres a stored procedure that does this?

The way I'm thinking of doing it is to read the all the access tables into separate hash arrays and then INSERTing them into the MSSQL database after checking for any duplicates. This all sounds a bit time consuming (there are a large number of tables) and processor intensive.

If anyones done anything like this before, I'd love to hear their views......!

Thanks!

View 9 Replies View Related

SqlException Was Unhandeld By User Code??? (Invalid Object Name 'Access Table')

Jul 6, 2006

Hello,I am getting a SqlException with title "SqlException was unhandled by user code" and then it says "Invalid object name 'Access Table'.here is my code (this is from my login page:)<script runat="server">

Protected Sub Login1_Authenticate(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.AuthenticateEventArgs)

Dim conn As SqlConnection
Dim cmd As SqlCommand
Dim cmdString As String = "SELECT [Password] FROM [AccessTable] WHERE" & _
" (([Username] = @Username) AND ([Password] = @Password))"

conn = New SqlConnection("Data Source=GDB03SQL;Initial Catalog=GDBRemitance;Persist Security Info=True;User ID=remitance;Password=remitance")
cmd = New SqlCommand(cmdString, conn)
cmd.Parameters.Add("@Username", SqlDbType.VarChar, 50)
cmd.Parameters("@Username").Value = Me.Login1.UserName
cmd.Parameters.Add("@Password", SqlDbType.VarChar, 50)
cmd.Parameters("@Password").Value = Me.Login1.Password
conn.Open()
Dim myReader As SqlDataReader
myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
If myReader.Read() Then
FormsAuthentication.RedirectFromLoginPage(Me.Login1.UserName, False)
Else
Response.Write("Invalid credentials")
End If
myReader.Close()

End Sub
</script>  The error is comming from the myReader = cmd.Execute(CommandBehavior.CloseConnection)Thanks for any suggestions or ideas.

View 2 Replies View Related

SQL Server 2005 - Studio Express Vs. Visual Studio 2005 Install

May 5, 2006

I'm very confused.  I installed Visual Studio 2005 and thought I understood that SQL Server 2005 came with it, but it appears that it's SQL Server 2005 - Express.  Can anyone tell me what I need to do in order to get Data Transformation Services loaded or the equivalent of DTS in SQL Express? 
 
 

View 1 Replies View Related

SQL Server Management Studio Express:Cannot Access Destination Table ‘dbo.FromExcel’in SqlConnection-VBExpress Programming(P.1)

Oct 18, 2007

Hi all,
In the Object Explorer of my SQL Server 2005 Management Studio Express, I do not have €˜Northwind€™ Database installed yet. I executed the following source code (that was copied from a book) in my VB 2005 Express:
/////////////////////----Form9.vb----//////////////////////////
Imports 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
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
///////////////////////////////////////////////////////////////////////
This is Part 1 (The length of input exceeds 50000 characters). Part 2 will be posted in this site shortly.

View 4 Replies View Related

Visual Studio

Jun 17, 2008

Hi,

I am new to this environment, Can Someone help me in how to write the SSIS Packages using the Visual Studio in STEPS or using any links.

Thanks in Advance.

View 2 Replies View Related

DMX Vs Visual Studio

Dec 22, 2006

Hi



Are there any (important?) advantages of using data mining through DMX instead of Visual Studio 2005 on the SQL server 2005?





/Dennis

View 1 Replies View Related

Database In Visual Studio

Jan 13, 2007

When I try to create a database that will have fields for a date and a time the only data type option in visual studio is datetime. This stores data as dd/mm/yyyy-00.00.

how can I set it to store either a date dd/mm/yyy or a time 00.00 but not both.

As it is now I cant seem to store a time.

Thanks

jim

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved