date and time diffs: contains

Showing posts with label contains. Show all posts

Thursday, March 29, 2012

Datatype Conversion Problem.

Hi ALL!

I have a table named 'Table1' which contains a column 'Name'.
The data type of column [Name] is varchar(50).

When i try to change its datatype to binary by trying following code

ALTER TABLE Table1 Alter Column [Name] Binary(5000)

It gives following error.

" Creation of table 'bp_MAIN' failed because the row size would be 10021, including internal overhead. This exceeds the maximum allowable table row size, 8060. "

So, how can i change the datatype of this column ?

Regards,
Shabber Abbas.Not meaning to be thick here, but why are you changing a varchar to a binary? Did you want instead to change it to nvarchar?

Regards,

hmscott|||In this case, you are implying that you want to convert varchar data to binary. I don't think that can be done automatically. The error might be misleading.

If that's the only field, it shouldn't give that error, but a table consisiting of only a binary field seems like it's not very useful. Is a blob out of the question? it only takes up 16bytes of the page. Yould definetely need to export/import then.

You should be able to add a binary column, or export the data, recreate the table with a binary field, and then import the data, with suitable massaging.|||You could use varbinary, but if the amount of data in the row exceeds 8060, you will get errors, instead of warnings.

Tuesday, March 27, 2012

Datasource information retrieved from a database

Hi everyone,
I have an assembly deployed on my report server that contains a number of
functions used by my reports. The assembly has full trust and all of the
functions work fine except one.
The purpose of this function is to query a database table for the correct
connection string for the report datasource. The code that returns the
connection string works correctly in a test windows app. When I run the
report through report manager, I get the following error:
Does this mean that a report server can only use the database where it
stores its server information? This would be inconvenient and I would like
to avoid it if possible.
Also, I would prefer my assembly not to have full trust, and instead only
have the permissions necessary to allow it to read from the database where
the connection string information is stored. So far, I have found plenty of
sample code describing the XML that I need to add to grant
FileIOPermissions, but nothing on database-related permissions. Can anyone
point me in the right direction?
Thanks in advance,
Ed AllisonFor anyone else who gets stuck on this, the solution is here:
http://blogs.sqlxml.org/bryantlikes/archive/2004/07/21/845.aspx
"Ed Allison" <ed@.optix.co.uk> wrote in message
news:eMGQtxzUGHA.5996@.TK2MSFTNGP10.phx.gbl...
> Hi everyone,
> I have an assembly deployed on my report server that contains a number of
> functions used by my reports. The assembly has full trust and all of the
> functions work fine except one.
> The purpose of this function is to query a database table for the correct
> connection string for the report datasource. The code that returns the
> connection string works correctly in a test windows app. When I run the
> report through report manager, I get the following error:
> Does this mean that a report server can only use the database where it
> stores its server information? This would be inconvenient and I would
> like to avoid it if possible.
> Also, I would prefer my assembly not to have full trust, and instead only
> have the permissions necessary to allow it to read from the database where
> the connection string information is stored. So far, I have found plenty
> of sample code describing the XML that I need to add to grant
> FileIOPermissions, but nothing on database-related permissions. Can
> anyone point me in the right direction?
> Thanks in advance,
> Ed Allison
>

Thursday, March 22, 2012

Dataset Query Parameter

I have a dataset query that contains the expression:
Cost * (1 - ABS(SIGN(DATEPART(mm, OrderDate) - DATEPART(mm,
DATEADD(DateInterval.Month, - 11, { fn NOW() })))))
I have had no luck replacing the { fn NOW() } with a parameter and was
wondering if it was at all possible.To use parameters in a dataset query expression, use @.ParameterName.
"Todd Simmons" wrote:
> I have a dataset query that contains the expression:
> Cost * (1 - ABS(SIGN(DATEPART(mm, OrderDate) - DATEPART(mm,
> DATEADD(DateInterval.Month, - 11, { fn NOW() })))))
> I have had no luck replacing the { fn NOW() } with a parameter and was
> wondering if it was at all possible.
>|||I tried that and get a "Syntax error or access violation"
"Harolds" wrote:
> To use parameters in a dataset query expression, use @.ParameterName.
> "Todd Simmons" wrote:
> > I have a dataset query that contains the expression:
> >
> > Cost * (1 - ABS(SIGN(DATEPART(mm, OrderDate) - DATEPART(mm,
> > DATEADD(DateInterval.Month, - 11, { fn NOW() })))))
> >
> > I have had no luck replacing the { fn NOW() } with a parameter and was
> > wondering if it was at all possible.
> >

dataset problem

hi,

i am using dataset for passing value to crystal report.
when the stored procedure contains 2 tables then how to create the dataset1.xsd for two table.
query with join works fine in QA.
i tried by giving two tables in dataset schema but how to give two tables with selected fields as per the query.
which table i should mention in fill method.

please tell me a procedure how to do this.

i tried an alternative method also.
by creating dataset at runtime using adapter.
but without filteration as per query all data appears in the report.

thanksafter setting dataset using adapter, aand setting it to crystal use record selection formula.|||iam using crystal report.net in vb.net
can you send a sample code for this.
your help will be appreciated.|||Hi u can code something like this.

Dim srcCr As Object
Dim rptDoc As New ReportDocument

