updating a database

updating a database

• Paul Crowder

  Can you send a simple code example...
  
  

• werner5

  Johan Stenberg wrote:

  Since you are using SQL express, you may have encountered the problem described in "Where is my data - Understanding the file copy for desktop projects" section in this blog: http://blogs.msdn.com/smartclientdata/archive/category/10356.aspx Best regards, Johan Stenberg

  
  
  Wow thanks, I literally spent hours in my ado.net 1.1 book trying to figure it out!
  
  

• Andy Cutler

  spotty wrote:

  VB Express beta 2 allows updating database without a problem with a couple of caveats. Microsoft Access or SQL Express are the two supported databases out of the box for express editions.   I'm not sure which database you are trying to update. All versions of visual basic support database access - the standard edition and above will support more database formats out of the box such as ODBC and oracle providers. There are numerous samples of using the data binding capability to update databases using VB Express Beta 2.    Do you have specific errors that are occuring when you are trying to update a database, what type of database are you trying to update.

  
  
  Spotty, I'm having similar problems; I'm running VB.net express beta 2 with ms sql express CTP. I'm trying to insert data into a database by using code only. Why Because I'm going to be adding hundreds of thousands of records. I've been reading some books on ado.net but it seems ado.net 1.1 doesn't work in 2.0 as with the express editions. Reading from 1.1 books, it seems I can connect to a db using the System.Data.SqlConnection class to connect to a ms sql db and then I can hardcode in sql queries using the System.Data.SqlCommand class or connect the SqlCommand class to a DataSet using a tableAdapter (dataAdapter in 1.1). But when I try to copy the EXACT code from my book it doesn't work in 2.0. I thought 1.1 ado.net code was suppose to work in 2.0 Help plz!
  
  Nick
  
  

• Paul E.

  Since you are using SQL express, you may have encountered the problem described in "Where is my data - Understanding the file copy for desktop projects" section in this blog:
  
  http://blogs.msdn.com/smartclientdata/archive/category/10356.aspx
  
  Best regards,
  Johan Stenberg
  
  

• DavP

  Also another relatively common mistake for not getting the updates back into the database is that the DataAdapter Insert/Update/Delete commands are either incorrect or not entered.
  
  Sure you can add me - but if you enter questions into the forums then everyone can benefit and you may get a quicker response.   The VB Forum is a good place to enter questions.
  
  
  

• bchase

  I actually got it to work last night, but thanks anyways. I also put the connection string into the app settings and just used
  
  

  My.Settings.SQLExpressConStr

  
  
  
  to reduce typing and errors since there's a wizard to connect to the db.
  
  It seems you're very knowledgable about DB, would you mind if I added you to my AIM list so that I may ask a question or two when I need it
  
  

  
  
  

• DaveGoliath

  Thank you a ton for that link, I worked on figuring this out for two days and was completely confused.
  
  Barrett
  
  

• .net_Coder

  VB Express beta 2 allows updating database without a problem with a couple of caveats.
  
  Microsoft Access or SQL Express are the two supported databases out of the box for express editions.   I'm not sure which database you are trying to update.
  
  All versions of visual basic support database access - the standard edition and above will support more database formats out of the box such as ODBC and oracle providers.
  
  There are numerous samples of using the data binding capability to update databases using VB Express Beta 2.    Do you have specific errors that are occuring when you are trying to update a database, what type of database are you trying to update.
  
  

  
  

• mohan kalyan

  If you right click on the Appropriate TableAdapter on the form or click on the smart tag you will get the option "Edit Queries in Dataset Designer"
  
  This will bring up a daigram of the dataset.
  
  If you select the dataset and use Configure...   it allows you to configure the
  Select, Insert, Update and Delete statements.
  
  Depending on which ones you select (Example you may want to make the dataset ReadOnly and therefore wouldn't specific the insert/delete or update items).
  
  This will create underlying sql commands which you can see by Right Clicking the TablesAdapter section at the bottom of the dataset and viewing the properties.
  
  This will show such properties as SelectCommand, UpdateCommand, DeleteCommand and InsertCommand depending on how you configured the above.
  
  You can then look at the specific SQL that each of these will run.
  
  

• John Morris

  spotty wrote:

  Here's some code that should connect to a SQL Express server called "xyz\SQLexpress" and update the database "DZManifest" and insert a record into a tables called BusinessCode. Try Dim MyConnection As New SqlClient.SqlConnection("server=xyz\sqlexpress;Database=DZManifest;Trusted_Connection=true") Dim mycommand As New SqlClient.SqlCommand("Insert Into BusinessCode (CodeType, Value, Description, Status) VALUES (0,2,'fred1',1)", MyConnection) MyConnection.Open() mycommand.ExecuteNonQuery() MyConnection.Close() Catch ex As Exception    MessageBox.Show(ex.Message) End Try   Try to identify if there is an exception being raised that is letting you know why it can't update the database - which is they try ... Catch... End Try block with a messagebox displaying the exception message if there is a problem.

  
  
  I got that code up there to work BUT it doesn't save into the actually database once I close the program What am I doing wrong
  
  

• greyhound75635

  Here's some code that should connect to a SQL Express server called "xyz\SQLexpress" and update the database "DZManifest" and insert a record into a tables called BusinessCode.
  
  

  Try Dim MyConnection As New SqlClient.SqlConnection("server=xyz\sqlexpress;Database=DZManifest;Trusted_Connection=true") Dim mycommand As New SqlClient.SqlCommand("Insert Into BusinessCode (CodeType, Value, Description, Status) VALUES (0,2,'fred1',1)", MyConnection) MyConnection.Open() mycommand.ExecuteNonQuery() MyConnection.Close() Catch ex As Exception    MessageBox.Show(ex.Message) End Try

  
  
  Try to identify if there is an exception being raised that is letting you know why it can't update the database - which is they try ... Catch... End Try block with a messagebox displaying the exception message if there is a problem. 
  
  
  
  

  
  

• djflanger

  Could you elaborate more on what you mean "that the DataAdapter Insert/Update/Delete commands are either incorrect or not entered"
  
  How could I check on this and how could I fix it
  
  
  
• updating a database