Database

4-Aug-03 8:52

I repost it because i'm afriad people will miss my "reply". I make it more clear about my problem here.

Thanks to Ray Cassick for his reply.

But i still have questions. I think i need to give you more information of my current problem.

HERE IS MY ORIGINAL DATABASE DESIGN:
For normal product, i have to store these attributes:
<code>- Prod_id
- Prod_title
- Prod_ISBN
- Prod_publisher
- Prod_cost
- Prod_retailprice
- Prod_description
- Category_id
- Prod_max (Max On Hand)
- Prod_min (Min On Hand)
- Shelf_id
- Status_id</code>

For consignment product, i have to store these attributes:
<code>- Cons_Prod_id
- Cons_Prod_description
- Cons_cost
- Cons_retailprice
- Cons_member_id [FK] (for the table that stores the info of the person who consign this item)</code>

For Invoice, i have:
<code>- Invoice_number
- Prod_id [FK]
- Cons_Prod_id [FK]
- Qty</code>

I think in the invoice table, it's not making sense to have both Prod_id and Cons_Prod_id in the table. Because if a product has Prod_id, it won't have Cons_Prod_id or vice versa. In this case, there will be wasting memory space.

So I decided to modify it and use inherit relationship in order to use ONE Prod_id ONLY in the invoice table.

However, if i use your suggestion, it just solves the problem on how to distinguish the normal and consignment product.

I still need a table to store those extra information such as Cons_member_id because some information won't exist in both products. (e.g. Consignment table does not need Prod_max and Prod_min, but need cons_member_id)

Then i stuck here now... OMG | :OMG:

I very appreciate your help to solve my problem!!
Thanks alot!!!!! Laugh | :laugh:

Cursor Error

Anonymous4-Aug-03 7:22

4-Aug-03 7:22

I have a problem: I receive a "Microsoft Cursor Engine error '80004005'
Data provider or other service returned an E_FAIL status." when querying my Access database. The query works as some records are returned. I can also eliminate the error by doing one of two things: I can remove an "order by" clause in the SQL statement so the results are not sorted and the error goes away. I can also eliminate the error by changing the data format of one of the fields in the database from Memo to text, (I am not sorting on that field). I have coded the cursor as "aduseclient" "adopenstatic" and know that it is not a permissions issue or failure to create the temp files on the client side. Everything I have seen on the large text field issues and "order by" clause says making it client side cursor should solve any issues. Any suggestions??

Close the connection?

Anonymous4-Aug-03 2:58

Re: Close the connection?

4-Aug-03 2:58

I have a form that uses a data adapter to query some data and populate some textboxes on the form. I then have a button that I hand coded to update the datasource and uses the textboxes as inputs for my parameters. My problem is that after i do the initial update it wont let me do another on if the data is changed without closing the form.

Here is the exception that is thrown:

Exception Text **************
System.Data.SqlClient.SqlException: The variable name '@weight' has already been declared. Variable names must be unique within a query batch or stored procedure.
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

Is there a way to reset this variable or reset the connection?

Rob Graham4-Aug-03 4:02

Rob Graham

4-Aug-03 4:02

Do Sqlcommand1.Parameters.clear() before running through the code that adds the parameters to the SqlCommand.Parameters collection, otherwise you are trying to add the same parameter name to the collection multiple times. It is ok to call clear on an empty parameters collection...

Genius may have its limitations, but stupidity is not thus handicapped. - Elbert Hubbard

Re: Close the connection?

Anonymous4-Aug-03 5:10

Re: Close the connection?

4-Aug-03 5:10

Thanks Rob that worked perfectly. I really appreciate it.

Rocky Moore4-Aug-03 4:02

Rocky Moore

4-Aug-03 4:02

Could you possibly supply some code? How did you generate the data adapter?

To reset a connection you can call Close() on the connection and then Open() again. There seems to be a problem though, you should not get that message in a normal data adapter call. What is the select and update strings for that data adapter?