srcCr = rptDoc
srcCr.SetDataSource(dsObj) --dsobj is ur dataset
rptDoc.Load("\reports\abc.rpt")

srcCr.RecordSelectionFormula = "{command.AccID}=124"

hope it helps you

Dataset parameters question.

I have a report, which contains student details and the courses to
which the student is registered.
I have 2 datasets, one which retrieves the student data, and another
paramaterized report which retrieves all courses for this student.
What I need to do is set this parameter to be the student_id of the
current student.
e.g. I want the report to be displayed as:
Student Details
Student ID: S1000
Name: AN Other Address: 1 22nd Street
Courses:
Math
Chemistry
Physics
Spanish
I can select the correct dataset and field in the textbox for course
name, but the report keeps asking me to enter a student id. I want it
to use the student id of the current student.
Does anyone know how I go about this?
Appreciate any help.Donâ't you use a student id parameter to filter the student? This one should
be used to filter courses.
If youâ're not filtering the students, you may have more than one student. In
this case you can use a single dataset that joins student and courses, and
group by students.
"DJ" wrote:
> I have a report, which contains student details and the courses to
> which the student is registered.
> I have 2 datasets, one which retrieves the student data, and another
> paramaterized report which retrieves all courses for this student.
> What I need to do is set this parameter to be the student_id of the
> current student.
> e.g. I want the report to be displayed as:
> Student Details
> Student ID: S1000
> Name: AN Other Address: 1 22nd Street
> Courses:
> Math
> Chemistry
> Physics
> Spanish
> I can select the correct dataset and field in the textbox for course
> name, but the report keeps asking me to enter a student id. I want it
> to use the student id of the current student.
> Does anyone know how I go about this?
> Appreciate any help.
>|||IF USING A SUBREPORT
Your Student data is in your main report, and your Course data (including
StudentID) is in your subreport. Your subreport is placed inside the list
(or table or whatever) that iterates on the student data.
In the main report, right-click on the subreport and select Properties.
Then go to the Parameters tab and map the subreport parameters to the
appropriate fields.
IF NOT USING A SUBREPORT
You might be able to do this without a subreport. Just join the course
table to the student table in your dataset. Put everything inside a table
(or nested lists if it's a freeform report). You can put rectangles and
lists inside table cells, which is cool. Map everything to the same dataset.
Then set outer grouping levels at the student level, and inner grouping or
details at the course level.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"DJ" <superdj@.hotmail.com> wrote in message
news:72abb98.0501070456.d13dd9d@.posting.google.com...
>I have a report, which contains student details and the courses to
> which the student is registered.
> I have 2 datasets, one which retrieves the student data, and another
> paramaterized report which retrieves all courses for this student.
> What I need to do is set this parameter to be the student_id of the
> current student.
> e.g. I want the report to be displayed as:
> Student Details
> Student ID: S1000
> Name: AN Other Address: 1 22nd Street
> Courses:
> Math
> Chemistry
> Physics
> Spanish
> I can select the correct dataset and field in the textbox for course
> name, but the report keeps asking me to enter a student id. I want it
> to use the student id of the current student.
> Does anyone know how I go about this?
> Appreciate any help.|||I have tried this using a parameterized sub report, and set the
parameter to be the student id. The dataset that retrieves the student
data is simply 'select * from students' so this should bring back all
the students and the sub report should bring back all the courses for
these students.
But still it asks me to enter a student id!
DJ wrote:
> I have a report, which contains student details and the courses to
> which the student is registered.
> I have 2 datasets, one which retrieves the student data, and another
> paramaterized report which retrieves all courses for this student.
> What I need to do is set this parameter to be the student_id of the
> current student.
> e.g. I want the report to be displayed as:
> Student Details
> Student ID: S1000
> Name: AN Other Address: 1 22nd Street
> Courses:
> Math
> Chemistry
> Physics
> Spanish
> I can select the correct dataset and field in the textbox for course
> name, but the report keeps asking me to enter a student id. I want it
> to use the student id of the current student.
> Does anyone know how I go about this?
> Appreciate any help.|||In your parent report parameters, check for a student_id field. You might
have specified one earlier that needs to be deleted. The data source for
your subreport should be something like "select * from courses where
student_id = @.student_id". Then you can link the parameters in the parent
and child reports.
On the other hand, you could do the whole thing in a single report, with a
data source like "select * from students inner join courses on
students.student_id = courses.student_id". Then you put the fields in a
table with student info in the header rows, and group info in the detail
rows.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
<superdj@.hotmail.com> wrote in message
news:1105349201.298466.198340@.c13g2000cwb.googlegroups.com...
>I have tried this using a parameterized sub report, and set the
> parameter to be the student id. The dataset that retrieves the student
> data is simply 'select * from students' so this should bring back all
> the students and the sub report should bring back all the courses for
> these students.
> But still it asks me to enter a student id!
>
> DJ wrote:
>> I have a report, which contains student details and the courses to
>> which the student is registered.
>> I have 2 datasets, one which retrieves the student data, and another
>> paramaterized report which retrieves all courses for this student.
>> What I need to do is set this parameter to be the student_id of the
>> current student.
>> e.g. I want the report to be displayed as:
>> Student Details
>> Student ID: S1000
>> Name: AN Other Address: 1 22nd Street
>> Courses:
>> Math
>> Chemistry
>> Physics
>> Spanish
>> I can select the correct dataset and field in the textbox for course
>> name, but the report keeps asking me to enter a student id. I want it
>> to use the student id of the current student.
>> Does anyone know how I go about this?
>> Appreciate any help.
>

Dataset Merge and @@IDENTITY

I have an application that contains a dataset of product data that is stored in SQL Server 2000. The original table in SQL Server has an nteger primary key that is an Identity field.

The application allows users to add new records to the dataset and then update the datasource. The update datasource call writes the new rows to SQL Server with no problems. However, quite oftern when the update datasource happens the identity field creatred in the dataset has already been allocated (someone else added a record at same time).

Now, things are still OK as SQL Server will happily add the record, but now the identity field allocated by sql Server is different to the one in my original dataset.

How do I get my original dataset to show the revised identity field generated by SQL Server?

I know you can get the value using @.@.IDENTITY but how do I get that back into the dataset?Try using the SCOPE_IDENTITY function instead of @.@.IDENTITY. It has a narrower scope, so you should get back the value you want.

Don|||Getting the revised value isnt the problem.

The isssue is how do I reflect that back into the orignal dataset.

The only way I can see to do this is process additions to the datasource separately from updates and after each insert, fetch the revised IDENTITY field and then write a load of code to examine the new datasource generated value, compare it with the value originally created by the dataset and if it differs, then change the dataset value.

That make sense?

Wednesday, March 21, 2012

DataSet Filter

I have one dataset that contains thousands of records i want to filter the dataset by one of the name

select col1,col2,col3 from tablename

this col1 contains various names i want to check wheather an name exists in the col1 or not

please guide me

Hi, Febin

For your problem there is two possible solutions, First one is RowFilter and second one is Select

Solution 1 (RowFilter)

Dim ds As New Data.DataSet

Dim CN As New SqlClient.SqlConnection(MyClass1.ConnStr)

Dim Stmt As String

Stmt = "select * from USER_MASTER"

Dim DA As New SqlClient.SqlDataAdapter(stmt, CN)

DA.Fill(ds, "USER_MASTER")

ds.Tables("USER_MASTER").DefaultView.RowFilter = "DEPT ='ADMIN'"

MsgBox(ds.Tables("USER_MASTER").DefaultView.Count)

Solution 2 (Select)

Dim ds As New Data.DataSet

Dim CN As New SqlClient.SqlConnection(MyClass1.ConnStr)

Dim Stmt As String

Stmt = "select * from USER_MASTER"

Dim DA As New SqlClient.SqlDataAdapter(stmt, CN)

DA.Fill(ds, "USER_MASTER")

Dim R() As DataRow

R = ds.Tables("USER_MASTER").Select("DEPT ='ADMIN'", "EMP_NAME")

If R.Length > 0 Then

MsgBox(R.Length & " Record(s) Found")

End If

In the sample code it shows the number of records found for dept called "ADMIN",

Happy Coding

sql

Thursday, March 8, 2012

DataFlow Task & Filters

Hi,

I am getting data from an external source. External data has a column called "Type". I have a variable in my package which contains the list of types as shown below:

Filtered_type_List = 2,4,8,10,11

If this variable(Filtered_type_List) is blank, then I need all the data from the external source and if it is not blank then I only need the records matching to his list. How can I implement this in DataFlow Task?

Thanks

You could do this in an expression. Something like:

"SELECT * FROM MyTable " + (LEN(MySSISVariable) != 0 ? "WHERE MyColumn IN (" + MySSISVariable + ")" : "" )

That expression will (I think) add a WHERE clause if the length of the string inside the variable (which I have called MySSISVariable) is not zero.

HTH

-Jamie

|||

Hi Jamie,

Where should I put this "Select" statement,

1. Source using SQL Command as variable using OLE DB Source or

2. Lookup transformation

Thanks

|||

OLE DB Source. Set it to 'SQL Command from variable' and paste the expression that I provided above into the variable expression. The variable will require EvaluateAsExpression=TRUE.

-Jamie

Sunday, February 26, 2012

DataBinding with Insert query problem

Hello,

I have a page with a detailsview where I can add articles (Generated by visual studio). Now the table contains a field (Autor) wich must contain the username of the Autor from the article. But when I run my page now I have to give it in manually ( in a textbox). I've searching for a way to bind the Profile Username with the insert Sql Query, @.Autor value.

I tought maybe I should insert the value of the Profile username in the textbox and put the textbox visibel on false.

But When i saw the component code I saw that Text= is already bound, so it's not possible to insert a value

<asp:TextBox ID="auteurTextBox" Visible="true" runat="server" Text='<%# Bind("auteur")%>'>

 Here is whole the page code (line 23 is the textbox).

 1<asp:FormView ID="FormView1" runat="server" DataKeyNames="id" DataSourceID="SqlDataSource1" AllowPaging="True" CellPadding="4" ForeColor="#333333" style="left: 30%; position: relative">2 <EditItemTemplate>3 <asp:Label ID="idLabel1" Visible="false" runat="server" Text='<%# Eval("id")%>'></asp:Label>4 <asp:TextBox ID="auteurTextBox" Visible="false" runat="server" Text='<%# Bind("auteur")%>'>5 </asp:TextBox>6 soort:7 <asp:TextBox ID="soortTextBox" runat="server" Text='<%# Bind("soort")%>'>8 </asp:TextBox><br />9 titel:10 <asp:TextBox ID="titelTextBox" runat="server" Text='<%# Bind("titel")%>'>11 </asp:TextBox><br />12 text:13 <asp:TextBox ID="textTextBox" Height="200px" TextMode="MultiLine" Rows="20" Width="260px" runat="server" Text='<%# Bind("text")%>'>14 </asp:TextBox><br />15 <asp:LinkButton ID="UpdateButton" runat="server" CausesValidation="True" CommandName="Update"16 Text="Update">17 </asp:LinkButton>18 <asp:LinkButton ID="UpdateCancelButton" runat="server" CausesValidation="False" CommandName="Cancel"19 Text="Cancel">20 </asp:LinkButton>21 </EditItemTemplate>22 <InsertItemTemplate>23 <asp:TextBox ID="auteurTextBox" Visible="true" runat="server" Text='<%# Bind("auteur")%>'>24 </asp:TextBox><br />25 Soort:26 <asp:TextBox ID="soortTextBox" runat="server" Text='<%# Bind("soort")%>'>27 </asp:TextBox><br />28 titel:29 <asp:TextBox ID="titelTextBox" runat="server" Text='<%# Bind("titel")%>'>30 </asp:TextBox><br />31 text:32 <asp:TextBox ID="textTextBox" runat="server" Text='<%# Bind("text")%>'>33 </asp:TextBox><br />34 <asp:LinkButton ID="InsertButton" runat="server" CausesValidation="True" CommandName="Insert"35 Text="Insert">36 </asp:LinkButton>37 <asp:LinkButton ID="InsertCancelButton" runat="server" CausesValidation="False" CommandName="Cancel"38 Text="Cancel">39 </asp:LinkButton>40 </InsertItemTemplate>41 <ItemTemplate>42 <asp:Label ID="idLabel" Visible="false" runat="server" Text='<%# Eval("id")%>'></asp:Label><br />43 <asp:Label ID="auteurLabel" Visible="false" runat="server" Text='<%# Bind("auteur")%>'></asp:Label><br />44 soort:45 <asp:Label ID="soortLabel" runat="server" Text='<%# Bind("soort")%>'></asp:Label><br />46 titel:47 <asp:Label ID="titelLabel" runat="server" Text='<%# Bind("titel")%>'></asp:Label><br />48 text:49 <asp:Label ID="textLabel" runat="server" Text='<%# Bind("text")%>'></asp:Label><br />50 <asp:LinkButton ID="EditButton" runat="server" CausesValidation="False" CommandName="Edit"51 Text="Edit">52 </asp:LinkButton>53 <asp:LinkButton ID="DeleteButton" runat="server" CausesValidation="False" CommandName="Delete"54 Text="Delete">55 </asp:LinkButton>56 <asp:LinkButton ID="NewButton" runat="server" CausesValidation="False" CommandName="New"57 Text="New">58 </asp:LinkButton>59 </ItemTemplate>60 <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />61 <EditRowStyle BackColor="#2461BF" />62 <RowStyle BackColor="#EFF3FB" />63 <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />64 <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />65 </asp:FormView>66 <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues"67 ConnectionString="<%$ ConnectionStrings<img src="http://pics.10026.com/?src=images/smilies/biggrinn.gif" border="0" alt="">atabankConnectie%>" DeleteCommand="DELETE FROM [artikel2] WHERE [id] = @.original_id AND [auteur] = @.original_auteur AND [soort] = @.original_soort AND [titel] = @.original_titel AND [text] = @.original_text"68 InsertCommand="INSERT INTO [artikel2] ([auteur], [soort], [titel], [text]) VALUES (@.auteur, @.soort, @.titel, @.text)"69 OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT * FROM [artikel2] WHERE [auteur] = @.auteur"70 UpdateCommand="UPDATE [artikel2] SET [auteur] = @.auteur, [soort] = @.soort, [titel] = @.titel, [text] = @.text WHERE [id] = @.original_id AND [auteur] = @.original_auteur AND [soort] = @.original_soort AND [titel] = @.original_titel AND [text] = @.original_text">71 <DeleteParameters>72 <asp src="images/smilies/tongue.gif" border="0" alt="">arameter Name="original_id" Type="Int32">73 <asp src="images/smilies/tongue.gif" border="0" alt="">arameter Name="original_auteur" Type="String">74 <asp src="images/smilies/tongue.gif" border="0" alt="">arameter Name="original_soort" Type="String">75 <asp src="images/smilies/tongue.gif" border="0" alt="">arameter Name="original_titel" Type="String">76 <asp src="images/smilies/tongue.gif" border="0" alt="">arameter Name="original_text" Type="String">77 </DeleteParameters>78 <UpdateParameters>79 <asp src="images/smilies/tongue.gif" border="0" alt="">arameter Name="auteur" Type="String">80 <asp src="images/smilies/tongue.gif" border="0" alt="">arameter Name="soort" Type="String">81 <asp src="images/smilies/tongue.gif" border="0" alt="">arameter Name="titel" Type="String">82 <asp src="images/smilies/tongue.gif" border="0" alt="">arameter Name="text" Type="String">83 <asp src="images/smilies/tongue.gif" border="0" alt="">arameter Name="original_id" Type="Int32">84 <asp src="images/smilies/tongue.gif" border="0" alt="">arameter Name="original_auteur" Type="String">85 <asp src="images/smilies/tongue.gif" border="0" alt="">arameter Name="original_soort" Type="String">86 <asp src="images/smilies/tongue.gif" border="0" alt="">arameter Name="original_titel" Type="String">87 <asp src="images/smilies/tongue.gif" border="0" alt="">arameter Name="original_text" Type="String">88 </UpdateParameters>89 <InsertParameters>90 <asp src="images/smilies/tongue.gif" border="0" alt="">arameter Name="auteur" Type="String">91 <asp src="images/smilies/tongue.gif" border="0" alt="">arameter Name="soort" Type="String">92 <asp src="images/smilies/tongue.gif" border="0" alt="">arameter Name="titel" Type="String">93 <asp src="images/smilies/tongue.gif" border="0" alt="">arameter Name="text" Type="String">94 </InsertParameters>95 </asp:SqlDataSource>96 </LoggedInTemplate>97 </asp:LoginView>98</asp:Content>99

Can somebody help me?

Hi,

instead of doing that in the asp code, you could achieve the same goal in the code behind:

1. Removed the insert parameter 'auteur' for the asp code

2. add in the page load event the following code:

protected void Page_Load(object sender, EventArgs e)
{

SqlParameter param = new SqlParameter("auteur", SqlDbType.NVarChar);
param.Value = Profile.UserName;

SqlDataSource1.InsertParameters.Add(param);
}

It may need some adjustment to meet your needs but I believe you got the idea :)

