Best Way To Retrieve One Row For Use In Code

Mar 14, 2006

i have a SQL backend and some of the data in my tables is more or less configuration data and some times i only want to pull one row of data which i can do just fine with a SQL query but whats the best method to pull it. 

assume i have a query like this.

Select A, B, C from Table1 where ID=xyz

whats the easiest way to get A, B and C in to string variables?

I know i can use the sqldatasource control but i just feel there is too much overhead there.

whats your suggestions?

Thanks

Justin

View 1 Replies


ADVERTISEMENT

Retrieve Code Of A View?

Dec 11, 2014

I can retrieve the sql code of a view as follows:

exec sp_helptext @viewName

The result will be in several lines which shows the lines of the sqlcode...

Is it possible to return the sql but not the line numbers?

View 4 Replies View Related

Retrieve Images From SQL Db: Code Problem.

Aug 1, 2006

Hi,
I want to get an image from a sql server database and display it with an asp:image control. I use C# in MS Visual Studio .Net 2005 and Sql server 2005.
All I've done is:



// and Page Display_image.aspx.cs
protected void Page_Load(object sender, EventArgs e)
{
try
{


SqlConnection Con = new SqlConnection(
"server=localhost;" +
"database=;" +
"uid=;" +
"password=;");

System.String SqlCmd = "SELECT img_data FROM Image WHERE business_id = 2";

System.Data.SqlClient.SqlCommand SqlCmdObj = new System.Data.SqlClient.SqlCommand(SqlCmd, Con);

Con.Open();

System.Data.SqlClient.SqlDataReader SqlReader = SqlCmdObj.ExecuteReader(CommandBehavior.CloseConnection);

SqlReader.Read();

System.Web.HttpContext.Current.Response.ContentType = "image/jpeg";

// I write this:
System.Web.HttpContext.Current.Response.BinaryWrite((byte[])SqlReader["img_data"]);

// Or this:
//System.Drawing.Image _image = System.Drawing.Image.FromStream(new System.IO.MemoryStream((byte[])SqlReader["img_data"]));

//System.Drawing.Image _newimage = _image.GetThumbnailImage(100, 100, null, new System.IntPtr());

//_newimage.Save(System.Web.HttpContext.Current.Response.OutputStream, System.Drawing.Imaging.ImageFormat.Jpeg);


Con.Close();

}
catch (System.Exception Ex)
{

System.Web.HttpContext.Current.Trace.Write(Ex.Message.ToString());
}
}

Both work the same way. I mean the image is displayed well but when I view code of the web page I see this:

