Problem in inserting values in table through form

• • Reply

  14 years ago

  by aleeza

  layzleez

  , Palau

  Joined 14 years ago

  Hi,

  I am facing problem in this piece of code.

  Actually this is the coding of a form which is used to add new book in database.These fields are going to add in the book table.

  like BOOK ID , BOOK TITLE , AUTHOR NAME nad so on

  But whenever I run this code .It gives error on line cmd.ExecuteNonQuery();

  There are two possible reasons for this error  thet either my connection string is wrong or my Insert query is wrong.

  But  with the same conn string I ve run other forms and it didnt give any error.

  And i ve also checked my insert query in SQL. It ran perfectly alright.

  Please help me out in finding out error ion this code.

  *************************************************************

  private

  void btnAddBook_Click(object sender, System.EventArgs e)

  {

  string strBookId=txtBookId.Text;

  string strTitle =txtBookTitle.Text;

  string strAuthor_Id1=txtAuthorId1.Text;

  string strAuthor_Id2=txtAuthorId2.Text;

  string strEdition=txtBookEdition.Text;

  string strCategory=txtBookCategory.Text;

  string strQuery = "Insert into BOOK values("+ strBookId + ", '" + strTitle + "' ," + strAuthor_Id1 + " , "+ strAuthor_Id2 + " ,' "+strEdition +" ' , ' "+strCategory+ " ') ";

  OleDbConnection conn =

  new OleDbConnection("Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=LMS;Data Source=abs");

  conn.Open();

  OleDbCommand cmd =

  new OleDbCommand(strQuery,conn);

  ERROR=============>   cmd.ExecuteNonQuery();

  conn.Close();

  }

  **********************************************************

  waiting for your reply

  thanks

  Report abuse
• • Reply

  14 years ago

  by konikula

  Matej Kasper

  Trutnov, Czech Republic

  Joined 15 years ago

  did you try INSERT INTO book (bookid,strtitle...) VALUES (...)
  hope it helps
  
  
  

  Report abuse
• • Reply

  14 years ago

  by konikula

  Matej Kasper

  Trutnov, Czech Republic

  Joined 15 years ago

  anyway, I am using ExecuteReader for all types of command
  
  

  Report abuse
• • Reply

  14 years ago

  by aleeza

  layzleez

  , Palau

  Joined 14 years ago

  Hi,

  will you please tell me what is Execute Reader?

  Thanks for reply

  Report abuse
• • Reply

  14 years ago

  by Suresh.G.pandu

  Suresh.G

  Hyderabad, India

  Joined 15 years ago

  Hi aleeza,

  The code & insert query  you wrote both are correct,Once check ur database fields type and the type of  data you are inserting in to database as well as if u r passing any null values in it

  Report abuse
• • Reply

  14 years ago

  by a_eqap

  Ahmad Eqap

  , Jordan

  Joined 14 years ago

  The problem in insert statement, it should have both destination column and value.  The general structure is as below.
  
  INSERT INTO Book
                        (col(1)Name, col(2)Name... col(N)Name)
  VALUES      (col(1)Value,col(2)Value,...  col(N)Value).
  
  Ex: Let us suppose that we have Table Book with the following fields
  Table_Book(Book_Id, Book_ISBN, Book_Auth),
  
  
  
  
  
  
  

  
  
  

  ur statement should be :
  
  
  
  

  INSERT INTO Book
                        (Book_Id,Book_ISBN,Book_Auth)
  VALUES      (123,12,'Mike')
  
  
  The rest of ur code looks fine for me. I hope that I solved ur problem
  
  
  
  
  

  
  
  
  
  
  Report abuse
• • Reply

  14 years ago

  by aleeza

  layzleez

  , Palau

  Joined 14 years ago

  Hi ,

  Thanks a lot to all of you , who have given reply to my problem.

  My problem is no more a problem now.

  Thanks once again.

  Report abuse
• • Reply

  14 years ago

  by konikula

  Matej Kasper

  Trutnov, Czech Republic

  Joined 15 years ago
  ExecuteReader is query execution if you want to read returned data e.g. on SELECT ..
  here sample
  
  
  

   1    Private Sub Execute(ByVal query As String)
   2      If Not Connected Then Exit Sub
   3      SBButtons.Text = "Executing command"
   4      Dim i, x, y, Rownum, ColCount As Integer, item As String
   5      Rownum = 0
   6      For i = 0 To 100
   7        MaxSize(i) = 0
   8      Next
   9
  10      On Error GoTo Err
  11      Dim Command As New Odbc.OdbcCommand(query, Connection)
  12      Dim fdRead As Odbc.OdbcDataReader = Command.ExecuteReader(CommandBehavior.CloseConnection)
  13
  14      DataTable.Rows.Clear()
  15      DataTable.Columns.Clear()
  16
  17      ColCount = fdRead.FieldCount
  18      If ColCount > 0 Then
  19        If ColCount > cols Then
  20          cols = ColCount
  21        End If
  22        If fdRead.RecordsAffected > rows Then
  23          rows = fdRead.RecordsAffected
  24        End If
  25        ReDim Table(cols, rows)
  26
  27        'Adding columns in exact count to datagrid
  28        For x = 0 To ColCount - 1
  29          DataTable.Columns.Add("Column" & Trim(Str(x)), "")
  30        Next
  31
  32        'Reading query returned values to field and counting lines
  33        While fdRead.Read
  34          Rownum += 1
  35          For x = 0 To ColCount - 1
  36            Table(x, Rownum - 1) = fdRead.GetValue(x).ToString
  37          Next
  38        End While
  39
  40        'Adding rows to datagrid
  41        For y = 0 To Rownum
  42          DataTable.Rows.Add()
  43        Next
  44
  45        'Naming of columns
  46        For x = 0 To ColCount - 1
  47          DataTable.Columns.Item(x).HeaderText = fdRead.GetName(x)
  48        Next
  49
  50        'Filling cells with values from field
  51        For y = 0 To Rownum
  52          For x = 0 To ColCount - 1
  53            item = Table(x, y)
  54            DataTable.Rows.Item(y).Cells.Item(x).Value = item
  55            If Len(item) > MaxSize(x) Then MaxSize(x) = Len(item)
  56          Next
  57        Next
  58
  59        'Setting autosize
  60        For x = 0 To ColCount - 1
  61          If MaxSize(x) < 50 Then DataTable.Columns.Item(x).AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
  62          DataTable.Columns.Item(x).Resizable = DataGridViewTriState.True
  63        Next
  64      End If
  65      SBButtons.Text = "Command executed: " & fdRead.RecordsAffected & " rows affected"
  66      Exit Sub
  67  Err:
  68      MsgBox(Err.Description, MsgBoxStyle.Critical, "ERROR executing query - My MySql Browser ERROR")
  69      If Not Connection.State = ConnectionState.Open Then Call Reconnect()
  70    End Sub
  
  
  
  
  
  
  
  
  
  
  

  
  
  
  
  
  Report abuse