Cheers,

Yani

|||

Im having the exact same problem and Im a Newbie......i can't send the profile.username into the d.b. Could it be possible to elaborate a bit, my detailsview looks almost identical. I've spent two frustrating days and hope to have this figured out........... is there a way to put it into the sql insert statement?? im not the best coder by far!

Thanks!

|||

i tried using your example but keep getting errors

SqlParameter

param =newSqlParameter("profile",SqlDbType.NVarChar);

param.Value = Profile.UserName;

SqlDataSource1.InsertParameters.Add(param);

it keeps telling me that

Error 1 The best overloaded method match for 'System.Web.UI.WebControls.ParameterCollection.Add(System.Web.UI.WebControls.Parameter)' has some invalid arguments C:\Documents and Settings\Karl\My Documents\Visual Studio 2005\WebSites\WebSite_fitness\Members\diet_journal.aspx.cs 21 9 C:\...\WebSite_fitness\

Error 2 Argument '1': cannot convert from 'System.Data.SqlClient.SqlParameter' to 'string' C:\Documents and Settings\Karl\My Documents\Visual Studio 2005\WebSites\WebSite_fitness\Members\diet_journal.aspx.cs 21 45 C:\...\WebSite_fitness\

im totally helpless and frustrated....please help!!

|||

The SelectParameters collection is not a collection of SqlParameter. Use another overload of the add function:

