I am a noob to sql and asp programming. I am working on a db for the
gaming squad I am a member of. One of the pages I created is a roster
list of all the squad members. Part of this roster is listing each
member's email address. What several people have asked of me is to
make it so the email addresses can be clicked on to open their email
programs, just as html allows the mailto function to work.
Here is a copy of the coding I am currently using:
<td align="center"><font face="Arial"
color="#C0C0C0"><%=rst("email")%></font></td>
This will list the email address of the person, but how do I make it
so the line will function as a mailto command?
The page is saved as an .asp.
Dim smtpServer As String = Dts.Variables("MailSMTPServer").Value.ToString Dim htmlMessageTo As String = Dts.Variables("ToLine").Value.ToString Dim htmlMessageFrom As String = Dts.Variables("FromLine").Value.ToString Dim htmlMessageSubject As String = Dts.Variables("MailSubj").Value.ToString Dim htmlMessageBody As String = Dts.Variables("MailBody").Value.ToString Dim htmlMessageLog As String = Dts.Variables("ErrorLog2").Value.ToString Dim htmlMessage As MailMessage Dim mySmtpClient As SmtpClient Dim m As New System.Net.Mail.MailMessage Dim AttachLog As Attachment = New Attachment(htmlMessageLog)
htmlMessage = New MailMessage(htmlMessageFrom, htmlMessageTo, htmlMessageSubject, htmlMessageBody)
mySmtpClient = New SmtpClient("<SMTP Server name>") m.Attachments.Add(AttachLog)
mySmtpClient.Credentials = New NetworkCredential("<emailid>", "<password>")
mySmtpClient.Send(htmlMessage)
Dts.TaskResult = Dts.Results.Success End Sub
Please let me know, how can i see Attachment in my mail.
lSQL = "SELECT * FROM OPENQUERY(liorder,'SELECT a.KF_ORDER_NO AS OrdNo, f.KU_NAME AS Customer, " & _ "a.KF_ORDER_POS AS Pos, a.KF_SCHEIB_NR AS Pane, a.KF_QTY AS Qty, d.BREITE*d.HOEHE/1000*a.KF_QTY AS SQM, " & _ "a.KF_QTY*d.SUM_NETTO AS Val, a.KF_FERT_QTY AS Done, d.BREITE*d.HOEHE/1000*a.KF_FERT_QTY AS DoneSQM, " & _ "a.KF_FERT_QTY*d.SUM_NETTO AS DoneVal FROM LIORDER.AUF_KOPF c, LIPROD.KAPA_AUS_FERT a, LIORDER.KUST_ADR f, " & _ "LIORDER.AUF_POS d WHERE (f.KU_VK_EK = 0) AND (a.KF_SCHR_NR = 12) AND (c.AUF_NR = a.KF_ORDER_NO) AND " & _ "(c.KUNR = f.KU_NR) AND (a.KF_ORDER_NO = d.AUF_NR) AND (a.KF_ORDER_POS = d.AUF_POS) AND (f.KU_NAME IS NOT NULL) " & _ "GROUP BY a.KF_ORDER_NO, f.KU_NAME, a.KF_ORDER_POS, a.KF_SCHEIB_NR, a.KF_QTY, d.BREITE, d.HOEHE, a.KF_FERT_QTY, d.SUM_NETTO')" Set RS = New ADODB.Recordset RS.Open lSQL, DB
Do While Not RS.EOF Select Case RS!Pane Case "0" Or "1" glassSQL = "SELECT IDNR, GL_BEZ FROM OPENQUERY(liorder, 'SELECT a.IDNR, a.GL_BEZ FROM LIORDER.GLAS_DATEN a, LIORDER.AUF_POS b " & _ "WHERE a.IDNR = b.GLAS1 AND b.AUF_NR = " & RS!OrdNo & " AND b.AUF_POS = " & RS!Pos & "')" Set glassRS = New ADODB.Recordset glassRS.Open glassSQL, DB
Mike writes "I have a string, >!~[99DC# 4.12$2251.% 1.63& 3.58'2150.(-2050.>/~]
and I would like to extract parts of it and send it to a SQL database. here is the layout and data that should be in each column. The Data column will be filled in automatically, but once that happens I would like the other columns to get their data from the Data column.
Data = >!~[99DC# 4.12$2251.% 1.63& 3.58'2150.(-2050.>/~] Display_Address = 99 CE = 4.12 TPK = 2251 Si= 1.63 C = 3.58 TPL = 2150 TPS = 2050
/* hi people im doing a task and im not sure about some things. could u check if what im doing is good plz.*/
The task is: Produce a list of all female employees who earn more than the average salary of the male employees in the company. Display employee number, first name and last name.
The table: emp Attributes: EMPNO, FIRSTNAME, LASTNAME, SEX, SALARY
What i came up with: selectempno, firstname, lastname fromemp whereavg(salary)>any(selectavg(salary) fromemp wheresex='M') group by sex havingsex = 'F'
Here is my code to connect, any help would be appreciated. I am a total noob at this.
############### Code
<?php $host="localhost"; // Host name $username=""; // Mysql username $password=""; // Mysql password $db_name="orderofb_test"; // Database name $tbl_name="orderofb_test.members"; // Table name
// Connect to server and select databse. mysql_connect("$host", "$username", "$password")or die("cannot connect"); mysql_select_db("$db_name")or die("cannot select DB");
// username and password sent from signup form $myusername=$_POST['myusername']; $mypassword=$_POST['mypassword'];
$sql="SELECT * FROM $tbl_name WHERE username='$myusername' and password='$mypassword'"; $result=mysql_query($sql);
// Mysql_num_row is counting table row $count=mysql_num_rows($result); // If result matched $myusername and $mypassword, table row must be 1 row
if($count==1){ // Register $myusername, $mypassword and redirect to file "login_success.php" session_register("myusername"); session_register("mypassword"); header("location:login_success.php"); } else { echo "Wrong Username or Password"; } ?>
This is the error message I receive.
############### Code
Warning: mysql_connect() [function.mysql-connect]: Access denied for user 'nobody'@'localhost' (using password: NO) in /home/orderofb/public_html/php/checklogin.php on line 11 cannot connect
I changed all the permissons to 777 and I still receive this error.
$tbl_name="orderofb_test.members"; // Table name
Is this the correct table name? Here is a pic of my phpmyadmin
I am trying to create a script that will return information from 4 different tables in a database using different where clauses.
This is working correctly, however the primary table the query uses has multiple entries for different people and i only want to return the last row per person. I have got this to work using a MAX(t.column) statement, but when used in conjunction with all the where clauses things go wrong.
This is what i have
Code:
CREATE VIEW Firereport1 AS
select max(t.punch_id) as punch, e.EMPLOYEE_FIRSTNAME, e.EMPLOYEE_LASTNAME, c.CLOCK_NAME, t.PUNCH_TIME, d.DEPT2_DESCRIPTION
FROM TM_PUNCH t, EMPLOYEE e, COMM_CLOCK c, DEPT2 d
WHERE t.PUNCH_EMPID = e.EMPLOYEE_ID and e.EMPLOYEE_ONCLOCK <> 'False' and e.EMPLOYEE_LASTTIMELOC = c.CLOCK_ID and e.EMPLOYEE_LASTDEPT2 = d.DEPT2_ID
group by e.EMPLOYEE_FIRSTNAME, e.EMPLOYEE_LASTNAME, c.CLOCK_NAME, t.PUNCH_TIME, d.DEPT2_DESCRIPTION
GO
This returns multiple entries because there are multiple unique entries for t.punch_time.
What i was thinking is i need a where statement on t.time_punch, d.dept2_description, c.clock_name that says something along the lines of where return t.punch_time where t.punch_id=punch (**substitute correct column names**)
just wanted to ask the most efficient way to query for the following: say i have a table with the following columns A, B, C, D, E. i want to find the records whose A, B and C are the same but at least D or E are different and then display all the columns (A to E) for these records. thank you!
So as the subject states, I'm a total sql noob. I have a database in SQL 2k5 that was transfered over from an acces DB. In the sql db there are a few fields using nvarchar(255) but the data held is actually dates and times. I need to convert these over to datetime format but do no know how. Also, not all rows have a full mm/dd/yyyy hh:mm format. some hold only the date and can be seen as either m/d/yyyy or full out mm/dd/yyyy format. If anyone can help with this that would be great.
I installed sql sever 2005 enterprise edition in my computer.Where is the management studio? Is it a different program? Can I install the express edition of the management studio?Help please.
Ok so i have something like this for a simple quary,
select avg(reorderlevel)/ (avg(QOH)/(avg(reorderlevel)*2)) as "Needed qty",ItemCategory from item group by ItemCategory
What I basicly need to do is to output the quantity needed for QOH to be 2 times the amount of reorderlevel. then grouped by diffrent catagories.
Anyways I have the formula done and it should work except for some reason "(avg(QOH)/(avg(reorderlevel)*2))" gives me 0 when in fact it should give me 0.728383 or w/e. Im wondering if its omiting the decimals or what??
Because if i run that code it tells me i cant divid by zero
Sorry im new at this and might be doing this all wrong so go easy on me =/
Apologies first up -I dont do security so slapped wrists all round, but I have a little situation that I need to take seriously so I thought I'd try do the job right for a change. Scenario: I have a stored procedure that I want one, and only one, 'thing' to have access to. The 'thing' will be one and only one SSIS package invoked by external scheduling software so basically a call to the dtexec utility to run the package stored in the file system. At runtime therefore, this package has an ExecuteSQL task which connects to the server and runs the sproc. Given windows authentication how do I ensure that the ONLY user allowed to execute the stored procedure is whatever user happens to invoke the package but that this same user can never log in to sql server and execute the stored procedure. In even simpler terms, I want nothing on earth to run that stored procedure nor select the data from the table other than this package when it runs, regardless of who runs it, it is only internal processing by the package that gets the data.
My background is networking with a lot of SQL Server 6.5-2000 dba/sql code thrown in. I've written some straight ASP pages, some Access DB frontends to SQL etc and I just can't get this .Net stuff. I'm trying to work my way thru the getting started vids here and can't get a db connection to the membership db. I ran the aspnet_regsql.exe to create the db on my sql dev box, and it won't connect. I've added the code from http://aspnet.4guysfromrolla.com/articles/120705-1.aspx to the web config. I erased the connection strings from the web config....I installed SQL 2005 express on my local machine and STILL CAN'T get anything other than can't connect to data store, data store doesn't exit, click here to crash....blah blah blah. There is a problem with your selected data store. This can be caused by an invalid server name or credentials, or by insufficient permission. It can also be caused by the role manager feature not being enabled. Click the button below to be redirected to a page where you can choose a new data store. The following message may help in diagnosing the problem: Unable to connect to SQL Server database. blah blah Test the AspNetSqlProvider and get Could not establish a connection to the database. If you have not yet created the SQL Server database, exit the Web Site Administration tool, use the aspnet_regsql command-line utility to create and configure the database, and then return to this tool to set the provider. again. I know the machine itself is fine. It built the db on both the local machine and the remote sql server. I can connect to the local and remote sql servers using sql management studio. Can someone help a brother out?
I just created my first Asp.net app. I had to install it to a corporate server. What I found is that the corporate SQL Server 2000 was case sensitive in the stored procedures while my installation was not! How can I set my SQL Server 2000 to be case sensitive as well?
Hi All, I'm a newbie in stored procedure programming. I wanted to learn how to perform keyword(s) search with stored procedures. Take the Pubs database for example. How can i create a stored procedure which takes in a string of keywords and return the title_id, title and notes column ? like if i pass in "computer easy" as keywords then the stored procedure should return all the rows with ANY of these keywords in those 3 columns. Can anyone give me some ideas on how to do this? like do i have to use dynamic sql?, any tutorials or sample codes? Thanks in advance!regards
looks like a noob question but how can i manage msde2000...i can see sql is runnin but how can i create database , attach/detach database, write functions and so on...
I was given the task of writing a new website for my boss. problem being he wants the ability have a custom user signup form and then a picture gallery of service with comments listed off each one. I was also asked to produce this in .aspx with SQL. only one small glitch, never worked with either of them.... I have designed most of the database requirements off of examples I found online but have not found how to post data from the users signup form to the database correctly and retrieve it for the customers profile. any suggestions???? all help on this will be greatly appreciated
I'm wondering how/why this query works. Trying to get my head wrappedaround SQL. Basically the Query deletes from the Import table allrecords that are already in FooStrings so that when I do an insert fromthe FooStringsImport table into the FooStrings table, then I won't getprimary key violations.DELETE FROM FooStringsImportWHERE EXISTS(SELECT * FROM FooStringsWHERE FooStringsImport.FooKey = FooStrings.FooKey)It seems to work fine, but I'm wondering about how the EXISTS keywordworks.(SELECT * FROM FooStringsWHERE FooStringsImport.FooKey = FooStrings.FooKey)This part is going to return only records from FooStrings correct? Ordoes it do a cartesian product since I've specified more than one tablein the WHERE statement?I wonder if it only returns records in FooStrings, then I don't see howa record from FooStringsImport would "EXISTS" in the records returnedfrom FooStrings.The reason I wondered about the cartesian product is because, if onlyFooStrings is specified in the FROM part of the SELECT statement, thenI was thinking it is only going to return FooString records. Theserecords would then be returned by the select statement to the WHEREEXISTS, which would look for FooStringImport records, but would findnone because the select statement only returned FooString records.I'm guessing maybe because it has to do a cartesian product to evaluatethe WHERE Pkey's equal, then the "SELECT *" just goes ahead and getsALL the fields, and not just those in FooStrings.FooStrings and FooStringsImport are identically structured tables,where the FooKey is set as the primary key in each table:CREATE TABLE [dbo].[FooStrings] ([FooKey] [bigint] NOT NULL ,[Name] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[StartDate] [datetime] NULL ,[EndDate] [datetime] NULL ,[Code] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]GOThanks in advance. I'm so appreciative of the help I've gotten here,as I've been able to write several very useful queries on my own nowafter everyones help and plus lots of reading on my own.
Well i guess it is a bit more than just group by but here we go...
General description :( background info on the problem; may or may not be needed)
I am trying to get a list of active card holders for a particular month. A card holder is considered active if their status was active any time during that month. I have a card holder status in my CardHolder table but that will only give me their current status. I also have a CardHolderHistory table that saves any changes to a card holder including their status. Using the CardHolderHistory table i can find out who was active for any month in question by using the following algorithm:
A card holder is active if they meet one or both of the following conditions;
1) The last entry before the month in question has them as active in CardHolderHistory or
2) They were set to active any time during the month in question
The problem:
I create a temporary table and copy the card holders into the table with separate select into statements for each of the above conditions. For the first condition, I first gather all the CardHolderHistory entries before the month in question with the following select
into #MonthlyCards from cardholderhistory left join Employer on PKEmployerID = cardholderhistory.FKEmployerID
where changedate<@datespecified This gives me ALL of the CardHolderHistory before the month in question (@dateSpecified). It would look something like this
EmployerName First Initial Last Status ChangeDate EmployerID ------------------------------------------------------------------------------------------------------------------------------- . . . Some Fake Company John P Doe A 2006-05-02 11:34:26.360 109 Some Fake Company John P Doe A 2006-03-28 11:14:10.520 109 Some Fake Company Jane T Doe S 2006-05-30 15:34:14.900 109 Some Fake Company Jane T Doe A 2006-03-28 12:20:03.670 109 Some Fake Company Jane T Doe A 2006-03-01 10:12:45.320 109 . . (S = suspended; A = Active) .
Now I need to make sure people are only listed once and remove everyone whose status = suspended for their last change date. That is my problem; I can€™t figure out how to remove these people. The next step i took was to do a group by with EmployerName, FirstName, Initial, LastName, Status, Max(ChangeDate), FKEmployerID. This removes everyone listed multiple times except if their status changes. After the group by the list would be EmployerName First Initial Last Status ChangeDate EmployerID ------------------------------------------------------------------------------------------------------------------------------- Some Fake Company John P Doe A 2006-05-02 11:34:26.360 109 Some Fake Company Jane T Doe S 2006-05-30 15:34:14.900 109 Some Fake Company Jane T Doe A 2006-03-28 12:20:03.670 109
Now I need to delete Jane completely, since her latest status is S. I could delete where Status = €˜S€™ but that leaves her as active from 3/28. I can€™t figure out how to completely remove Jane. The rest I can figure out I think. Please help me get rid of Jane for good, I don€™t like her.
I'm trying to pass a parameter into a procedure and not getting the results I want. There's probably better ways to write the procedure, and if you want to suggest one, that's ok, but I'm most interested in just getting the stupid thing to work. I'm trying to search a bit field in a table with the bit field being the parameter that isn't working. In the code I include below, @category is the parameter that isn't working. For this case, "category" is a bit field in a table and I want to be able to pass in different fields and find out if they're set. When I use the parameter @category, the result isn't right. If I hard code the field name (for the case below, "Compensation" is the DB field name), it works. I'm declareing the calling procedures parameter as a bit field too. Code follows:
CALLING PROCEDURE:
DECLARE
@include int,
@Compensation bit,
@performanceBool bit
-- Find out if this category is included in the Plan Summary
-- CALLING PROCEDURE WITH PROBLEM PARAMETER IS BELOW
Am trying to setup a SSIS package between a sql2000, sql2005 source and a sql2005 destination.
I have 2 concerns, firstly, due to performance reasons (we have 2 huge legacy databases):
After 1st run,
Source table has: 1 - 1000 records
Destination table has: 1 - 1000 records
For 2nd run,
Source table has: 1 - 1500 records
Destination table has: 1 - 1500 records
How I insert only the 1001th record - 1500th record, without touching the 1st to 1000th record?
Secondly, if there are any changes in values in the records 1st to 1000th record, how to I compare and only update the value that has changed? Is there any particular configuration setting in sql that I can use?
I'm currently working on an application that needs to store address information. I figured a database of some sort would be the perfect thing to use.
The thing is: The application will work locally, there will be no server running on a network
There shouldn't be any login and password protection for the db When I later on install the application on a computer, a fresh and clean db should be created within the applications folde
How can I create and access a db like that using c# and MS SQL Server?
Any hints, tips, example code, links...anything?
Many thanks in advance!!!
PS. I've been working some with visual c# express and recently installed SQL Server & Manager. I've previusly made some php web pages using MySQL but I´m a total noob when it comes to working with SQL and c# (= I'm stuck!).
I'm in a db design, implement, and mgmt class this semester and we are teaching ourselves SQL using MS SQL 2005. I downloaded and installed MS SQL Server 05 Std version successfully. However, when I open the Server Mgmt Studio I am prompted to connect to a server??
EX: Server Type: Database Engine Server Name:????????? Windows Authentication
To my knowledge we have been given no server info and are only supposed to be learning the language. Can I just cancel this window and continue?
The only help we have been given is a O Reilly: Learning SQL on SQL Server 2005 and they kind of skip all the installation and setup stages.
I have followed all the tutorials and I think I have a pretty good grasp on how to get info from the SQL database running on my machine in the App Data directory. My question is simple and probably the answer is nested in the 148 pages of this very informative forum. How do I take the 15 textbox fields worth of data and insert them into the SQL db? I am pretty familiar with general sql concepts (mysql, mssql, postgresql), but I have only been in this IDE for about a week now and I need to get my project up and running quickly. I am just unfamiliar with VB in general, but I do understand the object.method concept. I gathered that I need to make the insert statement part of the button click "event". I cannot find any good "starter" documentation on getting this process to work. I understand the grid and form and detail view really well and have tested my 2nd app with success, but it requires the db to be populated by a web front end. I have read a few MSDN links but they are not helping much, they just keep linking me back to grid and detal and form views. Help? Please? Pretty Please?- Chris
I'm beginning development on a medium/ sized in-house web based system. Well moving from asp/mysql to asp.net/sql server. Re-doing DB design as well.My question is does it make sense for me to develop the SQL DB in Visual Studio 2005 pro, and then later move it to a real SQL server? Or basically what is the best and/or most practical way to do this development. Any other IDE's out there?Thanks
hi, From my ealier post I learned that a MDF file can be read and written to even if I did not attach the database in the SQL Sever Management Studio. Is that behavior because of the connection string where is has AttachDBFilename=|DataDirectory|DNN12_24_07.mdf , Is that what "attaches" the database? This runs on SQL Express2005. Should I change the connection string to: name="SiteSqlServer" connectionString="Server=(local);Database=ASPNET;uid=;pwd=;" providerName="System.Data.SqlClient" /> ? Is there a statment in the XML of web.config that defines |DataDirectory| of the original connection string that points to APP_DATA where the mdf file physically resides? Thanks in advance? -KK
Hi, I am very new to the .net framework, but I have a lot of experince in php/mysql applications. So this is this is proboly going to seem like a nwebie question. I am trying to pull a single item out of the database programaticlly, take the value of that item and set it to a varible so I can work with it in other areas. Here is basically what I have this is on the vb page in a sub I have the varible MonthlyPrice Defined earlier in the pageDim connString As String = _ConfigurationManager.ConnectionStrings("ConnectioString").ConnectionString'Create a SqlConnection instanceUsing myConnection As New SqlConnection(connString)'Specify the SQL queryConst sql As String = "SELECT Price FROM Plans Where PlainID='" & PlanId & "'"'Create a SqlCommand instanceDim myCommand As New SqlCommand(sql, myConnection)'Get back a DataSetDim myDataSet As New DataSet'Create a SqlDataAdapter instanceDim ReadData As New SqlDataReader(myCommand) 'Bind the DataSet to the GridViewMonthlyPrice = ReadData("Price")'Close the connectionmyConnection.Close()End Using I just want to get the price of the plan where planID is equal to the value of my varible planID. I want to set that equal to MonthlyPrice which is a decimal so I can add it into some calculations and return a value. I don't really want to bind the data to anything but everytime I search google I can't find anything excpet for binding data to grideview and things like that. I have tried several diffrent approaches that I have found out there but have gotten errors or and things I am not understanding. I originally was trying to access a datasource I already have defined that is on the aspx page.<asp:SqlDataSource ID="SQLSelectPlan" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT * FROM [Plans] WHERE ([PlanID] = @PlanID2)"> <SelectParameters> <asp:FormParameter FormField="grSelectPlan" Name="PlanID2" Type="Int32" /> </SelectParameters> </asp:SqlDataSource> Is there a way for me to grab this datasource which works and is tied to a detailed view, take the value in the price collom and stick it in the MonthlyPrice varible on my vb page? That is the approach that seems the most logical to me. If someone has better way I would really like to know.
I'm not sure if this is a SQL question or if it is for the programming language but here goes:
I have a BIT column in a table referring to an active record. When the item is checked on the submit form the BIT changes to 1 to make the item active on the page. Is there a way to set up the DB table so that only one record in the table is set to 1? Meaning if Item A is already active (with 1) and then I update Item B to active, can Item A go back to inactive (or 0)?
Ok, I know a LITTLE about SQL 2000. I am just starting to get my feet wet in the area. I know how to install SQL, backup DB's and Restore them. Can even do them over the network now. YAY ME! Anyway, here is my question. I have a production server with 4 RAID arrays. I have one for my OS, one for the Main SQL DB's (heavy transaction DB's), one for my log files, and one that is supposed to be for my temp DB. Well when I installed SQL it asked me for the default data path, which would be where my main SQL db's go. I dont remember it asking me where I want the temp DB to go. How do I change the location of the tempdb.mdf and tempdb.ldf to the drive arrays I want them to go to, even though I have already installed SQL.
Thanks. And sorry ahead of time for the noobiness of the question. I did do a search first, but didnt really see anything that would help me.
I know it's unbelievable, but i didn't ever use SP before. Sorry for this noobish question, don't beat me for this - please ;-)
In my SELECT Query i like to have some (WHERE) paramteters le's say: ID, NAME and AGE. I allways programmicali generate a WHERE statement in relation to which parameters I really get, because not allwasy i get all the parameters together. This is fine for "normal" Tables.
How does this work with SP? If I don't get NAME, do I set the SP-Parameter NAME to null to unconsider it? Or do I have to make some IF statements in the SP to check, if NAME has some value?
