dataset error

  • Thread starter Thread starter One Handed Man
  • Start date Start date
O

One Handed Man

Its telling you that your dataDapter update command string is missing or
invalid. Check this

Here's one I cooked earlier

'setup People Update Command

peopleUpdateCmd.CommandText = "UPDATE People SET Address1 = ?, Address2 = ?,
Address3 = ?, Address4 = ?, Country" & _

" = ?, County = ?, DOB = ?, Email = ?, FirstName = ?, [Home Phone] = ?,
LastName " & _

"= ?, MiddleInnitial = ?, [Mobile Fone] = ?, [Post Code] = ?, Town = ? WHERE
(ID " & _

"= ?) AND (Address1 = ?) AND (Address2 = ?) AND (Address3 = ?) AND (Address4
= ?)" & _

" AND (Country = ?) AND (County = ?) AND (DOB = ?) AND (Email = ? OR ? IS
NULL AN" & _

"D Email IS NULL) AND (FirstName = ?) AND ([Home Phone] = ?) AND (LastName =
?) A" & _

"ND (MiddleInnitial = ? OR ? IS NULL AND MiddleInnitial IS NULL) AND
([Mobile Fon" & _

"e] = ? OR ? IS NULL AND [Mobile Fone] IS NULL) AND ([Post Code] = ?) AND
(Town =" & _

" ?)"

peopleUpdateCmd.Connection = con

peopleUpdateCmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Address1",
System.Data.OleDb.OleDbType.VarWChar, 50, "Address1"))

peopleUpdateCmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Address2",
System.Data.OleDb.OleDbType.VarWChar, 50, "Address2"))

peopleUpdateCmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Address3",
System.Data.OleDb.OleDbType.VarWChar, 50, "Address3"))

peopleUpdateCmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Address4",
System.Data.OleDb.OleDbType.VarWChar, 50, "Address4"))

peopleUpdateCmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Country",
System.Data.OleDb.OleDbType.VarWChar, 50, "Country"))

peopleUpdateCmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("County",
System.Data.OleDb.OleDbType.VarWChar, 50, "County"))

peopleUpdateCmd.Parameters.Add(New System.Data.OleDb.OleDbParameter("DOB",
System.Data.OleDb.OleDbType.DBDate, 0, "DOB"))

peopleUpdateCmd.Parameters.Add(New System.Data.OleDb.OleDbParameter("Email",
System.Data.OleDb.OleDbType.VarWChar, 50, "Email"))

peopleUpdateCmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("FirstName",
System.Data.OleDb.OleDbType.VarWChar, 20, "FirstName"))

peopleUpdateCmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Home_Phone",
System.Data.OleDb.OleDbType.VarWChar, 50, "Home Phone"))

peopleUpdateCmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("LastName",
System.Data.OleDb.OleDbType.VarWChar, 20, "LastName"))

peopleUpdateCmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("MiddleInnitial",
System.Data.OleDb.OleDbType.VarWChar, 3, "MiddleInnitial"))

peopleUpdateCmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Mobile_Fone",
System.Data.OleDb.OleDbType.VarWChar, 50, "Mobile Fone"))

peopleUpdateCmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Post_Code",
System.Data.OleDb.OleDbType.VarWChar, 10, "Post Code"))

peopleUpdateCmd.Parameters.Add(New System.Data.OleDb.OleDbParameter("Town",
System.Data.OleDb.OleDbType.VarWChar, 50, "Town"))

peopleUpdateCmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_ID",
System.Data.OleDb.OleDbType.Integer, 0,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"ID", System.Data.DataRowVersion.Original, Nothing))

peopleUpdateCmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_Address1",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"Address1", System.Data.DataRowVersion.Original, Nothing))

peopleUpdateCmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_Address2",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"Address2", System.Data.DataRowVersion.Original, Nothing))

peopleUpdateCmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_Address3",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"Address3", System.Data.DataRowVersion.Original, Nothing))

peopleUpdateCmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_Address4",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"Address4", System.Data.DataRowVersion.Original, Nothing))

peopleUpdateCmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_Country",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"Country", System.Data.DataRowVersion.Original, Nothing))

peopleUpdateCmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_County",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"County", System.Data.DataRowVersion.Original, Nothing))

peopleUpdateCmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_DOB",
System.Data.OleDb.OleDbType.DBDate, 0, System.Data.ParameterDirection.Input,
False, CType(0, Byte), CType(0, Byte), "DOB",
System.Data.DataRowVersion.Original, Nothing))

peopleUpdateCmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_Email",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"Email", System.Data.DataRowVersion.Original, Nothing))

peopleUpdateCmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_Email1",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"Email", System.Data.DataRowVersion.Original, Nothing))

peopleUpdateCmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_FirstName",
System.Data.OleDb.OleDbType.VarWChar, 20,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"FirstName", System.Data.DataRowVersion.Original, Nothing))

peopleUpdateCmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_Home_Phone",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"Home Phone", System.Data.DataRowVersion.Original, Nothing))

peopleUpdateCmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_LastName",
System.Data.OleDb.OleDbType.VarWChar, 20,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"LastName", System.Data.DataRowVersion.Original, Nothing))

peopleUpdateCmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_MiddleInnitial",
System.Data.OleDb.OleDbType.VarWChar, 3,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"MiddleInnitial", System.Data.DataRowVersion.Original, Nothing))

peopleUpdateCmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_MiddleInnitial1",
System.Data.OleDb.OleDbType.VarWChar, 3,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"MiddleInnitial", System.Data.DataRowVersion.Original, Nothing))

peopleUpdateCmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_Mobile_Fone",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"Mobile Fone", System.Data.DataRowVersion.Original, Nothing))

peopleUpdateCmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_Mobile_Fone1",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"Mobile Fone", System.Data.DataRowVersion.Original, Nothing))

peopleUpdateCmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_Post_Code",
System.Data.OleDb.OleDbType.VarWChar, 10,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"Post Code", System.Data.DataRowVersion.Original, Nothing))

peopleUpdateCmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_Town",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"Town", System.Data.DataRowVersion.Original, Nothing))

daPeople.UpdateCommand = peopleUpdateCmd




--
Regards - One Handed Man

Author : Fish .NET & Keep .NET
=========================================
This posting is provided "AS IS" with no warranties,
and confers no rights.


john said:
hi

im trying to update a dataset


DataRow r = ds.Tables["customer"].NewRow();
r["Name"]="customer1";
ds.Tables["customer"].Rows.Add(r);
da.Update(ds,"customer");


when i run
da.Update(ds,"customer");

this piece of code i get the following error


Update requires a valid InsertCommand when passed DataRow collection with
new rows.
i can see the updated data in the dataset but i cant update it to the sql
2000 database.



thank you for your time
Click to expand...
 
OK then you insert command is invalid

--
Regards - One Handed Man

Author : Fish .NET & Keep .NET
=========================================
This posting is provided "AS IS" with no warranties,
and confers no rights.


shahrukh said:
but im trying to add a new record..
and i copied my code from professional c#.. the book..
so why doesnt it work?


One Handed Man said:
Its telling you that your dataDapter update command string is missing or
invalid. Check this

Here's one I cooked earlier

'setup People Update Command

peopleUpdateCmd.CommandText = "UPDATE People SET Address1 = ?, Address2
Click to expand...
=
?,
Address3 = ?, Address4 = ?, Country" & _

" = ?, County = ?, DOB = ?, Email = ?, FirstName = ?, [Home Phone] = ?,
LastName " & _

"= ?, MiddleInnitial = ?, [Mobile Fone] = ?, [Post Code] = ?, Town = ? WHERE
(ID " & _

"= ?) AND (Address1 = ?) AND (Address2 = ?) AND (Address3 = ?) AND (Address4
= ?)" & _

