How To Query And Show The Result In A Form

May 12, 2006

I am an beginner in VC#2005,

my question is:

I connect a table and transtorm to WM5.0 (PDAphone),

I don't know how to show the result of my query,

EX:

table1,(two column: name , phoneNo)

private void button1_Click(object sender, EventArgs e)
{
SqlCeConnection cn = null;
try
{
cn = new SqlCeConnection("Data source=\Programs files\sqltest1\sqlPDA.sdf");


SqlCeCommand cmd = new SqlCeCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select * from Table1 where phoneNo='0922123456'";


SqlCeDataAdapter da = new SqlCeDataAdapter();
DataSet ds = new DataSet();
da.SelectCommand = cmd;

}
finally
{
if (cn.State != ConnectionState.Closed)
{
cn.Close();
}

}
}

there is nothing happen, please help me.

thanks

View 3 Replies


ADVERTISEMENT

Query Results To Only Show ONE Result Per Locale

Feb 5, 2015

My query produces accurate results just produces one instance for each sales region. I am needing it to only give me one instance of each sales region and give me total counts. What should I re-write?

Code:
Select
salesmanname,
case when [state] IN ('GA', 'FL', 'AL', 'SC', 'NC', 'TN') Then 'South'
when [state] IN ('CA', 'NV', 'WA', 'OR', 'TX') Then 'West'
when [state IN ('NY', 'NJ', 'PA', 'DL', 'CT') Then 'NE'
end As [Sales Region]

[Code] ....

View 1 Replies View Related

Analysis :: Show Dimension Names In MDX Query Result

Oct 28, 2015

I am facing an issue in MDX Query. I have a custom MDX query, When I run the query I am getting results too. Now my requirement is to show the dimension names in the query result, So that I can get those header names in the cell set itself. Please see the below image.

In this image I need to show 'Fiscal Year' and 'Fiscal Quarter' in that highlighted area. Is there any custom query for this?

View 3 Replies View Related

How To Make The SSMSE To Return Whole Records Without Any Close Query Form And Re-create Query Form Operation?

Dec 25, 2007

Hi,
I got a problem.
I installed Microsoft SQL Server Management Studio Express 2005 version.
And I created a Compact database.
I created an connection in SSMSE to connect the database and opened a query form.
then, i run the following sql:

Select * from Table1

It returned 3 records to me.
After that, I used program to insert record into this table.
Then i ran this sql again, it still show me 3 records.
I closed the query form, and re-created a new query form, then run the sql, it returned 4 records to me.

Why? It's very strange and difficult to operate, right?
Is there anyone know how to make the SSMSE to return whole records without any close query form and re-create query form operation?

Thanks a lot!

And Merry X'max!!!

View 4 Replies View Related

When Submitting Form, SQL Server Fields Only Show The First Letter

Nov 30, 2005

Hi, I'm new around here and am stumped.  I have created a form where I submit the information into a SQL Server 2000 table.  When I submit the information through the form, only the first character of each field populates inside of the SQL table.  This happens for every field except for two, zip and comments.  The zip field is numeric and the comments field is text.  All the other fields are varchar fields; and these are the fields with the problems.  I have attempted to change the data type to text, but that did not work either.  I am doing this through a stored procedure I created.  Here is the asp code:
Public Class WebForm1
Inherits System.Web.UI.Page
Protected WithEvents txtFirstName As System.Web.UI.WebControls.TextBox
Protected WithEvents txtLastName As System.Web.UI.WebControls.TextBox
Protected WithEvents txtAddress1 As System.Web.UI.WebControls.TextBox
Protected WithEvents txtAddress2 As System.Web.UI.WebControls.TextBox
Protected WithEvents txtCity As System.Web.UI.WebControls.TextBox
Protected WithEvents txtZip As System.Web.UI.WebControls.TextBox
Protected WithEvents txtEmail As System.Web.UI.WebControls.TextBox
Protected WithEvents txtPhone As System.Web.UI.WebControls.TextBox
Protected WithEvents txtComments As System.Web.UI.WebControls.TextBox
Protected WithEvents ddlState As System.Web.UI.WebControls.DropDownList
Protected WithEvents ddlGift1 As System.Web.UI.WebControls.DropDownList
Protected WithEvents ddlGift2 As System.Web.UI.WebControls.DropDownList
Protected WithEvents ddlGift3 As System.Web.UI.WebControls.DropDownList
Protected WithEvents Form1 As System.Web.UI.HtmlControls.HtmlForm
Protected WithEvents SqlConnection1 As System.Data.SqlClient.SqlConnection
Protected WithEvents SqlCommand1 As System.Data.SqlClient.SqlCommand
Protected WithEvents btnSubmit As System.Web.UI.WebControls.Button
#Region " Web Form Designer Generated Code "
'This call is required by the Web Form Designer.
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
Me.SqlConnection1 = New System.Data.SqlClient.SqlConnection()
Me.SqlCommand1 = New System.Data.SqlClient.SqlCommand()
'
'SqlConnection1
'
Me.SqlConnection1.ConnectionString = "data source=NDAVENPORT2;initial catalog=Bluestreak;integrated security=SSPI;persi" & _
"st security info=False;workstation id=NDAVENPORT2;packet size=4096"
'
'SqlCommand1
'
Me.SqlCommand1.CommandText = "dbo.[InsertFreeOrders]"
Me.SqlCommand1.CommandType = System.Data.CommandType.StoredProcedure
Me.SqlCommand1.Connection = Me.SqlConnection1
Me.SqlCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@RETURN_VALUE", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue, False, CType(10, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))
Me.SqlCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@firstname", System.Data.SqlDbType.VarChar, 50))
Me.SqlCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@lastname", System.Data.SqlDbType.VarChar, 50))
Me.SqlCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@address1", System.Data.SqlDbType.VarChar, 100))
Me.SqlCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@address2", System.Data.SqlDbType.VarChar, 100))
Me.SqlCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@city", System.Data.SqlDbType.VarChar, 50))
Me.SqlCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@state", System.Data.SqlDbType.VarChar, 50))
Me.SqlCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@zip", System.Data.SqlDbType.Decimal, 9, System.Data.ParameterDirection.Input, False, CType(18, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))
Me.SqlCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@email", System.Data.SqlDbType.VarChar, 50))
Me.SqlCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@phone", System.Data.SqlDbType.VarChar, 50))
Me.SqlCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@item1", System.Data.SqlDbType.VarChar, 50))
Me.SqlCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@item2", System.Data.SqlDbType.VarChar, 50))
Me.SqlCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@item3", System.Data.SqlDbType.VarChar, 50))
Me.SqlCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@comments", System.Data.SqlDbType.VarChar, 2147483647))
End Sub
Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
InitializeComponent()
End Sub
#End Region
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
End Sub
Private Sub btnSubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
If IsValid Then
SqlCommand1.Parameters("@firstname").Value = txtFirstName.Text
SqlCommand1.Parameters("@lastname").Value = txtLastName.Text
SqlCommand1.Parameters("@address1").Value = txtAddress1.Text
SqlCommand1.Parameters("@address2").Value = txtAddress2.Text
SqlCommand1.Parameters("@city").Value = txtCity.Text
SqlCommand1.Parameters("@state").Value = ddlState.SelectedItem.Text
SqlCommand1.Parameters("@zip").Value = txtZip.Text
SqlCommand1.Parameters("@email").Value = txtEmail.Text
SqlCommand1.Parameters("@phone").Value = txtPhone.Text
SqlCommand1.Parameters("@item1").Value = ddlGift1.SelectedItem.Text
SqlCommand1.Parameters("@item2").Value = ddlGift2.SelectedItem.Text
SqlCommand1.Parameters("@item3").Value = ddlGift3.SelectedItem.Text
SqlCommand1.Parameters("@comments").Value = txtComments.Text
SqlConnection1.Open()
SqlCommand1.ExecuteNonQuery()
SqlConnection1.Close()
Response.Redirect("Success.aspx")
End If
End Sub
End ClassMy stored procedure looks like this:CREATE PROCEDURE dbo.InsertFreeOrders  (  @firstname varchar( 50 ),  @lastname varchar( 50 ),  @address1 varchar( 100 ),  @address2 varchar( 100 ),  @city varchar( 50 ),  @state varchar( 50 ),  @zip numeric,  @email varchar( 50 ),  @phone varchar( 50 ),  @item1 varchar( 50 ),  @item2 varchar( 50 ),  @item3 varchar( 50 ),  @comments text  )ASInsert FreeOrders ( o_first_name, o_last_name, o_address_1, o_address_2, o_city, o_state, o_zip, o_email, o_phone, o_item_1, o_item_2, o_item_3, o_comments ) Values ( @firstname, @lastname, @address1, @address2, @city, @state, @zip, @email, @phone, @item1, @item2, @item3, @comments )GOSo it is working, just not completely and I am stumped here.  Any help would be great!  Thanks.

View 6 Replies View Related

ReportViewer Control Doesnt Show 100% Of Report When Included In .Net Web Form With Other Controls

Apr 17, 2008



I created some controls for filtering the report that will be displayed on the same page, but below these controls. The controls take about 200px of the top of the form, while the report will be displayed in the rest of the page.

I'm using the ReportViewer control in Async mode, so it's being rendered as an Iframe, and this is necessary because some reports will take up to a couple minutes, so I want the loading animation.

As many of you know, this type of scenario will cause two scrollbars to be shown on this Web form: one for the main form, and the other for the Iframe containing the report. To prevent this, I have html,body set to overflow:hidden. This works great at preventing the outer scrollbar from showing, and only the report has a scrollbar.

The problem is that the bottom 200px (see first paragraph above) of the report doesnt show and cant be scrolled to. Has anyone come across a solution for this situation?

I'm basically stuck either having a report with two side by side scrollbars, or a report with 1 scrollbar that cuts off the last portion of each page of a report.

View 1 Replies View Related

How To Show Result For Each Day Of The Past 30 Days?

Feb 27, 2007

Hello,I would like to show the result  fo each day of the past 30 days. I might not have data to display for everyday but I still want the date to show up01/27/2007      resultA01/28/2007      resultB01/29/2007      .....02/26/2007      resultC02/27/2007      resultD
 Thanks for your helpArnold

View 3 Replies View Related

Compute Sum Result Cannot Show In Report?

Aug 23, 2014

How to display sql compute sum command their result in amount and qty in report?

I am try this query is successful show the total amount and qty in studio management server and show in screen. But run this query on report is without to show last column of qty and amount, why ?

select
xsodetail.shopcode,xsodetail.memono,xsodetail.txdate,
xsodetail.sku,xsoheader.depositamt,xsopayment.paymentcode,xsopayment.paymentamt,
xsodetail.itemamt,xsodetail.salesqty

from xsoheader
inner join xsodetail on xsoheader.shopcode + xsoheader.memono = xsodetail.shopcode + xsodetail.memono
inner join xsopayment on xsoheader.shopcode + xsoheader.memono = xsopayment.shopcode + xsopayment.memono
where (xsodetail.sku = 'L000254' or xsodetail.sku='L000256') and xsoheader.voidflag='N' and xsodetail.txdate = CONVERT(varchar(100), GETDATE(), 112)
compute sum(xsopayment.paymentamt) , sum(xsodetail.salesqty)

View 2 Replies View Related

Rotate View - Show Result With 3 Columns In 1 Row

Jan 19, 2012

I have a view with 2 columns and 2 rows(No 1). I want to show the result with 3 columns in 1 row(No 2).

No1:
The view result :
ID | SubjectID
-- ---------
13279 | 900
13279 | 910

No2:
I want to show that result in this structure :
ID | SubjectID | SubjectID2
-- --------- ----------
13279 | 900 | 910

View 1 Replies View Related

How To Eliminate Timestamp - Only Show Date In Result

Feb 21, 2014

With this qry i need to only show in the result for ordate the date and not the time stamp. below is my qry and the first line of the results:

SELECT DISTINCT
adHock.dbo.PPVVINCE$.Corps, adHock.dbo.PPVVINCE$.House, adHock.dbo.PPVVINCE$.Cust,
IDST_EVENT_ORDERS.ORDATE,
IDST_EVENT_ORDERS.ORTIME,
IDST_EVENT_ORDERS.EVWMIN,
IDST_EVENT_ORDERS.EVTDES,

[Code] ....

result for the field trying to change

ORDATE
1/1/2014 12:00:00 AM

I Only want to see the date.

View 6 Replies View Related

Expression To Show Result 12 Months Back

Feb 18, 2008

I´m creating a report in SSRS 2005 where you can filter after month and year. The result is the total sale for that particular month. What I want to do, is adding an extra column to compare with the result in the same month last year. F.eks. say that I choose january 2008 I would like to see the results for january 2007.

I thought of writing an expression to do that, but as that isn't my stronges side I would appreciate some help

View 3 Replies View Related

I Want To Show OpenQuery() Result With Local Table Values

May 12, 2008

Hi, I have two queries as under:QUERY 1:SELECT * FROM OPENQUERY(MYSERVER, 'SELECT * FROM SCOPE() WHERE FREETEXT(''Text to Search'')') AS DocsQUERY 2:SELECT MediaID, LawID, LawDate, Agreement, Name, NameSearch, LawType, LawNo, RegID, IssueNo, AttachmentFrom Dept_LegalLawINNER JOIN Dept_LegalMinistries ON Dept_LegalLaw.RegID = Dept_LegalMinistries.RegIDINNER JOIN Dept_LegalLawType ON Dept_LegalLaw.LawID = Dept_LegalLawType.LawIDWHERE 1=1 AND 1=1 AND 1=1 AND 1=1 AND 1=1 AND 1=1 AND 1=1 AND 1=1 AND 1=1 AND 1=1both queries are working fine separately and I can generate the desired results separately, but I cannot merge them to get one single result for example, after a FREETEXT search I want to get some values from my local table (Query2) to display as one result. Like in Query1 FREETEXT will search the web page based on the given text and in Query2 will select the remaining data from the local database (Title, ID, Name etc etc).Try many thing but no success yet.Need urgent help.Thanks

View 2 Replies View Related

SSRS | For Multiple Dataset | How To Show No Result Message | Norows Property

May 5, 2008

Hi All,

I created one report having more than two datasets. How can I display the "No result found Message" If some of the dataset having no data.

Thanks,
Aneesh

View 4 Replies View Related

Saving Query Result To A File , When View Result Got TLV Error

Feb 13, 2001

HI,
I ran a select * from customers where state ='va', this is the result...

(29 row(s) affected)
The following file has been saved successfully:
C:outputcustomers.rpt 10826 bytes

I choose Query select to a file
then when I tried to open the customer.rpt from the c drive I got this error message. I am not sure why this happend
invalid TLV record

Thanks for your help

Ali

View 1 Replies View Related

End Result Is Main Query Results Ordered By Nested Result

May 1, 2008

As the topic suggests I need the end results to show a list of shows and their dates ordered by date DESC.
Tables I have are structured as follows:

SHOWS
showID
showTitle

SHOWACCESS
showID
remoteID

VIDEOS
videoDate
showID

SQL is as follows:

SELECT shows.showID AS showID, shows.showTitle AS showTitle,
(SELECT MAX(videos.videoFilmDate) AS vidDate FROM videos WHERE videos.showID = shows.showID)
FROM shows, showAccess
WHERE shows.showID = showAccess.showID
AND showAccess.remoteID=21
ORDER BY vidDate DESC;

I had it ordering by showTitle and it worked fine, but I need it to order by vidDate.
Can anyone shed some light on where I am going wrong?

thanks

View 3 Replies View Related

Query Works In 'test Query' But Refuses To Show Up In The Datagrid On A Web Page - Urgent!

Mar 28, 2007

Hey, i've written a query to search a database dependant on variables chosen by user etc etc. Opened up a new sqldatasource, entered the query shown below and went on to the test query page. Entered some test variables, everything works as it should do. Try to get it to show in a datagrid on a webpage - nothing. No data shows.
 SELECT dbo.DERIVATIVES.DERIVATIVE_ID, count(*) AS Matches
FROM dbo.MAKES INNER JOIN
dbo.MODELS ON dbo.MAKES.MAKE_ID = dbo.MODELS.MAKE_ID INNER JOIN
dbo.DERIVATIVES ON dbo.MODELS.MODEL_ID = dbo.DERIVATIVES.MODEL_ID INNER JOIN
dbo.[VALUES] ON dbo.DERIVATIVES.DERIVATIVE_ID = dbo.[VALUES].DERIVATIVE_ID INNER JOIN
dbo.ATTRIBUTES ON dbo.[VALUES].ATTRIBUTE_ID = dbo.ATTRIBUTES.ATTRIBUTE_ID
WHERE ((ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID1 and (@VAL1 is null or VALUE = @VAL1)) or
(ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID2 and (@VAL2 is null or VALUE = @VAL2)) or
(ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID3 and (@VAL3 is null or VALUE = @VAL3)) or
(ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID4 and (@VAL4 is null or VALUE = @VAL4)) )
GROUP BY dbo.DERIVATIVES.DERIVATIVE_ID
HAVING count(*) >= CASE WHEN @VAL1 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN @VAL2 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN @VAL3 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN @VAL4 IS NOT NULL THEN 1 ELSE 0 END -2
ORDER BY count(*) DESC

 Here is the page source
 
<%@ Page Language="VB" MasterPageFile="~/MasterPage.master" Title="Untitled Page" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DevConnectionString1 %>"
SelectCommand="&#9;SELECT dbo.DERIVATIVES.DERIVATIVE_ID, count(*) AS Matches&#13;&#10;&#9;FROM dbo.MAKES INNER JOIN&#13;&#10;&#9;&#9;&#9;&#9; dbo.MODELS ON dbo.MAKES.MAKE_ID = dbo.MODELS.MAKE_ID INNER JOIN&#13;&#10;&#9;&#9;&#9;&#9; dbo.DERIVATIVES ON dbo.MODELS.MODEL_ID = dbo.DERIVATIVES.MODEL_ID INNER JOIN&#13;&#10;&#9;&#9;&#9;&#9; dbo.[VALUES] ON dbo.DERIVATIVES.DERIVATIVE_ID = dbo.[VALUES].DERIVATIVE_ID INNER JOIN&#13;&#10;&#9;&#9;&#9;&#9; dbo.ATTRIBUTES ON dbo.[VALUES].ATTRIBUTE_ID = dbo.ATTRIBUTES.ATTRIBUTE_ID&#13;&#10;&#9;WHERE ((ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID1 and (@VAL1 is null or VALUE = @VAL1)) or&#13;&#10;&#9;&#9; (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID2 and (@VAL2 is null or VALUE = @VAL2)) or&#13;&#10;&#9;&#9; (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID3 and (@VAL3 is null or VALUE = @VAL3)) or&#13;&#10;&#9;&#9; (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID4 and (@VAL4 is null or VALUE = @VAL4)) )&#13;&#10;&#9;GROUP BY dbo.DERIVATIVES.DERIVATIVE_ID&#13;&#10;&#9;HAVING count(*) >= CASE WHEN @VAL1 IS NOT NULL THEN 1 ELSE 0 END +&#13;&#10;&#9;&#9;&#9;&#9;&#9;&#9;&#9;&#9;&#9; CASE WHEN @VAL2 IS NOT NULL THEN 1 ELSE 0 END +&#13;&#10;&#9;&#9;&#9;&#9;&#9;&#9;&#9;&#9;&#9; CASE WHEN @VAL3 IS NOT NULL THEN 1 ELSE 0 END +&#13;&#10;&#9;&#9;&#9;&#9;&#9;&#9;&#9;&#9;&#9; CASE WHEN @VAL4 IS NOT NULL THEN 1 ELSE 0 END -2&#13;&#10;&#9;ORDER BY count(*) DESC&#13;&#10;">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList1" Name="ATT_ID1" PropertyName="SelectedValue" />
<asp:ControlParameter ControlID="TextBox1" Name="VAL1" PropertyName="Text" />
<asp:Parameter Name="ATT_ID2" />
<asp:Parameter Name="VAL2" />
<asp:Parameter Name="ATT_ID3" />
<asp:Parameter Name="VAL3" />
<asp:Parameter Name="ATT_ID4" />
<asp:Parameter Name="VAL4" />
</SelectParameters>
</asp:SqlDataSource>
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:DevConnectionString1 %>"
SelectCommand="SELECT * FROM [ATTRIBUTES]"></asp:SqlDataSource>
<br />
<asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSource2"
DataTextField="ATTRIBUTE_NAME" DataValueField="ATTRIBUTE_ID">
</asp:DropDownList>
<asp:TextBox ID="TextBox1" runat="server" AutoPostBack="True"></asp:TextBox><br />
<br />
<br />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="DERIVATIVE_ID"
DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundField DataField="DERIVATIVE_ID" HeaderText="DERIVATIVE_ID" InsertVisible="False"
ReadOnly="True" SortExpression="DERIVATIVE_ID" />
<asp:BoundField DataField="Matches" HeaderText="Matches" ReadOnly="True" SortExpression="Matches" />
</Columns>
</asp:GridView>
</asp:Content>
 AFAIK I have configured the source to pick up the dropdownlist value and the textbox value (the text box is autopostback).
 Am i not submitting the data correctly? (It worked with a simple query...just not with this one). I have tried a stored procedure which works when testing just not when its live on a webpage.
 Please help!
 
(Visual Web Devleoper 2005 Express and SQL Server Management Studio Express)
 

View 4 Replies View Related

How To Form Query?

Jul 24, 2004

i am using this statement

select dateadd(dd,1,20010331)


and it's throwing an error

Arithmetic overflow error converting expression to data type datetime.

what's wrong?

View 2 Replies View Related

How To Form The Query

Apr 5, 2008

I have two tables. The table is below.

Table name 1 : Income
Income RentMonth
1500 Jan
1500 Feb
1500 Apr

Table name 2 : Expense
Expense ExpMonth
200 Jan
300 Mar
400 Apr

The result table becomes (Profit=Income-Expense)
Profit Month
1300 Jan
1500 Feb
-300 Mar
1100 Apr

But I form the query by join the both Income and Expense tables to subtract the Income and Expense month wise.

But one moth is in one table the same month is not in another table.

For Example Feb month is in Income table, but not in Expense table.And Mar month is in Expense table and not in Income table. So how will I form the query to achieve my result table as i indicated above. Kindly help me.

Kamal.

View 2 Replies View Related

How To Form This Query

Jan 14, 2007

I have a table SIM_Temp where I ahve three fields val1,val2,val3. I have some data like

SIM_Temp
--------


Val1----------Val2-------------Val3
===================================



AIRMECH------172371.13---------2004
AIRMECH------4269.5------------2003
ETA----------2871704.6724------2005
ETA----------6244149.0769------2004
ETA----------9046873.6504------2003


What I want is, I want this to be read like

CustomerName 2003------------2004----------2005

AIRMECH------------4269.5---------172371.13-----Nil
ETA--------------9046873.6504-----6244149.0769--9046873.6504


Please advice me, how could I do this.

Thank you
Ceema

View 7 Replies View Related

How To Form This Query

Jan 17, 2007

I have a table SIM_TempCustomer where I have 5 fileds nad Values Like


SIM_TempCustomer
----------------

Val1.........Val2.........Val3.........Val4.........Val5
---------------------------------------------------------
A.............NUll........1.............Null.........3
B.............Null........Null..........Null.........2
C.............Null........6.............12...........Null


What I want i, if there is any value in any fields, other null values
in the field should be replaced by 0, if all the rows of the filed
are null, simply leave as it is....


Like the select statement of this SIM_TempCustomer should give a result like


Val1.........Val2.........Val3.........Val4.........Val5
---------------------------------------------------------
A.............NUll........1.............0............3
B.............Null........0.............0............2
C.............Null........6.............12...........0


Could any one help me.

Thanks
Ceema

View 1 Replies View Related

Need Help With Sql Query Statement In ASP Form

Jan 28, 2008

I have an ASP form that takes the information that is entered on the form and inserts it into a Microsoft Access database.  It works great.  In addition to the fields from the form, I also want to add the current date into the InitDate field.  How would I modify the SQL query below to insert the current date into the COS database? conn.execute SqlQry        Sql =         "INSERT INTO COS ([Name of School], [Director of COS], [Address], [City], [State], [zip], [PhoneNumber], "        Sql = Sql &    "[general_notes], [type], [DEPT], ) "  
     Sql = Sql &    "VALUES ('" & m_CompanySchoolName & "',
'" & m_FullName & "', '" & m_StreetAddress & "', '"
& m_City        Sql = Sql &    "', '" & m_State &
"', '" & m_Zip & "', '" & m_TelephoneNumber & "', '"
& m_Message & "', 'COSMETOLOGY', '"        Sql = Sql  & m_Department &    "', '" & m_EmailAddress & "')"                response.write Sql        response.end        conn.execute Sql       

View 14 Replies View Related

SQL Query Question In My Web Form - Help

May 15, 2008

Greetings!
Is this a good SQL query to access two columns in my database and present this data in my web app?
string DataBaseCommand = "Select UserName, [Password] from PatientDemographics Where UserName = '" + txtUserName.Text + "' && [Password] = '" + txtPassword.Text + "'";
Thank you,
 
Eduardo

View 4 Replies View Related

SQL Server 2014 :: Loop And Query CSV Files In Folder Using Union All Query To Form Resultant Table On Server?

Jun 27, 2014

I am trying to run a UNION ALL query in SQL SERVER 2014 on multiple large CSV files - the result of which i want to get into a table in SQL Server. below is the query which works in MSAccess but not on SQL Server 2014:

SELECT * INTO tbl_ALLCOMBINED FROM OPENROWSET
(
'Microsoft.JET.OLEDB.4.0' , 'Text;Database=D:DownloadsCSV;HDR=YES',
'SELECT t.*, (substring(t.[week],3,4))*1 as iYEAR,
''SPAIN'' as [sCOUNTRY], ''EURO'' as [sCHAR],

[Code] ....

What i need is:

1] to create the resultant tbl_ALLCOMBINED table

2] transform this table using PIVOT command with following transformation as shown below:

