Database

24-Feb-04 9:24

I am having difficulty inserting some information to a SQL Server Db. Here is my code:

*****************************************************************
// Create DataAdapter object for update and other operations
SqlDataAdapter myAdapter = new SqlDataAdapter("SELECT * FROM tblMovies", sqlConnection1);

// Create DataSet to contain related data tables, rows, and columns
DataSet myDataSet = new DataSet();

sqlConnection1.Open();
myAdapter.Fill(myDataSet, "tblMovies");

// Change data in tblMovie (dataSet)
DataRow movRow = myDataSet.Tables["tblMovies"].NewRow();
movRow["MovieName"] = tbxTitle;
movRow["MovieGenre"] = ddlGenre;
movRow["Checkedout"] = tbxChecked;
movRow["PersonWhoChecked"] = tbxWhoChecked;
myDataSet.Tables["tblMovies"].Rows.Add(movRow);

// Call Update command to update Db
myAdapter ????????????????????????;

sqlConnection1.Close();

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

The myAdapter line (3rd from the last line is what is holding me back (I think). I am creating a web page that takes input from a user (movie information input into textboxes) and submits it to the database.
If I use myAdapter.InsertCommand(myDataSet, "tblMovies");I get the message "denotes a method where a property was expected." I have tried a number of things and none have worked so any help would be HUGELY apprciated!

Thanks!

Edbert P24-Feb-04 11:28

24-Feb-04 11:28

You have to replace the myAdapter ?????????? line with these lines: Smile | :)

1. Set the InsertCommand of the data adapter.

SqlCommand cmd;
cmd = new SqlCommand("INSERT INTO tblMovies(MovieName, MovieGenre, Checkedout, PersonWhoChecked) " +
                       "VALUES (@MovieName, @MovieGenre, @Checkedout, @PersonWhoChecked)", sqlConnection1);
myAdapter.InsertCommand = cmd;

I don't think you need to add the parameters manually, as I'm using OleDbCommand and I can insert new records automatically.
However, if it doesn't work then you have to add the record using the parameter instead of adding the row, e.g.

cmd.Parameters.Add("@MovieName", SqlDbType.NChar, 50, "MovieName");
...

2. Update the database.

myAdapter.Update(myDataSet.Tables["tblMovies"]);

Voila! If I remember it correctly without using the DevEnv, it should work as intended. Poke tongue | ;-P

Read this link[^] to MSDN if you want to know more.

Edbert P.
Sydney, Australia.

HarleyJase24-Feb-04 12:09

24-Feb-04 12:09

Thanks a ton Edbert! I added those two lines so that my code looks like this:

// Create DataAdapter object for update and other operations
SqlDataAdapter myAdapter = new SqlDataAdapter("SELECT * FROM tblMovies", sqlConnection1);

// Create DataSet to contain related data tables, rows, and columns
DataSet myDataSet = new DataSet();

sqlConnection1.Open();
myAdapter.Fill(myDataSet, "tblMovies");

// Change data in tblMovie (dataSet)
DataRow movRow = myDataSet.Tables["tblMovies"].NewRow();
movRow["MovieName"] = tbxTitle;
movRow["MovieGenre"] = ddlGenre;
movRow["Checkedout"] = tbxChecked;
movRow["PersonWhoChecked"] = tbxWhoChecked;
myDataSet.Tables["tblMovies"].Rows.Add(movRow);

// Call Update command to update Db
SqlCommand cmd = new SqlCommand("INSERT INTO tblMovies(MovieName, MovieGenre, Checkedout, PersonWhoChecked) " +
"VALUES (@MovieName, @MovieGenre, @Checkedout, @PersonWhoChecked)", sqlConnection1);
cmd.Parameters.Add("@MovieName", SqlDbType.NVarChar, 50, "MovieName");
cmd.Parameters.Add("@MovieGenre", SqlDbType.NVarChar, 50, "MovieGenre");
cmd.Parameters.Add("@Checkedout", SqlDbType.NVarChar, 50, "Checkedout");
cmd.Parameters.Add("@PersonWhoChecked", SqlDbType.NVarChar, 50, "PersonWhoChecked");

sqlConnection1.Close();
myAdapter.Update(myDataSet.Tables["tblMovies"]);

but now I am getting the error, "Specified argument was out of the range of valid values. Parameter name: index " Any thoughts?

Edbert P24-Feb-04 17:34

24-Feb-04 17:34

If you are using MS Visual Studio, let it build one for you (do drag-drop of the data adapter, set the connection, then set the Select, Insert, Update, and Delete Commands through the properties window). Otherwise you can use whatever code it builds as an example.

For your code above, there are some things you might want to check:

1. First, make sure that when you're adding the Command Parameters you are specifying the valid DbType. I was only giving an example with the SqlDBType.NVarChar as I don't know your table structure. I bet that CheckedOut is actually of the type DBTimeStamp instead of NVarChar. If you are unsure of this, the easiest way is to let VS build one for you, otherwise check MSDN for SqlDbType. Also make sure that non-nullable columns are filled.

2. You also have to set the connection for the command (this was done in your code, just don't forget it), i.e.
cmd.Connection = sqlConnection1;

3. Set the command to the adapter, i.e.
myAdapter.InsertCommand = cmd;

If I'm not wrong, you don't need to open/close your sqlConnection as data adapter will do it automatically (it will open and close it everytime you call the fill/update commands).

I hope it will work.

Edbert P.
Sydney, Australia.

HarleyJase25-Feb-04 5:06

25-Feb-04 5:06

I really do apprciate your help Edbert so please bear with me as I try to understand here. I checked all of the DbTypes by checking them out in Server Explorer. They are all NVARCHAR 50. I did however use a drop down list as the genre. Thinking maybe this was the issue I replaced it with a standard textbox and kept the name the same for ease of troubleshooting. I am now getting the error: "Update requires a valid InsertCommand when passed DataRow collection with new rows". I had this error before but it has returned ... grrr. Confused | :confused:

I thought I was on the right track but maybe that track was way off course?!?!?!?!?! One of my issues here is that I do not have enought experience in .NET to know where these errors come from. Could you help me out agian?

thanks again ...

HarleyJase25-Feb-04 5:20

25-Feb-04 5:20

One other thing ... the line that the error is referring to is, "myAdapter.Update(myDataSet.Tables["tblMovies"]);". When I try the InsertCommand I get the error, "InsertCommand denotes a 'property' where a 'method' was expected.

Edbert P25-Feb-04 11:37

25-Feb-04 11:37

I had a suspicion that the error Specified argument was out of the range of valid values. Parameter name: index was returned by controls like combobox/listbox. However, since I wasn't quite sure what ddlGenre is I couldn't confirm this before.

The error Additional information: Update requires a valid InsertCommand when passed DataRow collection with new rows. may mean that:

1. You didn't set an InsertCommand. You told me you just changed ddlGenre with textbox, but did you include the DataAdapter.InsertCommand = cmd line?
The DataAdapter.InsertCommand is a property, not a method. You use it to set the InsertCommand into the DataAdapter, not to update the table. Please read the MSDN sample I have given Creating Insert Command[^] and Updating Database With Data Adapter[^] or check the code automatically generated by Visual Studio.

2. You didn't set a primary key in your table.

3. The InsertCommand provided is invalid. Try to test your insert command before you use it (especially if you write it yourself).

Edbert P.
Sydney, Australia.

HarleyJase26-Feb-04 7:05

26-Feb-04 7:05

Fixed it!!!!! Woo Hoo!!! Thanks again Edbert. You rock!

Edbert P26-Feb-04 11:01