" AND (Country = ?) AND (County = ?) AND (DOB = ?) AND (Email = ? OR ? IS
NULL AN" & _

"D Email IS NULL) AND (FirstName = ?) AND ([Home Phone] = ?) AND
Click to expand...
(LastName
=
?) A" & _

"ND (MiddleInnitial = ? OR ? IS NULL AND MiddleInnitial IS NULL) AND
([Mobile Fon" & _

"e] = ? OR ? IS NULL AND [Mobile Fone] IS NULL) AND ([Post Code] = ?) AND
(Town =" & _

" ?)"

peopleUpdateCmd.Connection = con

peopleUpdateCmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Address1",
System.Data.OleDb.OleDbType.VarWChar, 50, "Address1"))

peopleUpdateCmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Address2",
System.Data.OleDb.OleDbType.VarWChar, 50, "Address2"))

peopleUpdateCmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Address3",
System.Data.OleDb.OleDbType.VarWChar, 50, "Address3"))

peopleUpdateCmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Address4",
System.Data.OleDb.OleDbType.VarWChar, 50, "Address4"))

peopleUpdateCmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Country",
System.Data.OleDb.OleDbType.VarWChar, 50, "Country"))

peopleUpdateCmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("County",
System.Data.OleDb.OleDbType.VarWChar, 50, "County"))

peopleUpdateCmd.Parameters.Add(New System.Data.OleDb.OleDbParameter("DOB",
System.Data.OleDb.OleDbType.DBDate, 0, "DOB"))

peopleUpdateCmd.Parameters.Add(New System.Data.OleDb.OleDbParameter("Email",
System.Data.OleDb.OleDbType.VarWChar, 50, "Email"))

peopleUpdateCmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("FirstName",
System.Data.OleDb.OleDbType.VarWChar, 20, "FirstName"))

peopleUpdateCmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Home_Phone",
System.Data.OleDb.OleDbType.VarWChar, 50, "Home Phone"))

peopleUpdateCmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("LastName",
System.Data.OleDb.OleDbType.VarWChar, 20, "LastName"))

peopleUpdateCmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("MiddleInnitial",
System.Data.OleDb.OleDbType.VarWChar, 3, "MiddleInnitial"))

peopleUpdateCmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Mobile_Fone",
System.Data.OleDb.OleDbType.VarWChar, 50, "Mobile Fone"))

peopleUpdateCmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Post_Code",
System.Data.OleDb.OleDbType.VarWChar, 10, "Post Code"))

peopleUpdateCmd.Parameters.Add(New System.Data.OleDb.OleDbParameter("Town",
System.Data.OleDb.OleDbType.VarWChar, 50, "Town"))

peopleUpdateCmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_ID",
System.Data.OleDb.OleDbType.Integer, 0,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"ID", System.Data.DataRowVersion.Original, Nothing))

peopleUpdateCmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_Address1",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"Address1", System.Data.DataRowVersion.Original, Nothing))

peopleUpdateCmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_Address2",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"Address2", System.Data.DataRowVersion.Original, Nothing))

peopleUpdateCmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_Address3",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"Address3", System.Data.DataRowVersion.Original, Nothing))

peopleUpdateCmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_Address4",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"Address4", System.Data.DataRowVersion.Original, Nothing))

peopleUpdateCmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_Country",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"Country", System.Data.DataRowVersion.Original, Nothing))

peopleUpdateCmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_County",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"County", System.Data.DataRowVersion.Original, Nothing))

peopleUpdateCmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_DOB",
System.Data.OleDb.OleDbType.DBDate, 0, System.Data.ParameterDirection.Input,
False, CType(0, Byte), CType(0, Byte), "DOB",
System.Data.DataRowVersion.Original, Nothing))

peopleUpdateCmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_Email",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"Email", System.Data.DataRowVersion.Original, Nothing))

peopleUpdateCmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_Email1",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"Email", System.Data.DataRowVersion.Original, Nothing))

peopleUpdateCmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_FirstName",
System.Data.OleDb.OleDbType.VarWChar, 20,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"FirstName", System.Data.DataRowVersion.Original, Nothing))

peopleUpdateCmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_Home_Phone",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"Home Phone", System.Data.DataRowVersion.Original, Nothing))

peopleUpdateCmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_LastName",
System.Data.OleDb.OleDbType.VarWChar, 20,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"LastName", System.Data.DataRowVersion.Original, Nothing))

peopleUpdateCmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_MiddleInnitial",
System.Data.OleDb.OleDbType.VarWChar, 3,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"MiddleInnitial", System.Data.DataRowVersion.Original, Nothing))

peopleUpdateCmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_MiddleInnitial1",
System.Data.OleDb.OleDbType.VarWChar, 3,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"MiddleInnitial", System.Data.DataRowVersion.Original, Nothing))

peopleUpdateCmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_Mobile_Fone",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"Mobile Fone", System.Data.DataRowVersion.Original, Nothing))

peopleUpdateCmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_Mobile_Fone1",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"Mobile Fone", System.Data.DataRowVersion.Original, Nothing))

peopleUpdateCmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_Post_Code",
System.Data.OleDb.OleDbType.VarWChar, 10,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"Post Code", System.Data.DataRowVersion.Original, Nothing))

peopleUpdateCmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_Town",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"Town", System.Data.DataRowVersion.Original, Nothing))

daPeople.UpdateCommand = peopleUpdateCmd




--
Regards - One Handed Man

Author : Fish .NET & Keep .NET
=========================================
This posting is provided "AS IS" with no warranties,
and confers no rights.


john said:
hi

im trying to update a dataset


DataRow r = ds.Tables["customer"].NewRow();
r["Name"]="customer1";
ds.Tables["customer"].Rows.Add(r);
da.Update(ds,"customer");


when i run
da.Update(ds,"customer");

this piece of code i get the following error


Update requires a valid InsertCommand when passed DataRow collection with
new rows.
i can see the updated data in the dataset but i cant update it to the sql
2000 database.



thank you for your time
Click to expand...
Click to expand...
Click to expand...
 
hi

im trying to update a dataset


DataRow r = ds.Tables["customer"].NewRow();
r["Name"]="customer1";
ds.Tables["customer"].Rows.Add(r);
da.Update(ds,"customer");


when i run
da.Update(ds,"customer");

this piece of code i get the following error


Update requires a valid InsertCommand when passed DataRow collection with
new rows.
i can see the updated data in the dataset but i cant update it to the sql
2000 database.



thank you for your time
 
Have you tried using a CommandBuilder Object? You pass your DataAdapter as
a parameter to a CommandBuilder. It looks something like this:

OleDbDataAdapter refAdapter = new OleDbDataAdapter(strSelectA, master);
OleDbCommandBuilder refCommand = new OleDbCommandBuilder(refAdapter);

Of course, you're using SQL Server and not Access, so I guess it would be a
SqlDataAdapter and an SqlCommandBuilder instead of OleDb.

- carl
 
Thanks for posting, Carl and Terry. CommandBuilder should work in this case
and was probably omitted.

John, the CommandBuilder will automatically build Update, Insert and Delete
commands for your DataAdapter IF your command selects from only one table.
I advise using it.

Steven Bras, MCSD
Microsoft Developer Support/Data Access Technologies

This posting is provided "AS IS" with no warranties, and confers no rights.

Microsoft Security Announcement: Have you installed the patch for Microsoft
Security Bulletin MS03-026?  If not Microsoft strongly advises you to
review the information at the following link regarding Microsoft Security
Bulletin MS03-026
http://www.microsoft.com/security/security_bulletins/ms03-026.asp and/or to
visit Windows Update at http://windowsupdate.microsoft.com to install the
patch. Running the SCAN program from the Windows Update site will help to
insure you are current with all security patches, not just MS03-026.
 
Back
Top