Search results matching tag 'sql'

An SQL aggregation query

DLL — Thu, 17 Dec 2009 05:00:00 GMT

Hi there,

I have an table with 2 columns, a date column and an int column and I want to sum the int columns per month where a month start on the 15th and end on the 14th

So the first month in the results will be from today to the 14th (14th of Jan in this case). The second month will be to the 14th of the next month (Feb in this case). The last month in the result will be from the 15th to the end date in the table, so it's possible that the final month could be 2 days of data if for example the last date in the table is Sep17th

Makes sense?

Linq - SQL to GridView

lookitstony — Thu, 17 Dec 2009 05:00:00 GMT

I guess I am having a hard time understanding this. I create the Grid view and I link it to the SQL-Linq datacontext and if I select all fields then the gridview works. I then cut down what fields I want to show directly in the grid view. Now I go into my Linqdatasource and moditfy the selecting event to build my custom "where" clause and everything still works. Now instead of doing r.paymentstatus and getting a "1" I want to do r.tblpaymentstatus.paymentstatus so it retrieves the actual status instead of the number. As long as I do my select for every field in the datasource it works but if I limit down to only the fields I have showing in the gridview I get errors. They range from "anonymous<2> type mismatch" to another long error I will put below.

Goal: To select what fields i want to view and allow them to be editable. I want the tables that are referenced to show the data instead of the reference number. I am also thinkign about making template fields so I can arrange the data differently.. How can I get the r.table.field to populate instead of r.field inside a databound control in the template?

ERROR:System.Web.HttpException: DataBinding: '<>f__AnonymousType0`9[[System.Nullable`1[[System.DateTime, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]], mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089],[System.String, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089],[System.Nullable`1[[System.DateTime, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]], mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089],[System.Nullable`1[[System.DateTime, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]], mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089],[System.Nullable`1[[System.Decimal, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]], mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089],[System.String, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089],[System.String, mscorlib, Version=2.0.0.0,...' does not contain a property with the name 'ReImbursementInfoID'

C# Code:
protected void ldsReimbursement_Selecting(object sender, LinqDataSourceSelectEventArgs e)
    {
        PatientDataContext db = new PatientDataContext();

        var query = from r in db.tblReimbursementInfos
                    where r.SSN == dvPatient.Rows[3].Cells[1].Text
                    select new
                      {
                          Delivery_Date = r.Delivery_Date,
                          DeliveryType = r.tlkpDeliveryType.DeliveryType,
                          EDC = r.EDC,
                          EnrollmentDate = r.EnrollmentDate,
                          Physician_Reimbursement = r.Physician_Reimbursement,
                          PaymentStatus = r.tblPaymentStatus.PaymentStatus,
                          Notes = r.Notes,
                          DeliveryTypeID = r.DeliveryTypeID,
                          EnteredBy = r.EnteredBy


                      };

        e.Result = query;
    }

ASP.NET Code:
<asp:View ID="vwEnrollment" runat="server">
                    <asp:GridView ID="gvReimbursement" runat="server" AutoGenerateColumns="False" 
                        CellPadding="4" DataKeyNames="ReImbursementInfoID" 
                        DataSourceID="ldsReimbursement" ForeColor="#333333" GridLines="None">
                        <RowStyle BackColor="#EFF3FB" />
                        <Columns>
                            
                            <asp:BoundField DataField="Delivery_Date" HeaderText="Delivery" />
                            <asp:BoundField DataField="DeliveryType" HeaderText="DeliveryType" />
                            <asp:BoundField DataField="EDC" HeaderText="EDC"/>
                            <asp:BoundField DataField="Physician_Reimbursement" HeaderText="Physician_Reimbursement"/>
                            <asp:BoundField DataField="EnrollmentDate" HeaderText="Enrolled" />
                            <asp:BoundField DataField="Notes" HeaderText="Notes" />
                            <asp:BoundField DataField="PaymentStatus" HeaderText="Payment Status" />
                            <asp:BoundField DataField="DeliveryTypeID" HeaderText="DeliveryTypeID" />
                            <asp:BoundField DataField="EnteredBy" HeaderText="Entered By" />
                            
                        </Columns>
                        <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
                        <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
                        <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
                        <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
                        <EditRowStyle BackColor="#2461BF" />
                        <AlternatingRowStyle BackColor="White" />
                    </asp:GridView>

