6 Replies - 35810 Views - Last Post: 23 March 2008 - 04:59 PM Rate Topic: -----

#1 Hanzie  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 128
  • Joined: 19-August 07

Add new DataROW with values from datase-table

Post icon  Posted 23 March 2008 - 02:13 PM

Hello, iīm trying to add a new row to my database-table "Datumset".

I have the folowing code:
	Private Sub BtnDatumOpslaan_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnDatumOpslaan.Click

		'Create your query as you already have done  
		Dim strsql As String = "SELECT Tekeningen.BedrijvenID, Tekeningen.Bladnummer, Tekeningen.Naam, Tekeningen.TekeningId, max(Datums.Datum) AS Datum FROM Tekeningen INNER JOIN Datums ON Tekeningen.TekeningID=Datums.TekeningID WHERE Tekeningen.TekeningID=@TekeningID GROUP BY Tekeningen.BedrijvenID, Tekeningen.Bladnummer, Tekeningen.Naam, Tekeningen.TekeningID ORDER BY Bladnummer"
		'Get your connection string (You've done this right)  
		Dim strconnectionstring As String = My.Settings.DocRegDataConnectionString
		'Create your SqlConnection (Done)  
		Dim objconnection As New SqlConnection(strconnectionstring)
		objconnection.Open()
		'Create your SqlCommand (done)  
		Dim objcommandDatum As New SqlCommand()

		Dim TekenID As Integer
		TekenID = NaamComboBox.SelectedValue

		'**Set your command properties**  
		With objcommandDatum
			.CommandText = strsql
			.CommandType = CommandType.Text
			.Parameters.AddWithValue("@TekeningID", TekenID)
			.Connection = objconnection
		End With

		'Create a new SqlDataAdapter 
		Dim objdataAdapter As New SqlDataAdapter
		objdataAdapter.SelectCommand = objcommandDatum

		'Create a new DataSet  
		Dim objDataSetDatums As New DataSet

		'Fill the DataSet using the SqlDataAdapter  
		objdataAdapter.Fill(objDataSetDatums, "Datumset")

		objconnection.Close()

		Dim bindingsourcedatum As New BindingSource
		bindingsourcedatum.DataSource = objDataSetDatums.Tables("Datumset")

		DataGridView1.AutoGenerateColumns = True
		DataGridView1.DataSource = bindingsourcedatum

		'create and fill datarow
		Dim objdatarow As DataRow
		objdatarow = objDataSetDatums.Tables("Datumset").NewRow
		objdatarow("BedrijvenID") = objDataSetDatums.Tables("Datumset").Columns("BedrijvenID")
		objdatarow("TekeningID") = objDataSetDatums.Tables("Datumset").Columns("TekeningID")
		objdatarow("Naam") = objDataSetDatums.Tables("Datumset").Columns("Naam")
		objdatarow("Bladnummer") = objDataSetDatums.Tables("Datumset").Columns("Bladnummer")
		objdatarow("Datum") = DatumPickerBestaand.Value

		'add datarow
		objDataSetDatums.Tables("Datumset").Rows.Add(objdatarow)

		'update dataset-table
		objdataAdapter.Update(objDataSetDatums.Tables("Datumset"))



But i get the folowing error:

Unable to cast object of type 'System.Data.DataColumn' to type 'System.IConvertible'.Couldn't store <BedrijvenID> in BedrijvenID Column. Expected type is Int32.

What does this mean?
How can i convert my selected value in the column "BedrijvenID" to integer.
The value is an integer in my database!

PLEASE HELP

Is This A Good Question/Topic? 0
  • +

Replies To: Add new DataROW with values from datase-table

#2 PsychoCoder  Icon User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1641
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: Add new DataROW with values from datase-table

Posted 23 March 2008 - 02:46 PM

IN this block of code


objdatarow = objDataSetDatums.Tables("Datumset").NewRow
objdatarow("BedrijvenID") = objDataSetDatums.Tables("Datumset").Columns("BedrijvenID")
objdatarow("TekeningID") = objDataSetDatums.Tables("Datumset").Columns("TekeningID")
objdatarow("Naam") = objDataSetDatums.Tables("Datumset").Columns("Naam")
objdatarow("Bladnummer") = objDataSetDatums.Tables("Datumset").Columns("Bladnummer")
objdatarow("Datum") = DatumPickerBestaand.Value




You're actually trying to add the column itself because you havent specified you want the columns value. Change it to


