Dim oledbcom As New OleDb.OleDbCommand

oledbcom.CommandText = "Select ID from Customer where Company = ?"

oledbcom.Connection = oledbcon

Dim oledbparam As OleDb.OleDbParameter = _
oledbcom.Parameters.Add("@Company", OleDb.OleDbType.VarChar, 50)
oledbparam.Value = frmNewCompany.txtCompName.Text.Trim

Dim oledbreader As OleDb.OleDbDataReader = oledbcom.ExecuteReader

Dim id As Integer

While oledbreader.Read
id = oledbreader.GetInt32(0)
End While
'end of fetch

'insert the colour name in colour table
Dim oledbcom1 As New OleDb.OleDbCommand

oledbcom1.CommandText = ("INSERT INTO Colour (ID, [Colour Name]) values (?,?)")
oledbcom1.Connection = oledbcon

Dim oledbparam1 As OleDb.OleDbParameter = _
oledbcom1.Parameters.Add("@ID", OleDb.OleDbType.VarChar)
oledbparam1.Value = id

oledbcom1.Parameters.Add("@Colour", OleDb.OleDbType.VarChar, 15)
oledbparam1.Value = txtColourName.Text.Trim

oledbcom1.ExecuteNonQuery()

First of all to retrieve the ID, I would not use DataReader but this

id = Convert.ToInt32(oledbcom.ExecuteScalar())

and for the rest of the code, make sure your connection is open.

oledbcom.executereader is working without any errors.
and m using it because i want the last value to be fetched which i dont think is possible with executescalar.
m getting this error at the last line
oledbcom1.executenonquery()
plz help

oledbcom.executereader is working without any errors.
and m using it because i want the last value to be fetched which i dont think is possible with executescalar.
m getting this error at the last line
oledbcom1.executenonquery()
plz help

That error will occur when you specify a parameter type which is diff than the field structure in the table.

in this line oledbcom1.Parameters.Add("@ID", OleDb.OleDbType.VarChar) check if VarChar is the correct type for ID which I doubt, and I guess it should be integer.

hth

In that case you right. and also check what samir said and post back if there was any enhanced.

babbu,

Use bb code tags. Source code must be surrounded with bb code tags.

Parameter identifier may be vary from one database to another database. Which database are you using?

m using access.
i m aware of the data type thing u mentioned.
but whn i set the datat type to integer it gives an error stating unable to conver from string datatype to integer

i also tried it using data table, data adapter and commanbuilder.
it says syntax error in INSERT INTO statement

Use bb code tag to wrap your source code.

Dim oledbcom As New OleDb.OleDbCommand
  oledbcom.CommandText = "Select ID from Customer where  
       Company = ?"
  oledbcom.Connection = oledbcon

  oledbcom.Parameters.Add("?", OleDb.OleDbType.VarChar, 50)
  oledbcom.Parameters(0).Value=txtCompName.Text
  
Dim oledbreader As OleDb.OleDbDataReader
 oledbcon.open()
 oledbreader = oledbcom.ExecuteReader
 Dim id As Integer

 While oledbreader.Read
       id = oledbreader.GetInt32(0)
  End While
  'Close dataread & connection
  oledbreader.close()
  oledbcon.close()

'insert the colour name in colour table
Dim oledbcom1 As New OleDb.OleDbCommand

oledbcom1.CommandText ="INSERT INTO Colour (ID, [Colour Name]) values (?,?)"
oledbcom1.Connection = oledbcon

oledbcom1.Parameters.Add("?", OleDb.OleDbType.VarChar,50)
oledbcom1.Parameters.Add("?", OleDb.OleDbType.VarChar,40)
oledbcom1.Parameters(0).Value = id
oledbcom1.Parameters(1).Value = txtColourName.Text.Trim

oledbcon.open()
oledbcom1.ExecuteNonQuery() 
oledbcon.close()

thnx a lot. tht worked.
but for knowledge sake can u plz tell me why the data adapter did not work.

heres the code

m1_rowPosition = m1_dtCustomer.Rows.Count
        Dim drNewRow As DataRow = m_dtColour.NewRow()
        drNewRow("ID") = m1_rowPosition
        drNewRow("Colour Name") = txtColourName.Text.Trim
        m_dtColour.Rows.Add(drnewrow)
        m_daDataAdapter.Update(m_dtColour)

Your have to implement InsertCommand, UpdateCommand, DeleteCommand, and Select command.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.