PAGEFIELD: set on Level = 'Item'
COLUMNFIELD: Sale_Week (showing 1 to 52 numbers for columns)
ROWFIELD: sCOUNTRY, sCHAR, CATEGORY, MANUFACTURER, BRAND, DESCRIPTION, EAN (in this order)
DATAFIELD: 'Sale Value with Innovation'

3] Can the transformed form show columnfields >255 columns i.e. if i want to show all KPI values in datafield?

P.S: the CSV's contain the same number of columns and datatype but the columns are >100, so i dont think it will be feasible to use a stored proc to create a table specifying that number of columns.

View 9 Replies View Related

One Form To Update Different SQL Tables Based Upon Form Selection

Apr 16, 2008

Hello,
My company Intranet has a form that agents can use to post their comments about the company to upper management, but our customer service department would like to modify the form so that the agent has to pick from a comment type.
The dropdown options on the form will be as follows:
ComplimentsComplaintsGeneral CommentsSuggestions
Each dropdown option has a designated table in a SQL DB.Using postback on the same page, I need to change which fields of the form are visible based upon which dropdown selection the user chooses, and I need the fields to then be inserted into the table that corresponds with the dropdown selection item.
For example: If the Compliments dropdown selection is picked, I need a text box to show for the user's location, the name of the customer, account number, and the message box. Once the submit button is clicked, the characters in these boxes need to be inserted into the Compliments table using its data adapter.
However, if the user selects Suggestions, the name of the customer and the account number should not be visible, since these fields do not exist and when the submit button is pressed, the Suggestions table should be updated.
If you need more information, I will provide whatever is needed.
As always, thanks for everyone's assistance.
Chris

