|
In addition to the above reply, you can create the layout of a database schema via the ADOX object, but I found this to be combersome and downright overkill for what seem to be a simple task. In the end I used SQL scripts to generate the required tables, relationships, etc.
I Dream of Absolute Zero
|
|
|
|
|
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!
|
|
|
|
|
You have to replace the myAdapter ?????????? line with these lines: 
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. 
Read this link[^] to MSDN if you want to know more.
Edbert P.
Sydney, Australia.
|
|
|
|
|
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?
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
