Database

15-Jul-04 23:47

what is the syntax to perform SELECT query on a stored View in an Access Data Base?

Colin Angus Mackay15-Jul-04 23:57

15-Jul-04 23:57

I am not too familiar with MS Access, but I wouldn't have thought it would be any different from a table:

SELECT * FROM view_name WHERE blah=blah

SELECT * FROM table_name WHERE blah=blah

"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell

The Second EuroCPian Event will be in Brussels on the 4th of September

Can't manage to P/Invoke that Win32 API in .NET? Why not do interop the wiki way!

blankg16-Jul-04 0:27

16-Jul-04 0:27

I tried this way ,it didnt work (it works fine with sql server) .
thanks anyway , does anyone have a diffrent way?

Mekong River17-Jul-04 4:53

Mekong River

17-Jul-04 4:53

I think it is the same syntax as the above submitted comment. It might be the same between a view in sql server and view in access. Because view in access is using access probject and view is store in the sql server. But some issue you should consern about such as:

1. Does your system connect to sql server database every time? and make sure that it is not interrup.
2. When create database object (table, query, view...) try to use naming convention. (Ex: if you want to create table name "Customers", name it "tblCustomers" instead of "Customers). Naming convention help you to identify the right database object in your SELECT sql statement.
3. Use bracket if your database object name has a space in the middle. (Ex: you have table named "Customer name", in SELECT statement use the bracket to [Customer name], SELECT * FROM [Customer name])

Check it again and told us if it still have a probme. Post an error message when you challenges with it again.

Roath Kanel
APO-CEDC
Save Children Norway-Cambodia Office

blankg17-Jul-04 9:51

17-Jul-04 9:51

Problem solved, thanks a lot to both u guys.

Mekong River17-Jul-04 18:18

Mekong River

17-Jul-04 18:18

How could you solve it? Do you found any other solution to solve it or get the solution from our idea? I just want to know, if our idea is right then I will help the other people who have the same problem like you. If it is wrong I will try to research on it. Any way, I still try all of my best to help you and other people. Cool | :cool:

Roath Kanel
APO-CEDC
Save Children Norway-Cambodia Office

blankg17-Jul-04 21:35

Input string was not in a correct format

17-Jul-04 21:35

Using the brackets solved the exception in my query.

DotNet15-Jul-04 18:27

DotNet

15-Jul-04 18:27

Hello,

In my SQL Server, Table named Hardware, Attributes - HardwareID(VarChar), Price(money). When I want to get the edit text boxes, I declare as
Dim hprice As String = Ctype(e.Item.Cells(4).Controls(0), TextBox).Text

What can I do to stop the error below?

----------------------------------------------------------------------
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.ArgumentException: System.FormatException: Input string was not in a correct format. at System.Number.ParseDecimal(String s, NumberStyles style, NumberFormatInfo info) at System.Decimal.Parse(String s, NumberStyles style, IFormatProvider provider) at System.Convert.ToDecimal(String value, IFormatProvider provider) at System.String.System.IConvertible.ToDecimal(IFormatProvider provider) at System.Convert.ToDecimal(Object value) at System.Data.Common.DecimalStorage.Set(Int32 record, Object value) at System.Data.DataColumn.set_Item(Int32 record, Object value)Couldn't store <> in Price Column. Expected type is Decimal.

Concerned

Re: Input string was not in a correct format

Colin Angus Mackay15-Jul-04 22:19

Re: Input string was not in a correct format

15-Jul-04 22:19

The error is not occuring on the line you have specified.

From the call stack, it looks like you are attempting to insert an empty string (I am assuming this is coming from the text box). Is this so? If that is the case then it is cannot be converted to a valid decimal format. You need to check for the user inputting invalid information before attempting to insert it into the database.

If you need to insert a NULL valid into the field use System.DBNull.Value

Does this help?

Colin Angus Mackay16-Jul-04 2:22

Re: Input string was not in a correct format

16-Jul-04 2:22

From the email that you sent me the appropriate line appears to be:

UpdateCommand.Parameters.Add("@Price", SqlDbType.Decimal, 9).Value = hprice

But only if the textbox used to populate hprice (which you mentioned in your previous post) contains a value that cannot be interpreted as a decimal.

As I've already mentioned, you need to perform validation on the user input to ensure that invalid and nonsense values are rejected properly. As this is a price, I would guess that the only valid characters are the numeric characters and the decimal point (or comma - depending on your locality)

One easy way around this to perform the conversion yourself, then pass the converted value as the parameter. Wrap the conversion in a try catch block so that if the input from the user is incorrect you can catch it and report to the user their input is incorrect.

Decimal convertedHPrice = Convert.ToDecimal(hprice);

UpdateCommand.Parameters.Add("@Price", SqlDbType.Decimal, 9).Value = convertedHPrice

Does this help?

DotNet16-Jul-04 3:57

DotNet

16-Jul-04 3:57

Colin...... the code is here...

Sub DataGrid_Update(Sender As Object, E As DataGridCommandEventArgs)

' update the database with the new values

' get the edit text boxes
Dim id As String = (CType(e.Item.Cells(2).Controls(0), TextBox)).Text
Dim lname As String = (CType(e.Item.Cells(3).Controls(0), TextBox)).Text

'********************** Problem is here below here...
Dim hprice As String = (Ctype(e.Item.Cells(4).Controls(0), TextBox)).Text
Dim hdesc As String = (CType(e.Item.Cells(5).Controls(0), TextBox)).Text

Dim myConnection As New SqlConnection(ConnectionString)
Dim UpdateCommand As SqlCommand = new SqlCommand()
UpdateCommand.Connection = myConnection

If AddingNew = True Then
UpdateCommand.CommandText = "INSERT INTO Hardware(HardwareID, Name, Price, Description) VALUES (@HardwareID, @Name, @Price, @Description)"
Else
UpdateCommand.CommandText = "UPDATE Hardware SET HardwareID = @HardwareID, Name = @Name, Price = @Price, Description = @Description WHERE HardwareID = @HardwareID"
End If

UpdateCommand.Parameters.Add("@HardwareID", SqlDbType.Varchar, 5).Value = id
UpdateCommand.Parameters.Add("@Name", SqlDbType.VarChar, 50).Value = lname
'************************************** Problem is here below here...
UpdateCommand.Parameters.Add("@Price", SqlDbType.Money, 8).Value = hprice
UPdateCommand.Parameters.Add("@Description", SqlDbType.VarChar, 100).Value = hdesc
' execute the command
Try
myConnection.Open()
UpdateCommand.ExecuteNonQuery()

Catch ex as Exception
Message.Text = ex.ToString()

Finally
myConnection.Close()

End Try

' Resort the grid for new records
If AddingNew = True Then
DataGrid1.CurrentPageIndex = 0
AddingNew = false
End If

' rebind the grid
DataGrid1.EditItemIndex = -1
BindGrid()

End Sub

can i get some operation infomation from SQLsrv2k ?

fu015-Jul-04 15:58

fu0

15-Jul-04 15:58

i thought SQLsrv2k had a interface for 3rd part software to gain operation infomation. For example, while a record is inserted into a table, the 3rd part software might get this information.
also it might like the windows message mechanism, any operation may produce a message to windows or others.

is this possible ?

Re: can i get some operation infomation from SQLsrv2k ?

RichardGrimmer15-Jul-04 23:28

RichardGrimmer

15-Jul-04 23:28

DO you mean TRIGGERS? Can set one up On insert / update / delete which can then execute code inside SQL Server, or do you mean the ability for a third party app to recieve change notification?

"Now I guess I'll sit back and watch people misinterpret what I just said......"
Christian Graus At The Soapbox

Re: can i get some operation infomation from SQLsrv2k ?

fu017-Jul-04 15:39

fu0

17-Jul-04 15:39

Triggers might be a solution for this. But it need to modify the database. So i prefer a third party application to do this without modify the database.
i thought it can receive change notification like windows hook doing.
where can i start ? And is there aother way ?

Re: can i get some operation infomation from SQLsrv2k ?

RichardGrimmer19-Jul-04 5:40

RichardGrimmer

19-Jul-04 5:40

Think that you would struggle to do it - the 3rd party app would need to constantly poll the DB looking for changes, which at best would affect performance!

"Now I guess I'll sit back and watch people misinterpret what I just said......"
Christian Graus At The Soapbox

Is This Complex Update Possible?

perlmunger15-Jul-04 11:38

15-Jul-04 11:38

I am trying to take the result of a caculation of two fields in one table and place it into a field in another table? Is this possible?

I have two tables StoreItems and TicketItems. I need to get a unit cost from my StoreItems table by dividing the cost for an entire case (CaseCost) by the case count (CaseCount) and then placing that result into the Cost field of an item in the TicketItems table with a corresponding PLU (like an SKU). PLU and StoreID are the two fields in the tables that link them together. In a nutshell I need this:

StoreItems.Cost = TicketItems.CaseCost / TicketItems.CaseCount

Keep in mind that I need to run this on every record in the two tables so just passing in a StoreID and PLU is not enough. This has to be done across the board--that is, every TicketItems record needs to have its Cost column set when this has finished running.

Thank you.

-Matt

------------------------------------------

The 3 great virtues of a programmer:
Laziness, Impatience, and Hubris.
--Larry Wall

Colin Angus Mackay15-Jul-04 12:03

15-Jul-04 12:03

Off the top of my head the following should do what you want.

[EDIT] Oops. I made a mistake the Cost field you are updating is in the StoreItems table, the original query was updating the TicketItems table Blush | :O

[/EDIT]

UPDATE StoreItems
SET Cost = TicketItems.CaseCost / TicketItems.CaseCount
FROM TicketItems, StoreItems
WHERE TicketItems.PLU = StoreItems.PLU

You can get more information in your SQL Server books online, look up the index for UPDATE, UPDATE (described)

Does this help?

perlmunger15-Jul-04 12:21

15-Jul-04 12:21

Actually, no you were right the first time. The cost field is in the TicketItems table not the StoreItems table. So what did that query look like?

-Matt

------------------------------------------

The 3 great virtues of a programmer:
Laziness, Impatience, and Hubris.
--Larry Wall

Colin Angus Mackay15-Jul-04 12:29

15-Jul-04 12:29

Almost the same actually:

UPDATE TicketItems
SET Cost = TicketItems.CaseCost / TicketItems.CaseCount
FROM TicketItems, StoreItems
WHERE TicketItems.PLU = StoreItems.PLU

I'm now a bit confused, which table do the CaseCost and CaseCount items reside? If it is StoreItems then the query will look like this instead:

UPDATE TicketItems
SET Cost = StoreItems.CaseCost / StoreItems.CaseCount
FROM TicketItems, StoreItems
WHERE TicketItems.PLU = StoreItems.PLU

The second query assumes:
TicketItems.PLU
TicketItems.Cost

StoreItems.PLU
StoreItems.CastCost
StoreItems.CaseCount

PLU is the common field that joins the two tables.

This update will update every row in the TicketItems table.

Does this help? Or have I confused you in return?

perlmunger15-Jul-04 12:39

15-Jul-04 12:39

I think the second one is right. Let me try it out.

Thanks a lot for your help.

-Matt

------------------------------------------

The 3 great virtues of a programmer:
Laziness, Impatience, and Hubris.
--Larry Wall

perlmunger15-Jul-04 13:02

15-Jul-04 13:02

It worked and I understand it. You're a life saver!! Thank you!!

-Matt

------------------------------------------

The 3 great virtues of a programmer:
Laziness, Impatience, and Hubris.
--Larry Wall

Colin Angus Mackay15-Jul-04 13:07

15-Jul-04 13:07

perlmunger wrote:
It worked and I understand it.

Excellent. Big Grin | :-D

dishanf19-Jul-04 1:57

dishanf

19-Jul-04 1:57

Yes . you can update from one or more table ., Try this sql. may be want to change some col names. I think you get the idea.. Wink | ;)

UPDATE StoreItems
SET Cost = T.CaseCost / T.CaseCount
FROM StoreItems S INNER JOIN TicketItems T
ON S.PLU = T.PLU

HTH

D!shan