View 3 Replies View Related

Using LIKE And Wild Cards In Form Query

Dec 11, 2006

Hi,
I am using Visual Studio 2005. I am trying to create a ASP.net website and use a web form to get the results of an enquiry. It is almost the same as the presentation video on this website called "How to Create Data Driven Website", but I have added a new twist to it.  I want to use the "LIKE" instead of "=" in my SQL statement and I want to use wild cards in the parameter value that I enter.
<form id="form1" runat="server">    <div>        &nbsp;<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
<%        dim str as string = "%" & textbox1.text & "%"        </%>        <asp:TextBox ID="TextBox2" runat="server" Visible="False" Text=str></asp:TextBox>        <asp:Button ID="Button1" runat="server" Text="Submit" Width="87px" BackColor="Khaki" ForeColor="Navy" PostBackUrl="~/Default.aspx" />
and the ControlID of the Datagrid will take in the value of "TextBox2" instead of "TextBox1" as so:
   <SelectParameters>                <asp:ControlParameter ControlID="TextBox2" DefaultValue="SO06000001" Name="SO" PropertyName="Text"                    Type="String" />
 However the syntax above does not work. May I ask what is the correct syntax?
Thanks.
Peter
 

View 2 Replies View Related

String Manipulation With Web Form Query

Feb 24, 2006