SqlDataSource1.InsertParameters.Add("profile",Profile.UserName);

Cheers,

Yani

|||

Thanks Yani, appreciate it... im going to try it when I get home. is it possible to use it in the asp detailsview? basically i bind a textbox during insert and have this problem...... either i can set the to text = bind("profile") or text= membership.get()username. The latter returns my username but wont insert it into my table...lol im quite frustrated and my experience is somewhat lacking..... i use the controls kinda out of the box.......

thanks again !

|||

if maybe u can help with the asp aspect...... this is what my code looks like.... i cant get your page load event to work...... can u maybe walk me through step by step... im really having a rough time!

Thanks!!

@.PageLanguage="C#"MasterPageFile="~/Members/MasterPage.master"AutoEventWireup="true"CodeFile="diet_journal.aspx.cs"Inherits="Members_diet_journal"Title="Untitled Page" %>

asp:ContentID="Content1"ContentPlaceHolderID="ContentPlaceHolder1"Runat="Server"> Diet Journal<br/> <asp:DetailsViewID="DetailsView1"runat="server"AutoGenerateRows="False"DataKeyNames="meal_hist_id_pk"DataSourceID="SqlDataSource1"Height="50px"Style="position: static"Width="125px"DefaultMode="Insert"><Fields><asp:BoundFieldDataField="meal_hist_id_pk"HeaderText="meal_hist_id_pk"InsertVisible="False"ReadOnly="True"SortExpression="meal_hist_id_pk"/><asp:TemplateFieldHeaderText="date"SortExpression="date"><EditItemTemplate><asp:TextBoxID="TextBox2"runat="server"Text='<%# Bind("date") %>'></asp:TextBox></EditItemTemplate><InsertItemTemplate><asp:CalendarID="Calendar1"runat="server"SelectedDate='<%# Bind("date") %>'Style="position: static"></asp:Calendar></InsertItemTemplate><ItemTemplate><asp:LabelID="Label2"runat="server"Text='<%# Bind("date") %>'></asp:Label></ItemTemplate></asp:TemplateField><asp:BoundFieldDataField="meal_des_fk"HeaderText="meal_des_fk"SortExpression="meal_des_fk"/><asp:TemplateFieldHeaderText="profile"SortExpression="profile"><EditItemTemplate><asp:TextBoxID="TextBox1"runat="server"Text='<%# Bind("profile") %>'></asp:TextBox></EditItemTemplate><InsertItemTemplate><asp:TextBoxID="TextBox1"runat="server"Text='<%# bind("profile") %>'></asp:TextBox></InsertItemTemplate><ItemTemplate><asp:LabelID="Label1"runat="server"Text='<%# Bind("profile") %>'></asp:Label></ItemTemplate></asp:TemplateField><asp:BoundFieldDataField="calories"HeaderText="calories"SortExpression="calories"/><asp:BoundFieldDataField="fat"HeaderText="fat"SortExpression="fat"/><asp:BoundFieldDataField="carbs"HeaderText="carbs"SortExpression="carbs"/><asp:BoundFieldDataField="protein"HeaderText="protein"SortExpression="protein"/><asp:BoundFieldDataField="fibre"HeaderText="fibre"SortExpression="fibre"/><asp:CommandFieldShowInsertButton="True"/></Fields></asp:DetailsView><asp:SqlDataSourceID="SqlDataSource1"runat="server"ConflictDetection="CompareAllValues"ConnectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\diet_journal.mdf;Integrated Security=True;User Instance=True"DeleteCommand="DELETE FROM [meal_history] WHERE [meal_hist_id_pk] = @.original_meal_hist_id_pk AND [date] = @.original_date AND [meal_des_fk] = @.original_meal_des_fk AND [profile] = @.original_profile AND [calories] = @.original_calories AND [fat] = @.original_fat AND [carbs] = @.original_carbs AND [protein] = @.original_protein AND [fibre] = @.original_fibre"InsertCommand="INSERT INTO [meal_history] ([date], [meal_des_fk], [profile], [calories], [fat], [carbs], [protein], [fibre]) VALUES (@.date, @.meal_des_fk, @.profile, @.calories, @.fat, @.carbs, @.protein, @.fibre)"OldValuesParameterFormatString="original_{0}"ProviderName="System.Data.SqlClient"SelectCommand="SELECT [meal_hist_id_pk], [date], [meal_des_fk], [profile], [calories], [fat], [carbs], [protein], [fibre] FROM [meal_history]"UpdateCommand="UPDATE [meal_history] SET [date] = @.date, [meal_des_fk] = @.meal_des_fk, [profile] = @.profile, [calories] = @.calories, [fat] = @.fat, [carbs] = @.carbs, [protein] = @.protein, [fibre] = @.fibre WHERE [meal_hist_id_pk] = @.original_meal_hist_id_pk AND [date] = @.original_date AND [meal_des_fk] = @.original_meal_des_fk AND [profile] = @.original_profile AND [calories] = @.original_calories AND [fat] = @.original_fat AND [carbs] = @.original_carbs AND [protein] = @.original_protein AND [fibre] = @.original_fibre"OnSelecting="SqlDataSource1_Selecting"><DeleteParameters><asp:ParameterName="original_meal_hist_id_pk"Type="Int32"/><asp:ParameterName="original_date"Type="DateTime"/><asp:ParameterName="original_meal_des_fk"Type="Int32"/><asp:ParameterName="original_profile"Type="String"/><asp:ParameterName="original_calories"Type="Int32"/><asp:ParameterName="original_fat"Type="Int32"/><asp:ParameterName="original_carbs"Type="Int32"/><asp:ParameterName="original_protein"Type="Int32"/><asp:ParameterName="original_fibre"Type="Int32"/></DeleteParameters><UpdateParameters><asp:ParameterName="date"Type="DateTime"/><asp:ParameterName="meal_des_fk"Type="Int32"/><asp:ParameterName="profile"Type="String"/><asp:ParameterName="calories"Type="Int32"/><asp:ParameterName="fat"Type="Int32"/><asp:ParameterName="carbs"Type="Int32"/><asp:ParameterName="protein"Type="Int32"/><asp:ParameterName="fibre"Type="Int32"/><asp:ParameterName="original_meal_hist_id_pk"Type="Int32"/><asp:ParameterName="original_date"Type="DateTime"/><asp:ParameterName="original_meal_des_fk"Type="Int32"/><asp:ParameterName="original_profile"Type="String"/><asp:ParameterName="original_calories"Type="Int32"/><asp:ParameterName="original_fat"Type="Int32"/><asp:ParameterName="original_carbs"Type="Int32"/><asp:ParameterName="original_protein"Type="Int32"/><asp:ParameterName="original_fibre"Type="Int32"/></UpdateParameters><InsertParameters><asp:ParameterName="date"Type="DateTime"/><asp:ParameterName="meal_des_fk"Type="Int32"/><asp:ParameterName="profile"Type=String/><asp:ParameterName="calories"Type="Int32"/><asp:ParameterName="fat"Type="Int32"/><asp:ParameterName="carbs"Type="Int32"/><asp:ParameterName="protein"Type="Int32"/><asp:ParameterName="fibre"Type="Int32"/></InsertParameters></asp:SqlDataSource><br/>|||