I have changed the code so many times tryign to figure out how to get this to work.. so this may not look right because it has been completely mutilated.

Thanks for the help if anyone can help!

Re: error: 26 - Error Locating Server/Instance Specified

shantanu.gupta — Sun, 13 Dec 2009 05:00:00 GMT

Here is the resolution to error 26. Check this video to configure your error. It was a real headache for me for a long time. But this worked.

http://techpint.com/programming/error-26-%E2%80%93-error-locating-serverinstance-specified-sql-server

2008 sqlserver express ssmse w/ tools download

Bob MacNeel — Thu, 10 Dec 2009 05:00:00 GMT

I've downloaded VWD 2008 express and SQL server 2008 express and they are both working OK. I've been trying to download SQL server 2008 SSMSE with tools and cannot find any link that works, including all of the microsoft links. Can anyone please recommend a link that works OK for them

Data Formats

Yanayaya — Wed, 09 Dec 2009 05:00:00 GMT

Please forgive me, this MAY be a sql problem but I have to check as there may be something I can do in ASP.NET to sort this out.

I have a table that has data input from my asp webform.

The first data entry is a date field. This data is input via a calendar extender via AJAX. I display the data from this eable in a gridview however the data that is input into date and diplayed as 01/01/2009 00:00:00

How can I ensure it just shows the date and not the time extensions on the end.

I think my next question may be SQL related. I have a "carriage cost" drop down in my form that gets it's data from a table. The data type is "Money" however it is dispalying all values as 4.9900 when it should just read 4.99.

Any ideas and my apologies if this has crossed into the wrong place. I am just a bit unsure.

sql problem "select x as y from table"

anguswong — Tue, 08 Dec 2009 05:00:00 GMT

"select x as y from table" is easy. but what if i want to convert the name of two coolumns altogether?

say: columnname-x to name-y and columnname-a to name-b?

how should i write in the sql?

Problem formating dates

rich_at_delta — Tue, 08 Dec 2009 05:00:00 GMT

I have read all sorts of help files on formatting the date but I am getting no where. Can you help?

I have a web service that gets some parameters and builds a SQL string. One of the values is a date/time, which I want in the following format:

"yyyy-MMM-dd HH:mm:ss.fff" i.e. Month as 3 letters (preferrably) but more importantly, seconds only to 3 decimals
It is currently doing precision down to 7 decimals.

I have tried all manner of "brute force" methods to try and get it to format it, but it won't.

If I use - "yyyy-MMM-dd HH:mm:ss" - it almost does it - Result - "2009-12-09 13:45:22"

If I use - "yyyy-MMM-dd HH:mm:ss.fff" - it gives me - Result - "2009-12-09 13:45:22.1230000"

"@" & col.ColumnName).Value = Now.ToString("yyyy-MMM-dd HH:mm:ss.fff")

