Let's say I have a table (Table1) with three columns (Col1, Col2, Col3) and two rows. Let's say I have a button on a page. I want to send the contents of those two rows in an email in the Click event handler of the button. Here's the SQL framework I've got:
[Code]....
How would i go about emailing every address in a db table the same message?
SmtpMail.SmtpServer = "mail.CHANGE.com";
string strTo = ?????????;
string strFrom = "noreply@CHANGE.com";
string strSubject = "subjectbox.Text";
string strContent= "content.Text";
SmtpMail.Send(strFrom, strTo, strSubject, strContent);
I am using this code to fill my datalist:
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim myConnection As SqlConnection Dim myCommand As SqlDataAdapter
myConnection = New SqlConnection("mydb")
myCommand = New SqlDataAdapter("SELECT * FROM Posts", myConnection)
Dim ds As DataSet = New DataSet() myCommand.Fill(ds)
DataList1.DataSource = ds DataList1.DataBind()
End Sub
It works and fills the datalist with the whole table. When this page opens the address bar is like [URL].Ii would like the datalist to show all contents in the table with this specific thread ID.
I have one table named 'dbo.ac_Products' Within this table there is a column named 'ProductId' There is also another table named 'dbo.ac_CatalogNodes' Within this table there is a column named 'CatalogNodeId' and a column named 'CatalogNodeType'
I need to delete all the rows from 'dbo.ac_Products' and 'dbo.ac_CatalogNodes' where 'ProductId' = 'CatalogNodeId' and 'CatalogNodeType' = 1
I have table a and would like update table a whose values from table b, but not update all rows at table a. Only update record ID in table a match record ID in tableC.
Here is my query
update a
set a.Desc = b.Desc,
a.Value = b.Value
from TableA a, TableB b
where a.name = b.name
and a.ID = b.ID
and a.ID in ( select ID from TableC)
I only have 12 a.ID match TableC, but above query update whole TableA.
How to only update 12 record at TableA from TableB?
I am working on storing pdf file in sql server database and then reading a portion of it and displaying in html.
View 2 RepliesRecently i have come across one question in sql query
Question: Which SQL Query helps to view the TOP N rows From the particular SQL table?
I have a large database of user profile data. I need to be able to compare 1 row against all of the other rows, for all columns to find the best match.There are around 30 columns of data for each row. I need to be able to find the 5 best matching rows.
I.E.
Row 1 matches on 2 column's
Row 2 matches on 12 column's
Row 3 matches on 7 column's
Row 4 matches on 4 column's
Row 5 matches on 9 column's
Row 6 matches on 23 column's
Row 7 matches on 29 column's
In the end it will show me the data for rows 7, 6, 2, 5, 3 Now to make it even harder, Some of the columns are bit, some are varchar.Some of them may be a direct match, some will need to search if the column for row2 contains the text of row 1.
I have a table think as table1, with 3 columns
col1 col2 col3 -- Columns
Y N 1 -- Data
but i want
colname val
col1 Y
col2 N
col3 1
I have a data table called Songs It has SongId as Primary Key of type INT SongName of Type VarChar UserId of Type Int. I simply want to check how many entries are in the Table already by The UserId before they insert more. So If there are already 3 songs by that User, I want to stop the insert process and warn them they have the maxium already. I think i am halfway there already using code below but problem is that UserId is of Type GUID, and not Int so I am missing something. On my Aspx, Page, I have a DetailsView for the Database Insert. I also have the following code using BusLogic Layer and DAL
SongsBLL
public int ScalarQuery(Guid ArtistId)
{
int returnValue = ScalarQuery(ArtistId);
return returnValue;
}
Code Behind ASPX page
This in in DetailsView_ Item Inserting Event
SongsBLL number = new SongsBLL();
currentCount = Convert.ToInt32(number.ScalarQuery(currentCount).ToString());..........
Suppose I have a temporary table (Shown below). The table is defined and populated in my stored procedure. After it is populated, I need to go through every row in the table. Here is what I need to do :
Check if Amount2 > Amount1 If Amount2 > Amount1 then set Amount1 = Amount2
It feels a little akward for me to use the temp table.
[code]....
I have a table called Student with a lot foreign keys that will be used after to filter students(table rows).The structure of this table is like:
StudentId GenderId DegreeId NatioanlityId
1 2 2 3
....
As a student can speak one or more language,the Sudent Table is linked to language table like this
StudentId LangaugeId
1 1
1 2
1 3
And a student can chose one or many subjects for exam:
StudentId ExamId
1 1
1 2
....
In my asp.net page I would like to filter students via checkbox using ajax for example, student having female and male gender with Degree 1,speaking language 1,2... I filter rows in a stored proc using user defined table,and I have to use a lot of IF statements like this
if(EXISTS(SELECT GenderId FROM @GenderTable))
if(EXISTS(SELECT DegreeId FROM @DegreeTable))
if(....)
else
if(...)
How can I avoid all the IF statements? I have more than 5 filters.It's boring.
The topic of sql server deadlock has been discussed many times, however, I was unsure that even two simultaneous inserts on a table can end up in a deadlock situation. Scenario: While testing our application (SQL Server 2005 as backend, ASP.net 3.5) we inserted records into a table simultaneously (simplified overview) and that resulted into a deadlock for more than 70% of users.
I could not get a hang of this as how an insert is being deadlock as this is not a case of multiple resources. After a detailed analysis (of reproducing the bug by two users) I found that both the processes were holding a RangeS-S lock on the primary key index of the table and were trying to convert this into RangeI-N lock, that resulted into a deadlock and one transaction being killed.
Question: Can we avoid or reduce these kind of deadlocks as this is not a case of change in order of access of resources? Cant' we force the transaction to get exclusive lock initially so that it blocks the other process and avoid deadlock? What (adverse) effects that may have? Also could some one explain more about RangeI-N lock.
Isolation Level for this was "Serializable".
I have a one sql table, it contain 5 column id, ipaddress, status, datetime, value. I need to find distinct rows as per ipaddress column basic..
View 7 RepliesI have a table which contains asp radiobuttonlists (see below) for a survey. Once submitted the results are written to a database.I would like to use this same table for a similar survey, but the trouble is the question order needs to be different. The questions are the same, just in a different order. Is there a way (server side) to change the order of the table rows?
<table cellpadding="0" cellspacing="0" border="0">
<tr>
<td> </td>
[code]...
write the query for merging two rows in a table with same date and time into one row...
2009/12/23 13:46:50 -0.43546 0.02395 -7.1621 0.45529
2009/12/23 13:46:50 0 0 0 0
2009/12/23 13:47:00 -0.47114 -0.00515 -7.1779 0.42751
2009/12/23 13:47:00 0 0 0 0
i want to merge this same datetime rows into one row ..
I've a table where one person teaches multiple classes. It looks like this.
Table1
Id Name Class Subject
----------------------------------------
2 Sam 12 English
2 Sam 10 Maths
2 Sam 10 English
2 Sam 8 History
2 Sam 12 Economics
Here Sam teaches Class 12 - English & Economics, Class 10 - Maths & English, Class 8 - History
I want to display the result like this -
Id Name Class Subject
---------------------------------------------------------------
2 Sam 12 English, Economics
2 Sam 10 Maths, English
2 Sam 8 History
How to concatenate multiple Rows?
Table1
t1c1 t1c2 t1c3
1 1 jim
2 2 ruth
3 4 paul
4 8 sam
5 16 NA
Table2 ( Need this)
t2c1 t2c2 t2c3 t2c4 t2c5
10 1 1 jim 2010-02-03
10 2 1 ruth 2010-02-03
10 4 1 paul 2010-02-03
10 8 1 sam 2010-02-03
The tricky part I have to perform is to copy rows( only t1c2 -> t2c2, t1c3->t2c4 columns) untill I see NA(Not applicable string) in t1c3 to Table2 putting (10 in t2c1, 1 in t2c3, current date in t2c5 columns for all rows).
I have a table that the user can add rows to through javascript by clicking on a "add rows button". In each row there are two columns one with a dropdown list and the other a text box. The dropdown list is populated with data through a sub on server side code.I need to be able to insert the data from each row into a table but I'm unable to do this. I clicked on my "add rows button" to add several rows and then did a test to see how many rows the table was returning on the server side and it did not include the five rows I added .. only the header row.If there is a better way to allow users to dynamically add rows to a table and then access the dynamically created table on the server then I'm all ears!
View 3 RepliesI have an excel sheet that contains around 30,000 rows and 18 colmns. These no of rows and columns may increase in future. I need to read all these records from excel sheet and insert into a table in sql database. For reading the excel book I am using Oledbconnections. The possible solutions I have known as per my knowledge, to insert the data are
1. To insert one record at a time which makes 30,000 database hits. How will this affect the performance?
2. To use liked servers - bu this is not working for me.I do not have database permissions to use linked servers. So, the only option i have is the first one.
I'm having a gridview in my aspx page with checkbox, User can select one or more than one record(s) at a time and clicks on delete button, Then i'm constructing an object of my DTO and adding all the checked row's Primary Key (DataKeyNames - GUID's in case) to a serializable class and generating an XML and sending it as parameter to the Stored Procedure.
My Doubt is, can i bulk delete all the rows from database table those are present in my XML document??
If it works, I can do bulk update with XML only, i already did bulk insertion by generating an XML..
My Another Question is, Will it affect the performance? Each time when i construct an DTO object and adding it to the Generic List??
WHich one is better, I mean ObjectDataSource or SQLDataSource or my XML method?
My XML will be like this
<root>
<Customer>
<CustomerID>GUID1</CustomerID>
<CustomerID>GUID2</CustomerID>
<CustomerID>GUID3</CustomerID>.....
The idea is that a pure html table will be generated on the client side. (using jQuery to add dynamic new rows to the table when the user clicks a button). These rows contain textboxes, dropdownlists, checkboxes and other input controls. When the user clicks a button, a PostBack is triggered in which I need to access this data from the table on server side, validate it and perform other actions on it.
I'm able to perform the client side and access the table data on server side by adding runat="server" to the table tag. However, I only see the initial table rows (not the ones added through JavaScript) and I have troubles retrieving the different inputs from the rows. put me in the right direction?? Or someone offering a better solution to the problem?
Although I'm a total novice at building web pages, I figured the best way to learn is to do it. I've used a master page and, so far, all the basic information pages work OK.
My problem starts when I have several options for the same Content Holder. Is it possible to use clickable cells to show various *.html files in a different row in the same table.
I can show an image using a script but not a file.
<script type ="text/javascript" >
function Change() {
document.getElementById ('tdContentItem').innerHTML ='<img src="Images/Thumbs/Councillors.png"/>'
}
</script>
What I need to show is a clickable imagemap that loads the resulting pages into the same row.
how to retrieve from the server-side contained a table html constructed this way:
<table id="myTable" >
<tr>
<th> <input type="text" value="name"/></th>
<th> <input type="text" value="quantity" /> </th>
</tr>
<tr>
<th> <input id="name_1" value="phone" /> </th>
<th> <input id="quantity_1" value="15" /> </th>
</tr>
<tr>
<th> <input id="name_2" value="mp3" /> </th>
<th> <input id="quantity_2" value="26" /> </th>
</tr>
</table>
I can not make use of <asp:Table> ... because for technical reasons I did not find a solution following this post: [URL]
How can retrieve the contents values of my table (dynamic) for each row. Rows will be added in client-side js
i have a table that has some columns and rows i want this table to have borders that are visible inside it i want every column an row to be closed in borders how can i do that?
View 1 Replies