I'm trying to look up customer records by e-mail domain by using a text box on a Web form. So if I want to look for all my customers that have an aol e-mail domain, I would type aol.com in the text box and the sub routine would know to count 7 characters from the right and through those characters into maybe a parameter query. I'm having problems passing this in. I can count the characters properly by using:
dim strText = MyTextBox.Textdim intLength = strText.Length
but having problems starting here......
MyCommand.SelectCommand.Parameters("@email").Value = MyTextBox.Text
..............
but how would I ultimately feed this into my sql satement? Select * from Customers Where email = right(@email,intLength)
Help appreciated.
Frank
 
 

View 1 Replies View Related

Passing Value From A Access Form To A Query

Jun 23, 2006

Frustration has gotten the best of me on this one. Can anyone help?
I need to pass the current value in an Access Data Project (back-end is MS SQL) text field to the where condition in an SQL using VBA. This is what I have, but does not work.

Private Sub Command44_Click()
Me.SS.SetFocus
Dim strSQL As String
Dim strSSecurity As String
strSSecurity = Me.SS
strSQL = "Update Employees Set employees.PositionID = '',employees.jobcode = '' Where employees.ss = strSSecurity"
DoCmd.RunSQL strSQL
End Sub

It is supposed to take the current Social Security number from the form and match it against the employees table. Once it finds the matching record it should update the PositionID and JobCode fields to '' (empty string)
But it doesn't
Anyone with any ideas?
Thanks
Dan

