Posted by david on 22. October 2008 20:12
moving along …
test 2 - 3 links - user account transactions
I had some trouble with the control … took me a while to realize that the grid view doesn’t respond to simple click … that’s why the Select is in there to the left – a property of the grid view.
But – the data is moving … simple links & parameters are working.
shite – just realized it’s working on my local box, not the server.
mmm publish … ok it works.
now what.
Comments: 52
Filed Under:
Tags:
Posted by david on 22. October 2008 10:44
Tommy Emmanuel, Mombasa, Only
Tommy Emmanuel, Endless Road, Windy & Warm
Trace Bundy, Adapt, Patanga
Trace Bundy, Adapt, Canon
Adam Rafferty, Gratitude, America
Comments: 28
Filed Under:
Tags:
Posted by david on 22. October 2008 09:27
creating sample data is a pain in the arse …
then I need to actually fill the transactions table with my bogus data. retrace steps:
- create a sample set of users, took a list of 300 names & emails from the AdventureWorks db
- create a sample set of accounts for those users.
- Each user owns their own set of accounts and are completely variable – in production.
- So, to get some test data in there, without typing 1200 unique items, I created 4 basic accounts – a cable account, a creditcard, an electric account, and a waste disposal account.
- I inserted those into the accounts table, prefacing the account names with the userid. Result: 1200 unique accounts, but consistent enough that I might notice problems
- Finally, create transactions for each account.
- again in Excel, built a list of 33 transactions, 4 to 8 transactions per each of the 4 account types noted above. Covers a few months of activity, varying dates, varying balances.
-
To get these transactions into the db, I needed to massage the import table a little, assigning a code to the transactions representing the types of accounts. I built a foreign key of sorts in the Accounts table, using the optional Account Number field as a temporary bucket. This was so that I could Join the Accounts table to the transactionSample table to feed the Transactions table insert. There was probably a way to do this without the intermediate step, but I was at a loss.
1: UPDATE [Accounts]
2: SET AccountNumber =
3: CASE
4: WHEN (AccountName LIKE '%_CableTelevision') THEN 1
5: WHEN (AccountName LIKE '%_CreditCard') THEN 2
6: WHEN (AccountName LIKE '%_ElectricUtilities') THEN 3
7: WHEN (AccountName LIKE '%_WasteDisposal') THEN 4
8: END
-
-
Now I’ve got the accounts table linked to the transactionsample table, i can run an insert such that for every account, a complete set of sample transactions are added to the transactions table, linked appropriately to the unique accounts and the userID that the account was attached to:
1: INSERT INTO billz.Transactions
2: (
3: UserID
4: ,AccountID
5: ,Amount
6: ,Note
7: ,UserDate
8: )
9: SELECT
10: Accounts.UserID
11: ,Accounts.AccountID
12: ,transactionSample.Amount
13: ,transactionSample.Note
14: ,transactionSample.UserDate
15: FROM
16: billz.Accounts JOIN dbo.transactionSample ON
17: Accounts.AccountNumber = transactionSample.AccountCode
Comments: 1
Filed Under:
Tags:
Posted by david on 22. October 2008 05:39
Well - ok – back to the books … following Apress p503, which recommends learning to hardcode everything before using the controls ASP provides.
This is a sample page:
pageload creates a connection object, and label shows state – which is closed just yet.
hit the connect button, connection object created, connection is opened, label is changed to show open. Connection is closed, label is appended with new status.
worked locally, uploaded to server. Didn’t work there cuz I had to get host to make folder a webapp.
now it works.
code behind:
1: public partial class testRead : System.Web.UI.Page
2: {
3: protected void Page_Load(object sender, EventArgs e)
4: {
5: string connectionString = WebConfigurationManager.ConnectionStrings["egertonConnectionString"].ConnectionString;
6: SqlConnection myConnection = new SqlConnection(connectionString);
7: lblInfo.Text = "<br /><b>Connection Is:</b> " +
8: myConnection.State.ToString();
9: }
10: protected void Button1_Click(object sender, EventArgs e)
11: {
12: string connectionString = WebConfigurationManager.ConnectionStrings["egertonConnectionString"].ConnectionString;
13: SqlConnection myConnection = new SqlConnection(connectionString);
14: try
15: {
16: //try open connection
17: myConnection.Open();
18: lblInfo.Text = "<br /><b>Connection Is:</b> " +
19: myConnection.State.ToString();
20: }
21: catch (Exception err)
22: {
23: // handle an error
24: lblInfo.Text = "Error reading the database. ";
25: lblInfo.Text += err.Message;
26: }
27: finally
28: {
29: // either way, make sure its closed Even if unsuccessful open
30: myConnection.Close();
31: lblInfo.Text += "<br /><b>Now Connection Is:</b> ";
32: lblInfo.Text += myConnection.State.ToString();
33: } } }
Now need to actually get some data.
So create new page, testRead1. Create a connection, load a proc (testReadUsers) with SqlCommand, load results into DataReader. Works. Got a dropdown list that shows all users. Unfortunately, I only had 1 user in my DB.
1: public partial class testRead1 : System.Web.UI.Page
2: {
3: private string connectionString =
4: WebConfigurationManager.ConnectionStrings["egertonConnectionString"].ConnectionString;
5:
6: protected void Page_Load(object sender, EventArgs e)
7: {
8: if (!this.IsPostBack)
9: {
10: FillUserList();
11: }
12: }
13: private void FillUserList()
14: {
15: lstUser.Items.Clear();
16:
17: // Define the Select statement.
18:
19: // Define the ADO.NET objects.
20: SqlConnection con = new SqlConnection(connectionString);
21:
22: // 1. create a command object identifying
23: // the stored procedure
24: SqlCommand cmd = new SqlCommand(
25: "testReadUsers", con);
26:
27: // 2. set the command object so it knows
28: // to execute a stored procedure
29: cmd.CommandType = CommandType.StoredProcedure;
30:
31: SqlDataReader reader;
32:
33: // Try to open database and read information.
34: try
35: {
36: con.Open();
37: reader = cmd.ExecuteReader();
38:
39: // For each item, add the username to the displayed
40: // list box text, and store the userID in the Value property.
41: while (reader.Read())
42: {
43: ListItem newItem = new ListItem();
44: newItem.Text = reader["userName"].ToString();
45: newItem.Value = reader["userID"].ToString();
46: lstUser.Items.Add(newItem);
47: }
48: reader.Close();
49: }
50: catch (Exception err)
51: {
52: lblResults.Text = "Error reading list of userNames. ";
53: lblResults.Text += err.Message;
54: }
55: finally
56: {
57: con.Close();
58: }
59: }
60:
61: protected void lstUser_SelectedIndexChanged(object sender, EventArgs e)
62: {
63: }
Need some test data. Opened up AdventureWorks. Created a view on the person.contacts table:
1: ALTER VIEW [dbo].[ExportNames]
2: AS
3: SELECT ContactID AS UserID, EmailAddress AS UserEmail, LastName + SUBSTRING(FirstName, 1, 3) AS UserName, 'PASSWORD' AS Password
4: FROM Person.Contact
5: WHERE (ContactID < 300)
Imported that view into my hosted db. Then INSERTed that view into my User table.
Now I need Account records for each of those users. the following takes each userid that I just entered, adds 4 accounts per, prefixing the account name with the userid, so’z I can keep track of sht later on. this was a small pain in the ass, this was. But now I’ve 1200 records in the accounts table.
1:
2: DECLARE @sampleAccounts table
3: (
4: sampleAccountName varchar(50),
5: sampleRecurranceFlag bit,
6: sampleStatusFlag bit,
7: sampleDateCreatedUser smalldatetime,
8: sampleBalance money
9: )
10: INSERT INTO @sampleAccounts
11: (
12: sampleAccountName,
13: sampleRecurranceFlag,
14: sampleStatusFlag,
15: sampleDateCreatedUser,
16: sampleBalance
17: )
18: VALUES
19: ('CableTelevision','True','True','1/15/2008',-150.00)
20:
21: INSERT INTO @sampleAccounts
22: (
23: sampleAccountName,
24: sampleRecurranceFlag,
25: sampleStatusFlag,
26: sampleDateCreatedUser,
27: sampleBalance
28: )
29: VALUES
30: ('ElectricUtilities','True','True','3/15/2008',-50.00)
31:
32: INSERT INTO @sampleAccounts
33: (
34: sampleAccountName,
35: sampleRecurranceFlag,
36: sampleStatusFlag,
37: sampleDateCreatedUser,
38: sampleBalance
39: )
40: VALUES
41: ('WasteDisposal','True','True','1/15/2008',-25.00)
42:
43: INSERT INTO @sampleAccounts
44: (
45: sampleAccountName,
46: sampleRecurranceFlag,
47: sampleStatusFlag,
48: sampleDateCreatedUser,
49: sampleBalance
50: )
51: VALUES
52: ('CreditCard','True','True','1/15/2008',-9550.00)
53:
54: ----------------------------------------------------------------
55: DECLARE @ValidUserList table
56: ( validUserID int )
57:
58: INSERT INTO @ValidUserList (validUserID)
59: SELECT userID FROM billz.Users WHERE userID <> 1000
60:
61: DECLARE @ValidUserIDcount int
62: SET @ValidUserIDcount = @@ROWCOUNT
63:
64: ----------------------------------------------------------------
65: DECLARE @tempUserId INT
66: SET @tempUserID = 0
67:
68: DECLARE @anotherCounter int
69: SET @anotherCounter = 0
70:
71:
72: SELECT TOP 1 @tempUserID=validUserID from @ValidUserList
73: WHILE @anotherCounter < @ValidUserIDcount
74: BEGIN
75: SET @anotherCounter = @anotherCounter + 1
76: INSERT INTO billz.Accounts
77: (
78: AccountName,
79: RecurranceFlag,
80: StatusFlag,
81: DateCreatedUser,
82: Balance
83: )
84: SELECT
85:
86: (CONVERT(char(6),@tempUserID) + '_' + sampleAccountName) as AccountName,
87: sampleRecurranceFlag as RecurranceFlag,
88: sampleStatusFlag as StatusFlag,
89: sampleDateCreatedUser as DateCreatedUser,
90: sampleBalance as Balance
91: FROM @sampleAccounts
92:
93:
94: SELECT TOP 1 @tempUserID=validUserID from @ValidUserList
95: WHERE validUserID > @tempUserID
96: END
Test1 – ReadData into gridview, parameterized by dropdown.
finally got the grid view working … have a new testpage with drop down userlist, linked to account list in a grid view … pretty quick, too, it looks like.
I think that my initial problems with the gridview were due to my datascheming the procedure. when copied & deleted old billz. procedure, using dbo, the datagrid setup process “saw” the procedure & allowed me to walk through editing columns.
final code for the page:
1:
2: public partial class testRead1 : System.Web.UI.Page
3: {
4: private string connectionString =
5: WebConfigurationManager.ConnectionStrings["egertonConnectionString"].ConnectionString;
6:
7: protected void Page_Load(object sender, EventArgs e)
8: {
9: if (!this.IsPostBack)
10: {
11: FillUserList();
12: }
13: }
14:
15: private void FillUserList()
16: {
17: System.Text.StringBuilder StringForUserList = new System.Text.StringBuilder();
18: StringForUserList.Length = 0;
19:
20: lstUser.Items.Clear();
21:
22: SqlConnection con = new SqlConnection(connectionString);
23:
24: SqlCommand cmd = new SqlCommand(
25: "testReadUsers", con);
26:
27: cmd.CommandType = CommandType.StoredProcedure;
28:
29: SqlDataReader reader;
30:
31: try
32: {
33: con.Open();
34: reader = cmd.ExecuteReader();
35:
36: // For each item, add the author name to the displayed
37: // list box text, and store the unique ID in the Value property.
38: while (reader.Read())
39: {
40: StringForUserList.Append(reader["userName"].ToString());
41: StringForUserList.Append(" - ");
42: StringForUserList.Append(reader["userID"].ToString());
43: // string userNameAndID = StringForUserList.ToString();
44: ListItem newItem = new ListItem();
45: newItem.Text = StringForUserList.ToString();
46:
47: newItem.Value = reader["userID"].ToString();
48: lstUser.Items.Add(newItem);
49: StringForUserList.Length = 0;
50: }
51: reader.Close();
52: }
53: catch (Exception err)
54: {
55: lblResults.Text = "Error reading list of userNames. ";
56: lblResults.Text += err.Message;
57: }
58: finally
59: {
60: con.Close();
61: }
62: }
63: }
Comments: 0
Filed Under:
Tags:
Posted by david on 22. October 2008 05:01
trying to figure out exactly what sort of memory i have in my box & don’t feel like crawling around to pull a stick:
Auslogics System Info Freeware
that wouldn’t run on WS08
but this will:
“HWiNFO (dos) and HWiNFO32™ are professional hardware information and diagnostic tools supporting latest components, industry technologies and standards. Both tools are designed to collect and present the maximum amount of information possible about computer's hardware which makes them suitable for users searching for driver updates, computer manufacturers, system integrators and technical experts as well. Retrieved information is presented in a logical and easily understandable form and can be exported into various types of reports.” freeware source
-
Memory
-
Total Memory Size: 1 GBytes
-
Total Memory Size [MB]: 1024
-
Row: 0 - 512 MB PC3200 DDR-SDRAM Mushkin (Ramtron) Mushkin (991093)
-
Row: 1 - 512 MB PC3200 DDR-SDRAM Mushkin (Ramtron) Mushkin (991093)
GIGABYTE GA-K8NSC-939
Comments: 30
Filed Under:
Tags:
Posted by david on 22. October 2008 00:48
calling stored procs via ado.net
ObjectDataSource
Comments: 18
Filed Under:
Tags:
Posted by david on 21. October 2008 18:57
My friend, Rick, set me straight on the database design … steering me away from using xml columns in my tables. So, I will stop worrying about the db side so much … just get the tables structured well, in an orthodox manner.
I will use ADO.NET to access it. 3 layers, data layer, business logic where I’ll implement the ADO stuff &, eventually, the fancier projection stuff … and a front end that simply talks to the biz layer. Actually, 4, if you count the procs as it’s own layer.
Got tables set up for the most basic functionality: User, Company, Account. Got some test data in there too. and a proc to produce a basic view of the data at the data layer:
1: -- =============================================
2: -- Author: david
3: -- Create date: 2008-10-20
4: -- Description: Test - Simple read from
5: -- Accounts table, JOINED, Companies, JOINED, Users
6: -- =============================================
7: CREATE PROCEDURE billz.testReadAccounts
8:
9: AS
10: BEGIN
11: SET NOCOUNT ON;
12: -----------------------------------------
13: SELECT
14: billz.Users.UserID
15: ,billz.Users.UserName
16: ,billz.Users.UserEmail
17: ,billz.Accounts.AccountID
18: ,billz.Accounts.AccountName
19: ,billz.Accounts.Balance
20: -----------------------------------------
21: FROM
22: billz.Accounts
23: INNER JOIN
24: billz.Companies ON billz.Accounts.CompanyID = billz.Companies.CompanyID
25: INNER JOIN
26: billz.Users ON billz.Companies.UserID = billz.Users.UserID
27: END
Build a basic grid view, and add a datareader thingie to fill it:
- add a SQLDatasource
- choose the dataconnection that I finally got working above
- specify the stored procedure testReadAccounts.
- Error:
- So,
- go into the procedure code, from vstudio … The proc is there.
- execute the SELECT within
- & it returns the expected data
- So, try again to configure the datasource, using same steps – got same error.
- is it some sort of naming thing? the billz schema?
Comments: 51
Filed Under:
Tags:
Posted by david on 20. October 2008 15:35
LG Front Load WM1815CS
error code LE flashing on the display
machine stops at the beginning of a cycle with water in the bin
forum discussion of similar problem
Links to the LG Service Manual download site … “ a companion site for the LG CSA training site “ it has complete service manuals, even videos for some things …schematics, disassembly manuals – for most products from TVs to washers .. the link is to an IP address, so is not intentionally public … found it here
I ran into another site that was charging for this stuff .
as of now, the problem was resolved by pulling the plug for a little while. good thing, cuz the manual says call service for this error code.
Comments: 43
Filed Under:
Tags: