Adjust values in db base in the value modified on the form?

Hi everyone,

Here is the problem I am facing with. I have a form which has multiple fields including Price (read only), Discount(read/write), TotalSellPrice(read/write), Quantity(read/write) ... What I need to do is I need to adjust TotalSellPrice value if there was a new Discount value entered and vise versa. If both values have been changed I should use Discount value entered and calculate the TotalSellPrice. I am having hard time figuring the query out. Any thoughts or ideas in what direction should I go.

Thanks for your help!Hi everyone,

Here is the problem I am facing with. I have a form which has multiple fields including Price (read only), Discount(read/write), TotalSellPrice(read/write), Quantity(read/write) ... What I need to do is I need to adjust TotalSellPrice value if there was a new Discount value entered and vise versa. If both values have been changed I should use Discount value entered and calculate the TotalSellPrice. I am having hard time figuring the query out. Any thoughts or ideas in what direction should I go.

Thanks for your help!

Paste your query or the tables involved and explain what you want in new one?|||I don't have any query yet, I need to come up with the query that will check which value has been changed and do update approprietly.

Ex. #1 - Discount value is changed
Price is $100
Quantity was 0 (changed to) 2
Discount was 0% (changed to) 10%
TotalSellPrice $0 (hasn't been changed) -> I need to calculate it then. it becomes $180

Ex. #2 - Discount and TotalSellPrice values are changed
Price is $100
Quantity was 0 (changed to) 2
Discount was 0% (changed to) 10%
TotalSellPrice $0 (changed to) $150
Then I need to calculate TotalSellPrice since both Discount and TotalSellPrice values have been changed. TotalSellPrice becomes $180

Ex. #3 - TotalSellPrcie value is changed
Price is $100
Quantity was 0 (changed to) 2
Discount was 0% (hasn't been changed) -> I need to calculate it. Discount is 25%
TotalSellPrice $0 (changed to) $150

Ex. #4 - TotalSellPrice value is changed to number bigger then it's original one then I have to keep Discount at 0% don't go negative
Price is $100
Quantity was 0 (changed to) 2
Discount was 0% (hasn't been changed) -> I need to keep Discount at 0%
TotalSellPrice $0 (changed to) $250|||I don't have any query yet, I need to come up with the query that will check which value has been changed and do update approprietly.

Whatever I make out from the above problem is, you want to adjust the data according to the changes in the other fields.I think you can easily do it at the front end and send the update query to the database according to its invoice no.
An user can see the store data and update it anytime by changing the different fields.You can do the neccessary calculation at the front end.And pass a simple update query to the database.That will do the trick..|||I think you can easily do it at the front end and send the update query to the database according to its invoice no.
The thing is it would not be efficient from a programming stand point, because I have to grab all the information from the database and store it somewhere in the session, then compare it to what user enters, then create a query dynamically instead of using stored procedure. I need a back end query that will handle all these stuff for me.

Any idea?|||Any idea?

And you need not have to create a stored procedure for such a small calculation.I think so...;)|||Maybe I explained it incorrectly, I am sorry.
What I do I initially populate those fields with the default data from the database

Ex. - Initial page load
Price -> "$100"
Quantity -> "0"
Discount -> "0%"
TotalSellPrice -> "$0"

After user updates any of writable fields I do update and populate that form again but base on the rules I described before, I hope that explains everything.|||Maybe I explained it incorrectly, I am sorry.
What I do I initially populate those fields with the default data from the database

Ex. - Initial page load
Price -> "$100"
Quantity -> "0"
Discount -> "0%"
TotalSellPrice -> "$0"

After user updates any of writable fields I do update and populate that form again but base on the rules I described before, I hope that explains everything.
So...?? You can write the adjustment code in asp page,and simply update the record.Mind it you are not doing anything more than that..so I don't think on the point of efficiency ,use of stored procedure will make any difference...|||After user updates any of writable fields I do update and populate that form again but base on the rules I described before...You think it is efficient or good programming practice to make a call to the database every time a user changes a value on a form?

It's not, and you certainly wouldn't design scalable enterprise applications this way.

Your form should get complete recordsets from the database (even defaults), and should submit complete recordsets to the database.

And frankly, you don't have to store all the detail data to keep track of the new total. NewTotal = OldTotal - OldValue + NewValue. That is just three variables.

Address Searching (sp?)

I have a form that inputs an address (city/state/zip/etc.). Basically I
need to search our database for this address, prior to it being entered.
Problem in that people type St. vs. Street vs. St, Ave. vs. Avenue vs. Ave,
etc. Anyone have an affective algorithm for searching for comparable
addresses? Just curious if someone has run into/solved this problem
effectively.
Thanks,
JamesThe problem has been solved quite effectively by address correction software
vendors. It might be quite a task to write a proprietary set of functions to
do the same thing.
Don't know if this is feasible for you but you could have a separate field
to store the street type and then provide a drop-down list so that the user
doesn't have the option to input a variant (such as Ave. instead of Avenue)
"James" wrote:

> I have a form that inputs an address (city/state/zip/etc.). Basically I
> need to search our database for this address, prior to it being entered.
> Problem in that people type St. vs. Street vs. St, Ave. vs. Avenue vs. Ave
,
> etc. Anyone have an affective algorithm for searching for comparable
> addresses? Just curious if someone has run into/solved this problem
> effectively.
> Thanks,
> James
>
>

Address Searching (sp?)

I have a form that inputs an address (city/state/zip/etc.). Basically I
need to search our database for this address, prior to it being entered.
Problem in that people type St. vs. Street vs. St, Ave. vs. Avenue vs. Ave,
etc. Anyone have an affective algorithm for searching for comparable
addresses? Just curious if someone has run into/solved this problem
effectively.
Thanks,
James
The problem has been solved quite effectively by address correction software
vendors. It might be quite a task to write a proprietary set of functions to
do the same thing.
Don't know if this is feasible for you but you could have a separate field
to store the street type and then provide a drop-down list so that the user
doesn't have the option to input a variant (such as Ave. instead of Avenue)
"James" wrote:

> I have a form that inputs an address (city/state/zip/etc.). Basically I
> need to search our database for this address, prior to it being entered.
> Problem in that people type St. vs. Street vs. St, Ave. vs. Avenue vs. Ave,
> etc. Anyone have an affective algorithm for searching for comparable
> addresses? Just curious if someone has run into/solved this problem
> effectively.
> Thanks,
> James
>
>

Address Searching (sp?)

I have a form that inputs an address (city/state/zip/etc.). Basically I
need to search our database for this address, prior to it being entered.
Problem in that people type St. vs. Street vs. St, Ave. vs. Avenue vs. Ave,
etc. Anyone have an affective algorithm for searching for comparable
addresses? Just curious if someone has run into/solved this problem
effectively.
Thanks,
JamesThe problem has been solved quite effectively by address correction software
vendors. It might be quite a task to write a proprietary set of functions to
do the same thing.
Don't know if this is feasible for you but you could have a separate field
to store the street type and then provide a drop-down list so that the user
doesn't have the option to input a variant (such as Ave. instead of Avenue)
"James" wrote:
> I have a form that inputs an address (city/state/zip/etc.). Basically I
> need to search our database for this address, prior to it being entered.
> Problem in that people type St. vs. Street vs. St, Ave. vs. Avenue vs. Ave,
> etc. Anyone have an affective algorithm for searching for comparable
> addresses? Just curious if someone has run into/solved this problem
> effectively.
> Thanks,
> James
>
>

adding various fonts/weights to values in a string

HI
I am creating a form that needs to have strings of text in the text box.
The strings have numbers included and the numbers need to be a different
font. The form is built in a table, so splitting the row will not work.
Example:
Textbox 10 has the following value:
8. Fax Number
The number 8 needs to be arial narrow bold 6.96 and the fax number needs to
be arrial narrow 6.96
Any help would be appreciated.
Thank youHi Susan,
One of the ways to do what you need is to place a rectangle object right in
the textbox of the table. Then place two (or more) individual textboxed
inside of the rectangle that is inside of the table cell. You can set the
font weights on the individual textboxes at that point so:
"8" will be in its own textbox and the Fax Number data field in the other,
but they can be together within a single cell. This might add a little more
work, but it should solve the problem.
Rodney Landrum
"Susan R" <SusanR@.discussions.microsoft.com> wrote in message
news:B55B1631-A886-45A0-AFA5-5533CB87C0BE@.microsoft.com...
> HI
> I am creating a form that needs to have strings of text in the text box.
> The strings have numbers included and the numbers need to be a different
> font. The form is built in a table, so splitting the row will not work.
> Example:
> Textbox 10 has the following value:
> 8. Fax Number
> The number 8 needs to be arial narrow bold 6.96 and the fax number needs
> to
> be arrial narrow 6.96
> Any help would be appreciated.
> Thank you
>sql

adding sql db

hey i have a very simple form here:www.syscpupower.com

I would like to have the information from the form saved to a sql db with a time stamp.

here is the code i have for the page below:

"C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>"-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml" >"server"> "form1" runat="server">
  
"z-index: 108; left: 0px; position: absolute; top: 189px">  
"z-index: 109; left: 204px; width: 411px; position: absolute; top: 18px; height: 160px">"3" rowspan="3" style="text-align: center"> Please accept to view your detailed trip information.
 "Button1" runat="server" OnClick="Button1_Click" Style="z-index: 102; left: 322px; position: absolute; top: 261px" Text="Accept" Width="60px"> "Button2" runat="server" OnClick="Button2_Click" Style="z-index: 103; left: 392px; position: absolute; top: 261px" Text="Decline" Width="64px"> "Label1" runat="server" Style="z-index: 104; left: 254px; position: absolute; top: 198px" Text="Please Enter Your First and Last Name!" Width="273px"> "TextBox1" runat="server" Style="z-index: 105; left: 278px; position: absolute; top: 229px" Width="216px"> "RequiredFieldValidator1" runat="server" ControlToValidate="TextBox1" ErrorMessage="FirstName LastName" Font-Size="12px" Style="z-index: 106; left: 500px; position: absolute; top: 229px">* "ValidationSummary1" runat="server" Style="z-index: 110; left: 263px; position: absolute; top: 300px" Width="284px">

sorry, what exactly you want?

you want to know how to insert information to db?

or you want to know how to create db for it?

|||well i created the db, now i guess i need to findo out how to get the info from the text box into the database...|||

using (SqlConnection sc = new SqlConnection("Data Source=(local);Initial Catalog=AAAA;Integrated Security=SSPI;"))
{

string strSQL = "INSERT INTO tbXXXX(NAME) VALUES(TextBox1.text)
SqlCommand command = new SqlCommand(strSQL, sc);
command.Connection.Open();
command.ExecuteNonQuery()
}

|||

i have added the SQL database, THATS it...

I see the code that you have put here can you please explain the coding, how did you get this and where does it go in the coding. alsodi i need to create tables inside my db.

|||

Hey

You could double click button in design mode and add above code.

Above code follows those steps:

Create a connection -> build the sql -> create sql command ->open connection ->exec insert-> close command.

And to prevent Sql Inject you could choose sql parameter,it could be something like this:

SqlCommand cmd = new SqlCommand("select column1 from test where column1 = @.column1", conn);

cmd.Parameters.AddWithValue("@.column1", str_test);

If you are in trouble understanding the codeStarter Books might be helpful.

Adding Leading Zeros

I need to make sure that numbers returned form a SELECT have at least one digit before the decimal point. Right now I have the following SQL statement in a DTS

SELECT PIN, ROUND (SUM(AREA/43560), 2) AS Expr1
FROM table GROUP BY PIN ORDER BY PIN

This produces lines like this ...

1-0005 -01-001,6250.410000
1-0008 -01-001,940.810000
1-0010 -01-001,9.230000
1-0010 -01-001A,.730000
1-0010 -01-002,73.520000
1-0010 -01-003,.680000

I need the output to look like this (check lines 4 and 6) ...

1-0005 -01-001,6250.410000
1-0008 -01-001,940.810000
1-0010 -01-001,9.230000
1-0010 -01-001A,0.730000
1-0010 -01-002,73.520000
1-0010 -01-003,0.680000

Any ideas?

DavidHello,

when you use Oracle you can convert the sum to a char with a special format mask

SELECT PIN, TO_CHAR(ROUND (SUM(AREA/43560), 2), '0.99' AS Expr1

Hope that helps ?

Regards
Manfred Peter
(Alligator Company)
http://www.alligatorsql.com|||Oops. Forgot to mention that I'm running SQLServer 7. Haven't found a comparable function yet.

David|||Hello again,

that is important :)

SELECT PIN, CAST(ROUND (SUM(AREA/43560), 2) AS MONEY) AS Expr1

I am using the enterprise manager and it look ok ...

Hope this help ?

Regards
Manfred Peter
(Alligator Company)
http://www.alligatorsql.com|||When I tried

CAST(ROUND (SUM(AREA/43560), 2) AS MONEY)

I still got the following results

6250.4100
940.8100
9.2300
.7300
73.5200
.6800

:confused:

However, taking your lead, I tried

CAST(ROUND (SUM(AREA/43560), 2) AS CHAR)

and got

6250.410000
940.810000
9.230000
0.730000
73.520000
0.680000

:)

Works for me! Thanks!

David

Adding files and filegroups

Does SQL Server keep track of when new files and
filegroups are added or removed from a database, e.g., in
the form of a timestamp in the catalog? and how can a user
access this information?
Thank-you, Howardsp_helpdb will tell you more about the databases
sp_helpfilegroups returns the names and attributes of filegroups associated
with the current database
sysfiles : Contains one row for each file in a database. This system table
is a virtual table; it cannot be updated or modified directly.
Regards,
Dandy Weyn
MCSE, MCSA, MCDBA, MCT
www.dandyman.net
"Howard" <anonymous@.discussions.microsoft.com> wrote in message
news:07bc01c3d14e$04b438c0$a601280a@.phx.gbl...
> Does SQL Server keep track of when new files and
> filegroups are added or removed from a database, e.g., in
> the form of a timestamp in the catalog? and how can a user
> access this information?
> Thank-you, Howard|||I would assume this info would be in sysfiles or sysfilegroups. It's not
there, so I don't think it's possible to get from SQL Server.
But...
you CAN find the time a file was added.
Create date is kept by the OS for a file. Something like this would work:
xp_getfiledetails 'c:\temp\authors.txt'
But that would be the create date from an OS perspective. You would end up
with wrong info if for example you restored the database and overwrote
files, since they would then have new create dates...
or you could create your own polling program that periodically looks at the
relevant system tables mentioned above...
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Howard" <anonymous@.discussions.microsoft.com> wrote in message
news:07bc01c3d14e$04b438c0$a601280a@.phx.gbl...
> Does SQL Server keep track of when new files and
> filegroups are added or removed from a database, e.g., in
> the form of a timestamp in the catalog? and how can a user
> access this information?
> Thank-you, Howard|||This is essentially what I found; but it falls short of
what I need. Basically, I would like some sort of
notification (short of polling) since changes in the
file/filegroup structure would impact the usefullness of
component backup images that I may be keeping.
Thanks, Howard
>--Original Message--
>I would assume this info would be in sysfiles or
sysfilegroups. It's not
>there, so I don't think it's possible to get from SQL
Server.
>But...
>you CAN find the time a file was added.
>Create date is kept by the OS for a file. Something like
this would work:
>xp_getfiledetails 'c:\temp\authors.txt'
>But that would be the create date from an OS perspective.
You would end up
>with wrong info if for example you restored the database
and overwrote
>files, since they would then have new create dates...
>or you could create your own polling program that
periodically looks at the
>relevant system tables mentioned above...
>--
>Brian Moran
>Principal Mentor
>Solid Quality Learning
>SQL Server MVP
>http://www.solidqualitylearning.com
>
>"Howard" <anonymous@.discussions.microsoft.com> wrote in
message
>news:07bc01c3d14e$04b438c0$a601280a@.phx.gbl...
>> Does SQL Server keep track of when new files and
>> filegroups are added or removed from a database, e.g.,
in
>> the form of a timestamp in the catalog? and how can a
user
>> access this information?
>> Thank-you, Howard
>
>.
>

Adding datetime to database?

I have DateCreated with datetime datatype in my SQL Express 2005. I'd like to add the record to my Task table so I have a form in my ASPX and create a button event in my ASPX.CS here is the code

protected void Button_AddTask_Click(object sender, EventArgs e)
{
SqlDataSource newTask = new SqlDataSource();
newTask.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
newTask.InsertCommand = "INSERT INTO [Task] ([MemberAccountName], [Title], [Place], [TaskDetail],[DateCreated]) VALUES (@.MemberAccountName, @.Title, @.Place, @.TaskDetail,@.DateCreated)";
newTask.InsertParameters.Add("MemberAccountName", User.Identity.Name);
newTask.InsertParameters.Add("Title", TextBox_Title.Text);
newTask.InsertParameters.Add("Place", TextBox_Place.Text);
newTask.InsertParameters.Add("TaskDetail", TextBox_Detail.Text);
newTask.InsertParameters.Add("DateCreated",DateTime.Now.ToString());
newTask.Insert();
Response.Redirect("Default.aspx");
}

but I got this error

Arithmetic overflow error converting expression to data type datetime.
The statement has been terminated.

Any idea?
Sorry your guy, I think I found the problem...

In my SQL Express 2005 use the datetime format in DD/MM/YYYY

butDateTime.Now.ToString() giveMM/DD/YYYY

I found it by hardcoding "22/12/2005 00:00:00" and it works!!

So now how can I fix this??
|||

Check the other ways to use the add, there should be one that allows you to specify the parameter type. Tell it that it is a datetime, and pass in datetime.now (NOT datetime.now.tostring).

|||You can avoid date problems by always presenting your date in YYYYMMDD format to SQL Server.

In your original code, try this instead (add a format to the ToString method):

newTask.InsertParameters.Add("DateCreated",DateTime.Now.ToString("yyyyMMdd"));

|||Thank you everyone, now I can make it work

However there is a little problem with my Thai Buddhist year !!

There is 543 year different between Christ year and Buddhist year.

I can get the Datetime.Now show correctly in the page but when inserting into the database the system will add 543 automatically to the year !!! So when I retrieve the data back from the database it get the year 3091 !!

So what I have to do is
.
DateTime dt = DateTime.Now;
.
.
newTask.InsertParameters.Add("DateCreated", dt.AddYears(-543).ToString("yyyyMMdd HH:mm:ss"));
.

It's kind of weird, right??|||See my post 3 posts up.

Adding data to SQL database through Access

My table resides in a SQL database: It has two fields:
1. NameID - KEY 2. Name
I want the user to have the ability to add new names in an Access form and
disallow any names that already exist. SQL does not give me the ability to
set the Name field to 'No Duplicates' and Access does not let me set the NO
DUP property since this is a linked table.
What code or settings is required to disallow duplicates in a no-key field
between Access and SQL?First of all, are you using a .mdb or .adp file
I think the best way to do it is use the DLookup function in Access on the
AFterUpdate Event...
Please look at DLookup in the Help file in MS Access
Ed
"SharonInGa" wrote:

> My table resides in a SQL database: It has two fields:
> 1. NameID - KEY 2. Name
> I want the user to have the ability to add new names in an Access form and
> disallow any names that already exist. SQL does not give me the ability to
> set the Name field to 'No Duplicates' and Access does not let me set the N
O
> DUP property since this is a linked table.
> What code or settings is required to disallow duplicates in a no-key field
> between Access and SQL?
>|||What you seem to be looking for is a unique constraint. You
can find more information on this in SQL Server books
online. It's the same as the 'No Duplicates' in Access.
-Sue
On Thu, 20 Jan 2005 13:37:02 -0800, "SharonInGa"
<SharonInGa@.discussions.microsoft.com> wrote:

>My table resides in a SQL database: It has two fields:
>1. NameID - KEY 2. Name
>I want the user to have the ability to add new names in an Access form and
>disallow any names that already exist. SQL does not give me the ability to
>set the Name field to 'No Duplicates' and Access does not let me set the NO
>DUP property since this is a linked table.
>What code or settings is required to disallow duplicates in a no-key field
>between Access and SQL?

Adding data to SQL database through Access

My table resides in a SQL database: It has two fields:
1. NameID - KEY 2. Name
I want the user to have the ability to add new names in an Access form and
disallow any names that already exist. SQL does not give me the ability to
set the Name field to 'No Duplicates' and Access does not let me set the NO
DUP property since this is a linked table.
What code or settings is required to disallow duplicates in a no-key field
between Access and SQL?
First of all, are you using a .mdb or .adp file
I think the best way to do it is use the DLookup function in Access on the
AFterUpdate Event...
Please look at DLookup in the Help file in MS Access
Ed
"SharonInGa" wrote:

> My table resides in a SQL database: It has two fields:
> 1. NameID - KEY 2. Name
> I want the user to have the ability to add new names in an Access form and
> disallow any names that already exist. SQL does not give me the ability to
> set the Name field to 'No Duplicates' and Access does not let me set the NO
> DUP property since this is a linked table.
> What code or settings is required to disallow duplicates in a no-key field
> between Access and SQL?
>
|||What you seem to be looking for is a unique constraint. You
can find more information on this in SQL Server books
online. It's the same as the 'No Duplicates' in Access.
-Sue
On Thu, 20 Jan 2005 13:37:02 -0800, "SharonInGa"
<SharonInGa@.discussions.microsoft.com> wrote:

>My table resides in a SQL database: It has two fields:
>1. NameID - KEY 2. Name
>I want the user to have the ability to add new names in an Access form and
>disallow any names that already exist. SQL does not give me the ability to
>set the Name field to 'No Duplicates' and Access does not let me set the NO
>DUP property since this is a linked table.
>What code or settings is required to disallow duplicates in a no-key field
>between Access and SQL?

Adding data to SQL database through Access

My table resides in a SQL database: It has two fields:
1. NameID - KEY 2. Name
I want the user to have the ability to add new names in an Access form and
disallow any names that already exist. SQL does not give me the ability to
set the Name field to 'No Duplicates' and Access does not let me set the NO
DUP property since this is a linked table.
What code or settings is required to disallow duplicates in a no-key field
between Access and SQL?First of all, are you using a .mdb or .adp file
I think the best way to do it is use the DLookup function in Access on the
AFterUpdate Event...
Please look at DLookup in the Help file in MS Access
Ed
"SharonInGa" wrote:
> My table resides in a SQL database: It has two fields:
> 1. NameID - KEY 2. Name
> I want the user to have the ability to add new names in an Access form and
> disallow any names that already exist. SQL does not give me the ability to
> set the Name field to 'No Duplicates' and Access does not let me set the NO
> DUP property since this is a linked table.
> What code or settings is required to disallow duplicates in a no-key field
> between Access and SQL?
>|||What you seem to be looking for is a unique constraint. You
can find more information on this in SQL Server books
online. It's the same as the 'No Duplicates' in Access.
-Sue
On Thu, 20 Jan 2005 13:37:02 -0800, "SharonInGa"
<SharonInGa@.discussions.microsoft.com> wrote:
>My table resides in a SQL database: It has two fields:
>1. NameID - KEY 2. Name
>I want the user to have the ability to add new names in an Access form and
>disallow any names that already exist. SQL does not give me the ability to
>set the Name field to 'No Duplicates' and Access does not let me set the NO
>DUP property since this is a linked table.
>What code or settings is required to disallow duplicates in a no-key field
>between Access and SQL?

Adding Data from a form to a database

Very basic question here but I need to add a form to a page so it adds content, its as simple as that. I've done this kind of using a <asp.Formview> tag and it works, but becuase I think I've used a form view I have to click add to insert data first. Should I be using the <asp:formview> tag or is it a problem with the templates?

<asp:FormView ID="FormView1" runat="server" DataKeyNames="ContentID" DataSourceID="ObjectDataSource1">

<InsertItemTemplate>
Resource :<asp:DropDownList ID="ResourceIDTextBox" runat="server" Text='<%# Bind("ResourceID") %>' DataSourceID="ObjectDataSource2"
DataTextField="Resource" DataValueField="ResourceID">
</asp:DropDownList><br />
Headline:
<asp:TextBox ID="HeadlineTextBox" runat="server" Text='<%# Bind("Headline") %>' Width="451px"></asp:TextBox><br />
Date:
<asp:TextBox ID="DateTextBox" runat="server" Text='<%# Bind("Date") %>'>
</asp:TextBox><br />
Body:
<FTB:FreeTextBox id="FreeTextBox1" runat="Server" Text='<%# Bind("Body") %>' Width="542px" />
<br />
ImageURL:
<asp:TextBox ID="ImageURLTextBox" runat="server" Text='<%# Bind("ImageURL") %>'>
</asp:TextBox><br />
OpeningPara:
<asp:TextBox ID="OpeningParaTextBox" runat="server" Text='<%# Bind("OpeningPara") %>'>
</asp:TextBox><br />
Ret:
<asp:TextBox ID="RetTextBox" runat="server" Text='<%# Bind("Ret") %>'>
</asp:TextBox><br />
Man:
<asp:TextBox ID="ManTextBox" runat="server" Text='<%# Bind("Man") %>'>
</asp:TextBox><br />
Pro:
<asp:TextBox ID="ProTextBox" runat="server" Text='<%# Bind("Pro") %>'>
</asp:TextBox><br />
Com:
<asp:TextBox ID="ComTextBox" runat="server" Text='<%# Bind("Com") %>'>
</asp:TextBox><br />
IndustryID:
<asp:TextBox ID="IndustryIDTextBox" runat="server" Text='<%# Bind("IndustryID") %>'>
</asp:TextBox><br />
ContentTitle:
<asp:TextBox ID="ContentTitleTextBox" runat="server" Text='<%# Bind("ContentTitle") %>'>
</asp:TextBox><br />
Status:
<asp:TextBox ID="StatusTextBox" runat="server" Text='<%# Bind("Status") %>'>
</asp:TextBox><br />
Pub:
<asp:TextBox ID="PubTextBox" runat="server" Text='<%# Bind("Pub") %>'>
</asp:TextBox><br />
Fin:
<asp:TextBox ID="FinTextBox" runat="server" Text='<%# Bind("Fin") %>'>
</asp:TextBox><br />
<asp:LinkButton ID="InsertButton" runat="server" CausesValidation="True" CommandName="Insert"
Text="Insert">
</asp:LinkButton>
<asp:LinkButton ID="InsertCancelButton" runat="server" CausesValidation="False" CommandName="Cancel"
Text="Cancel">
</asp:LinkButton>
</InsertItemTemplate
<ItemTemplate>

<asp:LinkButton ID="NewButton" runat="server" CausesValidation="False" CommandName="New"
Text="Click here to Add New Content">
</asp:LinkButton>
</ItemTemplate
</asp:FormView>

<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" DeleteMethod="Delete"
InsertMethod="Insert" OldValuesParameterFormatString="original_{0}" SelectMethod="ContentADDMethod"
TypeName="DataSet2TableAdapters.ContentADDAdapter" UpdateMethod="Update">

<DeleteParameters>
<asp:Parameter Name="Original_ContentID" Type="Int32" />
</DeleteParameters
<UpdateParameters>
<asp:Parameter Name="ResourceID" Type="Int32" />
<asp:Parameter Name="Headline" Type="String" />
<asp:Parameter Name="Date" Type="String" />
<asp:Parameter Name="Body" Type="String" />
<asp:Parameter Name="ImageURL" Type="String" />
<asp:Parameter Name="OpeningPara" Type="String" />
<asp:Parameter Name="Ret" Type="String" />
<asp:Parameter Name="Man" Type="String" />
<asp:Parameter Name="Pro" Type="String" />
<asp:Parameter Name="Com" Type="String" />
<asp:Parameter Name="IndustryID" Type="Int32" />
<asp:Parameter Name="ContentTitle" Type="String" />
<asp:Parameter Name="Status" Type="String" />
<asp:Parameter Name="Pub" Type="String" />
<asp:Parameter Name="Fin" Type="String" />
<asp:Parameter Name="Original_ContentID" Type="Int32" />
<asp:Parameter Name="ContentID" Type="Int32" />
</UpdateParameters
<InsertParameters>
<asp:Parameter Name="ResourceID" Type="Int32" />
<asp:Parameter Name="Headline" Type="String" />
<asp:Parameter Name="Date" Type="String" />
<asp:Parameter Name="Body" Type="String" />
<asp:Parameter Name="ImageURL" Type="String" />
<asp:Parameter Name="OpeningPara" Type="String" />
<asp:Parameter Name="Ret" Type="String" />
<asp:Parameter Name="Man" Type="String" />
<asp:Parameter Name="Pro" Type="String" />
<asp:Parameter Name="Com" Type="String" />
<asp:Parameter Name="IndustryID" Type="Int32" />
<asp:Parameter Name="ContentTitle" Type="String" />
<asp:Parameter Name="Status" Type="String" />
<asp:Parameter Name="Pub" Type="String" />
<asp:Parameter Name="Fin" Type="String" />
</InsertParameters
</asp:ObjectDataSource>


<asp:ObjectDataSource ID="ObjectDataSource2" runat="server" InsertMethod="Insert"
OldValuesParameterFormatString="original_{0}" SelectMethod="GetData" TypeName="DataSet2TableAdapters.ResourcesTableAdapter">
<InsertParameters>
<asp:Parameter Name="Resource" Type="String" />
</InsertParameters>
</asp:ObjectDataSource>

I'm sorry, but what is your complaint? You have to click an add button in order to add a new record?

adding button to report

Hello

I think this is better in this forum:

I want to add a button to my reporting services, when clicked on shows a VB.Net form and lets the user select a specific account(accounts) from my SQL Server 2000 table. After user selects a specific account, the form disappears, and the selected account appears in the account parameter textbox on reporting services report...

I have seen this done, but am unable 2 find any helpful stuff on the net. Can anyone explain how this is done, or if they know of any useful links?

I am using Visual Studio .Net 2003

Thanks.

I dont fink I explained it properly..... instead of creating the query parameters in reporting services, I want to create it using asp.net (so that I can have a calendar control for DateTo and DateFrom instead of textboxes in reporting services... to ensure no problems with date format).

I have been looking at this for last 2 days, and found 1 such example which using a Report Viewer to send the parameters to the report services report by url from the asp.net page... (I am going to try and see if I can do this - but I am concerned because I am using Microsoft Server 2000 Reporting Services with Visual Studio .Net 2003.... is this possible or am I app 2 old?!

Any help would be greatly appreciated!!! Or any useful links?!

adding button to report

Hello

I think this is better in this forum:

I want to add a button to my reporting services, when clicked on shows a VB.Net form and lets the user select a specific account(accounts) from my SQL Server 2000 table. After user selects a specific account, the form disappears, and the selected account appears in the account parameter textbox on reporting services report...

I have seen this done, but am unable 2 find any helpful stuff on the net. Can anyone explain how this is done, or if they know of any useful links?

I am using Visual Studio .Net 2003

Thanks.

I dont fink I explained it properly..... instead of creating the query parameters in reporting services, I want to create it using asp.net (so that I can have a calendar control for DateTo and DateFrom instead of textboxes in reporting services... to ensure no problems with date format).

I have been looking at this for last 2 days, and found 1 such example which using a Report Viewer to send the parameters to the report services report by url from the asp.net page... (I am going to try and see if I can do this - but I am concerned because I am using Microsoft Server 2000 Reporting Services with Visual Studio .Net 2003.... is this possible or am I app 2 old?!

Any help would be greatly appreciated!!! Or any useful links?!

Adding an item using limited list in VBA

Hi all,

I am trying to add a new item (City) using a Combo Box in a Customers form. The Form is in Access 2002 and tables are MS SQL Server 2000.
I would like to use a code like one I used when working with Access 2002 tables but without success. If a new City is typed the code asks if
new city will be added. Can someone suggest a version which would work on a MS SQL Server 2000?

Thanks

Dani

PS

Here is the VBA code I use when working against Access 2002 tables.

Dim Response As Integer

Private Sub City_NotInList(NewData As String, Response As Integer)
' Add new City
On Error GoTo City_NotInList_Err

Dim conConnection As ADODB.Connection
Dim StrSQL As String
Dim iAffected As Integer

Set conConnection = CurrentProject.Connection
StrSQL = "INSERT INTO Cities (City) Values " _
& "(" & "'" & NewData & "'" & ");"

If LogMsgBox(Msg("Add new city") & " " & NewData _
& vbCrLf & Msg("Are you sure?"), vbQuestion + vbYesNo, _
Msg("Add new city"), Form.Name, "City_NotOnList", True) = vbYes Then
' Add new city
conConnection.Execute StrSQL, iAffected, adExecuteNoRecords
Response = acDataErrAdded
Else
' No
Response = acDataErrDisplay

End If

' Close ADODB connection
conConnection.Close

Exit_City_NotInList:
Set conConnection = Nothing ' Deassign ADO object
Exit Sub
City_NotInList_Err:
MsgBox Err.Description
Resume Exit_City_NotInListI found where is the proble.

Adding a Word document to a sql db

I have a form that has a Word object. I want to save that object to the sql db. The original front and backend was an Access db and this all worked using an ole field. The backend data has been moved to a sql 2000 db. The reading of original Word objects works fine but I can't get any new objects stored. I have seen postings that there is no way to by-pass saving the object to a temp file but really I can't get anything to work. The attached code makes no complaints but when I try to access the new object I either get an empty object or an error that the ole server has a problem.

The code below includes simply saving an exisiting doc but that doc does not come back out of the db. I tried just for grins to store the form object in a temporary Access table and then save that field. Actually I got no complaints but then I got the same results. The use of the external file mimics examples I've seen even on this forum. any suggestions are appreciated.

Rick

'Now save word doc that is contained in the form entity
If Not IsNull(Me.oleSubSectionDetail) Then
Dim rst As ADODB.Recordset
Dim mstream As ADODB.Stream

'Tried to make it happen by essentially moving
'a database field to a db field
Dim oleRst As DAO.Recordset
Set oleRst = CurrentDb.OpenRecordset("tmpOLE", dbOpenDynaset)
oleRst.AddNew
'tempole is an ole defined field
oleRst!tempole = Me.oleSubSectionDetail
oleRst.Update
oleRst.MoveFirst

'Select record I want to update
Set rst = New ADODB.Recordset
rst.Open "Select * FROM [tbl-SOW Detail] WHERE RecID = " & RecID, ADOConnection.SQLDB_Connect, adOpenKeyset, adLockOptimistic

Set mstream = New ADODB.Stream
mstream.Type = adTypeBinary
mstream.Open

mstream.LoadFromFile "c:\\Documents and Settings\rburge\My Documents\Standard Contracts\-Cover Page.doc"

'or

'mstream.Write oleRst!tempole

rst.Fields("SubsectionDetail").Value = mstream.Read
rst.Update

rst.Close
Set rst = Nothing
Set mstream = Nothing
End If

I have more information: actually the above is working. It is that once you use that method to save the doc in the image field you must stream it out and to a file to properly read it.

I was hoping someone might share some light on this:

1) if you stream the document in and then stream it out the file is twice the size it was when input. If you open and save the word doc the size is restored. I have read some articles that suggest there is some overhead for every byte stored in the database image field.

2) Originally the backend was an access db which had many documents already stored as ole data type. It was easy enough using DAO to store and retrieve the document image with simple a=b type statements. I upsized the backend to SQL and those same records will allow me to retrieve the image and do a simple form field = db field, even using ADO. Now when I go through the streaming process to save a document I now have to always retrieve it the same way, via the stream. I'm going to have trouble with distiguishing between legacy records and new or I'm going to have to read and write every document record in the db to make sure everybody is on the same page so to speak.

Can anyone explain the storage differences?

Thanks,

Rick

|||

I came up with a solution:

I did create a local temp access table with an ole field. First I stored the document in the ms access field and then I could copy the ms access field to the sql image field. This helped preserve doc that were already in the sql db and preserved the basic document retrievial code. I think that this was a better solution (for me anyways) than reading and writing files.