Rocky Moore <><

Dynamic where Clause

dabuskol4-Aug-03 0:46

dabuskol

4-Aug-03 0:46

1. With the statement below I was able to retrieved records using the @jv parameter.

declare @jv varchar(100)
set @jv = 'abh3'

SELECT jv,day, interval/4, sm, aiu, sum(att), ans,usg, acblk
FROM c204
WHERE [day] between '7-21-2003' and '7-22-2003'
and jv = COALESCE(@jv,jv)
GROUP BY jv,day,interval/4,sm,aiu,ans,usg,acblk
ORDER BY jv,day,interval/4,sm,aiu,ans,usg,acblk
GO

2. What is the solution if you have two value in one parameter like the example below, Does anyone know the answer. Please HELP Confused | :confused:

I'm using it in my stored procdure.

declare @jv varchar(100)
set @jv = 'abh3','hal3'

SELECT jv,day, interval/4, sm, aiu, sum(att), ans,usg, acblk
FROM c204
WHERE [day] between '7-21-2003' and '7-22-2003'
and jv = COALESCE(@jv,jv)
GROUP BY jv,day,interval/4,sm,aiu,ans,usg,acblk
ORDER BY jv,day,interval/4,sm,aiu,ans,usg,acblk
GO

Dabuskol

Re: Dynamic where Clause

Rocky Moore4-Aug-03 4:35

Rocky Moore

4-Aug-03 4:35

dabuskol wrote:
set @jv = 'abh3','hal3'

Not quite sure what you are doing here. SQL Server does not support arrays so it does not seem possible you will have two values in one parameter. You can break them up manually and then build the query and execute it dynamically.

Rocky Moore <><

Re: Dynamic where Clause

Peet Schultz5-Aug-03 5:57

Peet Schultz

5-Aug-03 5:57

Hi There

Looks like you will have to revert to Dynamic sql. Build up the statement as required and then excecute.
This is normally a bit slower than straight sql but should be able to do the trick.

Regards

Peet Schultz

USE [PUBS]

DECLARE @LAST_NAME NVARCHAR(100)
DECLARE @SQL NVARCHAR(1000)

SET @LAST_NAME = '(''White'',''Green'')'

SET @SQL = 'SELECT * FROM AUTHORS WHERE [AU_LNAME] IN' + @LAST_NAME

EXEC (@SQL)

Re: Dynamic where Clause

dabuskol5-Aug-03 18:27

dabuskol

5-Aug-03 18:27

Thank you for your help. my stored procedure is working fine and so far the speed of retrieving doesn't affect much. Hope to hear from you guys in the future...

Good Luck Laugh | :laugh:

dabuskol

mozooo

Anonymous3-Aug-03 22:36

3-Aug-03 22:36

mozooo neda

Re: mozooo

Anonymous3-Aug-03 22:38

UpdateCommand with Parameter

3-Aug-03 22:38

pasokh mozooo

oledbCommand

betengan3-Aug-03 15:13

betengan

3-Aug-03 15:13

i want to make an oledbCommand with a variable as in sql @variable..SELECT *Table From Table WHERE Column=(thatvariable),how can i declare this to use in my code, am using Microsoft Access

Re: oledbCommand

neda3-Aug-03 22:33

neda

3-Aug-03 22:33

neda

Re: oledbCommand

K4reem5-Aug-03 3:59

K4reem

5-Aug-03 3:59

thx neda for replying Rose | [Rose]

.. lol it was a good reply so Eek! | :eek:

.. my question was about how to declare an oledb parameter as in SELECT *Table From Table WHERE Column=(parameter),as declared in sql we use "@" b4 the parameter i was just asking wat i ll declare in ole instead of that "@". Confused | :confused:

Anonymous3-Aug-03 8:42

3-Aug-03 8:42

Please go easy on me -I am New.

I created a SqlCommand and create a text update query such as:

update part set weight = '1' where part_id = 'test part'

