|
Mark Sanders wrote:
Do you create a book table and an e-book table?
Yes.
This would seem to me to be extra work since the two book types only differ by one field.
Still, does a "download count" have anything to do with the paper version? Of course not, hence it has nothing to do in the "book" table.
Possibly you'd have even three tables. One describing the "book", one describing the proprties of the paper version and one describing the properties of the "electronic only" version.
++luck;
|
|
|
|
|
Can you give me a little more info on modeling the three tables? Would a record in the "e-book properties" table have a FK which relates to the PK of the "book" table?
Mark Sanders
sanderssolutions.com
|
|
|
|
|
Mark Sanders wrote:
Would a record in the "e-book properties" table have a FK which relates to the PK of the "book" table?
Yep, that would have been my choice.
|
|
|
|
|
Since I would only want one record in the properties table to be associated with one record in the book table would the bookID actually be both a FK and a PK?
Mark Sanders
sanderssolutions.com
|
|
|
|
|
-= Lazy way =-
Since it is only one field, you can just have a nullable field for the number of downloads, and then add a constraint to validate that the download field is null if it is not an e-book.
-=Better way=-
But if you have many properties of a book, and many properties of an e-book some of which are the same,most of which are not. You may want to consider another table to hold these poperties, with a schema in the format: BookPK, property, value
-=Best way=-
Have three tables ... properties of paper books, properties of e-books, and a list of books
|
|
|
|
|
I think you would get different answers from different people. My personal tack on it is to keep all book data in a single table. Then you let your stored procedures or business rules layer handle figuring things out.
I would just create one table for all books and then create a second table that contained the book id and its download count. Every book won't have a download count, but that's ok. You let your app or stored proc figure that out. You could even keep a lookup of book types and a table of book IDs with their book type. Just do a join on the two tables to get all books of that type. That type will tell your app whether you should do a download count look up. This way, if you ever added a new book type, you don't have to add a new specific book table just to accomodate one or two new parameters. You already have one that suits your needs. You just create new tables with the book ID as the lookup mechanism.
This is just one guy's opinion on this. Take it or leave it.
-Matt
------------------------------------------
The 3 great virtues of a programmer:
Laziness, Impatience, and Hubris.
--Larry Wall
|
|
|
|
|
What's wrong with this simple statement:
DECLARE @strSQL nvarchar(1000)
SET @strSQL='SELECT * FROM ##SPVA7D25F0176A1C4B4F9F7F979B7867744E WHERE rowindex=1'
EXEC @strSQL
When I execute this under Query Analyzer, I got:
"Server: Msg 2812, Level 16, State 62, Line 3
Could not find stored procedure 'SELECT * FROM ##SPVA7D25F0176A1C4B4F9F7F979B7867744E WHERE rowindex=1'."
Thanks
norm
|
|
|
|
|
I run it from Query Analyzer and get the same error,but if you put this statement in stored procedure it will work.I don't know why.
Mazy
"And the carpet needs a haircut, and the spotlight looks like a prison break
And the telephone's out of cigarettes, and the balcony is on the make
And the piano has been drinking, the piano has been drinking...not me...not me-Tom Waits
|
|
|
|
|
It is in fact part of a stored procedure. I took it out to make it easier for others to examine the cause of my agony.
norm
|
|
|
|
|
Typically, whenever I get a 'could not find' kind of error message, I suspect a permissions problem. Have you been granted execute permission for this stored procedure?
Chris Meech
"what makes CP different is the people and sense of community, things people will only discover if they join up and join in." Christian Graus Nov 14, 2002.
"AAAAAAAAAHHHHHH!!!!! Those leaks are driving me crazy! How does one finds a memory leak in a garbage collected environment ??! Daniel Turini Nov. 2, 2002.
|
|
|
|
|
it's a raw SQL string. It's no stored procedure, if you'd read my post again.
the error msg is very confusing.
norm
|
|
|
|
|
Use sp_executesql.
The Execute command is for stored procedures.
Julio-SESICO
|
|
|
|
|
Try
EXEC ( @strSQL ) (note the parentheses)
or
sp_executesql @strSQL
Bruce Duncan, CP#9088, CPUA 0xA1EE, Sonork 100.10030 Blackadder: Baldrick, have you no idea what irony is? Baldrick: Yeah, it's like goldy and bronzy only it's made of iron.
|
|
|
|
|
Hey, this solved the problem, thankyou. It will be very hard to find this bug
I've got a new one for you - I think you will solve this with ease:
CREATE PROC dbo.DADA(....) AS
...
... Body of a stored procedure ...
...
DECLARE @token VARCHAR(15)
SET @token=NULL
SET @strSQL = 'SELECT @token=strToken FROM ' + @temp_table_name + ' WHERE rowindex=' + CAST(@index AS VARCHAR(10))
SET @strSQL = CAST(@strSQL AS nvarchar(1000))
EXEC (@strSQL)
...
... Body of a stored procedure ...
...
GO
The error message from Query Analyzer: "Must declare the variable '@token'"
As u can see from the code fragment, @token has been declared.
norm
|
|
|
|
|
hmm, its complaining about @token in the SELECT statement. In this case the SELECT that you have built up is executing in a different 'scope' or 'session' (not sure how best to explain) in which no @token variable is declared.
In this case I would suggest using the sp_executesql stored proc instead on EXEC ( ) , as this allows you to pass variables into the built up statement.
Try something like this (untested)
SET @strSQL = 'SELECT @innerToken=strToken FROM ' + @temp_table_name + ' WHERE rowindex=' + CAST(@index AS VARCHAR(10))
sp_executesql @strSQL, N'@innerToken VARCHAR(15) OUTPUT', @innerToken = @token
Bruce Duncan, CP#9088, CPUA 0xA1EE, Sonork 100.10030 Blackadder: Baldrick, have you no idea what irony is? Baldrick: Yeah, it's like goldy and bronzy only it's made of iron.
|
|
|
|
|
Thanks, i wished i brought this to you earlier.
norm
|
|
|
|
|
Is it possible to SELECT,ONLY from column name?I have some tables that their column name could be anything and I want their NAME only in my program,not value in thei ROW.Is that possible?
Mazy
"And the carpet needs a haircut, and the spotlight looks like a prison break
And the telephone's out of cigarettes, and the balcony is on the make
And the piano has been drinking, the piano has been drinking...not me...not me-Tom Waits
|
|
|
|
|
I'm not sure if I understood you right.
Maybe you mean how to get the columnnames in you table. Try DESCRIBE tablename
--
karl
|
|
|
|
|
I am attempting to create an ASP.NET application which constantly accesses a database, from all the examples I have seen in the MSDN documentation it says that the connections should be opened and closed for each transactions. However I would like to lock the records that are shown on the screen so that they cannot be edited by another instance. I cannot find any examples of how to: -
1) Keep the connection open the whole time.
2) Pass the DAL object between ASP.NET pages so that I can have multiple pages containing the data.
Chris
|
|
|
|
|
I want to run stored procedure with some paraneters from my asp.net application.I use this code but I got run=time error at the last line:
SqlConnection conn = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
SqlParameter[] parameters =
{
new SqlParameter("@Table", SqlDbType.NVarChar , 50),
new SqlParameter("@ID" , SqlDbType.BigInt , 8)
};
parameters[0].Value = Page.Request.Params["Cat"];
parameters[1].Value = int.Parse(Page.Request.Params["id"]);
SqlCommand command = new SqlCommand();
foreach (SqlParameter parameter in parameters)
{
command.Parameters.Add( parameter );
}
command.Connection = conn;
command.CommandType = System.Data.CommandType.StoredProcedure;
command.CommandText = "sp_Votes_GetVotes";
if(conn.State == System.Data.ConnectionState.Closed)
conn.Open();
SqlDataReader dr = command.ExecuteReader();
The error is:
Error converting data type nvarchar to bigint.
Mazy
"And the carpet needs a haircut, and the spotlight looks like a prison break
And the telephone's out of cigarettes, and the balcony is on the make
And the piano has been drinking, the piano has been drinking...not me...not me-Tom Waits
|
|
|
|
|
Hiya getting a message saying "Database error - There were no columns specified to retrive." when using these 2 lines:
SqlString = "DELETE * from Branches WHERE Branch_No LIKE '4%' ";
OR
SqlString = "INSERT INTO Branches (Branch_No,Branch_Name) VALUES ('6','Derry') ";
But Branch_No is the first column and Branch_Name is the second..
Am I using the statements wrong??
Thanks,
grahamoj.
|
|
|
|
|
DELETE statement do not need * from.Change it to:
DELETE Branches WHERE Branch_No LIKE '4%'
For second one,Whats the type of columns?
Mazy
"And the carpet needs a haircut, and the spotlight looks like a prison break
And the telephone's out of cigarettes, and the balcony is on the make
And the piano has been drinking, the piano has been drinking...not me...not me-Tom Waits
|
|
|
|
|
You don't need the "*" but you'll need "from" so the right statement will be (at least in SQL92)
DELETE FROM branches WHERE branch_no LIKE '4%'
--
karl
|
|
|
|
|
Yes.
Mazy
"And the carpet needs a haircut, and the spotlight looks like a prison break
And the telephone's out of cigarettes, and the balcony is on the make
And the piano has been drinking, the piano has been drinking...not me...not me-Tom Waits
|
|
|
|
|
Would you have a full list of all the statements( that is right syntax ) available to use for me??
What is the syntax for the INSERT??
Thanks for ur help.
grahamoj.
|
|
|
|