View 2 Replies View Related

Using Query Or Vba ,transfer Data From One Form To

Feb 27, 2007

using a query or vba ,transfer data from one form to another,
table1 customers-form1 customer address,
table2 orders-form2 order address,
how do i transfer data from customer address to order address
yours M [/center]

View 1 Replies View Related

List Items From A Form Query

Feb 24, 2008

Hi,
I am a newbie, this is my first post (please go easy).

Iam at the moment trying to set up a query for someone looking for a property on an estate agents website.

From a drop down menu, the user can:
select an area (where they may like to live) from a list of areas.
select an amount of bedrooms from a list of bedrooms
select a minimum price from a list of prices
select a maximum price from a list of prices.

The query I worked out for this is as follows:
$data = mysql_query("SELECT * FROM property WHERE area like '$area' and bedrooms like '$bedrooms' AND price BETWEEN '$min_price' AND '$max_price'") or die(mysql_error());

This seems to work fine and shows all the properties that meet the criteria onto my webpage.

However, I then thought, someone may not care which area they live in and want to see all properties in all the areas, so I decided to add the option 'All areas' to my 'areas' list, I then did the same for the other lists, eg 'all bedrooms' option to my bedrooms list and so on.

I am now trying to write a query that incorporates where the 'all..' option is selected and have become very stuck!
Can someone set me off in the right direction for this.
I hope that makes sense?!?!

