|
I have found SQL Team[^] a valuable resource, especially when you start to get into more complex SQL statements.
Hope this helps!
Jeremy Oldham
|
|
|
|
|
 THANKS ALOT!!!
[It is possible to represent everything in this universe by using 0 and 1]
|
|
|
|
|
How can I add PrimaryKey to my DataTable in dataset in Runtime!
I have >>
<xs:key name="SignKey1"><br />
<xs:selector xpath="." /><br />
<xs:field xpath="mstns:PrsId" /><br />
</xs:key>
in my Dataset?! but it gives error that I dont have PrimaryKey! Isnt it PrimaryKey?!?!
Always,
Hovik.
|
|
|
|
|
Just starting to play around with database access in dotNET. I've run through a number of tutorials, and reviewed a number of articles on this web site. All was well and good until I tried to do something on my own... Uh Oh...
I created a simple relational database with the following tables...
Table - PhoneNumber
PhoneNumber_ID
PhoneNumber
PhoneNumberTypeID
PersonID
Table - PhoneNumberType
PhoneNumberType_ID
PhoneNumberType
Table - Person
Person_ID
FirstName
LastName
I threw some data in the PhoneNumberType and Person tables, and then wanted to see if I could create a form to add entries to the PhoneNumber table.
I added two comboboxes to the form, and bound them to datasets filled with the contents of PhoneNumberType and Person tables. The valuemember of the comboboxes is the ***_ID column of the selected row in the combobox. That's working fine.
I added a textbox to the form for the phone number.
So, I have all the elements necessary to add a new row to the PhoneNumber table, but I'm not sure of the most efficient method of performing the insert. After poking around on the internet for a while, it looks like there might be two options avaliable to me (there are probably more)...
1) Create a DataAdapter for the PhoneNumber table, then create and fill a dataset with all the phone numbers in the PhoneNumber table. Add the new row and then update the database with the new dataset (need to figure out how to do that). This seems like overkill for this simple function.
2) Create an SQLCommand and execute it with the new row values. I vaguely remember seeing an article that discussed using ?'s in the SQL command, and then filling in the parameters afterwards, but I can't seem to find it again.
Is either of these the preferred approach, or is there another approach I might consider.
Thanks... whew that was a mouthful.
|
|
|
|
|
Here's my brute force method...
// Load the current phone numbers
sqlDataAdapterPhoneNumber.Fill(dataSetPhoneNumber1);
// create a new row
System.Data.DataRow row = dataSetPhoneNumber1.Tables["PhoneNumber"].NewRow();
// Fill it
row["PhoneNumberTypeID"] = comboBoxPhoneTypeID.SelectedValue;
row["EntityID"] = comboBoxName.SelectedValue;
row["PhoneNumber"] = textBoxPhoneNumber.Text;
// Add it to the dataset
dataSetPhoneNumber1.Tables["PhoneNumber"].Rows.Add(row);
// Update the database (might experiment with GetChanges)
sqlDataAdapterPhoneNumber.Update(dataSetPhoneNumber1);
|
|
|
|
|
Hi there:
In how many ways we can get Dataset position?!
Always,
Hovik.
|
|
|
|
|
I just made a little application...
It uses xp visual sytles & a sql 2000 database...
Usually on my normal apps(that don't use visual styles or sql), I can just make a quick installer, or just copy the whole file to the other computer and use the application...
Now, with sytles and sql it wont...
What are the special things I have to do to get the app to run on other computers???
/\ |_ E X E GG
|
|
|
|
|
How can I Update more than two columns. Everytime I try to update more than two I get a syntax error. Can somebody help me. This code gave me the syntax error.
private void datagrid_UpdateCommand(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e)
{
int ID = (int) datagrid.DataKeys[(int) e.Item.ItemIndex];
string name = ((TextBox)e.Item.Cells[1].Controls[0]).Text;
string type = ((TextBox)e.Item.Cells[2].Controls[0]).Text;
string test = ((TextBox)e.Item.Cells[3].Controls[0]).Text;
string sql =
"UPDATE EmployeeInfo SET Fir=\"" + name +
"\", Las=\"" + type + "\"" +
"\", Res=\"" +test+ "\""
"WHERE ID=" + ID;
ExecuteNonQuery(sql);
datagrid.EditItemIndex = -1;
ReadRecords();
}
The code does not give the syntax error but only updates the two columns.
private void datagrid_UpdateCommand(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e)
{
int ID = (int) datagrid.DataKeys[(int) e.Item.ItemIndex];
string name = ((TextBox)e.Item.Cells[1].Controls[0]).Text;
string type = ((TextBox)e.Item.Cells[2].Controls[0]).Text;
string sql =
"UPDATE EmployeeInfo SET Fir=\"" + name +
"\", Las=\"" + type + "\"" +
" WHERE ID=" + ID;
ExecuteNonQuery(sql);
datagrid.EditItemIndex = -1;
ReadRecords();
}
Can anyone give some pointers. Thanks
|
|
|
|
|
The only thing that jumps out at me is that you do not have a space between your final field and the WHERE clause in the first bit of code. What is the syntax error you get?
Notice the difference, but it may just be a cut/paste anomaly...
Syntax error
kornstyle wrote:
"WHERE ID=" + ID;
non syntax error
kornstyle wrote:
" WHERE ID=" + ID;
|
|
|
|
|
I'm getting a "missing operator" syntax error.
|
|
|
|
|
Put the space in still didn't work.
|
|
|
|
|
Doh! If I would have looked harder the first time I would have caught it. I was thinking you were getting a SQL syntax error.
kornstyle wrote:
string sql =
"UPDATE EmployeeInfo SET Fir=\"" + name +
"\", Las=\"" + type + "\"" +
"\", Res=\"" +test+ "\""
"WHERE ID=" + ID;
Add a + to the end of the line where you set Res...
string sql = <br />
"UPDATE EmployeeInfo SET Fir=\"" + name + <br />
"\", Las=\"" + type + "\"" +<br />
"\", Res=\"" +test+ "\"" +<br />
"WHERE ID=" + ID;
|
|
|
|
|
|
think that was just a typo.
|
|
|
|
|
Hi,
My question for all you ADO.NET gurus is:
Is there a database-agnostic way to create a set of new tables through ADO.NET.
This is the sort of thing that I would have used ADOX for prior to .NET, but there seems no equivalent.
I am working on a product that will use any database, creating the required tables on the fly if they don't exist (usually as initialisation the first time the software is run).
Anybody got any bright ideas, or am I going to have to generate the correct 'CREATE TABLE' syntax for each possible database type?
Dr Herbie.
Remember, half the people out there have below average IQs.
|
|
|
|
|
what's wrong if you use this "correct 'Create Table'" syntax.
i create entire databases this way. SQL-Server creates the script,
and we pack this in a text file which can be read using text reader
and executed using an sql Command.
Can it be any simpler?
|
|
|
|
|
Using scripts from SQLServer is all very well, but we have to support ALL databases; SQLServer, Oracle, FoxPro, DBase, MySQL, MS Access, etc., (including any databases not yet written)
CREATE TABLE syntax is similar between different DB vendors, but not identicle. Therefore we need a vendor-agnostic method. ADOX supplied this, as the driver created the actual SQL statement to create the table from a structure you created in code. I was hoping ADO.NET would have something similar, but it doesn't look promising.
One option would be to get SQL Server to dump out table structures as XML, then use different XSLT files to transform the XML into database specific script files, but I was hoping to avoid any of that.
Ho-hum, better dig that 'Learn XSLT in one easy lesson' book out.
Dr Herbie.
Remember, half the people out there have below average IQs.
|
|
|
|
|
I'm looking for a tool that can take in a SQL statement and dump the data to an excel spreadsheet; preferably something that has some type of template system so the layout doesn’t have to be manually formatted each time a spreadsheet is created.
I realize it’s not that hard to this with .NET, but it just seems like this should be a pretty common problem people have. Lots of companies have data, and lots of companies use excel sheets. I seem to keep rewriting small tools every time someone needs to generate a new spreadsheet. I'm looking for a more robust solution.
Thanks for any help
Dave Ratti
|
|
|
|
|
Why not extract the data to XML and then use a style sheet to format it as HTML. If I recall you can take an HTML document and change its extension to cause Excel to be associated with it and Excel will happily load it into a worksheet.
This also gives you the flexibility to choose what output format you want. One stylesheet for XML, one for CSV, one for XLS.
.
|
|
|
|
|
Thanks for the response. Actually I think I've found the tool I was looking for:
http://www.oraxcel.com/projects/sqlxl/
I thought about writing my own tools that use XML/XLS but the problem is that although that is a very flexible solution, its not exactly the most user friendly option. The people here that will utlimately be using this tool wont be able to learn xml or xls. I could write a tool to easily create templates for what they want, but that takes time and the above tool is pretty much just what we are looking for.
We currently write scripts to export the data in CSV format and load that in excel and manually format the data. Going to xml->xls would be a much better approach but it still has 2 or 3 steps that just dont seem like they should be there, at least not to our people using it.
Thanks for the response
Dave Ratti
|
|
|
|
|
In SQL Server 2000 Enterprise Manager is there a way to add some type of organizer to stored procedures within a single database. Example I have 30 stored procedures that deal with reports, 30 that deal with updates and 30 that deal with getting data. I would like to divide these into their respective categories so I can see only report stored procedures when that’s what I want. Can this be done?
Thanks,
Jason W.
|
|
|
|
|
Nope... Enterprise Manager doesn't have any fancy organizing capabilities other than sorting the names.
Perhaps you could give your sp's a prefix that to do this?
|
|
|
|
|
I’m already using a prefix I was just hoping to organize a little better. Thanks for the help.
Jason W.
|
|
|
|
|
I am building 2 classes using C#. Their relationship is 1 ~ n. And each one has the corresponding table in database.
for example :
CompanyClass & EmployeeClass, table name in Database is Company & Employee
I think I should use collection to build CompanyClass, but I am not sure how to do it. There're many different types of collection in .Net.
Can anyone help me please?
Thanks in advance!
YYF
|
|
|
|
|
Shameless self-promotion...
Using CollectionBase and DictionaryBase
Will CompanyClass be a collection of Employees? Inheriting from CollectionBase is a little easier than implementing ICollection.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