Hi,

when you add parameters from the code behind like this :

SqlDataSource1.InsertParameters.Add("profile",Profile.UserName);

you need to remove the asp tag for that parameter from the aspx page:

<asp:ParameterName="profile"Type=String/>

From <InsertParameters> tag.

Cheers,

Yani

|||Thanks once again yani, I'll be trying that once i get home!! So if i understand correctly, i need to add

SqlDataSource1.InsertParameters.Add("profile",Profile.UserName); inside of the page load event

and remove<asp:ParameterName="profile"Type=String/>....... from the aspx page. For the insert command and values, do i keep @.profile??

also, for my insert template for the textbox...... do i leave it as bind(profile) ?

Thanks a million for taking the time with me, its greatly appreciated...... I'll owe you at least a case of beer!

Karl

|||

Well,

let me try to explain a lil bit more.

So you have for the InsertParameters in the asp code(aspx) sth like:

// some parameters

<asp:ParameterName="profile"Type=String/>

// some parameters

</InsertParameters>

When you compile the whole web site, the aspx pages are parsed and transformed into code, sth like

public class YourPageNameHere: Page

{

// some functions

SqlDataSource1.InsertParameters.Add(paramName, paramvalue);

}

but in your case

<asp:ParameterName="profile"Type=String/>

you have not specified the value here.

