|
Hi, I need to create a 6 character random alphnumberic serial number. How can this be achieved in SQL Server 2000? I have been searching and found that Oracle has a function for this, so I am assuming there should be one for SQL then. I could do this easily in .net - but I dont want to have to keep sending a request to the database to check that this number is unique. I would prefer to do the whole thing in a SQL function. So far the only way I have been able to work out, is too create a new GUID - select 6 characters from it - then check that these are not already used in the table (otherwise create a new one and repeat) and insert it.
Any help on this?
Thanks
Wayde
|
|
|
|
|
Ok, I found a way of doing this, may not be the best, but it what I thought of:
DECLARE @Length INT, @Count INT, @RandNum INT, @RandomString VARCHAR(6), @IsValid BIT
SELECT @RandomString = '', @Count = 0, @IsValid = 0, @Length = 6
WHILE (@IsValid = 0)
BEGIN
WHILE @Count < @Length
BEGIN
SET @RandNum = SUBSTRING(CAST(Rand() AS VARCHAR(20)), 3, 2)
IF (((@RandNum > = ASCII('0')) And (@RandNum <= ASCII('9')) Or (@RandNum > = ASCII('A')) And (@RandNum < = ASCII('Z'))))
BEGIN
SET @RandomString = @RandomString + CHAR(@RandNum)
SET @Count = @Count + 1
END
END
--Check here that the number is unique
IF (SELECT certificateSerialNumber FROM Certificate WHERE certificateSerialNumber = @RandomString) IS Null
BEGIN
SET @IsValid = 1
END
END
SELECT @RandomString
This cannot be used as a function though, due to the RandNumber generator. You can also emit the last part that checks if the value is unique. Change the @Length and @RandomString size to suit the size of the field that you need.
|
|
|
|
|
I had created a table User with a field UserOpinion which is of 'text' data type here maximum of onnly 10 lines i am able to enter data rest all data are getting truncated. Is there any way to avoid this truncation of data so that i can enter more than 100 lines of data.
ItsMe
|
|
|
|
|
Are you looking at the result in Query Analyzer? Query Analyzer has a limit on the number of characters it will display from any given column, which defaults to 256. You can change this in Tools, Options, Results tab, 'Maximum characters per column'.
Otherwise I've never heard of data being truncated like this. Check how you're adding it to the database. Perhaps you need to increase the Size property of the Parameter object you're using - I normally set this to the length of the data I'm going to set.
|
|
|
|
|
No i have designed a table with text as the data type. By rule this text data type enable a field to accept more than 2 lakh characters. But if inserted data containing 10 thousand characters and tried to fetch the data from backend , i also checked for the data length , it showed only maximum of 4000 not more than that.
ItsMe
|
|
|
|
|
That sounds like you're using a parameter of nvarchar type, which in classic ADO is adVarWChar or in ADO.NET SqlDbType.NVarChar. This type has a limit of 4000 characters in SQL Server 2000. You need to use adLongVarChar to match a text column in ADO, or SqlDbType.Text.
If you want Unicode support in your database field, use ntext rather than text. If developing exclusively for SQL Server 2005, consider using {n}varchar(max) rather than {n}text.
|
|
|
|
|
Hi,
I need to set mutiple values parameter in Sybase ASE stored procedure. I don't know whether Sybase support array type parameter and how to set it.
|
|
|
|
|
Hi,
using VS2005, C#
I have two datatables with a parent/child relationship using the MemberID int as the key to the relationship. The parent datatable is MembershipDetails and the child datatable is Subscriptions.
The user only knows the MembershipNumber but the program needs to ascertain the corresponding MemberID to insert it into the child table (Subscriptions) along with other data. The MembershipNumber is located in the MembershipDetails table.
In other words:
MembershipDetails.MembershipNumber begets MembershipDetails.MemberID 
Is it necessary to loop thru all the MembershipDetails rows to find the MemberID?
Glen Harvy
|
|
|
|
|
insert into Subscriptions<br />
select MemberID, 5 as QtyOrdered, getdate() as DateSubscribed from MembershipDetails where MembershipNumber = 1
In the absence of other field names and data, I made some up.
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Hi and thanks for your response.
What I was really after was the code to use in gathering the data from the dataset and not the database.
-- modified at 20:04 Monday 14th August, 2006
Glen Harvy
|
|
|
|
|
Hi,
I was wondering if I could reduce my SQL statements when using comboboxes for search parameters.
For example:
Combobox with this list:
<Categories
Shirts
Pants
Shoes
Because I don't want a Category search if "<Categories" is selected I have an if statement like this:
if (comboBox_Categories.SelectedIndex != 0)
{
SELECT yada yada WHERE ItemID=8 AND Categories=comboBox_Categories.SelectedValue
}
else
{
SELECT yada yada WHERE ItemID=8
}
I have several ComboBoxes that supply possible parameter variables (that may or may not be used). This creates many possible SQL statements. Any sugestions on how to go about this differently?
Thanks!
Ron
-- modified at 18:37 Sunday 13th August, 2006
|
|
|
|
|
SELECT ColumnNames
FROM TableName
WHERE ItemId = @ItemId AND
(@CategoryID IS NULL OR CategoryID = @CategoryID)
The last line is the key - If you send an empty selection on the ComboBox as a DBNull.Value to the SqlCommand then you'll get the behaviour you want out of one SQL Command.
The last line basically means. If the @CategoryId is NULL then I'm not interested in anything else, I'm TRUE therefor the whole OR expression will be TRUE. If the @CategoryID is something other than NULL then I have to evaluate that against the column CategoryID
Does this help?
|
|
|
|
|
Thanks Colin!
I thought there might be a better way to go about it.
I guess it's kinda like an "else if" statement... it'll only
go the the "else if" if the "if" statement was not satisfied.
Thanks again Colin,
Ron
|
|
|
|
|
 hi.
