How Can I Return A Collection Instead Of A SqlDataReader?
Jan 12, 2008
I have to create a CLR Proc for Reporting Services report that will use SharePoint (MOSS 2007) assemblies, so my datasource won't be a database, but the SharePoint object model that returns a collection.
How can I return these data (it's not necessary to be a collection exactly) without having to create a temp table just to return the SqlDataReader?
Thanks.
And sorry for my previous message, I just forgot that I was posting a message at the Global MSDN forums.
View 1 Replies
ADVERTISEMENT
Jan 2, 2006
This is my function, it returns SQLDataReader to DATALIST control. How
to return page number with the SQLDataReader set ? sql server 2005,
asp.net 2.0
Function get_all_events() As SqlDataReader
Dim myConnection As New
SqlConnection(ConfigurationManager.AppSettings("..........."))
Dim myCommand As New SqlCommand("EVENTS_LIST_BY_REGION_ALL", myConnection)
myCommand.CommandType = CommandType.StoredProcedure
Dim parameterState As New SqlParameter("@State", SqlDbType.VarChar, 2)
parameterState.Value = Request.Params("State")
myCommand.Parameters.Add(parameterState)
Dim parameterPagesize As New SqlParameter("@pagesize", SqlDbType.Int, 4)
parameterPagesize.Value = 20
myCommand.Parameters.Add(parameterPagesize)
Dim parameterPagenum As New SqlParameter("@pageNum", SqlDbType.Int, 4)
parameterPagenum.Value = pn1.SelectedPage
myCommand.Parameters.Add(parameterPagenum)
Dim parameterPageCount As New SqlParameter("@pagecount", SqlDbType.Int, 4)
parameterPageCount.Direction = ParameterDirection.ReturnValue
myCommand.Parameters.Add(parameterPageCount)
myConnection.Open()
'myCommand.ExecuteReader(CommandBehavior.CloseConnection)
'pages = CType(myCommand.Parameters("@pagecount").Value, Integer)
Return myCommand.ExecuteReader(CommandBehavior.CloseConnection)
End Function
Variable Pages is global integer.
This is what i am calling
DataList1.DataSource = get_all_events()
DataList1.DataBind()
How to return records and also the return value of pagecount ? i tried many options, nothing work. Please help !!. I am struck
View 3 Replies
View Related
Apr 15, 2008
Is there a way to return the fields collection of a dataset using the Reporting services Web Service? I can get to the reports but not the datasets. Thank you in advance for any support you can provide.
View 1 Replies
View Related
Jul 2, 2015
I have SCCM 2012 R2. I'm working on a custom report, using MS SQL Server Report Builder. I have other custom reports that work fine....they prompt for a collection and return results just on that one collection. The problem with the one I'm working on now is that it runs fine except for that....it prompts for a collection, lets you choose one collection,returns the desired info on each line, but it returns all systems, not just the one collection you selected. IOW, I'm having trouble getting the "where" clause to work right.
Here is the code for a report that works (it returns the selected collection and counts the patches needed by each machine):
select
   CS.Name0,
     CS.UserName0,
     CS.ResourceID,
case
when (sum(case when UCS.status=2 then 1 else 0 end))>0 then ((cast(sum(case when UCS.status=2 then 1 else 0 end)as int)))
[code]....
Here is the code for the report that I'm having trouble with. What you see runs but it has none of the collection selection code I've tried since I haven't gotten it to work:
SELECT
 DISTINCT(CPU.SystemName0) AS [System Name],
 CPU.Manufacturer0 AS Manufacturer,
 CPU.Name0 AS Name,
 COUNT(CPU.ResourceID) AS [Number of CPUs],
 CPU.NumberOfCores0 AS [Number of Cores per CPU],
Â
[Code] ....
get the collection selection code to work in the second (processor count) sample?
View 14 Replies
View Related
May 19, 2008
hi,
this is sanjeev,
i have SSIS package, using my c# program i want to add one execute package task to this package's sequence container.
it is creating the new package with out any probelm. but when i opened the package and try to move the newly created exeute package task it is giving the following error.
the element cannot be found in a collection. this error happens when you try to retrieve an element from a collection on a container during the execution of the package
this is my code
{
Package pkg = new Package();
string str = (string)entry.Key;
pkg.Name = str;
alEntity = (ArrayList)entry.Value;
ConnectionManager conMgr;
Executable chPackage;
TaskHost executePackageTask;
Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
//string PackagePath = @"c:Genesis.dtsx";
//p = app.LoadPackage(PackagePath, null);
p = new Package();
p.LoadFromXML(parentPackageBody, null);
p.Name = str;
//Sequence seqContainer;
IDTSSequence seqContainer;
//seqContainer = (Sequence)p.Executables["Extract Genesis Data"];
seqContainer = ((Sequence)p.Executables[0]);
string packageLocation = @"Geneva Packages";
conMgr = p.Connections["SQLChildPackagesConnectionString"];
foreach (string val in alEntity)
{
if (seqContainer.Executables.Contains("Load_" + val) == false)
{
chPackage = seqContainer.Executables.Add("STOCK:ExecutePackageTask");
executePackageTask = (TaskHost)chPackage;
executePackageTask.Name = "Load_" + val;
executePackageTask.Description = "Execute Package Task";
executePackageTask.Properties["Connection"].SetValue(executePackageTask, conMgr.Name);
executePackageTask.Properties["PackageName"].SetValue(executePackageTask, packageLocation + ddlApplication.SelectedItem.Text + @"" + executePackageTask.Name);
}
}
app.SaveToXml(Server.MapPath("../SynchronizeScript/Packages/" + ddlApplication.SelectedItem.Text + @"") + str + ".dtsx", p, null);
}
please let me know what is the wrong in my code.
thanks in advance.
regards
sanjeev bolllina
sanjay.bollina@gmail.com
View 14 Replies
View Related
Jan 28, 2008
i want to use datebase wich suport all latin languages but i cant find the collection
esspeacily
eng,
german,
france and Turksh
View 4 Replies
View Related
Nov 29, 2007
Hi..
Every time I want to read any record from data base I read it in dataset for example:SqlConnection con = new SqlConnection(@"Data Source=localhost ;Initial Catalog=university ;Integrated Security=True");
SqlCommand cmd = new SqlCommand("select [User_AuthorityID] from users where [UserID]='" + TextBox1.Text + "' and [UserPassword]='" + TextBox2.Text + "' ", con);SqlDataAdapter adp = new SqlDataAdapter();
adp.SelectCommand = cmd;DataSet ds = new DataSet();
adp.Fill(ds, "UserID");foreach (DataRow dr in ds.Tables["UserID"].Rows)
{
user_type = dr[0].ToString();
Session.Add("User_AuthorityID", user_type);
.........
Is there easier way to read data from data base?
How I can use SqlDataReader to do that?
Thanks..
View 4 Replies
View Related
Jun 1, 2008
Hey All,
I have come across a situation with two tables, they are dynamic and the user can add and edit values in the tables so I need to build a dynamic display control. It is all based around an FAQ system I have built. The user can create new FAQ categories (that is one table) then create a new FAQ Question & Answer (that is the second table). The tables are linked by the category id. So now I am trying to display the FAQ section like so.
CATEGORY NAME
QuestionAnswerQuestionAnswerCATEGORY NAME
QuestionAnswerQuestionAnswerCATEGORY NAME
QuestionAnswerQuestionAnswer
So my idea was to run a loop within a loop. First loop the category name, then within the category name, loop a second time to grab all of the questions & answers within the category id captured from the first loops sql select statement, then proceed to loop the category name again and of course repeat the process until all loops are completed. However I am getting, and I kinda figured I would get an error about my SQLDataReader. Bellow is my code maybe some type of edit or different recommendation is needed. Any help will do, thanks!Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
'--- Database Connection ---Dim sConnStr As String = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
Dim cnBKPost As New SqlConnection(sConnStr)
'--- End DB Connection ----
'----- FAQ's -------
Dim sql As String = "SELECT category_id, category_name FROM faq_category ORDER BY category_name DESC"Dim cmd As New SqlCommand(sql, cnBKPost)
cnBKPost.Open()Dim reader As SqlDataReader = cmd.ExecuteReader()
Dim str As New StringBuilder()
Dim catid As IntegerDo While reader.Read()
'--- Category Titles ----catid = reader("category_id")
str.Append("<h2>" & reader("category_name") & "</h2>")
'--- End Category Title ---
'--- Get FAQ's In Category ---
Dim sqlcat As String = "SELECT faq_question, faq_answer FROM tbl_faq WHERE faq_category = '" & catid & "'"Dim cmdcat As New SqlCommand(sqlcat, cnBKPost)
Dim readerfaq As SqlDataReader = cmdcat.ExecuteReader()Do While readerfaq.Read()
str.Append("<p><font style='font-size:12pt;font-color:#daa520;>'" & reader("faq_question") & "</font><br />")str.Append(reader("faq_answer") & "</p>")
str.Append("<br /><br /><br />")
Loop
readerfaq.Close()
'--- End Get FAQ's in Category ---
Loop
reader.Close()
cnBKPost.Close()Me.Literal1.Text = str.ToString()
End Sub
End Class
View 2 Replies
View Related
Jun 25, 2004
i'm using c# and SqlDataReader to simply retrieve data from one column of a database. problem is it's an integer i'm trying to retrieve, and so i'm trying to put it into an int variable, and i get the error "CS0029: Cannot implicitly convert type 'object' to 'int'" . i've looked for an answer for about an hour and every example for the SqlDataReader that i can find deals with strings only or the examples are too complex for me to understand.
there must be an easy way to retrieve this data and put it into an integer! help...
my line of code that creates the error:
int intGuestNum = dtrSelectTotalSessions["online_numSessions"];
View 5 Replies
View Related
Feb 27, 2004
I recently migrated a database from server A to server B. The backup jobs I am trying to build on server B are failing because of the following error:
--------------
[Microsoft SQL-DMO] Error 21776: [SQL-DMO]The name 'WinDat' was not found in the Databases collection. If the name is a qualified name, use [] to separate various parts of the name, and try again.
-------------
How do I add this database to the databases collection so it will be recognized?
Thanks in advance for your time and help.
View 9 Replies
View Related
Aug 16, 2007
I can add two reportitem controls, ie reportitems!begbal.value + reportitems!deposits.value, without a problem. However, when I add the 3rd reportitem control to the expression, ie + reportitems!withdrawals.value, some really funky arithmetic occurs. All of these controls I am referring to are in the same group footer.
Any help out there would be greatly appreciated.
View 1 Replies
View Related
Aug 29, 2006
Hi, from what I can find, there isn't a way to get the number of rows returned from a SQLDataReader command. Is this correct? If so, is there a way around this? My SQLDataReader command is as follows:Dim commandInd As New System.Data.OleDb.OleDbDataAdapter(strQueryCombined, connInd)Dim commandSQL As New SqlCommand("GetAssetList2", connStringSQL)Dim resultDS As New Data.DataSet()'// Fill the dataset with valuescommandInd.Fill(resultDS)'// Get the XML values of the dataset to send to SQL server and run a new queryDim strXML As String = resultDS.GetXml()Dim xmlFileList As SqlParameterDim strContainsClause As SqlParameter'// Create and execute the search against SQL ServerconnStringSQL.Open()commandSQL.CommandType = Data.CommandType.StoredProcedurecommandSQL.Parameters.Add("@xmlFileList", Data.SqlDbType.VarChar, 1000).Value = strXMLcommandSQL.Parameters.Add("@strContainsClause", Data.SqlDbType.VarChar, 1000).Value = strContainsConstructDim sqlReaderSource As SqlDataReader = commandSQL.ExecuteReader()results.DataSource = sqlReaderSourceresults.DataBind()connStringSQL.Close()And the stored procedure is such:DROP PROC dbo.GetAssetList2;GOCREATE PROC dbo.GetAssetList2(@xmlFileList varchar(1000),@strContainsClause varchar(1000))ASBEGINSET NOCOUNT ONDECLARE @intDocHandle intEXEC sp_xml_preparedocument @intDocHandle OUTPUT, @xmlFileListSELECT DISTINCTAssetsMaster.AssetMasterUID,SupportedFiles.AssetPath,FROM AssetsMaster, OPENXML (@intDocHandle, '/NewDataSet/Table',2) WITH (FILENAME varchar(256)) AS x,SupportedFilesWHEREAssetsMaster.AssetFileName = x.FILENAMEAND AssetsMaster.Extension = SupportedFiles.Extension UNIONSELECT DISTINCTAssetsMaster.AssetMasterUID,SupportedFiles.AssetPath,FROM AssetsMaster, OPENXML (@intDocHandle, '/NewDataSet/Table',2) WITH (FILENAME varchar(256)) AS x,SupportedFilesWHEREAssetsMaster.AssetFileName <> x.FILENAMEAND CONTAINS ((Description, Keywords), @strContainsClause)AND AssetsMaster.Extension = SupportedFiles.ExtensionORDER BY AssetsMaster.Downloads DESCEXEC sp_xml_removedocument @intDocHandle ENDGOHow can I access the number of rows returned by this stored procedure?Thanks,James
View 3 Replies
View Related
Feb 16, 2007
How do I tell when there is no more data to read in a SQLDataReader?
For example, I have an open datareader that I pass into a function that MIGHT still have a valid row in it when it returns from the function. How do I tell? I can't do a read() because then that current record will go away. I need to be able to tell if there is a current record without doing another read.
TIA,
View 4 Replies
View Related
Jun 18, 2007
Say I have this SQL query running into an SqlDataReader select TaskName, TaskDescription from tblTasks where TaskID = 5 There are two different ways to get the data out of the reader (maybe more) TaskName.Text = Reader.GetString(0);andTaskName.Text = Reader.GetString(Reader.GetOrdinal("TaskName")); My question is, is there a major difference in terms of efficiency between these two? The second one is definitely more robuts (in a situation where you are calling a stored procedure, and the stored procedure might change, etc) but the first one has fewer operations. Is the increase in robustness of the second one worth the potential performance hit, if any? Thank you, -Madrak
View 2 Replies
View Related
Jul 23, 2007
May I know what is the purpose of having SqlDataReader in Example A? I can see the same output when I tried out both. Should I use Example A or Example B? Currently, I'm using Example B since it is lesser code.Example A Dim objDR As SqlDataReader 'Create Data Reader
LoginConn.Open()
strSQL = "SELECT CountryID, CountryName FROM Country ORDER BY CountryName "
cmd = New SqlCommand(strSQL, LoginConn) objDR = cmd.ExecuteReader() 'Populate the DataReader ddlNationality.DataSource = objDR ddlNationality.DataBind() ddlNationality.SelectedValue = dvUserProfile.Item(0)("Nationality")LoginConn.Close() Example BLoginConn.Open() strSQL = "SELECT CountryID, CountryName FROM Country ORDER BY CountryName " cmd = New SqlCommand(strSQL, LoginConn) ddlNationality.DataSource = cmd.ExecuteReader() 'Populate the DataReader ddlNationality.DataBind() ddlNationality.SelectedValue = dvUserProfile.Item(0)("Nationality")LoginConn.Close()
View 2 Replies
View Related
Dec 31, 2007
Hello every one and happy new year...
i have a problem with SqlDataReader used in asp.net application:
I defined a public object of SqlDataReader and assigned it the resultset of a query,
well, this happened ,lets say in page 1# but whan i want to use this datareader in another page
it keeps telling my that the reader is closed so i can't abstract information from, regarding that i used it's methods such "Read()" and "NextResult()"
but there is no use, what should i do, help me please !!!!!?
View 1 Replies
View Related
Jan 4, 2008
Hello im fairly new to ASP.Net and have a problem with an Intranet Page I am creating. In this part of the page I want to retrive a value from an SQL Table based on criteria and then store it as a variable to be used elsewhere on my page. I have tried using the SQL Data reader to retrive the value but somewhere in my code I am going wrong. Can anyone advise me on this please? See code below
My Visual Studio debugger has point out that there is a problem with my r = cmd.ExecuteReader() line
Oh and the connection to my SQL database is opened further up the page from this code.
Dim strSQL As String = "SELECT top 1 OrderID FROM tblStationeryOrder WHERE ORDERMADEBY = '" & lstUsers.SelectedValue & "' ORDER BY OrderID DESC"Dim cmd As New System.Data.SqlClient.SqlCommand
cmd.CommandText = strSQLDim r As System.Data.SqlClient.SqlDataReader
r = cmd.ExecuteReader()Do While r.Read()
Dim OrderID As Integer = r!OrderID
Exit Do
View 4 Replies
View Related
Apr 25, 2008
I found this tutorial at C# Station called "Reading Data with the SqlDataReader".
In my code behind file I followed what I think the tutorial was telling me to do but I keep getting a syntax error near '='
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near '='. (Line 45: rdr = cmd.ExecuteReader();)
Heres my code:
protected void Page_Load(object sender, EventArgs e) { this.lblDate.Text = DateTime.Today.ToString("d"); string username; username = Convert.ToString(Session["usr"]); SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["csSecurity"].ConnectionString); SqlDataReader rdr = null; SqlCommand cmd = new SqlCommand("SELECT PROG_OFF FROM Logon_Table WHERE usr = " + username, conn); try { conn.Open(); rdr = cmd.ExecuteReader(); }
Any suggestions here would be great. Any links to tutorials you know of would be helpful also.
Thanks
View 2 Replies
View Related
May 18, 2008
good afternoon everybody
this is my code: and their is an error ,,really dunno where and why coz it seems logical to me :)Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
If RadioButton1.Checked = True Then
Dim admin As Stringadmin = "SELECT * from ADMINISTRATOR where UserName='" & TextBox1.Text & "' and UserPassword='" & TextBox2.Text & "'"
Dim sConnect As String = "Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Database.mdf;Integrated Security=True;User Instance=True"Dim cnt As New SqlConnection
cnt.ConnectionString = sConnect
cnt.Open()Dim com As New SqlCommand
com.Connection = cnt
com.CommandText = adminerror!!>>>>Dim rd As SqlDataReader
rd = com.ExecuteReader
If rd.Read ThenSession("admn") = rd("UserName")Session("id") = rd("UserID")
rd.Close()
cnt.Close()Response.Redirect("~/adminpage.aspx")
ElseSession("admn") = 0
Label3.Visible = True
rd.Close()
cnt.Close()
End If
Else
Dim instrct As Stringinstrct = "SELECT * from instructor where name='" & TextBox1.Text & "' and passs='" & TextBox2.Text & "'"
Dim sConnect As String = "Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Database.mdf;Integrated Security=True;User Instance=True"Dim con As New SqlConnection
con.ConnectionString = sConnect
con.Open()Dim com As New SqlCommand
com.Connection = con
com.CommandText = instrctDim r As SqlDataReader
r = com.ExecuteReader
If r.Read ThenSession("inst") = r("inst_name")
r.Close()
con.Close()Response.Redirect("~/instructorpage.aspx?id=" & Session("inst"))
ElseSession("inst") = 0
Label3.Visible = True
r.Close()
con.Close()
End If
End If
End Sub
-----------------
this is the error line:
The data types text and varchar are incompatible in the equal to operator.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: The data types text and varchar are incompatible in the equal to operator....
so,,,any suggestions?
View 2 Replies
View Related
Jun 7, 2008
I have an sp, which has 2 select statements, so iam using a sqldatareader and binding the data to a dropdown.
the first binding is fine, but when i say dataReader.NextResult(), It is null.It says the reader is closed. Can any one tell a work around for this.
thanx in advance,
Anil Kumar.
View 6 Replies
View Related
Jun 10, 2008
see ths code. please
== DAL ==
public static SqlDataReader ExecuteReader(SqlParameter param, string sp) { using (conn = new SqlConnection(connectString)) { SqlDataReader sdr = null; try { conn.Open();
comm = new SqlCommand(); comm.CommandText = sp; comm.CommandType = CommandType.StoredProcedure; comm.Connection = conn; comm.Parameters.Add(param); sdr = comm.ExecuteReader(CommandBehavior.CloseConnection); } catch(SqlException e) { WriteToLog(e.Message); } return sdr; } }
and, call above ExecuteReader() flowing this,
== BL ==
public UserEntity GetUserInformation(string UID, string sp) { UserEntity ue = new UserEntity();
SqlParameter userUID = new SqlParameter("@USR_ID", SqlDbType.VarChar, 20); userUID.Value = UID;
using (SqlDataReader sdr = DbHelper.ExecuteReader(userUID, "SP_GETUSERINFORMATION")) { if (sdr != null) { while (sdr.Read()) { ue.UserId = sdr["lecture_usr_id"].ToString(); ue.UserName = sdr["lecture_usr_realname"].ToString(); ue.Email = sdr["lecture_usr_email"].ToString(); ue.Phone = sdr["lecture_usr_phone"].ToString(); ue.Lastlogin = (DateTime)sdr["lecture_usr_lastlogin"]; ue.SignDate = (DateTime)sdr["lecture_usr_signdate"]; } } return ue; } }
error message is,
Invalid attempt to call Read when reader is closed.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.InvalidOperationException: Invalid attempt to call Read when reader is closed.Source Error:
Line 77: if (sdr != null)
Line 78: {
Line 79: while (sdr.Read())
Line 80: {
Line 81: ue.UserId = sdr["lecture_usr_id"].ToString();Source File: C:Users
aphyrDocumentsVisual Studio 2008ProjectslectureBusinessLogicUserManager.cs Line: 77
I can't understand!!! I try to debuging!!! but, I don't know.
please help me!
View 2 Replies
View Related
Jun 14, 2008
suppose I have a data reader which is returned by excuting a command "SELECT [xxx], [yyy], FROM [zzz]" , then I reads the data as normal. while I am reading , there is another thread that excute an insert command to that table , does this insertion effect the order of data that I am reading?
View 5 Replies
View Related
Apr 17, 2005
I have an SP that returns a result set that contains a tinyint. My problem is that, when I try and access this value using GetInt16 (or 32), I get an error saying that "Specified cast is not valid". TinyInt is 1 byte, or 8 bits. GetInt16 'Gets the value of the specified column as a 16-bit unsigned integer'. I am assuming that this is the root cause of my problem. But, there doesn't seem to be a GetInt8 ?! Any ideas?
Thanks, Martin
View 1 Replies
View Related
Jan 29, 2006
hi..i am kind of new to asp.net n having trouble with the SqlException error message.. n this code works as 1st page pass the id to second page and the second page took the id to execute the query..i dun know the wer the error occurs..can give a help..Thanks.
private void Page_Load(object sender, System.EventArgs e)
{
SqlConnection connection = null;
SqlCommand command = null;
string sqlConnection = null;
string sql = null;
string ab = null;
sqlConnection = ConfigurationSettings.AppSettings["MSQLConnectionString"];
connection = new SqlConnection(sqlConnection);
if (!Page.IsPostBack)
{
try
{
if (Request.QueryString["categoryID"] == null)
{
}
else
{
ab= Request.QueryString["categoryID"].ToString(); //getting the id from page that pass this values
sql = "Select groupname, categoryid, description from groups where groups.categoryid=?"; // can this query execute?
command = new SqlCommand(sql, connection);
connection.Open();
command.Parameters.Add(new SqlParameter("categoryid", ab));
reader = command.ExecuteReader(); // error on here "SqlException"
while (reader.Read())
{
group.InnerText = reader["groupname"].ToString();
desc.InnerText = reader["description"].ToString();
}
}
}
finally
{
if (reader != null)
{
reader.Close();
}
if (connection != null)
{
connection.Close();
}
}
}
View 1 Replies
View Related
Feb 9, 2007
actuallu the earlier obviously worked but now i am fetching values from 2 colums and in collection we can pass 2 strings i did that.but then how to increment the collection c.will just post the code .
query fetching 2 records
can add only 1
NameValueCollection c = new NameValueCollection();
cmd2.CommandText="select Pname ,HoursWorked from TimeSheet1 ts , ProjResource pr,ProjectDetails pd where ts.rid = pr.rid and ts.pcode=pr.pcode and pd.Pcode = ts.Pcode and pr.Rid = '" + Ridtxt.Text + " '";
cmd2 .Connection = con1;
con1.Open();
SqlDataReader dr = cmd2.ExecuteReader();
while (dr.Read())
{
c.Add(dr["pname"].ToString(), dr["hoursworked"].ToString());
}
as earliar
supose c# 12
asp 11
only c# getting added
even though loop is while dr.read()
{
}
actually how to increment key as in c both strings are occupied
View 3 Replies
View Related
Jul 3, 2004
hi evryone
I have a question!
in my ASP.NET page I use a SqlDataAdapter object for working on my database
first I declare it as a global vaiable(SqlDataAdapter objDataAdapter;)
then I construct it in my Page_Load procedure like this
(objDataAdapter=new SqlDataAdapter(strSql,objConnention))
then when I want to use it in my other procedures an error tells me that your object does not exist
for solving this error I should construct my objDataAdapter in evry procedure using it.
but I think such variables should not be erased from Heap automatically(with Garbage Collection)
because it has a reference to Stack. I mean the global variable...
any opinion ?
Thanks
View 3 Replies
View Related
Feb 12, 2005
I am collecting companyID's form a data grid, I want to pass the selected values to a sproc via a variable. Any idea on the syntax?
this works using a query string within my code
WHERE (dbo.Promotions.ExpirationDate > GETDATE()) AND (dbo.Promotions.CompanyID IN (" + selectedCompanies + "))
this doesn't within my sproc
WHERE (dbo.Promotions_ByLink.ExpirationDate > GETDATE()) AND (dbo.Promotions_ByLink.CompanyID IN (@SelectedCompanies))
I also tried
WHERE (dbo.Promotions_ByLink.ExpirationDate > GETDATE()) AND (dbo.Promotions_ByLink.CompanyID IN (SELECT @SelectedCompanies))
and
WHERE (dbo.Promotions_ByLink.ExpirationDate > GETDATE()) AND (dbo.Promotions_ByLink.CompanyID IN (' + @SelectedCompanies + '))
Thanks
View 4 Replies
View Related
Oct 26, 2000
hi , what is the script to change the unicode collection in sql server 7.0
Ahemd
View 1 Replies
View Related
Feb 6, 2006
I'm having trouble obtaining errors raised in a stored
procedure via the ADO Errors collection after the second
FETCH NEXT statement from within that stored procedure.
Consider the following table created in a SQL Server
database:
CREATE TABLE TestTable
(
TestInt int
)
go
INSERT TestTable(TestInt) values(1)
INSERT TestTable(TestInt) values(2)
INSERT TestTable(TestInt) values(3)
This is a very simple table with one column, and three
rows containing the values 1, 2 and 3.
Consider this stored procedure:
CREATE PROCEDURE TestStoredProc
as
BEGIN
set rowcount 0
Set NoCount ON
declare @TestInt int
declare @ErrMsg char(7)
declare TestCursor cursor forward_only for
select * from TestTable
open TestCursor
Fetch next from TestCursor into @TestInt
While @@fetch_status<>-1
Begin
select @ErrMsg = 'Error ' + convert(char, @testint)
raiserror(@ErrMsg, 16, 1)
raiserror(@ErrMsg, 16, 1)
Fetch next from TestCursor into @TestInt
end
Close TestCursor
DeAllocate TestCursor
return
END
This stored procedure simply defines a cursor on all rows
in TestTable. For each row fetched from the cursor, the
error message 'Error n' is raised twice, where n is the
integer that had just been fetched from the cursor.
Finally, consider this VB code using ADO to execute the
above stored procedure. After the stored procedure is
executed, the code loops through the errors collection,
and creates a message box for each error in the collection:
Private Sub Form_Load()
Dim cn As Connection
Dim cm As Command
Dim oErr As Error
On Error Resume Next
Set cn = CreateObject("ADODB.Connection")
cn.Open "Data Source=<Some SQL Server>; Initial
Catalog=<Some Database Name>; Provider=SQLOLEDB; Persist
Security Info=False; Integrated Security=SSPI"
Set cm = CreateObject("ADODB.Command")
Set cm.ActiveConnection = cn
cm.CommandType = adCmdStoredProc
cm.CommandText = "TestStoredProc"
cm.Execute
For Each oErr In cn.Errors
MsgBox oErr.Description
Next
End
End Sub
When this code is executed, only two message boxes appear
with the message "Error 1".
Any help on this matter would be greatly appreciated :)
View 3 Replies
View Related
Nov 13, 2007
If I have N different xml document formats and I want to store those xml data files as typed xml in one table (one column), can I do it using xml schema collection (by adding schema file to schema collection for each document type and assigning xml schema collection to this column)?
Is this possible using xml schema collection? Or did I miss something about xml schema collection usage scenario?
Thank you!
View 4 Replies
View Related
Mar 7, 2008
I need to feed head office sql server with the data from regional servers. Servers are spread through all continents
Data input done locally on Head office server as well and plus need to ship data from other servers.
So clarify this - Head office server is not standby one. Mirroring is out of the picture, I think..
Initially, I thought ship a log every 15 min and restore on Head office server but is this going to create an issue for the local data processing?
Any bright ideas welcome!
View 2 Replies
View Related
Apr 7, 2008
Is it possible to address the whole set of user stored procedures in one command?
For example, I want to GRANT EXEC on all sprocs in my db; can I do it in one command or do I have to do the GRANT for each sproc individually?
View 19 Replies
View Related
Mar 27, 2008
Precedence Constraint Editor Values:
Expression Operation: Expression and Constraint
Value: Failure
Expression: @GotRecs == 1
Radio button: Logical AND. All contraints must evaluate to True
Stored Procedure has an IF (that contains a SELECT) statement that checks for records if no records are returned processing stops. I SET the parameter after the BEGIN that follows the IF statement.
The step that contains the above code is step #3. I placed the constraint between step #3 and step #4 (Data Flow task) just Exports to a Flat File.
When I clicked the TEST button in the Precedence Constraint Editor I got the following msg:
Error at Constraint 3: The variable €œGotRecs€? was not found in the variables collection. The variable might not exist in the correct scope.
View 12 Replies
View Related
