|
SQL Reporting Services can be used with ASP.NET. It provides the facility to export reports to a number of formats, including Excel and PDF.
Paul
|
|
|
|
|
hi thank you for helping
i read this is other places but i dont know how i can use of SQL Reporting Services.
i set SQL Server Surface Area Configuration and i know i must write a Query but i dont know where i write it and how relate it to asp.net. offcorse i read "Export huge data to Excel" topic in this site but i dont know that solve my problem or not. i want use this services in asp.net and i dont know how do i use it
|
|
|
|
|
I need some help writing a stored proc. First, I am inserting a range of ZipCodes into a db, ZipBegin and ZipEnd(12345-12346). Although that seems relatively simple, however, I need to check those ranges and make sure that there is no other over lapping range.
for more clarity:
range A 12345-12346 is in the db
range B 12344-12347 is being inserted.
as you can see these ranges overlap, if that occurs I need to use a raiserror and rollback the transaction.
Does anyone have any suggestions on how I could do that?
Chance favors the prepared mind....
|
|
|
|
|
Something like:
create procedure dbo.usp_InsertZipCodeRange
@StartZip int,
@EndZip int
as begin
declare @CurrentZip int
if (@StartZip > @EndZip or @StartZip < 0) begin
raiserror 'Invalid arguments specified'
return @@ERROR
end
if exists (select 1 from dbo.ZipCodes
where ZipCode between @StartZip and @EndZip) begin
raiserror 'Specified range overlaps existing Zip codes'
return @@ERROR
end
set @CurrentZip = @StartZip
while (@CurrentZip <= @EndZip) begin
insert into dbo.ZipCodes (ZipCode) values (@CurrentZip)
set @CurrentZip = @CurrentZip + 1
end
return 0
end If Zip codes are strings then Right('00000' + Convert(varchar, @CurrentZip), 5) should allow you to convert an integer value into the appropriate string.
Regards
Andy
If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".
|
|
|
|
|
Hi, guys. I really need some assistance with the DoCmd.SendObject object in VBA in Microsoft Access. It appears that I am only limited to specific ObjectTypes that does not include an approach to do so dynamically in code.
It appears in code that there are fewer options than there are with the implementation of macros which also permit "Stored Procedures" and "Server Views". Can I send the results of strSQLFQT to the SendObject? You see, I need to loop through two tables at the same time, and for every occurrence of the DISTINCT item in rstMRL there could be many records in rstFQT. I would like to send only the relevent records from rstFQT to each person in rstMRL. Can someone kindly tell me where I am going wrong.
The code I have so far is as follows:
Option Compare Database
Dim db As New DAO.DBEngine
Dim rec As Recordset
Function Email_New()
On Error GoTo Email_New_Err
DoCmd.OpenQuery "Final Query Test 2", acViewNormal, acReadOnly
DoCmd.GoToRecord acQuery, "Final Query Test 2", acFirst
Call SendEmails
Email_New_Exit:
Exit Function
Email_New_Err:
MsgBox Error$
Resume Email_New_Exit
End Function
Function SendEmails()
On Error GoTo ErrorHandler
Dim Cnxn As ADODB.Connection
Dim strConn As String
Dim rstMRL As ADODB.Recordset
Dim cmdSQLMRL As ADODB.Command
Dim strSQLMRL As String
Dim rstFQT As ADODB.Recordset
Dim cmdSQLFQT As ADODB.Command
Dim strSQLFQT As String
Dim strHoldRecString As String
Dim strMessage As String
Set Cnxn = New ADODB.Connection
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=J:\GELCO DATABASE\Gelco_USA.mdb;"
Cnxn.Open strConn
Set cmdSQLMRL = New ADODB.Command
Set cmdSQLMRL.ActiveConnection = Cnxn
strSQLMRL = "Select [Rep Number], [Rep ID], [Email Address] " & _
"FROM [Master Rep List Test] ORDER BY [Rep Number]"
cmdSQLMRL.CommandType = adCmdUnknown
cmdSQLMRL.CommandText = strSQLMRL
Set rstMRL = cmdSQLMRL.Execute()
rstMRL.MoveFirst
strHoldRecString = rstMRL![Rep Number]
Set cmdSQLFQT = New ADODB.Command
Set cmdSQLFQT.ActiveConnection = Cnxn
' Attempted Dynamic SQL here...
strSQLFQT = "SELECT [Confirmation #], [Rep Name], " & _
"[Report #], [Email Address], [Days Aged] " & _
"FROM [Final Query Test 2] WHERE [Days Aged] > 30 " & _
"AND [Rep ID2] = '" & strHoldRecString & "'"
MsgBox strSQLFQT ' Shows the results
cmdSQLFQT.CommandText = strSQLFQT
Set rstFQT = cmdSQLFQT.Execute()
rstFQT.MoveFirst
Do Until rstMRL.EOF
DoCmd.SendObject acSendQuery, "Final Query Test 2", acFormatXLS, _
"expense.reports@wrigley.com", , , "Missing Expense Reports", _
"Hello There, you have some files missing", False
' Somehow requery the code from the second table here...
strHoldRecString = rstMRL![Rep Number]
strSQLFQT = "SELECT [Confirmation #], [Rep Name], " & _
"[Report #], [Email Address], [Days Aged] " & _
"FROM [Final Query Test 2] WHERE [Days Aged] > 30 " & _
"AND [Rep ID2] = '" & strHoldRecString & "'"
rstMRL.MoveNext
Loop
' I am able to loop through the records for each table properly with this code which should identify the fields that I need to include.
'Do Until rstMRL.EOF
'strMessage = "Rep Number: " & rstMRL![Rep Number] & _
' vbCr & " Rep ID: " & rstMRL![Rep ID] & _
'vbCr & " Email Address: " & rstMRL![Email Address] & ""
'MsgBox strMessage
'rstMRL.MoveNext
'Loop
'Do Until rstFQT.EOF
'strMessage = "Confirmation #: " & rstFQT![Confirmation #] & _
' vbCr & " Rep Name: " & rstFQT![Rep Name] & _
'vbCr & " Report #: " & rstFQT![Report #] & _
' vbCr & " Email Address #: " & rstFQT![Email Address] & _
' vbCr & " Days Aged: " & rstFQT![Days Aged] & ""
'MsgBox strMessage
' rstFQT.MoveNext
'Loop
ErrorHandler:
If Not rstMRL Is Nothing Then
If rstMRL.State = adStateOpen Then rstMRL.Close
End If
Set rstMRL = Nothing
If Not rstFQT Is Nothing Then
If rstFQT.State = adStateOpen Then rstFQT.Close
End If
Set rstFQT = Nothing
If Not Cnxn Is Nothing Then
If Cnxn.State = adStateOpen Then Cnxn.Close
End If
Set Cnxn = Nothing
If Err <> 0 Then
MsgBox Err.Source & "-->" & Err.Description, , "Error"
End If
End Function
-- modified at 13:58 Friday 17th August, 2007
|
|
|
|
|
I have an ms sql database that has foreign key relationships and until today I hadn't mapped those out via sql server, they were just on paper.
So for example, I have a Company with many People.
Before, when I deleted a Company, it's many people were orphaned. Now, since I've mapped the relationship for the sql server, when I try to delete a Company, I get an error. I think in mysql talk this is called a cascading delete.
How do I do this with ms sql?
/\ |_ E X E GG
|
|
|
|
|
Which database product are you using? With most databases you just need to add ON DELETE CASCADE to your foreign-key definition.
If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".
|
|
|
|
|
I'm using sql server 2000, but I just found the property for cascading deletes under the relationships menu on my table.
/\ |_ E X E GG
|
|
|
|
|
when i run my application i got error "SQL Error # 55030 Optimistic Conncurrency Error". please provide solution for it
|
|
|
|
|
If your database is SQL-Server then this is probably a user-defined error message that is produced by one of the following:- A stored procedure within your database (look for instances of
RAISERROR ). - A trigger within your database (look for
RAISERRROR ). - The front-end application that is accessing your database (this depends on which programming language has been used on the front-end - for VB/VbScript look for
Err.Raise ). You will need to investigate your system more deeply to identify where the error comes from.
Regards
Andy
If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".
|
|
|
|
|
I've a table with one of columns having an image datatype.
The data in the image column is table data.
Currently, the image data is read/inserted/updated through an application for which there is no source code available and we intend to rewrite this in C#/VB.
I know the structure of the table in image column.
The structure of main table is tb_MyTable(Col1 int, Col2 Varchar(100), col3 image).
The inner table in image column has an int column and 2 varchar columns.
I tried "select * from tb_MyTable" from front end(C#) and i see that the data type of image column is a byte array.
How can i read the table data residing in this image column into a datatable in C#/VB?
OR can it be done in SQL itself?
|
|
|
|
|
The following link[^] demonstrates how to read and write image data from SQL Server in C#.
Paul
|
|
|
|
|
Vadlamudi VenuGopal wrote: The inner table in image column has an int column and 2 varchar columns.
Vadlamudi VenuGopal wrote: How can i read the table data residing in this image column into a datatable in C#/VB?
OR can it be done in SQL itself?
You have to find out what format it is in and decode it. SQL Server does not store "inner tables" in image columns. What ever is in the image column is likely nothing to do with SQL Server. The original application must have put data there in a format it understood.
|
|
|
|
|
Hi friends,
I have a table like this
Id City
-- ----
1 Chennai
2 Vilupuram
3 Trichy
4 Madurai
But i want Output like this
Chennai Vilupuram Trichy Madurai
Simply i want to display a column horizandally in a row. Is it possible? Help me.
Thanks
--Jegastar
|
|
|
|
|
jegastar wrote: Simply i want to display a column horizandally in a row. Is it possible? Help me.
Use cursor and iterate through each row. Assign value to a variable and select the variable at the end.
|
|
|
|
|
Hi,
Try this
DECLARE @str varchar(50)
When using space as the delimiter
---------------------------------
select @str = coalesce(@str+' ','')+Msg
from Table1
SELECT @str
Result:
Chennai Vilupuram Trichy Madurai
When using comma as the delimiter
---------------------------------
Result:
Chennai,Vilupuram,Trichy,Madurai
Senthil
|
|
|
|
|
hi;
i new in MFC and i have a problem with adding and deleting but basically i can attribute my problem to lack of proper tutorials on the same. ANy site i try to get tutorials i end up with getting complex stuff posted by Gurus tryiong to implement not the usual basic works that am in need of so i have been tring to understand the whole concept by following one simple code example i have and so in that connection if you know a site i can get real introduction tutorials to ADO in MFC i will appreciate but in the mean time
help me get thru this problem at hand(my name is king'ori from kenya);
I want to add supplierid (not null primary key) and suppliername(not null) to the SUPPLIERS table (which i have alreaady created);
so i have included the adofields.h in my project..,done the ::initialization, the opening() plus i have these functions
void MyView:: RefreshSupplier()
{
if(p_rsSupplier->BOFILE || p_rsSupplierEOFILE)
return;
m_strSupplierName =(const char*)_bstr_t(FieldValue(p_rsSupplier,"suppliername");
m_lSupplierID = long(FieldValue(p_rsSupplier,"supplierID"));
UpdateData(FALSE);
}
vooid MyView:: UpdateSupplier()
{
if(p_rsSupplier->BOFILE || p_rsSupplier->EOFILE)
retirn;
CString strSupplier;
long lsupplier;
UpgateData();
if(lSupplier != m_lSupplierID)
FieldValue(p_rsSupplier,"supplierid",m_lSupplierID);
if(strSupplier != m_strSupplierName)
FieldValue(p_rsSupplier,"suppliername",m_strSuppliername);
}
void MyView::BlankSupplier()
{
m_lSupplierID = 0;
m_strSupplierName = _T(" ");
}
problem comes i dont understand the adding flow,- for instance why do you have to blank the recordset? what comes first p_rsSupplier->Addnew() or is it UpdateSupplier() and why;
this is not working
void MyView::AddSupplier()
{
BlankSupplier(); //why this and my fields are not null?
p_rsSupplier->AddNew();
UpdateData();
p_rsSupplier->PutCollect(_variant_t("Suppliername",_variant((LPCTSTR)m_strName);
p_rsSupplier->PutCollect....suppierID);
p_rsSupplier->MoveNext();
RefreshSupplier();
}
void MyView::DeleteSupplier()
{
if(p_rsSupplier->BOFILE || p_rsSupplier->EOFILE)
{
if(Warning MSgBx ==YES)
{
p_rsSupplier->Delete(adAffectCurrent);
p_rsSupplier->MovePrevious();
RefreshSupplier();
}
|
|
|
|
|
hi;
i new in MFC and i have a problem with adding and deleting but basically i can attribute my problem to lack of proper tutorials on the same. ANy site i try to get tutorials i end up with getting complex stuff posted by Gurus tryiong to implement not the usual basic works that am in need of so i have been tring to understand the whole concept by following one simple code example i have and so in that connection if you know a site i can get real introduction tutorials to ADO in MFC i will appreciate but in the mean time
help me get thru this problem at hand(my name is king'ori from kenya);
I want to add supplierid (not null primary key) and suppliername(not null) to the SUPPLIERS table (which i have alreaady created);
so i have included the adofields.h in my project..,done the ::initialization, the opening() plus i have these functions
void MyView:: RefreshSupplier()
{
if(p_rsSupplier->BOFILE || p_rsSupplierEOFILE)
return;
m_strSupplierName =(const char*)_bstr_t(FieldValue(p_rsSupplier,"suppliername");
m_lSupplierID = long(FieldValue(p_rsSupplier,"supplierID"));
UpdateData(FALSE);
}
vooid MyView:: UpdateSupplier()
{
if(p_rsSupplier->BOFILE || p_rsSupplier->EOFILE)
retirn;
CString strSupplier;
long lsupplier;
UpgateData();
if(lSupplier != m_lSupplierID)
FieldValue(p_rsSupplier,"supplierid",m_lSupplierID);
if(strSupplier != m_strSupplierName)
FieldValue(p_rsSupplier,"suppliername",m_strSuppliername);
}
void MyView::BlankSupplier()
{
m_lSupplierID = 0;
m_strSupplierName = _T(" ");
}
problem comes i dont understand the adding flow,- for instance why do you have to blank the recordset? what comes first p_rsSupplier->Addnew() or is it UpdateSupplier() and why;
this is not working
void MyView::AddSupplier()
{
BlankSupplier(); //why this and my fields are not null?
p_rsSupplier->AddNew();
UpdateData();
p_rsSupplier->PutCollect(_variant_t("Suppliername",_variant((LPCTSTR)m_strName);
p_rsSupplier->PutCollect....suppierID);
p_rsSupplier->MoveNext();
RefreshSupplier();
}
void MyView::DeleteSupplier()
{
if(p_rsSupplier->BOFILE || p_rsSupplier->EOFILE)
{
if(Warning MSgBx ==YES)
{
p_rsSupplier->Delete(adAffectCurrent);
p_rsSupplier->MovePrevious();
RefreshSupplier();
}
|
|
|
|
|
in my project, i use toolbar toolbar in my buttons...i used case structure for the functions of each buttons in the toolbar but theres an error in it...
my codes goes like this:
Select Case
Case 0
Dim subjects As New subjects
subjects.Show()
Me.Hide()
Case 1
Dim file_maintenance As New file_maintenance
file_maintenance.Show()
Me.Hide()
Case 2
Dim grade_entry As New grade_entry
grade_entry.Show()
Me.Hide()
End Select
is this code correct???
or this:
dim keyin as integer
Select Case keyIn
Case 0
Dim subjects As New subjects
subjects.Show()
Me.Hide()
Case 1
Dim file_maintenance As New file_maintenance
file_maintenance.Show()
Me.Hide()
Case 2
Dim grade_entry As New grade_entry
grade_entry.Show()
Me.Hide()
End Select
in the first code, there is an error so i can't run my program...
in the other code, the 1st button is only functioning...
what is the really correct code for this???
|
|
|
|
|
Rharzkie wrote: what is the really correct code for this???
What is the correct forum for this question because I really don't see anything to do with SQL or databases in here!?
|
|
|
|
|
You would be better-off with the VB.Net forum. However, it looks like you haven't assigned any value to the keyIn variable.
If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".
|
|
|
|
|
Breaking forum rules will result in you getting no help.
_____________________________________________
Flea Market! It's just like...it's just like...A MINI-MALL!
|
|
|
|
|
Hi Rharzkie,
In your first part of your code you didn't pass parameter for the case.
It should be
Select Case <parameter>
Case 0
Dim subjects As New subjects
subjects.Show()
Me.Hide()
Case 1
Dim file_maintenance As New file_maintenance
file_maintenance.Show()
Me.Hide()
Case 2
Dim grade_entry As New grade_entry
grade_entry.Show()
Me.Hide()
End Select
Senthil
|
|
|
|
|
I was successful in creating my Stored Procedure. But if tomorrow I want to access and see the code that I have written for the Stored Procedure how should I access that?
|
|
|
|
|
What in? SQL Server 2005? SQL Server 2000? How you view it depends on the database.
Deja View - the feeling that you've seen this post before.
|
|
|
|