|
I am using SQL Server 2000. I ran into a situation where I needed to increment a revision. The revision could be up to two letters in length. The code below works great. This is just for an example to anyone who may need it.
Check This out:
declare @r varchar(2)
declare @MaxRevLetter INT
declare @seriesLetter1 VARCHAR(1)
declare @seriesLetter2 VARCHAR(1)
declare @NewRevision VARCHAR(2)
-- SET Revision Letter(s) to Check
SET @r = 'CX'
SET @MaxRevLetter = ASCII('X')
-- test for length. If the Revision is only one letter then it is easy to handel.
IF LEN(@r) > 0 AND LEN(@r) < 2 BEGIN
-- if the revision letter is a Z then we can not increment it using the following method
-- so set it.
IF (@r != 'Z') BEGIN
IF ASCII(LTRIM(RTRIM(@r))) <> @MaxRevLetter BEGIN
SET @NewRevision = CHAR(ASCII(@r)+1)
END
END
ELSE BEGIN
SET @NewRevision = 'X'
END
END
ELSE BEGIN
-- get the letters in the Revision
SET @seriesLetter1 = SUBSTRING(@r,0,LEN(@r) )
SET @seriesLetter2 = SUBSTRING(@r,2,LEN(@r) )
-- because we know that if there is a Z the letter must be set and can not be
-- incremented. We need to test for a Z.
IF (@seriesLetter1 + @seriesLetter2 != 'ZZ' AND
@seriesLetter1 + @seriesLetter2 != 'ZX' AND
@seriesLetter1 + @seriesLetter2 != 'XZ' AND
@seriesLetter1 + @seriesLetter2 != 'XX') BEGIN
-- Ok if the first letter is NOT an X and the second letter is not an X
-- increment the second letter so AB would be AC
IF (ASCII(@seriesLetter2) <> @MaxRevLetter) BEGIN
SET @seriesLetter2 = CHAR(ASCII(@seriesLetter2)+1)
SELECT NewRevision = @seriesLetter1 + @seriesLetter2
END
ELSE BEGIN
-- ok the second letter was an X Ex: AX so we need to increment the first letter
-- and set the second letter back. AX would be BA
SET @seriesLetter1 = CHAR(ASCII(@seriesLetter1)+1)
SET @seriesLetter2 = 'A'
SELECT NewRevision = @seriesLetter1 + @seriesLetter2
END
END
ELSE BEGIN
-- ok there is some combo of X and Z in the string. so set the value
IF (@seriesLetter1 = 'X' AND @seriesLetter2 = 'Z') BEGIN
SET @seriesLetter1 = 'X'
SET @seriesLetter2 = 'X'
SELECT NewRevision = @seriesLetter1 + @seriesLetter2
END
IF (@seriesLetter1 = 'Z' AND @seriesLetter2 = 'X') BEGIN
SET @seriesLetter1 = 'X'
SET @seriesLetter2 = 'A'
SELECT NewRevision = @seriesLetter1 + @seriesLetter2
END
IF (@seriesLetter1 = 'Z' AND @seriesLetter2 = 'Z') BEGIN
SET @seriesLetter1 = 'Z'
SET @seriesLetter2 = 'X'
SELECT NewRevision = @seriesLetter1 + @seriesLetter2
END
IF (@seriesLetter1 = 'X' AND @seriesLetter2 = 'X') BEGIN
SET NewRevision = 'The Revision XX is the last revision possable.'
END
END
END
enjoy.
Let me know if you have any questions
Will
|
|
|
|
|
The following line is not correct:
IF (@seriesLetter1 = 'X' AND @seriesLetter2 = 'X') BEGIN
SET @NewRevision = @seriesLetter1 = 'The Revision XX is the last revision possable.'
END
please change it to
IF (@seriesLetter1 = 'X' AND @seriesLetter2 = 'X') BEGIN
SET @NewRevision = 'The Revision XX is the last revision possable.'
END
Will
|
|
|
|
|
I've been using MySQL for some months which has a build-in "md5()" function for md5 excryption, very convenient. Now I need to do something uses SQL server 2000 instead of MySQL, I found that there's no such function or similar feature in SQL server 2000... Hmm then how do you encrypt user passwords? Do you have to develop your own DLL to do the encryption(which would suck)?
|
|
|
|
|
Do it on the client side. For unmanaged code, look at the CryptoAPI documentation; for managed code, there's the System.Security.Cryptography.MD5 class.
You might be able to use OPENQUERY and an extended stored procedure, but then we're heavily in the realms of suckiness. 
|
|
|
|
|
Hi
I'm trying to save a mp3 files in a table in access file.
SO I'm using ADO. I can save and read data in access file if it is of the tipes (Long , String, BOOL .) But I don't know how to Read & Write BIG array.
I think that I should use VARIANT but I did not know how!
Thanks very much for any kind of help
![Rose | [Rose]](https://www.codeproject.com/script/Forums/Images/rose.gif)
|
|
|
|
|
I am now interested in SQL / database programming and I would like to find and learn about sql. Does anyone know some good compilers maybe free?? also a website or book about sql / database programming. I have 1 year in visual c++ 6 and about 1/2 a year in c++(you know in console...).
I would appreciate it if anyone replied. 
[It is possible to represent everything in this universe by using 0 and 1]
|
|
|
|
|
SQL is a generic term for a language used to comunicate with a database. That language can vary from various SQL databases. There is no "compiler" as there is in C++. Choose your database software and then learn the associated SQL syntax that goes with the database software.
Some Free database software:
----------------------------
MSDE (Microsoft Data Engine)
MySQL
PostgreSQL
Some Non-Free software:
-----------------------
Microsoft SQL Server
Oracle
DB/2
Once you've chosen your database software that you'll need to search online for some SQL primers. There are tons of sites which will cover the basics of SQL programming.
|
|
|
|
|
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
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