I then created a button on my form and applied the following code:

Private Sub Button_load_it_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button_load_it.Click

Me.SqlConnection1.Open()
Me.SqlCommand1.ExecuteNonQuery()
Me.SqlConnection1.Close()

End Sub

this works fine.

I now want to use a parameter for my weight input instead of the value '1' as in my query. The parameter will be supplied from a textbox on my form.

I cant seem to grasp the idea and nobody seems to be able to help me. I have read various books but none seem to describe the simple thing I am trying to accomplish.

Thanks , chris

ChiYung3-Aug-03 9:13

3-Aug-03 9:13

Show your new SQL Command string after you modified it for using parameter.

Arjan Einbu3-Aug-03 12:06

Arjan Einbu

3-Aug-03 12:06

Something like this?

<br />
SqlCommand cmd = new SqlCommand("SELECT some_column FROM some_table WHERE another_column = @some_value");<br />
cmd.Parameters.Add("@some_value", your_parameter);<br />
cmd.ExecuteNonQuery();<br />

(You put in your parameter in the second line...)

Anonymous3-Aug-03 12:21

3-Aug-03 12:21

My parameter is coming from a textbox as its input. Is the "your_parameter" where i refererence the textbox?

Anonymous3-Aug-03 13:33

How to create "inheritance" relationship in Ms Access?

3-Aug-03 13:33

This is what I tried to add and it caused and exception

Me.SqlCommand1.Parameters.Add("@WEIGHT", SqlDbType.Decimal)
Me.SqlCommand1.Parameters("@WEIGHT").Value = Me.TextBox_SBR_Runtime.Text

Me.SqlConnection1.Open()
Me.SqlCommand1.ExecuteNonQuery()
Me.SqlConnection1.Close()

The exception occured on this line:

Me.SqlCommand1.ExecuteNonQuery()

ChiYung3-Aug-03 8:34

Re: How to create "inheritance" relationship in Ms Access?

3-Aug-03 8:34

Hi all,

I would like to know how to create a inheritance relationship in Ms Access 2000.
I want to create a database for the product in a bookstore. But they have 2 types of products: Normal and consignment.

Each of the products will have a product ID. Since i need to have another relationship to the "invoice" table, I need to create this inheritance so that the invoice table can "connect" to all products.

e.g.

Normal Table:
*Prod_id
title...etc.

Consignment Table:
*Prod_id
title....etc.

Parent Table for product:
*Prod_id

Invoice Table:
*Invoice_id
*Prod_id
Qty...etc.

I cannot merge two products into a table because they carry different information.

Does anyone know????? thank!!!!

Note: Normal product and consignment product cannot be the same product, so the design that the "invoice" table carries the Normal_Prod_id and Consign_Prod_id DO NOT work in this case....i don't want to waste database space.

Ray Cassick3-Aug-03 9:40

Ray Cassick

3-Aug-03 9:40

ChiYung wrote:
I cannot merge two products into a table because they carry different information.

I don't buy that at all... If this is a book store then most certainly thier products will have some attributes that are the same. They are all books and thus have attributes that are the same.

The rule is that all consignments are products, but not all products are consignment.

Create a general table that will hold ALL the common data about each 'product' (title, ISBN, etc...) then create secondary tables for those attributes that specificaly make them a 'Normal'or 'Consignment' type product. It might just be simpler, depending upon how many attributes that make them different, to just keep it all in one table and populate fields as needed, leaving some null wheer they don't apply.

Paul Watson wrote:
"At the end of the day it is what you produce that counts, not how many doctorates you have on the wall."
George Carlin wrote:
"Don't sweat the petty things, and don't pet the sweaty things."
Jörgen Sigvardsson wrote:
If the physicists find a universal theory describing the laws of universe, I'm sure the a**hole constant will be an integral part of that theory.

Re: How to create "inheritance" relationship in Ms Access?

ChiYung3-Aug-03 9:47