SQL Server, SQL Server Express, and SqlDataSource Control

reduce quantity from database

zaihanadya — Mon, 23 Nov 2009 04:14:55 GMT

Hi, im a new user…n ive a problem on how to reduce quantity automatically from database?do I need 2 make 2 tables? Is there any codes that can help me out? For example I develop an inventory system for chemical, once user submit d order button the quantity reduce in database…pliz help me…

cant update db

Speranza — Mon, 23 Nov 2009 02:51:30 GMT

i have table with 3 columns

id > int autoincrement PK

name

surname

        int userid = Convert.ToInt32(Session["id"]);
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
        string upstr = "update customers set name=@name,surname=@surname where id=@id";
        SqlCommand cmd = new SqlCommand(upstr, con);
        cmd.CommandType = CommandType.Text;
        SqlParameter param = new SqlParameter();
        param.ParameterName = "@id";
        param.Value = userid;
        cmd.Parameters.Add(param);
        con.Open();
        cmd.Parameters.AddWithValue("@name", name.Text);
        cmd.Parameters.AddWithValue("@surname", surname.Text);
        cmd.ExecuteNonQuery();
        cmd.Dispose();
        con.Close();

i put a break point to   param.value=userid //// it shows me user id correctly but  param.value has null value and cant update customer.

can you help me about this topic or is there anyway for update customer?

unable to restore backup

vijji — Wed, 18 Nov 2009 05:07:56 GMT

getting error differential backup cannot be restored because no files are ready to roll forward??

every time i restored with recovery mode =true,im successfully restored the backup.

this time im facing problem,im having a single user database "xyz",and having daily backup.

im getting the same error for all the referential points.

Connection String Woes....

curtisdehaven — Tue, 17 Nov 2009 21:17:48 GMT

Hi All,

We're deploying our ASP.Net application into a production environment. It connects to customer-owned SQL server database - external to us.

Our application includes some proprietary code that connects to the same database via DSN. In setting up the DSN, we configure it to authenticate using "Windows NT authentication using the network login id" and it works fine.

We're now trying to come up with a connection string for the ASP.Net side of the application. So far we've come up with...

<add name="My Server" connectionString="Data Source=MYSERVER; Initial Catalog=MyDatabase; Integrated Security=True;" />

When we go to open the connection, it's erroring out with the message "Login failed for user 'MYDOMAIN/MYSERVER$"

Obviously, by leaving the UID and PWD out of the connection string, it's picking up a login name (and i spose as password) but it doesn't appear to be the same one the DSN is using - the so-called "network login id".

Any thoughts? (Oh yeah, it's Server 2008 / ASP.Net Version 2.0)

Many thanks - Curt

Select distinct

Noorstudio — Mon, 23 Nov 2009 03:59:04 GMT

Hello.

What's wrong on my query that I am getting similar numbers??

Select distinct (ConsumptionKWH ),ConsumptionYear, ConsumptionMonth from PeriodicConsumptions WHERE AccountNo = '0000'

Result is: 

10000	2009	1
10000	2009	4
10000	2010	9
20000	2009	2
20000	2009	3
30000	2010	3
30000	2011	8
40000	2009	11
40000	2011	11
50000	2010	11
60000	2010	10
60000	2011	3
70000	2011	5

Thanks

Complicated SQL query

Cohen Asaf — Sun, 22 Nov 2009 15:10:14 GMT

Hi..

I'm having with a tripple join..

By solving this query problem I hope to avoid a) calling the sproc from LINQ (the sproc uses a #temp table)

and B) returning tabular data from dataset.

The query searches for sport results of teams one round after they beat a certain team (for example the reigning champs)

so the end result of the query is

Exec Teams_That_Beat_the_Champ(@Season, @Champ)

40.000 %Won 10.000 %Drew 40.000 Lost%

The way achieve the result now is create a #temp table with all the teams that beat the champ in a certain season and join that with the next round results for those teams:

Results table:

My query:

ALTER PROCEDURE [dbo].[Beat_Champ_In_Season]
    -- Add the parameters for the stored procedure here
    @Season int, @Champ int
AS
BEGIN
Declare @t int

    -- Insert statements for procedure here
CREATE TABLE #Temp ( Times int, wk int, teamID int)

INSERT Into #Temp (Times, wk, teamID)
Select Count(*) As Times, [Week], (Case When HteamID=@Champ Then AteamID When ATeamID=@Champ then HTeamID END) As teamID From Results_Table
Where
((HTeamID=@Champ and Ascore>Hscore) Or (ATeamID=@Champ and Hscore>Ascore)) And Season=@Season And [Week]<38
Group By HTeamID, ATeamID, [Week]
Order By [Week]

