0

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()

4
Contributors
11
Replies
17
Views
8 Years
Discussion Span
Last Post by __avd
0

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.

0

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

0

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

0

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

0

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?

0

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

0

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

0

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()
0

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

0

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)
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.