View 20 Replies View Related

Query In Dataview/update Form

Dec 23, 2006

Question related to Visual Basic Video Lesson 09 (Databinding.wmv) by Bob Tabor.

Made a table and a UI form according to this lesson (table colums: CustomerID, FirstName, LastName).

Is it possible to use the same or an identical looking query form to find "Bob" by typing Bob in the Fist Name textbox or "Tabor" by typing Tabor in the Last Name textbox as in FileMaker?

Thanks,

View 4 Replies View Related

Select Query Form Multiple Databases

Dec 14, 2007

How do I write an sql statement to link tables in two databases and query them? For example: SELECT db1.table1.field1, db2.table1.field1 FROM Table1 INNER JOIN db1.table1.field1 ON db2.table1.field1 = db1.table1.field1
 
Thanks

View 6 Replies View Related

Hierarchy Query To Form Organization Structure

Jun 8, 2014

I need to write a sql that generate the hierarchy in an organization.Below an example

emplid empname supervisor_id superv_name
1 subu null null
2 vid 1 sub
3 ram 4 satis
4 satis 2 vid

i need an output to this query as below and also one important the supervisor ie supervisor_id and name is null is the top level,every employee also has to report to him and also to his all above supervisors.whoever joinng new to org the hierachy should be follwed

empid empname supervisor_id superv_name
3 ram 4 satis
4 satis 2 vid
2 vid 1 subu
4 satis 1 subu
3 ram 1 subu
3 ram 2 vid
5 kumar 1 subu
5 kumar 4 satis
5 kumar 2 vid
1 subu null null