i want to know how i can handle closing page event.
i mean that if i want to do something before closing page.
thank for help.
ABONASSER
|
|
|
|
|
This forum deals with database issues. Perhaps you might want to try a forum that deals with web application issues.
|
|
|
|
|
Hey.
I know that the Structured Query Language for MDB (Access) is differrent then the SQL of MySQL.
I'm currently moving my databases from Access to MySQL, but I don't know the syntax of the SQL.
If anyone can help me, or give me a dictionary or some internet help and some samples, I would appriciate it.
Thanks alot.
NaNg.
|
|
|
|
|
See my answer to you in the C# forum.
|
|
|
|
|
What is the erorr with this select statement
Set RS = DB.OpenRecordset("select * from Users where User_name=" & Text1.Text & " and User_pass=" & Text2.Text & " and User_type=" & Combo1.List(Combo1.ListIndex))
I use it in vb6 with DAO 3.5
but the compiler tell that too few parameter.Expected 2
all these fields in the table is text
please help
thanx
BE OR NOT TO BE
|
|
|
|
|
You are missin single quotes around the sring values (Text1.Text, Text2.Text) so they are treated as symbolic names for parameters: (extra spaces around ' not needed)
"select * from Users where User_name= ' " & Text1.Text & " ' and User_pass= ' " & Text2.Text & " ' and
|
|
|
|
|
Hello !
Can anybody help me ?
I'm interested to develop an application that uses MySql.Data.dll at runtime with reflection. I got a NullReferenceException when I try to set the value of SelectCommand in MySql.Data.MySqlClient.MySqlDataAdapter namespace.
I don't understand why, I've used Activator.CreateInstance....
Here It is the code of my app:
Dim asm As Assembly = Assembly.LoadFrom("C:\MySql.Data.dll")
Dim myConnectionString As String
myConnectionString = "server=localhost;" _
& "uid=root;" _
& "pwd=xxxxxxx;" _
& "database=test;"
Dim params() As Object = {myConnectionString}
Dim MySqlConnectionClass As Object = Activator.CreateInstance(asm.GetType("MySql.Data.MySqlClient.MySqlConnection"), params)
Dim t As Type = MySqlConnectionClass.GetType
Dim MySqlDataAdapterClass As Object = Activator.CreateInstance(asm.GetType("MySql.Data.MySqlClient.MySqlDataAdapter"))
Dim t2 As Type = MySqlDataAdapterClass.GetType
Dim params2() As Object = {"SELECT * FROM WEB_USER WHERE UserName= 'test' AND Password = 'test'", MySqlConnectionClass}
Dim MySqlCommandClass As Object = Activator.CreateInstance(asm.GetType("MySql.Data.MySqlClient.MySqlCommand"), params2)
Dim t3 As Type = MySqlCommandClass.GetType
Dim mi As PropertyInfo = t2.GetProperty("SelectCommand", BindingFlags.Public)
mi.SetValue(MySqlDataAdapterClass, t3, Nothing) <-- ERROR
I've also tried (in substitution to the last line):
Dim mi As PropertyInfo = t2.GetProperty("SelectCommand", BindingFlags.Public)
mi.SetValue(MySqlDataAdapterClass, MySqlConnectionClass, Nothing) <-- ERROR
Many Thanks !
Gian Paolo
|
|
|
|
|
Well, neither of the two lines you tried would appear to do what you want.
Gian Paolo WNA wrote: mi.SetValue(MySqlDataAdapterClass, t3, Nothing)
This attempts to make a call that would look like this normally:
MySqlDataAdapterClass.SelectCommand = t3 t3 is a Type object, but the property is looking for a MySqlCommand object.
Gian Paolo WNA wrote: mi.SetValue(MySqlDataAdapterClass, MySqlConnectionClass, Nothing) <-- ERROR
That attempt would look like this normally:
MySqlDataAdapterClass.SelectCommand = MySqlConnectionClass Again, this is the wrong object type. The SelectCommand property is looking for a MySqlCommand object, but you supplied a MySqlConnection object.
You might wish to try this:
mi.SetValue(MySqlDataAdapterClass, MySqlCommandClass, Nothing)
|
|
|
|
|
Hey guys, Could someone give me a list of all the SQL Server 200 updates? I just want the names of each and every ittby bitty one and not the download links! Thanks in advance.
|
|
|
|
|
|
For publicly available releases, all you really need is SQL Server 2000 Service Pack 4. There have been no security patches since then. If you want to search for security bulletins for a specific product, try here[^].
Future updates for SQL Server 2000 will be available on Microsoft Update. Go to http://update.microsoft.com/microsoftupdate/[^] to get started. If you have Windows Server Update Services, updates for SQL Server 2000 will be on there.
If you're experiencing a specific problem, contact Support.
|
|
|
|
|
Can I add or remove column in datagrid at runtime
neeraj
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
