1 Replies - 5499 Views - Last Post: 10 January 2010 - 10:53 AM Rate Topic: -----

#1 aklo  Icon User is offline

  • D.I.C Head

Reputation: 18
  • View blog
  • Posts: 229
  • Joined: 23-January 09

Insert data into database

Posted 10 January 2010 - 08:15 AM

I'm using visual studio 2005 to develop a mobile application.
The database i'm using is sql compact edition.

I know in normal sql like mysql, ms sql i can do a multiple insert of data but in
sql ce it don't work!

I know of a long way that is to just create a createVendor1, createVendor2 as many time as i need to insert the data..but i don't think this is the right way to do it.

this is what i have currently, if i remove the 2nd insert , it works.
NOTE: There is a semi colon at the end of the insert but that is not the problem as removing it also doesn't work.

  Private Sub FormOrder_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
		Dim createVendor As SqlCeCommand = connection.CreateCommand()
		createVendor.CommandText = _
		"INSERT INTO Vendor(VendorName) VALUES('F & N');" & _
		"INSERT INTO Vendor(VendorName) VALUES('Coka-Cola')"
		connection.Open()
		createVendor.ExecuteNonQuery()


	End Sub


I also like to know how to write a function in visual studio 2005
my idea is to use a function if possible to do the above...i've now create more insert statement...as you can see , it is extremely bloated.



  If Not dr.Read Then
			'Insert values into vendor table.
			Dim createVendor As SqlCeCommand = connection.CreateCommand()
			createVendor.CommandText = _
			"INSERT INTO Vendor(VendorName) VALUES('F & N')"

			Dim createVendor2 As SqlCeCommand = connection.CreateCommand()
			createVendor2.CommandText = _
			"INSERT INTO Vendor(VendorName) VALUES('Coka-Cola')"

			createVendor.ExecuteNonQuery()
			createVendor2.ExecuteNonQuery()

			'Insert values into product table
			Dim insertProduct As SqlCeCommand = connection.CreateCommand()
			insertProduct.CommandText = _
				"INSERT INTO Product(ProductName, VendorName, UnitPrice) VALUES('Miranda Orange','F & N', 2)"

			Dim insertProduct2 As SqlCeCommand = connection.CreateCommand()
			insertProduct2.CommandText = _
				"INSERT INTO Product(ProductName, VendorName, UnitPrice) VALUES('Snapple','F & N', 5)"


			Dim insertProduct3 As SqlCeCommand = connection.CreateCommand()
			insertProduct3.CommandText = _
				"INSERT INTO Product(ProductName, VendorName, UnitPrice) VALUES('Normal Coke','Coka-Cola', 1)"

			Dim insertProduct4 As SqlCeCommand = connection.CreateCommand()
			insertProduct4.CommandText = _
				"INSERT INTO Product(ProductName, VendorName, UnitPrice) VALUES('Diet Coke','Coka-Cola', 3)"

			insertProduct.ExecuteNonQuery()
			insertProduct2.ExecuteNonQuery()
			insertProduct3.ExecuteNonQuery()
			insertProduct4.ExecuteNonQuery()



		End If

This post has been edited by aklo: 10 January 2010 - 10:34 AM


Is This A Good Question/Topic? 0
  • +

Replies To: Insert data into database

#2 Martyr2  Icon User is offline

  • Programming Theoretician
  • member icon

Reputation: 4332
  • View blog
  • Posts: 12,127
  • Joined: 18-April 07

Re: Insert data into database

Posted 10 January 2010 - 10:53 AM

From what I understand SQL CE just doesn't support multiple statements in a single call like its big brother SQL Server. But you should still be able to simply change the commandText property to your additional insert statement and then call ExecuteNonQuery() again. You just have to do it one at a time when using CE. Other providers like Access don't allow this either. My guess is that only the big vendors will including SQL Server, MySQL and Oracle.

So yeah, one statement at a time. What do you expect? You are using a mini, scaled-back, version of a database. :)
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1