So it does not know what to put here.

So instead of doing that in the asp code.

you could do it in the code behind.

SqlDataSource1.InsertParameters.Add("profile",Profile.UserName);

(in the page load function);

where Profile object is accessible.

For the insert command and values, do i keep @.profile??

also, for my insert template for the textbox...... do i leave it as bind(profile) ?

In the insert command - it should remain - since it is the pure sql statement. So keep it as is.

For the insert template...i think you should remove it... actually what are you trying to do there ?

to put as parameter @.profile - the name entered in the text box, or the Profile.UserName ?!

If it is the first you could do sth like:

<asp:ControlParameter ControlID="TextBox1" PropertyName="Text" Name="profile" Type="string" />

Where TextBox1 is the ID of the TextBox control that will be used for entering the profile name.

If this does not solve the problem , please clarify what are you trying to accomplish exactly.

Cheers,

Yani

|||

Awesome, your explanation really cleared things up.... thank you for being super patient and so clear on your explanations...... I'll try this as soon as i get home!.... I can't wait to get this up and running!!

Karl

|||

Hi again, I just tried what you had suggested.........

and keep getting this error

Cannot insert the value NULL into column 'profile', table 'C:\DOCUMENTS AND SETTINGS\KARL\MY DOCUMENTS\VISUAL STUDIO 2005\WEBSITES\WEBSITE_FITNESS\APP_DATA\DIET_JOURNAL.MDF.dbo.meal_history'; column does not allow nulls. INSERT fails.
The statement has been terminated.