....
GIF89aåo÷óå݉97)HS¢Å’xL£0¬B´ç¬D(áâK‹†ô²–I€8`È®û l1ãÂ?#KžL(ˆ*X±â€°Î"«M豆Ÿ
....

TOO MUCH characters before the html tag. And any code of the master page used for this page does not work as well!

Can anyone help me with this? I've tried for 2 days but I still fail.

Thanks.

View 2 Replies View Related

Cannot Retrieve Code Page Info...

Nov 20, 2006

Hello again!

I got a problem using an OLE-DB Source in my data flow task. It is an Informix-Server using the newest OLE DB-Driver provided by IBM. However, when I set up the OLE DB-Source with connection manager and SQl-Query, I get the following warning message:

Warning at {2B334CA3-A792-4BC2-93AA-12FADCF1E696} [OLE DB Source [45]]: Cannot retrieve the column code page info from the OLE DB provider.  If the component supports the "DefaultCodePage" property, the code page from that property will be used.  Change the value of the property if the current string code page values are incorrect.  If the component does not support the property, the code page from the component's locale ID will be used.


Later when I try to execute the package, the destination adapter gets errors like

Error 3 Validation error. Data Flow Task: OLE DB Destination [2985]: Columns "antrag" and "Antrag" cannot convert between unicode and non-unicode string data types. Informix_OLEDB.dtsx 0 0 
 

I guess that SQL Server cannot get the data types from the Source.

Has anyone an idea? Thanks in advance!

View 11 Replies View Related

SQL Sp Or View To Retrieve Originial Sql Code

Nov 5, 2007

I am interested in retrieving the original SQL code of various stored procedures & views that I have created & pass them into a field within a table.

I have used one before but cannot remember whether I used a stored proc or system view to retrieve this information, can anyone be of assistance???

View 3 Replies View Related

Unable To Retrieve The @@Error Code In Stored Procedure

Aug 25, 2006

Hi All,

In my application on click of Delete button, actually I am not deleting the record. I am just updating the flag. But before updating the record I just want to know the dependency of the record ie. if the record I am deleting(internally updating) exists any where in the dependent tables, it should warn the user with message that it is available in the child table, so it can not be deleted. I want to check the dependency dynamically. For that I have written the following procedure. But it is updating in both cases.

CREATE proc SProc_DeleteRecord
@TableName varchar(50),
@DeleteCondition nVarchar(200)
As
Begin
Begin Transaction
Declare @DelString nvarchar(4000)
set @DelString = 'Delete from ' + @TableName + ' where ' + @DeleteCondition
execute sp_executeSql @DelString
RollBack Transaction --because i donot want to delete the record in any case
If @@Error <> 0
Begin
select '1'
End
Else
Begin
Declare @SQLString nvarchar(4000)
set @SQLString = 'Update ' + @TableName + ' set DeletedFlag=1 where ' + @DeleteCondition
execute sp_executeSql @SQLString
select '0'
End

End

Thanks & Regards

Bijay

View 4 Replies View Related

Way To Retrieve Actual Size Of Ntext Column In Native Code?

Aug 3, 2007

Howdy folks! (I feel im creating too many topics...but all my questions are unrelated :-/)

Dev environment: SSCE, Native c++ OLE code, Win CE 5

I have a routine that calculates the size of a set of columns in order to create a byte buffer for retrieving data from an IRowset and inserting it into a custom class. This method works great for all data types except ntext. When I look at the column information from an ntext field, the DBCOLUMNINFO::ulColumnSize member holds 536,870,911 (the max length of an ntext type). Obviously I dont want to allocate 1GB of memory for each ntext column. Is there anyway (natively) to determine the actual number of characters in a field?

View 12 Replies View Related

How To Create Apps That Write Code To Retrieve Data With Foreign Keys?

Apr 6, 2007

Hi GuysOff late, I've grown with programming that requires more than a number of tables that has foreign keys with other tables' primary keys. It takes a really cumbersome coding to retrieve the code from another table with the other table having foreign keys. My question is, how do we program VS 2005 such that it does all the retrieval of the data from the database instead of us writing the code all by ourself?Is it really good database technique to bend the normalcy rules and have one to two columns having redundant data?Can anyone tell me how to write code that retrieves the foreign key data when the data from the other table is called?Thanks

View 2 Replies View Related

In Code Behind, What Is Proper Select Statement Syntax To Retrieve The @BName Field From A Table?

Apr 8, 2006

In Code Behind, What is proper select statement syntax to retrieve the @BName field from a table?Using Visual Studio 2003SQL Server DB
I created the following parameter:Dim strName As String        Dim parameterBName As SqlParameter = New SqlParameter("@BName", SqlDbType.VarChar, 50)        parameterBName.Value = strName        myCommand.Parameters.Add(parameterBName)
I tried the following but get error:Dim strSql As String = "select @BName from Borrower where BName= DOROTHY V FOWLER "
error is:Line 1: Incorrect syntax near 'V'. 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 'V'.
Source Error:
Line 59: Line 60: Line 61:         myCommand.ExecuteNonQuery()   'Execute the query

View 2 Replies View Related

Java Code To Retrieve Data From Stored Procedure Which Returns Cursor Varying Output?

May 11, 2015

java code to retrieve the data returned by SQL server stored procedure which is of CURSOR VARYING OUTPUT type and display the details on console.

View 3 Replies View Related

How To Write .net Code To Place XML Messages On Queues And Retrieve XML Messages From Queues.

Apr 2, 2008

Hello, please help!!

I have spent days searching the web and forums for an answer to this simple question and cannot find an example.

I have built a service broker application on sql server 2005. The application puts some xml on an incoming queue which is basically a few parameters to be used in a query. This queue will then call a stored proc which does some business logic and puts the resulting results in another queue also in xml.

I have written a test harness in SQL to put messages on the inbound queue and then some sql to retrieve the returned code from the outbound queue.

What I want to do is be able to convert the SQL which does this into .net code to be used by an application. i.e. write in .net some code to put xml on a queue and then write some .net code to retrieve xml from another queue.

I wouldn't have thought this would be a difficult thing to do and would have been done hundreds of times, but unable to find anything to simply send and retrieve XML to service broker queues....

thanks for your help.. its really needed. I found some links, but they are really vague and often doing select statments in service broker or something like this. I don't want to call any sql, just send and recieve XML on the queues.

any example code that does this, would be really helpfull

kind regards,
David Weeden
Database Developer

View 2 Replies View Related

Help With Converting Code: VB Code In SQL Server 2000-&&>Visual Studio BI 2005

Jul 27, 2006

Hi all--I'm trying to convert a function which I inherited from a SQL Server 2000 DTS package to something usable in an SSIS package in SQL Server 2005. Given the original code here:
Function Main()
on error resume next
dim cn, i, rs, sSQL
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=sqloledb;Server=<server_name>;Database=<db_name>;User ID=<sysadmin_user>;Password=<password>"
set rs = CreateObject("ADODB.Recordset")
set rs = DTSGlobalVariables("SQLstring").value

for i = 1 to rs.RecordCount
sSQL = rs.Fields(0).value
cn.Execute sSQL, , 128 'adExecuteNoRecords option for faster execution
rs.MoveNext
Next

Main = DTSTaskExecResult_Success

End Function

This code was originally programmed in the SQL Server ActiveX Task type in a DTS package designed to take an open-ended number of SQL statements generated by another task as input, then execute each SQL statement sequentially. Upon this code's success, move on to the next step. (Of course, there was no additional documentation with this code. :-)

Based on other postings, I attempted to push this code into a Visual Studio BI 2005 Script Task with the following change:

public Sub Main()

...

Dts.TaskResult = Dts.Results.Success

End Class

I get the following error when I attempt to compile this:

Error 30209: Option Strict On requires all variable declarations to have an 'As' clause.

I am new to Visual Basic, so I'm on a learning curve here. From what I know of this script:
- The variables here violate the new Option Strict On requirement in VS 2005 to declare what type of object your variable is supposed to use.

- I need to explicitly declare each object, unless I turn off the Option Strict On (which didn't seem recommended, based on what I read).

Given this statement:

dim cn, i, rs, sSQL

I'm looking at "i" as type Integer; rs and sSQL are open-ended arrays, but can't quite figure out how to read the code here:

Set cn = CreateObject("ADODB.Connection")

cn.Open "Provider=sqloledb;Server=<server_name>;Database=<db_name>;User ID=<sysadmin_user>;Password=<password>"

set rs = CreateObject("ADODB.Recordset")

This code seems to create an instance of a COM component, then pass provider information and create the recordset being passed in by the previous task, but am not sure whether this syntax is correct for VS 2005 or what data type declaration to make here. Any ideas/help on how to rewrite this code would be greatly appreciated!

View 7 Replies View Related

How To Show Description In Report Instead Of Code (Desc For Code Is In Master Table)

Mar 28, 2007

Dear Friends,



I am having 2 Tables.

Table 1: AddressBook
Fields --> User Name, Address, CountryCode



Table 2: Country
Fields --> Country Code, Country Name


Step 1 : I have created a Cube with these two tables using SSAS.



Step 2 : I have created a report in SSRS showing Address list.

The Column in the report are User Name, Address, Country Name



But I have no idea, how to convert this Country Code to Country name.

I am generating the report using the Layout tab. ( Data | Layout | Preview ) Report1.rdl [Design]



Anyone help me to solve this issue. Because, in our project most of the transaction tables have Code and Code description in master table. I need to convert all code into corresponding description in all my reports.




Thanks in advance.





Regards
Ramakrishnan
Singapore
28 March 2007

View 4 Replies View Related

Many Lines Of Code In Stored Procedure && Code Behind

Feb 24, 2008

Hello,
I'm using ASP.Net to update a table which include a lot of fields may be around 30 fields, I used stored procedure to update these fields. Unfortunatily I had to use a FormView to handle some TextBoxes and RadioButtonLists which are about 30 web controls.
I 've built and tested my stored procedure, and it worked successfully thru the SQL Builder.The problem I faced that I have to define the variable in the stored procedure and define it again the code behind againALTER PROCEDURE dbo.UpdateItems
(
@eName nvarchar, @ePRN nvarchar, @cID nvarchar, @eCC nvarchar,@sDate nvarchar,@eLOC nvarchar, @eTEL nvarchar, @ePhone nvarchar,
@eMobile nvarchar, @q1 bit, @inMDDmn nvarchar, @inMDDyr nvarchar, @inMDDRetIns nvarchar,
@outMDDmn nvarchar, @outMDDyr nvarchar, @outMDDRetIns nvarchar, @insNo nvarchar,@q2 bit, @qper2 nvarchar, @qplc2 nvarchar, @q3 bit, @qper3 nvarchar, @qplc3 nvarchar,
@q4 bit, @qper4 nvarchar, @pic1 nvarchar, @pic2 nvarchar, @pic3 nvarchar, @esigdt nvarchar, @CCHName nvarchar, @CCHTitle nvarchar, @CCHsigdt nvarchar, @username nvarchar,
@levent nvarchar, @eventdate nvarchar, @eventtime nvarchar
)
AS
UPDATE iTrnsSET eName = @eName, cID = @cID, eCC = @eCC, sDate = @sDate, eLOC = @eLOC, eTel = @eTEL, ePhone = @ePhone, eMobile = @eMobile,
q1 = @q1, inMDDmn = @inMDDmn, inMDDyr = @inMDDyr, inMDDRetIns = @inMDDRetIns, outMDDmn = @outMDDmn,
outMDDyr = @outMDDyr, outMDDRetIns = @outMDDRetIns, insNo = @insNo, q2 = @q2, qper2 = @qper2, qplc2 = @qplc2, q3 = @q3, qper3 = @qper3,
qplc3 = @qplc3, q4 = @q4, qper4 = @qper4, pic1 = @pic1, pic2 = @pic2, pic3 = @pic3, esigdt = @esigdt, CCHName = @CCHName,
CCHTitle = @CCHTitle, CCHsigdt = @CCHsigdt, username = @username, levent = @levent, eventdate = @eventdate, eventtime = @eventtime
WHERE (ePRN = @ePRN)
and the code behind which i have to write will be something like thiscmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@eName", ((TextBox)FormView1.FindControl("TextBox1")).Text);cmd.Parameters.AddWithValue("@ePRN", ((TextBox)FormView1.FindControl("TextBox2")).Text);
cmd.Parameters.AddWithValue("@cID", ((TextBox)FormView1.FindControl("TextBox3")).Text);cmd.Parameters.AddWithValue("@eCC", ((TextBox)FormView1.FindControl("TextBox4")).Text);
((TextBox)FormView1.FindControl("TextBox7")).Text = ((TextBox)FormView1.FindControl("TextBox7")).Text + ((TextBox)FormView1.FindControl("TextBox6")).Text + ((TextBox)FormView1.FindControl("TextBox5")).Text;cmd.Parameters.AddWithValue("@sDate", ((TextBox)FormView1.FindControl("TextBox7")).Text);
cmd.Parameters.AddWithValue("@eLOC", ((TextBox)FormView1.FindControl("TextBox8")).Text);cmd.Parameters.AddWithValue("@eTel", ((TextBox)FormView1.FindControl("TextBox9")).Text);
cmd.Parameters.AddWithValue("@ePhone", ((TextBox)FormView1.FindControl("TextBox10")).Text);
cmd.Parameters.AddWithValue("@eMobile", ((TextBox)FormView1.FindControl("TextBox11")).Text);
So is there any way to do it better than this way ??
Thank you

View 2 Replies View Related

Custom Code (Embedded Code) Question

Oct 16, 2007



Hi all,

Could someone tell me if custom code function can capture the event caused by a user? For example, onclick event on the rendered report?

Also, can custom code function alter the parameters of the report, or refresh the report?

Thanks.

View 2 Replies View Related

Putting SqlDataSource Code In Code-behind

Jan 25, 2007

Hi,I need some help here. I have a SELECT sql statement that will query the table. How do I get the return value from the sql statement to be assigned to a label. Any article talk about this? Thanks  geniuses.  

View 2 Replies View Related

&&<Code&&>-8462&&</Code&&>

Apr 19, 2006

Hi:

My service broker is working with 2 different instances in local server.But could not able to get working on 2 different servers because of Conversation ID cannot be associated with an active conversation error which I have posted.

After I receive the message successfully...in the end I get this message sent...

<Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error">

<Code>-8462</Code>

<Description>The remote conversation endpoint is either in a state where no more messages can be exchanged, or it has been dropped.</Description>

</Error>

Why am i gettting this error after the conversation.

Thanks,

Pramod

View 7 Replies View Related

SSIS Error Code DTS_E_OLEDBERROR. An OLE DB Error Has Occurred. Error Code: 0x8000FFFF.

Jan 28, 2008

Hi All,

Recently in an SSIS package I am getting the following error for a particular Data flow task.





Error: 2008-01-25 12:01:48.58

Code: 0xC0202009

Source: Import Datasynapse Data User Events Source [3017]

Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x8000FFFF.

End Error

Error: 2008-01-25 12:01:48.73

Code: 0xC004701A

Source: Import Datasynapse Data DTS.Pipeline

Description: component "User Events Source" (3017) failed the pre-execute phase and returned error code 0xC0202009.

End Error

Our guess is when the data size of User Events table is more it throws this error. If we try to transfer small subset of data it succeeds. What could be reason for this error?

Since this is very urgent, immediate response would be very much appreciated.

Thanks & Regards,
Prakash Srinivasan

View 4 Replies View Related

Retrieve AM Or PM

Aug 10, 2006

HI Chaps

very simple question this time

Is there any method availabe to retrieve AM or PM value from date, IN TSQL

hoping will get answer shortly

regards,

Anas

View 7 Replies View Related

RETRIEVE VALUE AFTER ADD FROM SQLDATASOURCE

Jun 29, 2007

I am using an SQLDataSource to add a product, this works fine, but I would like to know what syntax is used to retrieve the product ID in this case which is return by the SPROC
 
Thanks
 
Steve

View 1 Replies View Related

Retrieve Data

Jan 18, 2008

I need to retrieve points column from my database for the specific user that is signed on and sum all of them. How can I do it using the Gridview and also code in vb for a label? Thanks.  

View 4 Replies View Related

Retrieve Bit Type Value

May 7, 2004

I am trying to populate some controls on a web page with values retrieved from a sql server database recordset. The text type controls work fine. However I have a Check Box on my form. I get a runtime error when I try to write into it. So tried wrting the value into a text control and was suprised to see the value retrieved was "S00817". Heres the relevant line of code

Message.Innerhtml=MyDataset.tables(0).Rows(0)(12)

I would expect this to come up with a 1 or a 0. I get S00817 if the database record holds a 1 or a 0. So what is going on here?

View 2 Replies View Related

Retrieve A Particular Row From A Result Set

Aug 21, 2004

Hi all,

Is it possible to retrieve a particular row from a result set? For eg if my query returns 5 rows and i want to just retrieve the 3rd row from the result....is it possible? If yes...can someone tell me the syntax for it....would appreciate the gr8 help...

Thanks,
SQL Novice

View 1 Replies View Related

How Can I Retrieve Data?

Nov 19, 2005

Here is my sql procedure:
ALTER PROCEDURE dbo.SoftWareShow /* ( @parameter1 int = 5, @parameter2 datatype OUTPUT ) */ @SoftID uniqueidentifierAS SELECT [SoftID], [SoftName], [SoftJoinDate], [SoftSize], [SoftMode], [SoftRoof], [SoftHome], [SoftDemo], [SoftFirstClassID], [SoftSecondClassID], [SoftDesc], [SoftReadCount], [SoftDownCount],ltrim(rtrim([SoftUrlOne])) SoftUrlOne, ltrim(rtrim([SoftUrlTwo])) SoftUrlTwo, ltrim(rtrim([SoftUrlThree])) SoftUrlThree, ltrim(rtrim([SoftUrlFour])) SoftUrlFour FROM [SoftWare] WHERE ([SoftID] = @SoftID) RETURNwhere I retrieve data using sqldatasource, an error appear. how can do ?

View 1 Replies View Related

How To I Retrieve The First Value From A Table In T-SQL?

May 21, 2006

I want to do something similar to ExecuteScalar in ADO.net but instead in T-SQL.Basically I want to do a query that will return the first value in the table queried and put it into a variable.  How do I do this?Thanks in advance.

View 3 Replies View Related

How Can I Retrieve Nth Record Only?

Jul 6, 2001

Could you help me out?

I am interested in retrieving certain record among outputs.

For example, if I use the following sql,

select * from info order by name asc
====================================

then I can retrieve 25 rows from info table.

What I want to do is that I want to retrieve 15th record only among possible 25 records.

How can I solve this problem?

Thanks in advance.

View 2 Replies View Related

Retrieve Even If No Hits?

Apr 29, 2005

Hello!

is it possible to have the count(*) to display 0 when there is no matching hits for that n_id?

my query looks like this but only displays the n_id and it's respective count(*) when count(*) is more than 0...

select n_id, count(*) from tblTable
where nSomething > nSomethingElse AND nSomething IS NOT NULL
group by n_id

any idaes?

View 1 Replies View Related

Retrieve Second Max Value In A Sql Table

Feb 25, 2004

Hi all,
I want to retrieve the second maximum value of a column data present in SQL table.Please help....

A
----
10
25
23
15


here I want 23 as the result.

View 5 Replies View Related

Retrieve Insertiondate

Nov 23, 2004

Hi folks,

Does anyone know how to retrieve the date when data has been inserted

thnx in advance

View 4 Replies View Related

Retrieve Only 1 Row For Each ID (was Help On SQL! Urgent..)

Feb 3, 2005

i have a table called tblpictures which look something like this..

filename|ID
----------
1 |p1
2 |p1
3 |p2
4 |p2
5 |p3

is there a way to retrieve only 1 row for each ID? how will the select statement looks like?? please help me..

View 3 Replies View Related

Retrieve Using Bcp-urgent

Feb 25, 2005

hi gurus,
how to get " using bcp
e.g
select '"'+name+'"' from sysobjects
results
"name"
"name1"

I want to get the same using bcp ,so that i can populate into a file
something like

set @cmd='BCP "select '"' +name +'"' ,uid from sysobjects" QUERYOUT "' + 'vrs.txt' + '" -w -T -S -t , '
-- Executing the BCP Command
EXEC MASTER..XP_CMDSHELL @Cmd

View 3 Replies View Related

How To Retrieve One Record

May 23, 2006

I have a question, one user made mistake that she delete one record from a database. How can i retrieve this only one record. I just know how to restore the database.

Thanks.

View 3 Replies View Related

How To Retrieve Timestamp

Apr 28, 2004

Can any one please tell me how to retrieve values from timestamp column.
I am getting 1900-01-01 00:02:09.457. It is not storing current timestamp when record is created or modified.

Is there anything I need to set it up.

thanks

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved