How to insert data (probably simple question)

How to insert data (probably simple question)

• Vijay R

  interesting. Have you tried to insert data using the SQL classes and seeing if that works Try this:

  Friend Sub Datainsert(ByVal Name As String, ByVal Surname As String, ByVal DateOfGame As Date)
          Dim ConnectMe As New SqlClient.SqlConnection
         

          ConnectMe.ConnectionString = "Server=localhost;Database=ProjektKoncowy;Trusted_Connection=yes;"
          Dim Writeit As New SqlClient.SqlCommand("INSERT INTO ProjektKoncowy.dbo.Wyniki (Name, Surname, Points, Status, DateOfGame, KindOfGame, Autor) VALUES (Name, Surname , ' 3 ', ' 1 ', DateOfGame ,' 4 ','John Smith')", ConnectMe)
          Writeit.Connection.Open()
          Writeit.ExecuteNonQuery()
          Writeit.Connection.Close()
  End Sub
  

   

  do you still get the same error If so, for both and for SqlClient/command, can you post the stacktrace/innerexception of the error

  
  
  

• anu_ooo

  interesting - I've never worked with SQL OLEDB. However using your code, I am unable to create a correct OleDbConnection object, it gives me "An Ole Db provider was not specified in the connectionstring"

  however everything was great, including inserting, using this:

  Dim theConnectionString as String= "Provider=SQLOLEDB;Data Source=localhost;Initial Catalog=databaseName;Trusted_Connection=yes;"

  Dim theOleDbConnection as new OleDbConnection(theConnectionString)

  Dim theOleDbCommand as new OleDbCommand("INSERT INTO [tableName] (Name, Surname, Points, Status, DateOfGame, KindOfGame, Author) VALUES ('bob', 'bloggs', '3', '1', '2006/09/25', '4', 'John Smith"), theOleDbCommand) 'just providing my own values here

  theOleDbCommand.Connection.Open()

  theOleDbCommand.ExecuteNonQuery()

  theOleDbCommand.Connection.Close()

  I think perhaps, but could be very well wrong, it's something to do with your connection string - the correct connection string in .NET would be the one I have supplied here. Try it, see what happens

  
  
  

• airwalker2000

  Gaaaaaaaaaaaaawd....that's ugly
  

• simon_

  OKay lets try this:

  Private Sub TestOLEDB()

  Dim MyConn As Odbc.OdbcConnection

  Dim MyCMD As Odbc.OdbcCommand

  Dim MyTable As DataTable

  'Dim MYConnection As String = "Provider=SQLOLEDB;Data Source=01isdsh27438;Initial Catalog=jcc;Integrated Security=SSPI;"

  Dim MyConnection As String = "DRIVER=SQL Server;Server=YOURSERVERNAME;Database=YOURDATABASE;Trusted_Connection=yes;"

  Try

  MyConn = New Odbc.OdbcConnection(MYConnection)

  MyConn.Open()

  MyCMD = New Odbc.OdbcCommand

  With MyCMD

  .Connection = MyConn

  .CommandText = "Select * From YOURTABLENAME"

  .CommandType = CommandType.Text

  End With

  MyTable = New DataTable

  MyTable.Load(MyCMD.ExecuteReader)

  MyConn.Close()

  Me.DataGridView1.DataSource = MyTable

  Catch ex As Exception

  Throw ex

  End Try

  End Sub

  
  

• Myke1

  I created DSN (tested succesfully), then used it as you told me. This time an error occurs when I try to connect to database ("Wpisz.Connection.Open()"). I'll try to acquire some more info using inner exception.
  
  

• Tduffy77

  Really don't know here. Make sure that the date field value given is in the correct Date format. Would be nice if you can post the inner exception of the Exception itself as it would contain more detailed information usually. The output debug Window is fine, its normal to show those information really.
  
  

• Cade Roux

  For now I try to use Odbc again (I have to) and my code is as follows:
  
  Friend Sub Zapiswygranej(ByVal Imie As String, ByVal Nazwisko As String, ByVal DataGry As Date)
  Dim Polacz As New Odbc.OdbcConnection
  Dim aConnectionString As String = "Driver={SQL Native Client};Server=localhost;Database=ProjektKoncowy;Trusted_Connection=yes;"
  Dim Wpisz As New Odbc.OdbcCommand("INSERT INTO ProjektKoncowy.dbo.Wyniki (Imie, Nazwisko, Punkty, Status, DataGry, RodzajGry, Autor) VALUES (Imie, Nazwisko , ' 3 ', ' 1 ', DataGry ,' 4 ','Moje dane')", Polacz)
  Wpisz.Connection = Polacz
  Polacz.ConnectionString = aConnectionString
  Wpisz.Connection.Open()
  Wpisz.ExecuteNonQuery()
  Polacz.Close()
  End Sub
  
  Sorry for using polish words as the names of variables, I hope it's meaningless.
  The result is the same as in my first post.
  
  I "Call Stack" window I found this (last two lines):
  
  system.data.dll!System.Data.Odbc.OdbcCommand.ExecuteNonQuery() + 0xad bytes
  
  Kolkoikrzyzyk.exe!Kolkoikrzyzyk.zapisdobazy.Zapiswygranej(String Imie = "Gracz", String Nazwisko = "Bez Nazwiska", Date DataGry = #10/5/2006#) Line 9 + 0xa bytes Basic
  
  I do not know but something is missing I think, where are the rest of my variables which I want to insert, for example 3,1,Moje dane. Could there be the source of the problem
  
  Output-Debug window:
  'DefaultDomain': Loaded 'c:\windows\microsoft.net\framework\v1.1.4322\mscorlib.dll', No symbols loaded.
  'Kolkoikrzyzyk': Loaded 'F:\Moje projekty VB\Ko ko i krzy yk\Kolkoikrzyzyk\bin\Kolkoikrzyzyk.exe', Symbols loaded.
  'Kolkoikrzyzyk.exe': Loaded 'c:\windows\assembly\gac\system.windows.forms\1.0.5000.0__b77a5c561934e089\system.windows.forms.dll', No symbols loaded.
  'Kolkoikrzyzyk.exe': Loaded 'c:\windows\assembly\gac\system\1.0.5000.0__b77a5c561934e089\system.dll', No symbols loaded.
  'Kolkoikrzyzyk.exe': Loaded 'c:\windows\assembly\gac\system.drawing\1.0.5000.0__b03f5f7f11d50a3a\system.drawing.dll', No symbols loaded.
  'Kolkoikrzyzyk.exe': Loaded 'c:\windows\assembly\gac\microsoft.visualbasic\7.0.5000.0__b03f5f7f11d50a3a\microsoft.visualbasic.dll', No symbols loaded.
  'Kolkoikrzyzyk.exe': Loaded 'c:\windows\assembly\gac\system.data\1.0.5000.0__b77a5c561934e089\system.data.dll', No symbols loaded.
  An unhandled exception of type 'System.Data.Odbc.OdbcException' occurred in system.data.dll
  Additional information: System error.
  
  The longer I think the less i know.
  
  

• AamirKhurshid

  I,m sorry for not responding so long, health problems. I tried what you suggested in your posts and:
  1. An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in system.data.dll
  Additional information: System error.
  2. An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll
  Thanks once more for trying to help me, but thus I got to create Odbc connection for my exercise. Hmmm... I slowly become desperate, that's the last thing I got to do to finish whole program.
  
  

• RobertoPC

  ibrahim - the user actually needs to use ODBC connection and has problems with insert statements
  
  

• tackett

  Hello Elgor

  Perhaps you would do best to first get a simple conection to your database using a select statement.  then go on to building your update statement

   

   

  Try something like this:

  Private Sub TestOLEDB()

   

  Dim MyConn As OleDb.OleDbConnection

  Dim MyCMD As OleDb.OleDbCommand

  Dim MyTable As DataTable

  Dim MYConnection As String = "Provider=SQLOLEDB;Data Source=YOURSERVERNAME;Initial Catalog=YOURDATABASENAME;Integrated Security=SSPI;"

  Try

  MyConn = New OleDb.OleDbConnection(MYConnection)

  MyConn.Open()

  MyCMD = New OleDb.OleDbCommand

   

  With MyCMD

  .Connection = MyConn

  .CommandText = "Select * From YOURTABLENAME"

  .CommandType = CommandType.Text

  End With

  MyTable = New DataTable

  MyTable.Load(MyCMD.ExecuteReader)

  MyConn.Close()

  Me.DataGridView1.DataSource = MyTable

  Catch ex As Exception

  Throw ex

  End Try

  End Sub

   

  
  

• Yachtsman

  ok cool. I will see what I can find...since I've never connected to SQL Server using ODBC....ill be sure to post something if I find it.

  The command however looks fine to me.

  
  
  

• Pooja Katiyar

  Sorry, I haven't read this entire thread, but what I notice right off in the original post is: you're using ODBC but trying to set a connection string...

  The ODBC object in Windows should already be configured with all the appropriate connection info. The ODBCConnection object in code only needs to know what connection you want to use. Typically this connection string looks something like:

  "DSN=ODBCConnectionName" Where DSN equals the name of the connection you created in the ODBC Data Source Administrator.

  You can add Username (UID=) and password (PWD=) to the string if necessary.

  Changing the connection string should fix the problem.

  
  
  

• guilherme mendes

  system error hmmm doesn't help.... lol. Any chance of posting the entire exception including innerException when you get this error

  can you post the current code you have

  
  
  

• Aleniko29139

  I haven't tried anything else. This is something like homework and it has to be "odbc". Thanks for your help, I'll try to do something with it tomorrow, it's too late today (23:01).
  
  
• How to insert data (probably simple question)