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
ADVERTISEMENT
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
Nov 27, 2006
Hello, I have following Situation:I have a Dimension "SourceDirectory", whis has Elements"SourceDirectory1", "SourceDirectory2", ... until "SourceDirectory10".My Statement in MDX is whis:SELECT {[Measures].AllMembers} ON columns,{[SourceDirectory].Children} ON rowsFROM [CheckstyleError]It works. Now, how can I select all SourceDirectories but not the"SourceDirectory6", whis has only NULL-Values and I need't to see it inmy report.I would be glad to have an early reply!Thanks
View 1 Replies
View Related
Mar 8, 2008
Hi everyone,
So what I am trying to do is to have a simple textbox where the person enters a code.
Then I need to have that code in an SQL statement like: SELECT Participant_Code FROM Contacts WHERE (this is where im stuck, I need to have something like WHERE Participant_code = code (code is the textbox ID))
After this is done I need to keep this code from page to page, because I will need to add data from other textboxes to the database.
Thanks
View 2 Replies
View Related
Dec 7, 2004
Edited by SomeNewKid. Please post code between <code> and </code> tags.
// Pls copy the following HTML after that you generate the HTML page. Then you will know what I mean. I need to form "New Table" from Job Revence and Job Cost using SQL statement? thx
-----------The following code-----------------
<META HTTP-EQUIV="Content-Type" CONTENT="text/html;charset=big5">
<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=Content-Type content="text/html; charset=Big5">
<meta name=ProgId content=Excel.Sheet>
<meta name=Generator content="Microsoft Excel 9">
<link rel=File-List href="cid:filelist.xml@01C4DA4E.7D08F5E0">
<!--[if !mso]>
<style>
v:* {behavior:url(#default#VML);}
o:* {behavior:url(#default#VML);}
x:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style>
<![endif]-->
<style>
<!--table
{mso-displayed-decimal-separator:".";
mso-displayed-thousand-separator:",";}
.xl15
{padding-top:1px;
padding-right:1px;
padding-left:1px;
mso-ignore:padding;
color:windowtext;
font-size:9.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:????;
mso-generic-font-family:auto;
mso-font-charset:136;
mso-number-format:General;
text-align:general;
vertical-align:bottom;
mso-background-source:auto;
mso-pattern:auto;
white-space:nowrap;}
.xl22
{padding-top:1px;
padding-right:1px;
padding-left:1px;
mso-ignore:padding;
color:windowtext;
font-size:9.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:????;
mso-generic-font-family:auto;
mso-font-charset:136;
mso-number-format:General;
text-align:general;
vertical-align:bottom;
border:.5pt solid windowtext;
mso-background-source:auto;
mso-pattern:auto;
white-space:nowrap;}
.xl23
{padding-top:1px;
padding-right:1px;
padding-left:1px;
mso-ignore:padding;
color:windowtext;
font-size:9.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:????;
mso-generic-font-family:auto;
mso-font-charset:136;
mso-number-format:General;
text-align:general;
vertical-align:bottom;
border:.5pt solid windowtext;
background:#339966;
mso-pattern:auto none;
white-space:nowrap;}
.xl24
{padding-top:1px;
padding-right:1px;
padding-left:1px;
mso-ignore:padding;
color:windowtext;
font-size:12.0pt;
font-weight:700;
font-style:normal;
text-decoration:none;
font-family:????, serif;
mso-font-charset:136;
mso-number-format:General;
text-align:general;
vertical-align:bottom;
mso-background-source:auto;
mso-pattern:auto;
white-space:nowrap;}
-->
</style>
<!--[if gte mso 9]><xml>
<x:ExcelWorkbook>
<x:ExcelWorksheets>
<x:ExcelWorksheet>
<x:Name>Sheet1</x:Name>
<x:WorksheetOptions>
<x:DefaultRowHeight>225</x:DefaultRowHeight>
<x:Print>
<x:ValidPrinterInfo/>
<x:PaperSizeIndex>9</x:PaperSizeIndex>
<x:HorizontalResolution>-3</x:HorizontalResolution>
<x:VerticalResolution>0</x:VerticalResolution>
</x:Print>
<x:Selected/>
<x:Panes>
<x:Pane>
<x:Number>3</x:Number>
<x:ActiveCol>10</x:ActiveCol>
</x:Pane>
</x:Panes>
<x:ProtectContents>False</x:ProtectContents>
<x:ProtectObjects>False</x:ProtectObjects>
<x:ProtectScenarios>False</x:ProtectScenarios>
</x:WorksheetOptions>
</x:ExcelWorksheet>
</x:ExcelWorksheets>
<x:WindowHeight>10470</x:WindowHeight>
<x:WindowWidth>16755</x:WindowWidth>
<x:WindowTopX>240</x:WindowTopX>
<x:WindowTopY>30</x:WindowTopY>
<x:HasEnvelope/>
<x:ProtectStructure>False</x:ProtectStructure>
<x:ProtectWindows>False</x:ProtectWindows>
</x:ExcelWorkbook>
</xml><![endif]-->
</head>
<body>
<table x:str border=0 cellpadding=0 cellspacing=0 width=636 style='border-collapse:
collapse;table-layout:fixed;width:477pt'>
<col width=56 span=7 style='width:42pt'>
<col width=104 style='mso-width-source:userset;mso-width-alt:4437;width:78pt'>
<col width=140 style='mso-width-source:userset;mso-width-alt:5973;width:105pt'>
<tr height=22 style='height:16.5pt'>
<td height=22 class=xl24 colspan=2 width=112 style='height:16.5pt;mso-ignore:
colspan;width:84pt'>JOB TABLE</td>
<td class=xl15 width=56 style='width:42pt'></td>
<td class=xl15 width=56 style='width:42pt'></td>
<td class=xl15 width=56 style='width:42pt'></td>
<td class=xl15 width=56 style='width:42pt'></td>
<td class=xl15 width=56 style='width:42pt'></td>
<td class=xl15 width=104 style='width:78pt'></td>
<td class=xl15 width=140 style='width:105pt'></td>
</tr>
<tr height=15 style='height:11.25pt'>
<td height=15 class=xl23 style='height:11.25pt'>JobNo</td>
<td colspan=8 class=xl15 style='mso-ignore:colspan'></td>
</tr>
<tr height=15 style='height:11.25pt'>
<td height=15 class=xl22 style='height:11.25pt;border-top:none'>SE0001</td>
<td colspan=8 class=xl15 style='mso-ignore:colspan'></td>
</tr>
<tr height=22 style='height:16.5pt'>
<td height=22 class=xl22 style='height:16.5pt;border-top:none'>SE0002</td>
<td colspan=5 class=xl15 style='mso-ignore:colspan'></td>
<td class=xl24 colspan=2 style='mso-ignore:colspan'>New Table</td>
<td class=xl15></td>
</tr>
<tr height=15 style='height:11.25pt'>
<td height=15 colspan=6 class=xl15 style='height:11.25pt;mso-ignore:colspan'></td>
<td class=xl23>JobNo</td>
<td class=xl23 style='border-left:none'>no of Revence</td>
<td class=xl23 style='border-left:none'>no of Cost</td>
</tr>
<tr height=22 style='height:16.5pt'>
<td height=22 class=xl24 colspan=2 style='height:16.5pt;mso-ignore:colspan'>JOB
Revence</td>
<td colspan=4 class=xl15 style='mso-ignore:colspan'></td>
<td class=xl22 style='border-top:none'>SE0001</td>
<td class=xl22 align=right style='border-top:none;border-left:none' x:num>2</td>
<td class=xl22 align=right style='border-top:none;border-left:none' x:num>1</td>
</tr>
<tr height=15 style='height:11.25pt'>
<td height=15 class=xl23 style='height:11.25pt'>JobNo</td>
<td class=xl23 style='border-left:none'>ItemNo</td>
<td colspan=4 class=xl15 style='mso-ignore:colspan'></td>
<td class=xl22 style='border-top:none'>SE0002</td>
<td class=xl22 align=right style='border-top:none;border-left:none' x:num>1</td>
<td class=xl22 align=right style='border-top:none;border-left:none' x:num>0</td>
</tr>
<tr height=15 style='height:11.25pt'>
<td height=15 class=xl22 style='height:11.25pt;border-top:none'>SE0001</td>
<td class=xl22 align=right style='border-top:none;border-left:none' x:num>1</td>
<td colspan=7 class=xl15 style='mso-ignore:colspan'></td>
</tr>
<tr height=15 style='height:11.25pt'>
<td height=15 class=xl22 style='height:11.25pt;border-top:none'>SE0001</td>
<td class=xl22 align=right style='border-top:none;border-left:none' x:num>2</td>
<td colspan=7 class=xl15 style='mso-ignore:colspan'></td>
</tr>
<tr height=15 style='height:11.25pt'>
<td height=15 class=xl22 style='height:11.25pt;border-top:none'>SE0002</td>
<td class=xl22 align=right style='border-top:none;border-left:none' x:num>1</td>
<td colspan=7 class=xl15 style='mso-ignore:colspan'></td>
</tr>
<tr height=15 style='height:11.25pt'>
<td height=15 colspan=9 class=xl15 style='height:11.25pt;mso-ignore:colspan'></td>
</tr>
<tr height=22 style='height:16.5pt'>
<td height=22 class=xl24 colspan=2 style='height:16.5pt;mso-ignore:colspan'>JOB
Cost</td>
<td colspan=7 class=xl15 style='mso-ignore:colspan'></td>
</tr>
<tr height=15 style='height:11.25pt'>
<td height=15 class=xl23 style='height:11.25pt'>JobNo</td>
<td class=xl23 style='border-left:none'>ItemNo</td>
<td colspan=7 class=xl15 style='mso-ignore:colspan'></td>
</tr>
<tr height=15 style='height:11.25pt'>
<td height=15 class=xl22 style='height:11.25pt;border-top:none'>SE0001</td>
<td class=xl22 align=right style='border-top:none;border-left:none' x:num>1</td>
<td colspan=7 class=xl15 style='mso-ignore:colspan'></td>
</tr>
<![if supportMisalignedColumns]>
<tr height=0 style='display:none'>
<td width=56 style='width:42pt'></td>
<td width=56 style='width:42pt'></td>
<td width=56 style='width:42pt'></td>
<td width=56 style='width:42pt'></td>
<td width=56 style='width:42pt'></td>
<td width=56 style='width:42pt'></td>
<td width=56 style='width:42pt'></td>
<td width=104 style='width:78pt'></td>
<td width=140 style='width:105pt'></td>
</tr>
<![endif]>
</table>
</body>
</html>
View 2 Replies
View Related
Aug 26, 2007
Hello, I'm really stuck at trying to figure out how to write out the proper SQL statement for my problem. I'm relatively new to SQL, so this may not be so tough for some of you. Basically, a user logs in (I'm using the default membership provider) and goes to his INBOX to see his list of messages sent to him. The list is presented to him via a datalist. Each item of the datalist contains a table of 2 columns and 1 row as pictured below. The first box contains the user photo and user name of the person who SENT him the message (not the logged in user). The second box contains the subject title of the message.
FROM | SUBJECT | User Photo | || | Subject || User Name | |
Here is the list of the relevant 4 tables of my database and the relevant fields.....
aspnet_Users tableUserId (used to link to Member table)UserName
Member tablememberId (int - primary key)UserId (guid - used to link to aspnet_Users table)primaryPhotoId (int - used to link to Photo table)
Photo tablephotoId (int - primary key)photoUrl (string - path to file on local drive)
Message tablemessageId (int - primary key)fromMember (int - connects with memberId from Member table)toMember (int - connects with memberId from Member table)subject (varchar(max))
So basically, from a simplistic high level point of view, the datalist is going to list all of the messages where Message.toMember = the logged in user. The senders will be determined by the Member.fromMember fields. Intuitive enough so far, I guess. This is the SQL statement I have so far.....
SELECT aspnet_Users.UserName, Message.subjectFROM aspnet_Users, Member, MessageWHERE aspnet_Users.UserName = Profile.UserName AND aspnet_Users.UserId = Member.UserId AND Member.memberId = Message.toMember
Note that I'm grabbing the logged in user info from Profile.UserName. So far, this SQL statement should make the datalist crank out all messages that were sent to the logged in user. HOWEVER, how would I modify this so that the datalist generates the username of the sender, NOT the receiver (aka person who logged in)? Do you see the core of my dilemna here? I'm trying to get a resultset based on the Message.toMember (the logged in user), but also want data on the sender (the Message.fromMember so I can use the username and the photo of the SENDER, not the person logged in aka the RECEIVER). Currently, the aspnet_Users in the SELECT statement gets the username of the logged in person, not the sender.
And once we solve the issue of retrieving the sender's username, I also have to get his MAIN photo (I say "main" since a user can have multiple photos and the main one is determined by the value in a given member's primaryPhotoId field of the Member table) ?? I'm a newbie to ASP.NET and to databases in general so this may not be as tough for most of you and perhaps you're laughing at the simplicity hehe. The SQL statement so far asks to retrieve information based on the logged in user. But how do I also tell it to now go grab the Message.fromMember data, go back to the Member table to A)get the username after going back to the aspnet_Users table and B) to get the Member.primaryPhotoId, and then finally to the Photo table where the Photo.photoUrl string value is obtained..... and still hang on to the results I have up until now? And since I'm using the provided datalist control, I think I need to get all the results I need with just one SQL statement. This is indeed very very complicated for me lol.
This problem has been giving me migraines this whole weekend. Has anyone been through such a problem before? Any help would be greatly appreciated - thanks in advance.
View 22 Replies
View Related
May 8, 2008
hi in my package, some sql operations need the special user name and admin privilage. so how do i create my ssis package so that when it executes it takes the given username and password from the table in some database.
View 8 Replies
View Related
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
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
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
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
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
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
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
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> <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
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
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
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
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
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
View Related
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
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
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
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
Aug 13, 2007
Dear All,
I have created a table in my SQL server database, the problem i am facing is my insert query fails if i leave any form field empty (leave it blank). On my back-end table, only one field is mandatory, and others have been set with the constraint "allow null".
As per our business requirement, except one value is complusory while others are optional. If I enter all values in the form it works perfectly fine. Can you see in the code below - where am i possibly going wrong ?
<script language="VB" runat="server" > Sub Page_Load(Src As Object, e As EventArgs) If Page.IsPostBack Then Dim ConLath As SqlConnection Dim comLath As SqlCommand Dim insertcmd conLath = New SqlConnection("Data Source=SQLas;Initial Catalog=settle;User ID=sa;Password=password") ConLath.Open() insertcmd = "Insert into His_set values (@t_d,@s_p,@p_s,@v_oq,@i_oq,@v_qn,@i_qn,@v_qw,@i_qw)" comLath = New SqlCommand(insertcmd, ConLath) comLath.Parameters.Add(New SqlParameter("@t_d", SqlDbType.DateTime, 12)) comLath.Parameters("@t_d").Value = trade_date.Text comLath.Parameters.Add(New SqlParameter("@s_p", SqlDbType.Decimal, 8)) comLath.Parameters("@s_p").Value = sett_price.Text comLath.Parameters.Add(New SqlParameter("@p_s", SqlDbType.Decimal, 8)) comLath.Parameters("@p_s").Value = post_close.Text comLath.Parameters.Add(New SqlParameter("@v_oq", SqlDbType.Int, 8)) comLath.Parameters("@v_oq").Value = vol_oq.Text comLath.Parameters.Add(New SqlParameter("@i_oq", SqlDbType.Int, 8)) comLath.Parameters("@i_oq").Value = oi_oq.Text comLath.Parameters.Add(New SqlParameter("@v_qn", SqlDbType.Int, 8)) comLath.Parameters("@v_qn").Value = vol_qn.Text comLath.Parameters.Add(New SqlParameter("@v_qw", SqlDbType.Int, 8)) comLath.Parameters("@v_qw").Value = vol_qw.Text comLath.Parameters.Add(New SqlParameter("@i_qn", SqlDbType.Int, 8)) comLath.Parameters("@i_qn").Value = oi_qn.Text comLath.Parameters.Add(New SqlParameter("@i_qw", SqlDbType.Int, 8)) comLath.Parameters("@i_qw").Value = oi_qw.Text
Try comLath.ExecuteNonQuery() Catch ex As SqlException If ex.Number = 2627 Then Message.InnerHtml = "ERROR: A record already exists with " _ & "the same primary key" Else Message.InnerHtml = "ERROR: Could not add record, please " _ & "ensure the fields are correctly filled out" Message.Style("color") = "red" End If End Try
comLath.Dispose() ConLath.Close() End If End Sub
</script>
View 6 Replies
View Related
Apr 29, 2015
i have used CMS in SQL 2008 R2. i have added couple of servers in its group. i have executed a query & i need to send the query output in the form of email.
basically query is checking the rows count from couple of user tables in servers.
issue here is how to copy the data that is used by CMS? i need to work on automate the rows count in difft user table in db servers.
View 2 Replies
View Related
Jul 19, 2006
Hi everyone,
What is the main difference between first form normalizations and second form normalization ?
In my opinion, they are both generated for the same operation which is to prevent redundancy(in other words; duplication of data in several records).
So would you please explain it to me ?
Thanks
View 1 Replies
View Related
Jul 9, 2015
I am trying to fetch records from excel sheet using Select Query but I am getting the error message saying
"Msg 7302, Level 16, State 1, Line 1
Cannot create an instance of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"."
Here is my Query,
sp_configure 'show advanced options',1
reconfigure with override
go
sp_configure 'Ad Hoc Distributed Queries',1
reconfigure with override
go
reconfigure
SELECT *
FROM OPENROWSET
('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:M2MworkedworkedBOS.xlsx;HDR=YES', 'select * from [Sheet1$]') AS A;
View 5 Replies
View Related
Jul 23, 2005
HiI have a Platinum database which stores dates in integer form e.g the dateis formatted as below:Column_name Type Length Precision------------------------------ ------------------------------from_date int 4 10Some of the dates in the Platinum database are as follows:729115729359730059730241730302730455How can I bring them into SQL 2000 as valid dates.Thanks for your assistanceSam CJoin Bytes!
View 1 Replies
View Related
Dec 20, 2007
I have set up a 'Parameters' table that solely stores all pre-assigned selection values for a webform. I customized a stored query to Select the values from the Parameters table. I set up the webform. The result is that the form1.apsx automatically populates each DropDownList task with the pre-assigned values from the 'Parameters' table (for example, the stored values in the 'Parameters' table 'Home', 'Business', and 'Other' populate the drop down list for 'Type').
The programming to move the selected data from form1.aspx to a new table in the SQL database perplexes me. If possible, I would like to use the form1.aspx to Postback (or Insert) the "selected" data to a *new* column in a *new* table (such as writing the data to the 'CustomerType' column in the 'Customers' table; I clearly do not want to write back to the 'Parameters' table). Any help to get over this hurdle would be deeply appreciated.
View 1 Replies
View Related
Sep 3, 2007
Hello... im having a problem with my query optimization....
I have a query that looks like this:
SELECT * FROM table1
WHERE location_id IN (SELECT location_id from location_table WHERE account_id = 998)
it produces my desired data but it takes 3 minutes to run the query... is there any way to make this faster?... thank you so much...
View 3 Replies
View Related
Jan 3, 2008
1 SELECT distinct A.EAIndex,
2 A.ChainCode AS ChainCode,
3 A.JobDate,
4 C.BillerName,
5 B.BusinessCode,
6 D.ChainName,
7 A.BillBegDate,
8 A.BillEndDate,
9 A.PostDate,
10 A.BillCount,
11 A.ChAmount,
12 A.PostAmount,
13 A.ProChargeFee,
14 A.BalanceStatus AS BalanceStatus,
15 B.BillerInfoCode AS BillerInfoCode ,
16 CONVERT(varchar(10), A.BalanceDate, 112)BalanceDate,
17 A.AdjustDate,
18 C.BillerCode ,
19 (a.ChAmount- a.ProChargeFee) AS Fee,
20 E.EABillerCode, E.RelAdjustDate
21 FROM ZT_EAccount A
22 Inner Join ZT_BillerInfo B On Right('00000'+Rtrim(Ltrim(A.BillerCode)),5) = Right('00000'+Rtrim(Ltrim(B.BillerInfoCode)),5)
23 Inner Join ZT_Biller C ON B.ParentCode = C.BillerCode
24 Inner Join ZT_Chain D On A.ChainCode = D.ChainCode
25 Inner Join ZT_EAccountAdjust E ON a.EAIndex=E.EAIndex and a.BillerCode=E.BusCode
26 Where A.JobDate BETWEEN '20071001' AND '20071005' AND C.CompanyCode='533'
In line 13 I want to add a Query to make sure if ZT_BillerInfo.Rmflag = 1, if it's =1 then ProChargeFee =0if ZT_BillerInfo.RmFlag =0 , then ProChargeFee = a.ProChargeFee.I add this line �Case B.RmFlag = '1' then 0 else A.ProChargeFee 】instead of Line 13 (A.ProChargeFee)but I execute Sql I got error message on the line I have jsut modified..can you please help me to know why and how to correct it? thank you very much
View 4 Replies
View Related
Oct 15, 2007
I want to Add a if .. else statement to make sure if the Data is exist in Table if (exist ) { do a update query} else{ do a Insert Query}
I Know how to update and how to insert but how to check if the data is exist in Table and how to add a if ..else to query?
thank you very much
View 9 Replies
View Related