Set @t=(Select COUNT(*) From #Temp)

Select (100.0*Count(case when ((RTable.HTeamID=#Temp.teamID And RTable.Hscore>RTable.Ascore) Or (RTable.ATeamID=#Temp.teamID And RTable.Ascore>RTable.Hscore)) Then 1 else null end))/@t as Won,
       (100.0*Count(case when ((RTable.HTeamID=#Temp.teamID And RTable.Ascore>RTable.Hscore) Or (RTable.ATeamID=#Temp.teamID And RTable.Ascore<RTable.Hscore)) Then 1 else null end))/@t as Lost,
       (100.0*Count(case when (RTable.Ascore=RTable.Hscore) Then 1 else null end))/@t as Drew
From #Temp Join Results_Table as RTable
On RTable.Week=#Temp.wk+1 And
(RTable.ATeamID=#Temp.teamID Or RTable.HTeamID=#Temp.teamID )
Where RTable.Season=@Season
Delete #Temp
END

So my question is how to avoid using the #temp table

and what would be the best practice for running a program that searches for any re-occurances in sports results that are over say 80%..

Thanks,

Asaf

delete a record from table....if it exists

mjta — Thu, 19 Nov 2009 21:29:43 GMT

I want only one record to exist in a table per user. The user has an option to Select a value from a gridView. The selected record is inserted into a table...but, if there is a record for that member already there, then the existing record needs to be deleted and then the insert should take place. I don't know how to check for an existing record and if found, then deleted. The insert works fine (see code).

Basically, I'll have a userId stored in a session, and that is where the parameter will come from. I need an If statement to delete any existing records with the userId session value....then the insert, which will be in the same table as the delete. I need help with the "search and if found, delete" syntax. (I don't have UserId shown here as am insert parameter value...yet...FYI)

Protected Sub GridView1_RowCommand(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewCommandEventArgs) Handles GridView1.RowCommand
        If e.CommandName = "Select" Then
            Dim index As Integer = Convert.ToInt32(e.CommandArgument)
            Dim ds As New SqlDataSource()
            ds.ConnectionString = ConfigurationManager.ConnectionStrings("ConnectionString").ToString()
            ds.InsertCommandType = SqlDataSourceCommandType.Text
            ds.InsertCommand = "INSERT INTO Colors2(ColorName) VALUES(@ColorName)"
            ds.InsertParameters.Add("ColorName", GridView1.Rows(index).Cells(2).Text)
            ds.Insert()
        End If
        Response.Redirect("Default.aspx")
    End Sub

Server error that I don't understand

alcsew — Mon, 23 Nov 2009 02:35:25 GMT

I have a formview that I've done no editing to other than to have it autoinsert two values. I've been using this code for days with no issue. Now all of a sudden on this form I'm getting this error:

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

Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near ')'.

Source Error: 

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.  

Stack Trace: 


[SqlException (0x80131904): Incorrect syntax near ')'.]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +1950890
   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +4846875
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +194
   System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2392
   System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +204
   System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +954
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162
   System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +175
   System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +137
   System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation) +386
   System.Web.UI.WebControls.SqlDataSourceView.ExecuteUpdate(IDictionary keys, IDictionary values, IDictionary oldValues) +325
   System.Web.UI.DataSourceView.Update(IDictionary keys, IDictionary values, IDictionary oldValues, DataSourceViewOperationCallback callback) +92
   System.Web.UI.WebControls.FormView.HandleUpdate(String commandArg, Boolean causesValidation) +835
   System.Web.UI.WebControls.FormView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup) +509
   System.Web.UI.WebControls.FormView.OnBubbleEvent(Object source, EventArgs e) +95
   System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +37
   System.Web.UI.WebControls.FormViewRow.OnBubbleEvent(Object source, EventArgs e) +113
   System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +37
   System.Web.UI.WebControls.LinkButton.OnCommand(CommandEventArgs e) +118
   System.Web.UI.WebControls.LinkButton.RaisePostBackEvent(String eventArgument) +135
   System.Web.UI.WebControls.LinkButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +175
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1565

The code that I'm using in the code behind is as follows:

    Protected Sub FormView1_DataBound(ByVal sender As Object, ByVal e As EventArgs)
        Dim employeeidtextbox As TextBox = TryCast(FormView1.FindControl("employeeidtextbox"), TextBox)
        Dim WeekEndingDateTextbox As TextBox = TryCast(FormView1.FindControl("WeekEndingDateTextbox"), TextBox)


        If employeeidtextbox IsNot Nothing Then
            employeeidtextbox.Text = ddlEmployeName.SelectedValue


        End If

        If WeekEndingDateTextbox IsNot Nothing Then
            WeekEndingDateTextbox.Text = txtWeekEndingDate.Text

        End If
    End Sub

It inserts the data correctly and I don't see anything out of the ordinary in the database.

The code for the page itself is as follows:

 <asp:SqlDataSource ID="sqlTimeSheet" runat="server" 
                                            ConflictDetection="CompareAllValues" 
                                            ConnectionString="<%$ ConnectionStrings:SECTHWConnectionString %>" 
                                            DeleteCommand="DELETE FROM [tblTimeSheet] WHERE [TimeSheetID] = @original_TimeSheetID AND (([EmployeeID] = @original_EmployeeID) OR ([EmployeeID] IS NULL AND @original_EmployeeID IS NULL)) AND (([Key] = @original_Key) OR ([Key] IS NULL AND @original_Key IS NULL)) AND (([Date] = @original_Date) OR ([Date] IS NULL AND @original_Date IS NULL)) AND (([StartTime] = @original_StartTime) OR ([StartTime] IS NULL AND @original_StartTime IS NULL)) AND (([EndTime] = @original_EndTime) OR ([EndTime] IS NULL AND @original_EndTime IS NULL)) AND (([Town/District] = @original_column1) OR ([Town/District] IS NULL AND @original_column1 IS NULL)) AND (([WeekEndingDate] = @original_WeekEndingDate) OR ([WeekEndingDate] IS NULL AND @original_WeekEndingDate IS NULL)) AND (([TimeSheetComments] = @original_TimeSheetComments) OR ([TimeSheetComments] IS NULL AND @original_TimeSheetComments IS NULL)) AND (([StudentID] = @original_StudentID) OR ([StudentID] IS NULL AND @original_StudentID IS NULL))" 
                                            InsertCommand="INSERT INTO [tblTimeSheet] ([EmployeeID], [Key], [Date], [StartTime], [EndTime], [Town/District], [WeekEndingDate], [TimeSheetComments], [StudentID]) VALUES (@EmployeeID, @Key, @Date, @StartTime, @EndTime, @column1, @WeekEndingDate, @TimeSheetComments, @StudentID)" 
                                            OldValuesParameterFormatString="original_{0}" 
                                            SelectCommand="SELECT [TimeSheetID], [EmployeeID], [Key], [Date], [StartTime], [EndTime], [Town/District] AS column1, [WeekEndingDate], [TimeSheetComments], [StudentID] FROM [tblTimeSheet] WHERE ([EmployeeID] = @EmployeeID)" 
                                            
                                            
                                            UpdateCommand="UPDATE [tblTimeSheet] SET [EmployeeID] = @EmployeeID, [Key] = @Key, [Date] = @Date, [StartTime] = @StartTime, [EndTime] = @EndTime, [Town/District] = @column1, [WeekEndingDate] = @WeekEndingDate, [TimeSheetComments] = @TimeSheetComments, [StudentID] = @StudentID WHERE [TimeSheetID] = @original_TimeSheetID)">
                                            <SelectParameters>
                                                <asp:ControlParameter ControlID="ddlEmployeName" Name="EmployeeID" 
                                                    PropertyName="SelectedValue" Type="Int32" />
                                            </SelectParameters>
                                            <DeleteParameters>
                                                <asp:Parameter Name="original_TimeSheetID" Type="Int32" />
                                                <asp:Parameter Name="original_EmployeeID" Type="Int32" />
                                                <asp:Parameter Name="original_Key" Type="Int32" />
                                                <asp:Parameter Name="original_Date" Type="DateTime" />
                                                <asp:Parameter Name="original_StartTime" Type="DateTime" />
                                                <asp:Parameter Name="original_EndTime" Type="DateTime" />
                                                <asp:Parameter Name="original_column1" Type="Int32" />
                                                <asp:Parameter Name="original_WeekEndingDate" Type="DateTime" />
                                                <asp:Parameter Name="original_TimeSheetComments" Type="String" />
                                                <asp:Parameter Name="original_StudentID" Type="Int32" />
                                            </DeleteParameters>
                                            <UpdateParameters>
                                                <asp:Parameter Name="EmployeeID" Type="Int32" />
                                                <asp:Parameter Name="Key" Type="Int32" />
                                                <asp:Parameter Name="Date" Type="DateTime" />
                                                <asp:Parameter Name="StartTime" Type="DateTime" />
                                                <asp:Parameter Name="EndTime" Type="DateTime" />
                                                <asp:Parameter Name="column1" Type="Int32" />
                                                <asp:Parameter Name="WeekEndingDate" Type="DateTime" />
                                                <asp:Parameter Name="TimeSheetComments" Type="String" />
                                                <asp:Parameter Name="StudentID" Type="Int32" />
                                                <asp:Parameter Name="original_TimeSheetID" Type="Int32" />
                                                <asp:Parameter Name="original_EmployeeID" Type="Int32" />
                                                <asp:Parameter Name="original_Key" Type="Int32" />
                                                <asp:Parameter Name="original_Date" Type="DateTime" />
                                                <asp:Parameter Name="original_StartTime" Type="DateTime" />
                                                <asp:Parameter Name="original_EndTime" Type="DateTime" />
                                                <asp:Parameter Name="original_column1" Type="Int32" />
                                                <asp:Parameter Name="original_WeekEndingDate" Type="DateTime" />
                                                <asp:Parameter Name="original_TimeSheetComments" Type="String" />
                                                <asp:Parameter Name="original_StudentID" Type="Int32" />
                                            </UpdateParameters>
                                            <InsertParameters>
                                                <asp:Parameter Name="EmployeeID" Type="Int32" />
                                                <asp:Parameter Name="Key" Type="Int32" />
                                                <asp:Parameter Name="Date" Type="DateTime" />
                                                <asp:Parameter Name="StartTime" Type="DateTime" />
                                                <asp:Parameter Name="EndTime" Type="DateTime" />
                                                <asp:Parameter Name="column1" Type="Int32" />
                                                <asp:Parameter Name="WeekEndingDate" Type="DateTime" />
                                                <asp:Parameter Name="TimeSheetComments" Type="String" />
                                                <asp:Parameter Name="StudentID" Type="Int32" />
                                            </InsertParameters>
                                        </asp:SqlDataSource>
                                        <asp:SqlDataSource ID="sqlStudent" runat="server" 
                                            ConnectionString="<%$ ConnectionStrings:SECTHWConnectionString %>" 
                                            
                                            SelectCommand="SELECT [IDStudentName], [Student Name] AS Student_Name FROM [qryStudentName] ORDER BY [Student Name]">
                                        </asp:SqlDataSource>
                                        <asp:SqlDataSource ID="SqlEmployee" runat="server" 
                                            ConnectionString="<%$ ConnectionStrings:SECTHWConnectionString %>" 
                                            SelectCommand="SELECT [Therapist], [EmployeeID] FROM [tblEmployee] WHERE ([EmployeeID] = @EmployeeID)">
                                            <SelectParameters>
                                                <asp:ControlParameter ControlID="ddlEmployeName" Name="EmployeeID" 
                                                    PropertyName="SelectedValue" Type="Int32" />
                                            </SelectParameters>
                                        </asp:SqlDataSource>
                                        <asp:SqlDataSource ID="sqlKey" runat="server" 
                                            ConnectionString="<%$ ConnectionStrings:SECTHWConnectionString %>" 
                                            
                                            SelectCommand="SELECT KeyCode, KeyDescription, IDTimeSheetKey FROM tblTimeSheetKeyDefinitions ORDER BY KeyDescription">
                                        </asp:SqlDataSource>
                                        <asp:SqlDataSource ID="sqlTownData" runat="server" 
                                            ConnectionString="<%$ ConnectionStrings:SECTHWConnectionString %>" 
                                            
                                            SelectCommand="SELECT [Town/Agent] AS column1, TownAgentID FROM [tblTown-Agents] ORDER BY column1">
                                        </asp:SqlDataSource>
                                    </td>
                                </tr>
                                <tr>
                                    <td valign="top">
                                        <asp:FormView ID="FormView1" runat="server" AllowPaging="True" 
                                            DataKeyNames="TimeSheetID" DataSourceID="sqlTimeSheet" OnDataBound="FormView1_DataBound">
                                            <EditItemTemplate>
                                                TimeSheetID:
                                                <asp:Label ID="TimeSheetIDLabel1" runat="server" 
                                                    Text='<%# Eval("TimeSheetID") %>' />
                                                <br />
                                                EmployeeID:
                                                <asp:TextBox ID="EmployeeIDTextBox" runat="server" 
                                                    Text='<%# Bind("EmployeeID") %>' />
                                                <br />
                                                Key:
                                                <asp:TextBox ID="KeyTextBox" runat="server" Text='<%# Bind("Key") %>' />
                                                <br />
                                                Date:
                                                <asp:TextBox ID="DateTextBox" runat="server" Text='<%# Bind("Date") %>' />
                                                <br />
                                                StartTime:
                                                <asp:TextBox ID="StartTimeTextBox" runat="server" 
                                                    Text='<%# Bind("StartTime") %>' />
                                                <br />
                                                EndTime:
                                                <asp:TextBox ID="EndTimeTextBox" runat="server" Text='<%# Bind("EndTime") %>' />
                                                <br />
                                                column1:
                                                <asp:TextBox ID="column1TextBox" runat="server" Text='<%# Bind("column1") %>' />
                                                <br />
                                                WeekEndingDate:
                                                <asp:TextBox ID="WeekEndingDateTextBox" runat="server" 
                                                    Text='<%# Bind("WeekEndingDate") %>' />
                                                <br />
                                                TimeSheetComments:
                                                <asp:TextBox ID="TimeSheetCommentsTextBox" runat="server" 
                                                    Text='<%# Bind("TimeSheetComments") %>' />
                                                <br />
                                                StudentID:
                                                <asp:TextBox ID="StudentIDTextBox" runat="server" 
                                                    Text='<%# Bind("StudentID") %>' />
                                                <br />
                                                <asp:LinkButton ID="UpdateButton" runat="server" CausesValidation="True" 
                                                    CommandName="Update" Text="Update" />
                                                 <asp:LinkButton ID="UpdateCancelButton" runat="server" 
                                                    CausesValidation="False" CommandName="Cancel" Text="Cancel" />
                                            </EditItemTemplate>
                                            <InsertItemTemplate>
                                                EmployeeID:
                                                <asp:TextBox ID="EmployeeIDTextBox" runat="server" 
                                                    Text='<%# Bind("EmployeeID") %>' />
                                                <br />
                                                Key:
                                                <asp:TextBox ID="KeyTextBox" runat="server" Text='<%# Bind("Key") %>' />
                                                <br />
                                                Date:
                                                <asp:TextBox ID="DateTextBox" runat="server" Text='<%# Bind("Date") %>' />
                                                <br />
                                                StartTime:
                                                <asp:TextBox ID="StartTimeTextBox" runat="server" 
                                                    Text='<%# Bind("StartTime") %>' />
                                                <br />
                                                EndTime:
                                                <asp:TextBox ID="EndTimeTextBox" runat="server" Text='<%# Bind("EndTime") %>' />
                                                <br />
                                                column1:
                                                <asp:TextBox ID="column1TextBox" runat="server" Text='<%# Bind("column1") %>' />
                                                <br />
                                                WeekEndingDate:
                                                <asp:TextBox ID="WeekEndingDateTextBox" runat="server" 
                                                    Text='<%# Bind("WeekEndingDate") %>' />
                                                <br />
                                                TimeSheetComments:
                                                <asp:TextBox ID="TimeSheetCommentsTextBox" runat="server" 
                                                    Text='<%# Bind("TimeSheetComments") %>' />
                                                <br />
                                                StudentID:
                                                <asp:TextBox ID="StudentIDTextBox" runat="server" 
                                                    Text='<%# Bind("StudentID") %>' />
                                                <br />
                                                <asp:LinkButton ID="InsertButton" runat="server" CausesValidation="True" 
                                                    CommandName="Insert" Text="Insert" />
                                                 <asp:LinkButton ID="InsertCancelButton" runat="server" 
                                                    CausesValidation="False" CommandName="Cancel" Text="Cancel" />
                                            </InsertItemTemplate>
                                            <ItemTemplate>
                                                TimeSheetID:
                                                <asp:Label ID="TimeSheetIDLabel" runat="server" 
                                                    Text='<%# Eval("TimeSheetID") %>' />
                                                <br />
                                                EmployeeID:
                                                <asp:Label ID="EmployeeIDLabel" runat="server" 
                                                    Text='<%# Bind("EmployeeID") %>' />
                                                <br />
                                                Key:
                                                <asp:Label ID="KeyLabel" runat="server" Text='<%# Bind("Key") %>' />
                                                <br />
                                                Date:
                                                <asp:Label ID="DateLabel" runat="server" Text='<%# Bind("Date") %>' />
                                                <br />
                                                StartTime:
                                                <asp:Label ID="StartTimeLabel" runat="server" Text='<%# Bind("StartTime") %>' />
                                                <br />
                                                EndTime:
                                                <asp:Label ID="EndTimeLabel" runat="server" Text='<%# Bind("EndTime") %>' />
                                                <br />
                                                column1:
                                                <asp:Label ID="column1Label" runat="server" Text='<%# Bind("column1") %>' />
                                                <br />
                                                WeekEndingDate:
                                                <asp:Label ID="WeekEndingDateLabel" runat="server" 
                                                    Text='<%# Bind("WeekEndingDate") %>' />
                                                <br />
                                                TimeSheetComments:
                                                <asp:Label ID="TimeSheetCommentsLabel" runat="server" 
                                                    Text='<%# Bind("TimeSheetComments") %>' />
                                                <br />
                                                StudentID:
                                                <asp:Label ID="StudentIDLabel" runat="server" Text='<%# Bind("StudentID") %>' />
                                                <br />
                                                <asp:LinkButton ID="EditButton" runat="server" CausesValidation="False" 
                                                    CommandName="Edit" Text="Edit" />
                                                 <asp:LinkButton ID="DeleteButton" runat="server" CausesValidation="False" 
                                                    CommandName="Delete" Text="Delete" />
                                                 <asp:LinkButton ID="NewButton" runat="server" CausesValidation="False" 
                                                    CommandName="New" Text="New" />
                                            </ItemTemplate>
                                            <EmptyDataTemplate>
                                                <br />
                                                <asp:LinkButton ID="NewButton" runat="server" CausesValidation="False" 
                                                    CommandName="New" Text="New" />
                                            </EmptyDataTemplate>
                                        </asp:FormView>

I apologize in advance for posting so much code but I don't know where to begin to find the problem and I need to get this taken care of ASAP as I'm under a deadline.

Thanks!

Leesha

SQL Update help needed

tiwas — Sun, 22 Nov 2009 06:33:19 GMT

Hi,

I'm playing around with some *View tables and I seem to have figured out Edit and Delete. However, I cannot seem to find out how to do New.

Here are my questions:
1. Using the Query Builder, my query ends up as "INSERT INTO RegHour(userID, projectID, regStartTime, regEndTime, regBillable, regDescription) VALUES (,,,,,)". This doesn't quite seem right (and it obviously isn't since it's not working ;)
2. UserID is a field from another table. Is it possible to make this a dropdown menu? The UserID field is linked to the UserID in the users table, and I would like to show show the usernames but link to the ID
3. Anyone know any good tutorials on the different *View tables?

Cheers!

Authenticating against SQL database

tiwas — Sun, 22 Nov 2009 06:38:43 GMT

Hi,

I've followed the authentication tutorial on this site and I believe I've grasped the concepts pretty well. However, as far as I could see the tutorial didn't conclude with using an SQL db in the background...

Anyone got any pointers for how to proceed?

Cheers!

alternative Data delete from database

wangxg26 — Sun, 22 Nov 2009 22:59:55 GMT

I want to delete a certain data from the database daily, e.g. expired product. I can get all of them and use admin manually to do it through web page. It seems not good way I like.

Is there any automatical way to do this type database update?

Thanks

Dynamic LINQ query error: Overload resolution failed ....

haggis999 — Mon, 23 Nov 2009 00:24:11 GMT

I am a total newbie to creating dynamic LINQ queries as described in Scott Guthrie's blog article here:

http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx

Unfortunately, I can't get dynamic LINQ queries to work. I have downloaded the VB Dynamic Query Library, copied Dynamic.vb into my ASP.NET site and referenced System.Linq.Dynamic in my VB.NET code behind page. However, my page fails with the compilation error message "Overload resolution failed because no accessible 'Where' can be called with these arguments" on the following line of code:

Recipients = myDBC.Entrants.Where("FirstName = 'David'")

I am probably making a very basic error somewhere. Any help would be much appreciated.

David

Populating an array from a data dase, first index is skipped

istakoza — Sun, 22 Nov 2009 19:49:12 GMT

I am populating an array from a data base like this:

Dim i As Int16 = 0

Dim myCommand As New SqlCommand(SQL, myConnection)

myConnection.Open()

Dim myReader As SqlDataReader = myCommand.ExecuteReader

If myReader.Read Then

While myReader.Read()

LatArray(i) = myReader.Item("ForcedLat")

i = i + 1

End While

myReader.Close()

myConnection.Close()

However, the first result from that data base is always skipped and my array starts being populated with the second result.

To clarify, I always end up with MyArray(0)= "second value from database"

MyArray(1)="Third value from database" and so on.

how to add a primary key field to an existing data table?

ghassan_aljabiri — Sat, 21 Nov 2009 22:55:59 GMT

hello

my table is filled with 10000 records

it has no PK

now i need to add a new field of bigint

it should be an identity field (seed=1,increment=1)

it should be a PK too , where sould i start

Regards

Assign a value from a Database to a Textbox or Label

iLes — Sat, 21 Nov 2009 20:08:40 GMT

I am using Visual Web Developer Express with VB.net

And I Have a Drop Down Menu and what I want to do is when that menu changes, have the code behind (VB) to run a select statement and from SQLServerDataSource2 and take the results which will be a column called Product (will only be one row, one word result) and display it in a label or textbox. Cannot be a gridview or anything.

Can someone please please help me to figure this out, I have been googling for days with nothing that works for me. I just need the simplest solution.

I know how to add data to a table in VB, but for some reason I cant figure out how to run a select statment and use the output.

Thanks a bunch, I look forward to a response.

one of SQL Statement in Stored procedure

persistentDeveloper — Sat, 21 Nov 2009 21:37:25 GMT

I like to know how can i return all the records that matches the records whereever 'Norway' and the comma delimited string matched records.

//dbo.StringSplit is database funciton that splits the @special string into comma delimited , one.
(@special is NULL or special Like('%' + 'Norway' + '%') AND special IN((Select Items from dbo.StringSplit(@special,','))))

Above is the part of stored procedure. I like to know the right SQL statement that can be used to retrieve all records that matches

special Like ('' + 'Norway' + '') AND special IN((SELECT Items FROM dbo.StringSplit(@special,',')))

Thank you

Problem copying cell from Excel into SQL Server Management Studio Express

tottenham_rule_18 — Thu, 19 Nov 2009 19:31:12 GMT

I am trying to copy something like this into a table, but every time it just completely screws up:

(Grafix) Wooden Play People (Age 18m+)

Features:

- 9 twist and stack activity blocks.

- Wooden pieces.

- Approximate height: 13cm.

- Suitable for age 18m+.

Can anyone help me sort it out?

Thanks

Mike

How to find the top 5 entries In table for 5 unique fields?.

danmc13 — Thu, 19 Nov 2009 21:57:09 GMT

Hi hope somebody can point me In the right direction or can understand what Am trying to do, I'll try to explain as best I can, I've been at it for hours.

I'm trying to select the latest top entry In my Article table from each Section using it's SectionId where article publised = 1.

I know I could achieve this by using 5 different queries but have been trying to solve it with this one I basically need the latest entry for each section.

Any guidance would be great, Thanks.

SELECT DISTINCT 
                      TOP (5) SectionId AS ArticleId, UserId, SectionId, ArticleTitle, ArticleHeadline, ArticleByline, ArticleSummary, ArticleStory, DatePublished, 
                      ArticlePriority, SubmissionDate, ArticlePublished
FROM         Articles
WHERE     (ArticlePublished = 1)
ORDER BY DatePublished DESC

Concatenating Columns with the IDs

sonaasp — Thu, 19 Nov 2009 06:09:09 GMT

Dear All

I am getting result as below:

ID Colors

1 Red

1 Blue

1 Black

2 Red

2 Black

3 White

3 Orange

But I need the result as below:

ID Colors

1 Red,Blue,Black

2 Red,Black

3 White,Orange

Can Anybody help me how to form the query for above result............................

There is no position at row -1

Shog Saif — Thu, 19 Nov 2009 08:02:33 GMT

Hello

i have text box and 2 labels and when the user enters any data "number, or charachter" the associated name should be display in the labels.

this is my code

'Create Session item named StudentsID
Session("StudentsID") = IDTextBox.Text

        'Create table adapter to connect the application to the database
        Dim StudentAdapter As New TBTDataSetTableAdapters.Student_TableAdapter
        'Create data table to store the values in the database
        Dim StudentTable As TBTDataSet._Student_DataTable = StudentAdapter.GetData()

Dim rowNumber As Integer = Convert.ToInt32(Session("StudentsID")) - 1

        'Display student name of the enterd ID
        NameLabel2.Text = StudentTable(rowNumber).Student_Name
        ' Display student email of the enterd ID
        EmailLabel2.Text = StudentTable(rowNumber).Email_Address

this is the Error

There is no position at row -1

Help

thanks in Advanced

Problem in Parsing AMAZON XML Using Openxml

Ehsan_aiub — Sun, 22 Nov 2009 07:09:22 GMT

Hello Everybody,
I am trying to parse the below xml document using openxml in sqlserver 2005. Everything is fine except the features tag. I am trying to get all the features nodes of itemattributes node in one xml column(for ex. <features><feature>1</feature><feature>2</feature></features>) but I am getting only one at a time. Let me show you the tsql script that I am executing-

Source

DECLARE @xmlData XML;

set @xmlData = '<?xml version="1.0"?>
<ItemSearchResponse xmlns="http://webservices.amazon.com/AWSECommerceService/2005-10-05">
    <Items>
        <Item>
        <ASIN>B001MIZNEM</ASIN>
        <DetailPageURL>http://www.amazon.com/Cake-Mania-Game-Download-Pc/dp/B001MIZNEM%3FSubscriptionId%3D0Z6TA0JMJ7NM6RQP0WR2%26tag%3Dws%26linkCode%3Dxm2%26camp%3D2025%26creative%3D165953%26creativeASIN%3DB001MIZNEM</DetailPageURL>
        <SalesRank>69</SalesRank>

            <ItemAttributes>
                <Binding>Software Download</Binding>
                <Brand>Sandlot Games</Brand>
                <Edition>Standard</Edition>
                <Feature>48 Levels Of Baking Fun.</Feature>
                <Feature>Customize And Upgrade Your Kitchen</Feature>
                <Feature>4 Unique Bakeries To Run</Feature>
                <Feature>Hilarious Seasonal Characters.</Feature>
                <Format>Download</Format>
                <Label>Sandlot Games</Label>
            </ItemAttributes>
        </Item>
    </Items>
</ItemSearchResponse>';

Declare @iDoc int
    EXEC sp_xml_preparedocument @iDoc Output,@xmlData,'<ItemSearchResponse xmlns:ns="http://webservices.amazon.com/AWSECommerceService/2005-10-05"/>'


    SELECT ox.*,getdate(),getdate()
    From OPENXML(@iDoc,'/ns:ItemSearchResponse/ns:Items/ns:Item',3)
    With
    (
        [ASIN] nvarchar(50) 'ns:ASIN',
        DetailPageURL nvarchar(2000) 'ns:DetailPageURL',
        Features xml 'ns:ItemAttributes/ns:Feature'

    ) ox

    Exec sp_xml_removedocument @iDoc

Expected Output:

ASIN	DetailedPageURL	Features
B001MIZNEM	http://www.amazon.com/ Cake-Mania-Game-Download- Pc/dp/B001MIZNEM%3F SubscriptionId%3D0Z6TA0JMJ7 NM6RQP0WR2%26tag%3Dws%26 linkCode%3Dxm2%26camp%3D2025 %26creative%3D165953% 26creativeASIN%3DB001MIZNEM	<Features> <Feature>48 Levels Of Baking Fun.</Feature> <Feature>Customize And Upgrade Your Kitchen</Feature> <Feature>4 Unique Bakeries To Run</Feature> <Feature>Hilarious Seasonal Characters.</Feature> </Features>

Can anybody please help me to achieve the above result? Thanks,

trigger help??

.net_junkie — Sat, 21 Nov 2009 04:34:01 GMT

hi all i am new to triggers i have created a trigger to insert data below is my code**

create trigger insert2 
afetr insert on cust

as
declare @name,@add1
insert into cust(@name,@add1) values(name,add1)
select name,add1 from inserted

but im getting errors i dnt know the written trigger is correct or not so can anyone help me with this??i have another doubt when i write a trigger as it is a kind of stored proc i think there is no need to create another stored proc to insert data is that right??

how can i know when a user insert data into my table using triggers??

code not work in sql 2000

padh — Sat, 21 Nov 2009 12:21:04 GMT

Hi,

Here my code which is worked with sql 2005 version but doesnt worked with sql 2000.

my code is:

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

objCmd = New SqlCommand("spsurvey_master", objCon)
                objCmd.Parameters.AddWithValue("@int_userid", 0)
                objCmd.Parameters.AddWithValue("@var_username", "Hai")
                objCmd.Parameters.AddWithValue("@bit_measure", btType)

                Dim intReturnValue = New SqlParameter("@intUid", Data.SqlDbType.Int)

                objCmd.CommandType = Data.CommandType.StoredProcedure
                intReturnValue.Direction = Data.ParameterDirection.ReturnValue
                objCmd.Parameters.Add(intReturnValue)
                objCmd.ExecuteNonQuery()

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

and stored procedure code is

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

CREATE PROCEDURE [dbo].[spsurvey_master](@int_userid int,@var_username nvarchar(50),@bit_measure bit)
AS
BEGIN
DECLARE @intUid AS numeric(10);

SET NOCOUNT ON;

SET @intUid = 0
IF @intUid > 0
BEGIN
UPDATE survey_master SET user_id=@int_userid,user_name=@var_username,
measurement=@bit_measure WHERE uid = @intUid
END
ELSE
BEGIN
INSERT INTO survey_master (survey_datetime,user_id,user_name,measurement) VALUES
(getdate(),@int_userid,@var_username,@bit_measure)
SET @intUid = @@IDENTITY
END
RETURN @intUid
END

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

i know above procedure code is wrong with 2000 version. But even output parameter also not worked here.

Please help

regards

Padh

trigger problem??

.net_junkie — Sun, 22 Nov 2009 03:24:27 GMT

hi all i have created a stored proc to insert values and i have created a trigger to insert the same values into my db but whenever i insert the values and click the button same values are entering into my db two times i dnt knw why?? i dint write any code for trigger but how the same values are entering twice??any help??

how to call triggers??

.net_junkie — Sat, 21 Nov 2009 06:12:34 GMT

hi all i have written trigger now i want to call it from my code is it possible to call it like stored proc?? what i want to do is i want to know whenever a new user enters into my db i must get an alert that he is entered how to do it??plzz help me thanks.