objdatarow = objDataSetDatums.Tables("Datumset").NewRow
objdatarow("BedrijvenID") = objDataSetDatums.Tables("Datumset").Columns("BedrijvenID").ToString()
objdatarow("TekeningID") = objDataSetDatums.Tables("Datumset").Columns("TekeningID").ToString()
objdatarow("Naam") = objDataSetDatums.Tables("Datumset").Columns("Naam").ToString()
objdatarow("Bladnummer") = objDataSetDatums.Tables("Datumset").Columns("Bladnummer").ToString()
objdatarow("Datum") = DatumPickerBestaand.Value




Adding ToString() tells your code you want that columns value, not the column itself
Was This Post Helpful? 0
  • +
  • -

#3 Hanzie  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 128
  • Joined: 19-August 07

Re: Add new DataROW with values from datase-table

Posted 23 March 2008 - 03:03 PM

Thnx for the reply.

Iīm a big step further now!

I also noticed that I didnīt loaded the updatecommand with sqlcommandbuilder. I added it now!
When I now type (add) just some values for the new column values they a getting added to a new row judt fine!!
So this works!

But when I wanna add these values:

objdatarow("BedrijvenID") = objDataSetDatums.Tables("Datumset").Columns("BedrijvenID").ToString()
		objdatarow("TekeningID") = objDataSetDatums.Tables("Datumset").Columns("TekeningID").ToString()



I get the folowwing error:

Input string was not in a correct format.Couldn't store <BedrijvenID> in BedrijvenID Column. Expected type is Int32.

Any help?
Was This Post Helpful? 0
  • +
  • -

#4 Hanzie  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 128
  • Joined: 19-August 07

Re: Add new DataROW with values from datase-table

Posted 23 March 2008 - 03:12 PM

Oh, no!

I noticed that the columnsnames are added to my database, not the values in the row.

I have a column "Naam".
I want the rowvalue selected with a selectquery from this column, not the columnname?!

What am i doing wrong?
Was This Post Helpful? 0
  • +
  • -

#5 PsychoCoder  Icon User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1641
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: Add new DataROW with values from datase-table

Posted 23 March 2008 - 03:12 PM

You need to convert it to an Integer, as it is now it's a string


objdatarow("BedrijvenID") = CType(objDataSetDatums.Tables("Datumset").Columns("BedrijvenID").ToString(),Integer)
objdatarow("TekeningID") = objDataSetDatums.Tables("Datumset").Columns("TekeningID").ToString()


Was This Post Helpful? 0
  • +
  • -

#6 Hanzie  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 128
  • Joined: 19-August 07

Re: Add new DataROW with values from datase-table

Posted 23 March 2008 - 03:22 PM

When i want to convert the string to integer i get the folowng error:

Conversion from string "BedrijvenID" to type 'Integer' is not valid.

I believe this is logic because you just canīt convert the text "BedrijvenID" to an integer, the text is not a number!!!

But as posted before this reply, I donīt wanna get the columnname as value but the value in the row within this column.

Any help?
Was This Post Helpful? 0
  • +
  • -

#7 Hanzie  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 128
  • Joined: 19-August 07

Re: Add new DataROW with values from datase-table

Posted 23 March 2008 - 04:59 PM

Hello, i solved the problem of getting the columnnametext instead of the columnvalue in the row.

Ļ
'create and fill datarow
		Dim objdatarow As DataRow
		objdatarow = objDataSetDatums.Tables("Datumset").NewRow()
		objdatarow("BedrijvenID") = objDataSetDatums.Tables("Datumset").Rows(0).Item("BedrijvenID")
		objdatarow("TekeningID") = objDataSetDatums.Tables("Datumset").Rows(0).Item("TekeningID")
		objdatarow("Naam") = objDataSetDatums.Tables("Datumset").Rows(0).Item("Naam")
		objdatarow("Bladnummer") = objDataSetDatums.Tables("Datumset").Rows(0).Item("Bladnummer")
		objdatarow("Datum") = DatumPickerBestaand.Value

		'add datarow
		objDataSetDatums.Tables("Datumset").Rows.Add(objdatarow)

		'update dataset-table
		objdataAdapter.Update(objDataSetDatums.Tables("Datumset"))



But now i get the folowing error

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Tekeningen_Bedrijven". The conflict occurred in database "DocRegData", table "dbo.Bedrijven", column 'BedrijvenID'.
The statement has been terminated.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1