View 1 Replies View Related

Creating A View Form A Sequel Query

Apr 18, 2008

I've written a Union query that joins 16 different tables. I need to somehow make this into a permanent view so that i can query directly from excel to extract specific data - i guess i can set up excel to pull straight from this view then ?

I'm used to running excel VB code to SQL via Access (linking access up) but want to cut out the middleman now.

Any ideas ?

An example of the Union Query i have built is below :


SELECT[Vendor No_],[Posting Date],[Purchase (LCY)],[Buy-from Vendor No_],[External Document No_],[IC Partner Code]

FROM [NAV402].[dbo].[TESTCO Sweden$Vendor Ledger Entry]

WHERE[Posting Date]>='20070701' AND [Vendor No_] LIKE 'ZX%'

UNION

SELECT[Vendor No_],[Posting Date],[Purchase (LCY)],[Buy-from Vendor No_],[External Document No_],[IC Partner Code]

FROM [NAV402].[dbo].[TESTCO Taiwan$Vendor Ledger Entry]

WHERE[Posting Date]>='20070701' AND [Vendor No_] LIKE 'ZX%'

UNION

SELECT[Vendor No_],[Posting Date],[Purchase (LCY)],[Buy-from Vendor No_],[External Document No_],[IC Partner Code]

FROM [NAV402].[dbo].[TESTCO US$Vendor Ledger Entry]

WHERE[Posting Date]>='20070701' AND [Vendor No_] LIKE 'ZX%';


Thanks for any help you can give me.

PID

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved