|
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?
|
|
|
|
|
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.
|
|
|
|
|
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. 
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 ...
|
|
|
|
|
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.
|
|
|
|
|
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.
|
|
|
|
|
Fixed it!!!!! Woo Hoo!!! Thanks again Edbert. You rock!
|
|
|
|
|
I'm glad that you made it work  .
So what was the problem?
Edbert P.
Sydney, Australia.
|
|
|
|
|
Hi,
I would like to have an stored procedure that could paginate it's result, I have a SELECT query than can give from 10 rows as result to 5000 rows, what I would like is to do is "Give me from the rows 30, the next 50 rows", like a TOP, but a subset...
SELECT START_ROW 300 PAGE_SIZE 30 * FROM MyTable WHERE...
I have been searching on the net and the only thing that I have found are samples with Set Row count, but making several selects or even memory tables and nasty tricks like use ID or...
Does T-SQL implement this ?
|
|
|
|
|
 We have an old DOS program that is still in use daily. I am trying to use the existing dBase database (not sure what version) and write a more user friendly interface in vb6. I have used and ADO control and am connecting through ODBC. I can populate the datagrid control without a problem, but when I try try:
Adodc2.RecordSource = "SELECT * FROM Supplier WHERE JOB_NO = '" & JobNumber & " '"
I get a data type mismatch for the JobNumber. The job number would typically be 1394.02 for example. I have tried declaring it as an Integer, String, long, short and double. I keep getting the same error:
[Microsoft][ODBC dBase driver] data type mismatch in criteria expression.
I have been trying to sort this out for the last six hours. Can anyone help?
Byron
|
|
|
|
|
try not enclosing numericals in quotation marks
Adodc2.RecordSource = "SELECT * FROM Supplier WHERE JOB_NO = " & JobNumber
|
|
|
|
|
Thanks, that sorted it out.
|
|
|
|
|
please show me on how to connect my SQL database using web services
given2fly
|
|
|
|
|
you have to be a little more clear...
do you want to be able to retrieve and update the database(s)?
what languages and environments are you going to use? (ASP.NET/C#?)
with ASP.NET and visual studio, i would start with making a simple web service application, then write the methods i would want to expose, along with any private methods required
if you intend to send and receive any complex data, then you should probably design your message format beforehand.
see Place XML Message Design Ahead of Schema Planning to Improve Web Service Interoperability
also a general guide to webservices:
How ASP.NET Web Services Work
|
|
|
|
|
je dois faire une classe en C++ réalisant une communication avec une base de donnée je souhaiterai connaitre le nom de la classe ki gère cette communication sinon quelles sont les méthodes k je devrai utiliser
Merci d'avance;P
MagicFans91
|
|
|
|
|
English please....... 
I was born intelligent
Education ruined me!.
|
|
|
|
|
are you asking about inheritance in c++?
|
|
|
|
|
salut
tu devrais changer de forum en choisir un en français par exemple 
pour faire ce que tu veux cherche vers ADO
vas voir sur ce site il y a des exemples et des tutoriaux
http://www.cppfrance.com/
je peux pas t'aider plus je suis pas très douée
bon courage
Maeva
|
|
|
|
|
Hi all,
All these days, I was working with MS Access and deploying the same to the server was easier for me... (jus a upload).
Now, I am working on a project with SQL Server 2000 and this is going to be deployed into an Intranet Setup. So, the scenario is to in stall the database with few tables' contents mainteined. Even though this is going to be taken care by the 'intstallation team', I am quite curious to know about the process
My requirement is...
*** How would I take the database tables and the contents to the client's place (obviously it should be in CR-ROM, but How is the copying process done?)
*** How is the database installed into the client's SQL Server?
*** What next???
Thanks in advance.
I was born intelligent
Education ruined me!.
|
|
|
|
|
|
Thanks.. will walk through these one by one...
I was born intelligent
Education ruined me!.
|
|
|
|
|
i think he was asking more about how the data would be installed, etc... not how to deploy MSDE in an install...
usually i take the lazy root and let SQL enterprise manager generate the scripts for me, then i export the data from my machine and re-import on the target machine...
|
|
|
|
|
I have datasets with multiple tables that I would like to save as a new Access database (or at least an Excel workbook) instead of XML.
The format does change a bit, so I really don't want to go through a bunch of hoops hardcoding every table and field.
Isn't there a way to simply save a dataset as an MDB?
(ie, for each table in the dataset, create a table in the MDB of the same name, with the same fields, and the same records.)
I know it's a little unusual to do, but shouldn't this be simple?
|
|
|
|
|
I am having problems building a Query and would really appreciate if anybody had any ideas. I need to build a query where I select only the new people that have come in during a quarter that have children ages 0 to 5. (ie The person does not get counted if they came in during another quarter that year) I have been making multiple queries and runnning this report using VB6 code and it is very slow. Below is a list of my tables and other info: (I've simplified the tables to make it easier to list.)
Language\DB: VB6, DAO 3.6, Access Database
Tables\Queries:
tbl_Customer
l_Mem_id (Key)
dt_DateCreated
str_First_nm
str_Last_nm
Qry_Child
l_ID_id (Key)
l_Mem_id
dt_DOB
str_Name
l_age (l_Age: Int((DateDiff("d",[dt_DOB],Now()+1)/365.25))
Qry_Visit
l_ID_id (Key)
l_Mem_id
dt_Date
str_Reason
l_Quarter (l_Quarter: DatePart("q",[dt_Date])
Any help would be appreciated! Thanks 
|
|
|
|
|
Not sure if the exact syntax will work in Access, but here goes:
SELECT * FROM tbl_Customer WHERE
l_Mem_id IN (SELECT l_Mem_id FROM Qry_Child WHERE l_age BETWEEN 0 AND 5) AND
l_Mem_id IN (SELECT l_Mem_id FROM Qry_Visit WHERE dt_Date BETWEEN @startdate AND @enddate)
What a piece of work is man, how noble in reason, how infinite in faculties, in form and moving how express and admirable . . . and yet to me, what is this quintessence of dust? -- Hamlet, Act II, Scene ii.
|
|
|
|
|
Many thanks. I added a NOT IN statement to exclude them if they came in during another Quarter. One problem that I am having is the number of people is inconsistent if you run the query a couple of times. I even added people during earlier quarters that showed up during the specified dates and it would not show the person after I did a requery but the number of people would sometimes stay the same or change to 1 less and sometimes 2-3 times less people. I have inserted the revised SQL statement below Any suggestions?
SELECT * FROM tbl_Customer WHERE l_Mem_id In (SELECT l_Mem_id FROM Qry_Child WHERE l_age BETWEEN 0 AND 5) And l_Mem_id In (SELECT l_Mem_id FROM tbl_Visit WHERE dt_Date BETWEEN #4/1/2003# AND #6/30/2003#) And l_Mem_id Not In (SELECT l_Mem_id FROM tbl_Visit WHERE dt_Date BETWEEN #1/1/2003# AND #3/31/2003#)
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