Im using your page load event like this :

protectedvoid Page_Load(object sender,EventArgs e)

{

newSqlParameter("profile",SqlDbType.NVarChar);

SqlDataSource1.InsertParameters.Add(

"profile", Profile.UserName);

}

and my asp code looks like this:

@.PageLanguage="C#"MasterPageFile="~/Members/MasterPage.master"AutoEventWireup="true"CodeFile="diet_journal.aspx.cs"Inherits="Members_diet_journal"Title="Untitled Page" %>

</asp:SqlDataSource><br/>

</asp:Content>

To make it simple i just want to insert the date and profile.username............. I keep getting the null profile error though ...i dont know why it wont pass my value to the d.b

Im sorry if Im not getting it, I thought I was a little smarter than this but this problem is kicking my butt!!!

Karl

|||

SUCCESS!!! I got it to work, I used your explaination and I went about it a little bit differently

for the page load i used........

SqlDataSource1.InsertParameters.Add(

"profile",Membership.GetUser().UserName);

and it works.....I guess the profile.name wasn't what i needed, but I did remove the other insert parameter from the aspx page....."profile"

Thank you soooooo much ........I didnt realize thatI have so much to learn!

|||

Hi Karl,

it's great you did it on your own :) but not just copying sth from someone that you don't understand.

We all study each day, so there is much left to be learnt ;)

Cheers,

Yani

|||

Very true Yani !!

Thanks again for all the patience and help, it is very much appreciated!

Have a great day,

Karl

Tuesday, February 14, 2012

Database update and insert problem

Hi,

I have 3 short(ish) questions, If someone could help I'd be very grateful..

This is the situation - I have a formview which contains a button, which on clicking should insert a row into one table, and update a column value of another. I will provide the code at the end.

1. On clicking, I get the error:

System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'WHERE'.

it doesnt say where, but the stack trace is:

[SqlException (0x80131904): Incorrect syntax near the keyword 'WHERE'.]
 System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +180
 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +68
 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +199
 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2411
 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +147
 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +1089
 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +314
 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +413
 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +115
 System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation) +395
 System.Web.UI.WebControls.SqlDataSourceView.ExecuteUpdate(IDictionary keys, IDictionary values, IDictionary oldValues) +643
 System.Web.UI.DataSourceView.Update(IDictionary keys, IDictionary values, IDictionary oldValues, DataSourceViewOperationCallback callback) +78
 System.Web.UI.WebControls.FormView.HandleUpdate(String commandArg, Boolean causesValidation) +1151
 System.Web.UI.WebControls.FormView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup) +429
 System.Web.UI.WebControls.FormView.OnBubbleEvent(Object source, EventArgs e) +88
 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35
 System.Web.UI.WebControls.FormViewRow.OnBubbleEvent(Object source, EventArgs e) +109
 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35
 System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +86
 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +155
 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +4886

