|
Do you have access to the server utilities? or are you asking about how to do it programatically?
|
|
|
|
|
Hi !
In my application, I use a SQL command to create a table in an Access database (with ODBC).
I want ot have a text field, and I create it like this :
Name VARCHAR(128)
My problem is that in Access, this text field is set to : Empty String Not Allowed, but I want to allow empty string. How can I specify that in my SQL command ?
Thanks !
Jerome
|
|
|
|
|
Hello!
You should be able to simply modify your CREATE statement to something that looks like this:
Name VARCHAR(128) NULL
to explicitly allow null strings. Then again, writing
Name VARCHAR(128) NOT NULL
will explicitly require NON-NULL strings.
I hope this helps.
Cypher.
|
|
|
|
|
Thanks for answering, but the problem remains. Your solution does allow to have a NULL string, but doesn't allow to have an empty string (which is not a null string !).
Any ideas ?
Jerome
|
|
|
|
|
Jerome Conus wrote:
but doesn't allow to have an empty string
And you get some error message telling you this?
Someone correct me if I'm wrong, but I think that any attempt to store an empty string in a varchar type column will result in that column being set to NULL.
Chris Meech
It's much easier to get rich telling people what they want to hear. Chistopher Duncan
But for a man, barbecuing eggplant and portobello mushrooms is a sure way to have people question your sexual orientation. Kuro5hin
|
|
|
|
|
The thing is that in Access, there are two properties on a text field :
'NULL allowed'
and
'Empty string allowed'
But, with the SQL language, I don't know how to set these two properties.
This command :
CREATE T_Dummy (Label VARCHAR(30) NOT NULL);
Will change the first property (NULL allowed) but not the second one, which by default says 'no empty string allowed' and which is the one I'd like to change.
I couldn't find in the SQL language reference a way to change the second property and I wonder if it is something only Access understands.
Jerome
|
|
|
|
|
Jerome Conus wrote:
I wonder if it is something only Access understands
You are probably hitting the nail on the head.  You'll have to dig through some Access specific programming reference books to likely find any mention of it. Good Luck
Chris Meech
It's much easier to get rich telling people what they want to hear. Chistopher Duncan
But for a man, barbecuing eggplant and portobello mushrooms is a sure way to have people question your sexual orientation. Kuro5hin
|
|
|
|
|
Allow zero length is an Access specific property and cannot be set with SQL.
Just one more reason not to use Access.
Genius may have its limitations, but stupidity is not thus handicapped. - Elbert Hubbard
|
|
|
|
|
DECLARE @variableProc1 varchar(100)<br />
SET @variableProc1 = 'Val 1';<br />
EXECUTE('DECLARE @variableExec1 varchar(100); SET @variableExec1 = ''In variableExec1''; Print @variableExec1; ')<br />
--EXECUTE('DECLARE @variableExec2 varchar(100); SET @variableExec2 = ''In variableExec2'';')<br />
--SET @variableProc1 = variableExec2;<br />
PRINT @variableProc1;
Hi
In this example, I am able to print the value of @variableExec1, as long as I print it with EXECUTE context.
If I try assign the value of @variableExec2 to @variableProc1, it throws an error. Which I think because they are in different context.
My question is, how can I assign the value of @variableExec2 to @variableProc1 ?
Please advice. Thanks
Pankaj
Follow your goals, Means will follow you ---Gandhi---
|
|
|
|
|
You can't. The variable is declared only in the scope of the execute statement.
Genius may have its limitations, but stupidity is not thus handicapped. - Elbert Hubbard
|
|
|
|
|
Hi, in my server explorer produces this error msg :
"server explorer is unable to perform this operation"
"the requested name is valid, but no data of the requested type was found"
but i was able to connect the database to my program!
can someone tell me whats wrong....thanks
I really need an e-book about SQL server 2000, can anyone tell me where can I get one!?   
nevhile.net
|
|
|
|
|
|
I have 4 databases named: db1, db2, db3, db4 on the same SQL Server. All 4 databases have the same table Department(ID, Name) but the data is different. For example:
Table ID Name
db1.Department 1 Dept1
db2.Department 1 Dept2
db3.Department 1 Dept3
db4.Department 1 Dept4
I use SQL Query analyzer to execute the code fragment below:
declare @n int
declare @Name nvarchar(256)
set @n = 1
while (@n < 5)
begin
if (@n = 1) use db1
else if (@n = 2) use db2
else if (@n = 3) use db3
else if (@n = 4) use db4
select [Name] from Department where [ID] = 1
set @Name = [Name] from dbo.Department where [ID] = 1
print @Name
set @n = @n + 1
end
The results:
---------------------------------
Dept1
Dept4
---------------------------------
Dept2
Dept4
---------------------------------
Dept3
Dept4
---------------------------------
Dept4
Dept4
But I think the correct result should be
---------------------------------
Dept1
Dept1
---------------------------------
Dept2
Dept2
---------------------------------
Dept3
Dept3
---------------------------------
Dept4
Dept4
The question is: Is there anything wrong from me or from SQL???
|
|
|
|
|
It sounds like your first conditional is falling through to the last case.
Have you tried running it with conditions you know will always be false?
It looks like it should work....
|
|
|
|
|
Below is my query. When I do a count it is counted all location,I want to be have a count after group by executes. How do I do this?
SELECT a.Number, a.Location,count(a.Location)'Number'
FROM Reports_Location
GROUP BY a.Number, a.Location
|
|
|
|
|
Not sure of the meaning. But if you are looking for a count of locations you could try this
select count(*) from (
select unique location from reports_location
)
;
Chris Meech
It's much easier to get rich telling people what they want to hear. Chistopher Duncan
But for a man, barbecuing eggplant and portobello mushrooms is a sure way to have people question your sexual orientation. Kuro5hin
|
|
|
|
|
Hi all,
I'm a .NET newbie, and just kind of a programming/developing wannabe. I'm moving into C# from a (tiny) background in JavaScript only. I administer a club website in ASP and want to switch to ASP.NET. Here's my conundrum:
I have a membership database (Access) that includes a field for when a record was last updated (UpdDt), and who updated it (UpdLogin). The value in UpdLogin is a member's ID number, correlating to the MbrId field.
I want to show administrators a list of all members that were updated recently, and who updated them. I need to not just show the UpdLogin value, but the actual name of the person who did the update. So I need to (for each record) find the name, within the table I'm already using as the DataSource, and show that as "Updated By".
In ASP, I created 2 recordsets and looped through the first. For every record, I used:
rs2.MoveFirst<br />
rs2.Find("MbrId = "+updLogin)
then (making sure the record existed) grabbed the name.
I think it was inelegant, and probably inefficient, but it worked.
SO! How the heck do I do this in .NET? I'm still working on wrapping my head around DataGrids and DataSets and DataAdapters and DataViews and OhMyGoodnessDoesItEverEnd...
Have I missed some simple SQL solution to this? Do I require a couple of tables in a DataSet, and use some method similar to what I did in ASP?
Please, help a poor newbie in need! 
Best Regards,
Kevin Swanson
http://bcmc.ca[^]
|
|
|
|
|
Why don´t you get all the data in a snigle query ?
Free your mind...
|
|
|
|
|
Sheesh. Query. Thanks, Guillermo.
I went into Access, and designed a query. Of course, I didn't realize you could put more than one copy of the same table into the query builder!
Came out with a SQL statement like this:
SELECT Mbr.MbrId, Mbr.LastName, Mbr.FirstName, Mbr.UpdDt, Mbr_1.LastName, Mbr_1.FirstName<br />
FROM Mbr INNER JOIN Mbr AS Mbr_1 ON Mbr.UpdLogin = Mbr_1.MbrId<br />
ORDER BY Mbr.UpdDt DESC;
Well, yeah!
Very embarassed now. Going to quietly read some more books, and experiment...
Thanks again!
Kevin Swanson
|
|
|
|
|
Hello, all database masters 
I'm currently writing an application in Visual Basic.NET, and I would need to be able to update an ID_STATUS field for an Order table, using a combo box that would take it's value list from a Status table. I have been able to fix that in Access using the multi-columned comboboxes provided there, however I have no clue on how to do this in VB.NET. The Status table has only two columns, ID_STATUS (primary key, relates to "Order"'s ID_STATUS), and STATUS (which is the description for the specific ID). The IDs are numeric, that's how they are stored in the Status and Order tables, and I would like a use to be able to choose the Status from the combo box using the status' Text.
Would anyone have a clue on how to do this?
Matt.
|
|
|
|
|
Hi
I am using VB.net/ASP.NET and SQL Server 2000 for a web application.
I have AccountNo as identity column. I use some dummy records for testing. But, every time I do a Delete from table and Insert Dummy rows, the identity Number of AccountNo starts for (Max(AccountNo)+1) of previously inserted rows. This throws of the AccountNo associated with my Dummy rows and actual table table rows.
How can I reset the IDENTITY to start from 1 without re-creating the table?
Please advice. Thanks in advance.
Pankaj
Follow your goals, Means will follow you ---Gandhi---
|
|
|
|
|
Do a "Truncate Table MyTableName". That will delete all of the records from the table, and resets the seed for the identity column. Note that this will only work if you don't have any tables with foreign keys pointing at your table (you would have to drop the foreign key contraints before doing the truncate).
Hope this helps.
Andy
|
|
|
|
|
Thanks Andy...
Follow your goals, Means will follow you ---Gandhi---
|
|
|
|
|
I'm trying to get a view that contains a sub-query to work in a special way. Right now I'm getting the error message
ORA-01427: single-row subquery returns more than one row when I query the view.
Essentially what I want the subquery to return, is a single string of comma separated values of the multiple rows that exist. Is this possible?
So a table with 3 rows in it and the column that I'm querying has as data
ROWA
ROWB
ROWC
would return the string "ROWA,ROWB,ROWC".
Thanks.
Chris Meech
It's much easier to get rich telling people what they want to hear. Chistopher Duncan
But for a man, barbecuing eggplant and portobello mushrooms is a sure way to have people question your sexual orientation. Kuro5hin
|
|
|
|
|
After some thought, I realized that what I needed was a 'grouping function'. So I wrote my own function that returns a single string of the comma separated data. Works like a charm.
Chris Meech
It's much easier to get rich telling people what they want to hear. Chistopher Duncan
But for a man, barbecuing eggplant and portobello mushrooms is a sure way to have people question your sexual orientation. Kuro5hin
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