Partial code:
cmd.Parameters(

Multiple Scope_Identity() Values

Moon Unit — Mon, 07 Dec 2009 05:00:00 GMT

This may be more of a SQL question but I'd like to rule out my .net code first. I'm trying to retrieve multiple Scope_Identity() values from multiple insert statements in a single class:

    Protected Sub ASPxButtonSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles ASPxButtonSubmit.Click

        '------------------------------------Insert student info into tblStudents, return studentID and append to querystring
        Dim studentsAdapter As New SummerTableAdapters.tblStudentsTableAdapter
        Dim new_studentID As Integer = Convert.ToInt32(studentsAdapter.InsertStudent( _
                                                       StudentName1.LastName, StudentName1.FirstName, StudentName1.MiddleInitial, _
                                                       StudentAddress1.Address1, StudentAddress1.Address2, StudentAddress1.City, _
                                                       StudentAddress1.State, StudentAddress1.Country, StudentAddress1.PostCode, "3/25/1998"))

        '------------------------------------Generate paymentID in tblPayment
        Dim paymentIDAdapter As New SummerTableAdapters.tblPaymentTableAdapter
        Dim new_paymentID As Integer = paymentIDAdapter.GeneratePaymentID("Cash")

        '------------------------------------Redirect to Classes page w/ studentID and PaymentID in query string
        Response.Redirect("Classes.aspx?studentID=" & new_studentID & "&paymentID=" & new_paymentID)


    End Sub

I am using TableAdapters with the following code:

studentsAdapter

INSERT INTO [tblStudents] ([LastName], [FirstName], [MiddleInitial], [Address1], [Address2], [City], [State], [Country], [PostCode], [DateOfBirth]) VALUES (@LastName, @FirstName, @MiddleInitial, @Address1, @Address2, @City, @State, @Country, @PostCode, @DateOfBirth);

SELECT SCOPE_IDENTITY()

paymentAdapter

INSERT INTO tblPayment (PaymentType) VALUES (@PaymentType); 

SELECT SCOPE_IDENTITY()

The first SELECT SCOPE_IDENTITY() is returning the correct value (the autoID field) however the second always returns 1.

Help creating a dynamic sql query in my tableAdapter

Mattieshark — Mon, 07 Dec 2009 05:00:00 GMT

I have a string variable "@symptomesID" thats sends a list of ID's ( 1,2,3 or 2,3,6,7,33 ) as you can see it needs to be dynamic sometimes i need te send 1 id somethings i need to send more.

Problem is that in my WHERE the pk_symptomsId is a INT and is expecting a INT and not a STRING so i always get a Error and i cant figure out how to get this to work

i found somewhere on the internet that someone was explaning what i need to do but i cant get it to work in my tableAdapter query

http://forums.whirlpool.net.au/forum-replies-archive.cfm/928435.html

this is my query:

SELECT        tblDiseases.diseaseName, tblDiseases.pk_diseaseId AS diseaseID
FROM            tblSymptoms INNER JOIN
                         tblDiseaseSymptom ON tblSymptoms.pk_symptomsId = tblDiseaseSymptom.fk_SymptomId INNER JOIN
                         tblDiseases ON tblDiseaseSymptom.fk_DiseaseId = tblDiseases.pk_diseaseId
WHERE        (tblSymptoms.pk_symptomsId IN(@symptomesID))
GROUP BY tblDiseases.pk_diseaseId, tblDiseases.diseaseName

if i run this query it works but then its not dynamic and i manualy entered the ID's

SELECT        tblDiseases.diseaseName, tblDiseases.pk_diseaseId AS diseaseID
FROM            tblSymptoms INNER JOIN
                         tblDiseaseSymptom ON tblSymptoms.pk_symptomsId = tblDiseaseSymptom.fk_SymptomId INNER JOIN
                         tblDiseases ON tblDiseaseSymptom.fk_DiseaseId = tblDiseases.pk_diseaseId
WHERE        (tblSymptoms.pk_symptomsId IN (1,2,3))
GROUP BY tblDiseases.pk_diseaseId, tblDiseases.diseaseName

hope you can help me out!

thx

Re: How to count Page_Loads and check if there's data in a table

markgr26@hotmail.com — Mon, 07 Dec 2009 05:00:00 GMT

It looks like you do know how to query the database because you're using SqlConnection and SqlCommand in the code snippet you posted originally.

You need to look at some basic T-Sql skills for querying data. Get your query in a stored proc (if you're using Stored Procs to query) and execute Stored Proc using SqlCommand. Read up about the SqlCommand.ExecScalar() method - this will prob. be useful to you.