2. When updating, at the moment it will update with a new quantity provided in the text box, but id like the quantity in the textbox subtracted from the current column value - how would this be done?

3. For inserting, i was wondering if i need a command in the button_click to activate the insert command, similar to the one i have one for the update. I have tried but i cant seem to get the syntax right...

Code:

private bool ExecuteUpdate(int quantity)
{
SqlConnection con = new SqlConnection();
con.ConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\ASPNETDB.MDF;Integrated Security=True;User Instance=True";

con.Open();

SqlCommand command = new SqlCommand();
command.Connection = con;
TextBox TextBox1 = (TextBox)FormView1.FindControl("TextBox1");
Label labname = (Label)FormView1.FindControl("Label3");
Label labid = (Label)FormView1.FindControl("Label13");

command.CommandText = "UPDATE Items SET Quantityavailable = @.qty WHERE productID=@.productID";
command.Parameters.Add("@.qty", TextBox1.Text);
command.Parameters.Add("@.productID", labid.Text);
command.ExecuteNonQuery();

con.Close();
return true;
}

private bool ExecuteInsert(String quantity)
{
SqlConnection con = new SqlConnection();
con.ConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\ASPNETDB.MDF;Integrated Security=True;User Instance=True";

con.Open();

command.CommandText = "INSERT INTO Transactions (Usersname)VALUES (@.User)"+
"INSERT INTO Transactions (Itemid)VALUES (@.productID)"+
"INSERT INTO Transactions (itemname)VALUES (@.Itemsname)"+
"INSERT INTO Transactions (Date)VALUES (+DateTime.Now.ToString() +)"+
"INSERT INTO Transactions (Qty)VALUES (@.qty)"+
command.Parameters.Add("@.User", System.Web.HttpContext.Current.User.Identity.Name);
command.Parameters.Add("@.Itemsname", labname.Text);
command.Parameters.Add("@.productID", labid.Text);
command.Parameters.Add("@.qty", TextBox1.Text);
command.ExecuteNonQuery();

con.Close();
return true;
}

protected void Button2_Click(object sender, EventArgs e)
{
TextBox TextBox1 = FormView1.FindControl("TextBox1") as TextBox;
ExecuteUpdate(Int32.Parse(TextBox1.Text) );

}

Huge thanks for anyone who can help shed some light!!

Thanks again,

Jon

1. Debug your application and see the query which is being executed by the SqlDataSource's update event. There might be a space missing in the query. The error is self explanatory - there is syntax error. Try to run the query in the query analyzer.

' After successful updationdim qty asInteger =Integer(QuantityTB.TextBox)dim available asInteger =Integer(AvailableTB.TextBox)' Update the available text boxAvailableTB.TextBox = available - qty

if e.CommandName ="insert" then SqlDataSource1.Insert()end if

|||

Hi, thanks for your response!

1. I had tried debugging but it didn't detect any errors or warnings.. is this what you mean or am i missing the point?

2. I meant to update the column in the database table - it currently updates with just the number that is written in the textbox, but id like it to take away the number written in the textbox from the number that is in their already - do you mean use this code to create a textbox, then update the table with this new value?

3. Is that VB? Do you have it in C# if it is? Or if it isn't, where shall I put it? it doesn't like the syntax in button2_click...

Thanks alot for your help!!!

Cheers,

Jon

|||

1. When I meant debug, put a breakpoint after the query has been formed and now check the value of the query. Copy this to the query analyzer and see if it runs fine. If it does then your query is fine, problem lies somewhere else. If it does not, watch out the error thrown by query analyzer. In your original post, you had written that you were getting error message. Did that error disappear?

2. When you insert the ordered quantity, I thought you would deduct the quantity from the stock. I would suggest, whenever you want to insert a ordered product, then it should be like one transaction.

a. Check if the ordered quantity is not bigger than the available stock.

b. Only add the record, if the above condition is true. If the transaction went through OK, rebind the grid, so that it will show new values.

c. Rebinding of the gridview is good practice, because there can be more than a single user attempting to fill out the order.

No, you do not need to create a new textbox. I was just showing how to update the UI.

3. It is in VB, but simple to convert to C#. It ideally would be in the command event handler of the Insert button.

void InsertCommandBtn_Command(Object sender, CommandEventArgs e) {if (e.CommandName =="insert") { SqlDataSource1.Insert(); }}

|||

Hi cheers for help.

Your code seems to work fine. Im getting a different error message now, and need help updating the table - Ill put it to the forum.

Thanks again!

Jon

Thursday, March 29, 2012

Datatype Conversion Problem.

Datatype Conversion Problem.

Tuesday, March 27, 2012

Datasource information retrieved from a database

Thursday, March 22, 2012

Dataset Query Parameter

dataset problem

Dataset parameters question.

Dataset Merge and @@IDENTITY

Wednesday, March 21, 2012

DataSet Filter

Thursday, March 8, 2012

DataFlow Task & Filters

Sunday, February 26, 2012

DataBinding with Insert query problem

Tuesday, February 14, 2012

Database update and insert problem

date and time diffs

Blog Archive

About Me