assigning each record to one string

hello,

i would like to loop through a record set and assign each value to the
same string, (example i would like to return all of the first name in
the authors table = Authors_total.)

should i use a cursor or just a loop to do this? I have had some
trouble with the syntax in a cursor.

nicholas.gadaczDear Nicholas,

I hope following will be help full for you.
---------------
Declare @.varstr as varchar(4000) -- declreation of
set @.varstr = ''; --initializing you know the fact Null + somhting =
Null
select @.varstr = @.varstr+','+isnull(ProductName,',') from Product;
Select @.varstr;
---------------

Best of Luck :) :) :)

Saghir Taj
MCDBA
www.dbnest.com: Home of DB Professionals.

ngadacz@.ftresearch.com wrote:
> hello,
> i would like to loop through a record set and assign each value to
the
> same string, (example i would like to return all of the first name in
> the authors table = Authors_total.)
> should i use a cursor or just a loop to do this? I have had some
> trouble with the syntax in a cursor.
> nicholas.gadacz|||Why not do that client-side? SQL isn't the best place for this kind of
presentational functionality.

--
David Portas
SQL Server MVP
--|||I am still not sure how i would loop through all of the records. if a
use a cursor i get an error variable assignment is not allowed in a
cursor declaration.

The reason why I don't put this functionality is the client side is
that I have multiple client sides: asp php and soon .aspx (.net) with
changes I want to have the code centralized.

nicholas.gadacz|||(ngadacz@.ftresearch.com) writes:
> I am still not sure how i would loop through all of the records. if a
> use a cursor i get an error variable assignment is not allowed in a
> cursor declaration.

DELARE @.str varchar(8000), @.col varchar(30)

DECLARE cur INSENSTIVE CURSOR FOR
SELECT col FROM tbl ORDER BY col
OPEN cur
WHILE 1 = 1
BEGIN
FETCH cur INTO @.col
IF @.@.fetch_status <> 0
BREAK

SELECT @.str = CASE WHEN @.str IS NULL
THEN @.col
ELSE @.str + ',' + @.col
EMD
END
DEALLOCATE cur

This is one of the few things where you must use a cursor. Another poster
showed an example with a SELECT statement. However, that is not guaranteed
to work.

> The reason why I don't put this functionality is the client side is
> that I have multiple client sides: asp php and soon .aspx (.net) with
> changes I want to have the code centralized.

Beware that the above solution has a hard limit of the output string of
8000 characters.

In SQL2005 there will actually be a way to do this in a single statement,
by some fairly funny usage of the new XML stuff.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Assigning data from an SQL query to a variable

Hello all,

for a project I am trying to implement PayPal processing for orders

public void CheckOut(Object s, EventArgs e)
{

String cartBusiness ="0413086@.chester.ac.uk";
String cartProduct;
int cartQuantity = 1;
Decimal cartCost;
int itemNumber = 1;

SqlConnection objConn3;
SqlCommand objCmd3;
SqlDataReader objRdr3;

objConn3 =new SqlConnection(System.Web.Configuration.WebConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
objCmd3 =new SqlCommand("SELECT * FROM catalogue WHERE ID=" + Request.QueryString["id"], objConn3);
objConn3.Open();
objRdr3 = objCmd3.ExecuteReader();
cartProduct ="Cheese";
cartCost = 1;
objRdr3.Close();
objConn3.Close();

cartBusiness = Server.UrlEncode(cartBusiness);

String strPayPal ="https://www.paypal.com/cgi-bin/webscr?cmd=_cart&upload=1&business=" + cartBusiness;

strPayPal += "&item_name_" + itemNumber + "=" + cartProduct;
strPayPal += "&item_number_" + itemNumber + "=" + cartQuantity;
strPayPal += "&amount_" + itemNumber + "=" + Decimal.Round(cartCost);

Response.Redirect(strPayPal);

Here is my current code. I have manually selected cartProduct = "Cheese" and cartCost = 1, however I would like these variables to be site by data from the query.

So I want cartProduct = Title and cartCost = Price from the SQL query.

How do I do this?

Thanks

DO NOT use string concatenation like you have now. Use Parameterized Queries.

you close the SQL connection not the reader first. Then loop through the reader, get the values and assign the variables. I would definetely recommend using Try/Catch block to catch any errors.

The following code is for illustration only.

objConn3 =new SqlConnection(System.Web.Configuration.WebConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
objCmd3 =new SqlCommand("SELECT * FROM catalogue WHERE ID=" + Request.QueryString["id"], objConn3);

Try
If objConn3.State = 0 Then objConn3.Open()

objRdr3 = objCmd3.ExecuteReader();

If DataReader.HasRows Then
Do While objRdr3 .Read()
cartProduct = objRdr3 .Item("cartProduct"))
cartCost = objRdr3 .Item("cartCost"))
Loop

End If
Catch exc As Exception
Response.Write(exc)
Finally

objRdr3.close()
If objConn3.State = ConnectionState.Open Then
objConn3.Close()
End If
End Try

Assigning a particular flat file record to a string variable

Hey all!

Okay, can I assign line x of a flat file to a variable, parse that line, do my data transformations, and then move on to line x+1?

Any thoughts?

In other words, I'm looking at using a for loop to cycle through a flat file. I have the for loop set up, and the counter's iterating correctly. How do I point at a particular line in a flat file?

Thanks for any suggestions!

Jim Work
You could do this in the data flow using a script component to keep track of which row you are on....|||Phil, that sounds like a great plan. I am very, very, very new at this, though, and I could use a little more detail?

Going through a flat file line-by-line seems like it would be a very straightforward thing to me...
|||The data flow on its own operates on a row by row basis. That's what it's designed to do, and very fast, by the way.|||Oh, you're kidding me!

Thanks!!

assign new value of ReadWrite column of string type in script component?

I am new user on VB ( I wish ssis support c# script)

I have made a input string type column ( strName ) in script componen as ReadWrite.

In my script, I did following:

Row.strName = Row.strName + prefix

But I got following error at runtime:

The value is too large to fit in the column data area of the buffer.

at Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer.SetString(Int32 columnIndex, String value)

at Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer.set_Item(Int32 columnIndex, Object value)

at Microsoft.SqlServer.Dts.Pipeline.ScriptBuffer.set_Item(Int32 ColumnIndex, Object value)

at ScriptComponent_98d10a05854c460792443f2345d5d806.Input0Buffer.set_strName(String Value)

at ScriptComponent_98d10a05854c460792443f2345d5d806.ScriptMain.Input0_ProcessInputRow(Input0Buffer Row)

at ScriptComponent_98d10a05854c460792443f2345d5d806.UserComponent.Input0_ProcessInput(Input0Buffer Buffer)

at ScriptComponent_98d10a05854c460792443f2345d5d806.UserComponent.ProcessInput(Int32 InputID, PipelineBuffer Buffer)

at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)

Could anyone tell me what I did wrong?

Thanks!

Jun Fan wrote:

I am new user on VB ( I wish ssis support c# script)

It does. In SQL Server 2008!

Jun Fan wrote:

I have made a input string type column ( strName ) in script componen as ReadWrite.

In my script, I did following:

Row.strName = Row.strName + prefix

But I got following error at runtime:

The value is too large to fit in the column data area of the buffer.

at Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer.SetString(Int32 columnIndex, String value)

at Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer.set_Item(Int32 columnIndex, Object value)

at Microsoft.SqlServer.Dts.Pipeline.ScriptBuffer.set_Item(Int32 ColumnIndex, Object value)

at ScriptComponent_98d10a05854c460792443f2345d5d806.Input0Buffer.set_strName(String Value)

at ScriptComponent_98d10a05854c460792443f2345d5d806.ScriptMain.Input0_ProcessInputRow(Input0Buffer Row)

at ScriptComponent_98d10a05854c460792443f2345d5d806.UserComponent.Input0_ProcessInput(Input0Buffer Buffer)

at ScriptComponent_98d10a05854c460792443f2345d5d806.UserComponent.ProcessInput(Int32 InputID, PipelineBuffer Buffer)

at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)

Could anyone tell me what I did wrong?

Thanks!

What is "prefix"?|||

Jun Fan wrote:

In my script, I did following:

Row.strName = Row.strName + prefix

But I got following error at runtime:

The value is too large to fit in the column data area of the buffer.

Could anyone tell me what I did wrong?

Thanks!

Your concatenated string is too long for the defined datatype of the strName column.
|||

Prefix is another input string column.

Does string property on Row object is fixed length? If so what is legth. I don't have any very long string. Current my street name, and prifix were in two seperate input column. I was trying to concatenate them together.

For example, strName is "200", prefix is "E". I need put them in a single column as "200 e".

Any suggestion?

Thanks for help.

|||

Jun Fan wrote:

Prefix is another input string column.

Does string property on Row object is fixed length? If so what is legth. I don't have any very long string. Current my street name, and prifix were in two seperate input column. I was trying to concatenate them together.

For example, strName is "200", prefix is "E". I need put them in a single column as "200 e".

Any suggestion?

Thanks for help.

Right, but if strName is defined as three bytes, and you try to add another byte from "prefix," it will fail beacuse four bytes is larger than the defined three byte maximum for strName.|||

Thanks for helping.

Yes, My streetName and prefix both are DT_WSTR wiht max lengh 100. Even the acutual value on each property is a couple char, but it take up all lengh. So I could not concatenate them without trim both property.

Thanks Again!

Jun

assign new value of ReadWrite column of string type in script component?

I am new user on VB ( I wish ssis support c# script)

I have made a input string type column ( strName ) in script componen as ReadWrite.

In my script, I did following:

Row.strName = Row.strName + prefix

But I got following error at runtime:

The value is too large to fit in the column data area of the buffer.

at Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer.SetString(Int32 columnIndex, String value)

at Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer.set_Item(Int32 columnIndex, Object value)

at Microsoft.SqlServer.Dts.Pipeline.ScriptBuffer.set_Item(Int32 ColumnIndex, Object value)

at ScriptComponent_98d10a05854c460792443f2345d5d806.Input0Buffer.set_strName(String Value)

at ScriptComponent_98d10a05854c460792443f2345d5d806.ScriptMain.Input0_ProcessInputRow(Input0Buffer Row)

at ScriptComponent_98d10a05854c460792443f2345d5d806.UserComponent.Input0_ProcessInput(Input0Buffer Buffer)

at ScriptComponent_98d10a05854c460792443f2345d5d806.UserComponent.ProcessInput(Int32 InputID, PipelineBuffer Buffer)

at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)

Could anyone tell me what I did wrong?

Thanks!

Jun Fan wrote:

I am new user on VB ( I wish ssis support c# script)

It does. In SQL Server 2008!

Jun Fan wrote:

I have made a input string type column ( strName ) in script componen as ReadWrite.

In my script, I did following:

Row.strName = Row.strName + prefix

But I got following error at runtime:

The value is too large to fit in the column data area of the buffer.

at Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer.SetString(Int32 columnIndex, String value)

at Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer.set_Item(Int32 columnIndex, Object value)

at Microsoft.SqlServer.Dts.Pipeline.ScriptBuffer.set_Item(Int32 ColumnIndex, Object value)

at ScriptComponent_98d10a05854c460792443f2345d5d806.Input0Buffer.set_strName(String Value)

at ScriptComponent_98d10a05854c460792443f2345d5d806.ScriptMain.Input0_ProcessInputRow(Input0Buffer Row)

at ScriptComponent_98d10a05854c460792443f2345d5d806.UserComponent.Input0_ProcessInput(Input0Buffer Buffer)

at ScriptComponent_98d10a05854c460792443f2345d5d806.UserComponent.ProcessInput(Int32 InputID, PipelineBuffer Buffer)

at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)

Could anyone tell me what I did wrong?

Thanks!

What is "prefix"?|||

Jun Fan wrote:

In my script, I did following:

Row.strName = Row.strName + prefix

But I got following error at runtime:

The value is too large to fit in the column data area of the buffer.

Could anyone tell me what I did wrong?

Thanks!

Your concatenated string is too long for the defined datatype of the strName column.
|||

Prefix is another input string column.

Does string property on Row object is fixed length? If so what is legth. I don't have any very long string. Current my street name, and prifix were in two seperate input column. I was trying to concatenate them together.

For example, strName is "200", prefix is "E". I need put them in a single column as "200 e".

Any suggestion?

Thanks for help.

|||

Jun Fan wrote:

Prefix is another input string column.

Does string property on Row object is fixed length? If so what is legth. I don't have any very long string. Current my street name, and prifix were in two seperate input column. I was trying to concatenate them together.

For example, strName is "200", prefix is "E". I need put them in a single column as "200 e".

Any suggestion?

Thanks for help.

Right, but if strName is defined as three bytes, and you try to add another byte from "prefix," it will fail beacuse four bytes is larger than the defined three byte maximum for strName.|||

Thanks for helping.

Yes, My streetName and prefix both are DT_WSTR wiht max lengh 100. Even the acutual value on each property is a couple char, but it take up all lengh. So I could not concatenate them without trim both property.

Thanks Again!

Jun

Assign expression value using code

I am trying to use a variable to set an attribute value of an SSIS task but I keep running into the 4000 character limit of the string variable. Not sure why the variable which is of .NET type String has this limit when it doesn't when you are in a .NET environment. Regardless, can anyone provide some sample code that I could use to do this in a script task? I am trying to set the QueryString property of the Data Mining Query Task. All help would be appreciated.

Thank you in advance.

Variables and expressions in SSIS are limited to 4000 bytes. Sorry.|||As far as dynamically setting the QueryString, when you look at the properties of the Data Mining Query task, is there an "expressions" parameter? If you expand the expressions parameter, do you have an option for QueryString? (I'm not sitting in front of SSIS at the moment, so I can't verify.) If so, you may want to put your variable there instead of using a script.|||

All task properties support expressions, it is only Data Flow components that require the developer to actually set some code that says an expression is supported.

The problem is that the result of an expression cannot be greater than 4000 characters. Using a variable whose value is greater than 4000 characters will not work because this still has to pass through the expression evaluator to be assigned to the property value.

Since the DM Query Task does not offer anything other than a literal string for the query, this cannot be set dynamically within the package. I think this is a limitation, obviously it would be nice to have expressions with > 4000 characters but also tasks should be (consistently) developed with properties such as this accepting literals, variables and files. An ideal example is the Execute SQL Task with the SourceSQLType property that describes the interpretation of the SQL Statement property.

|||That is too bad. Hopefully this will change in a later version. Thanks for the help.

Assign a string to a variable

Hi
I'm using a varchar variable (@.Cadena) to store a select statement.
--
declare @.cadena varchar(1000), @.quincena int, @.anio int, @.numeroempleado
int, @.CadSDOS varchar(1000), @.sdos numeric(9,2)
set @.quincena = 24
set @.anio = 2004
set @.numeroempleado = 589
SET @.Cadena = '(SELECT CASE ' + CAST(@.Quincena AS varchar) + '
WHEN 1 THEN ImpQuin01
WHEN 2 THEN ImpQuin02
WHEN 3 THEN ImpQuin03
WHEN 4 THEN ImpQuin04
WHEN 5 THEN ImpQuin05
WHEN 6 THEN ImpQuin06
WHEN 7 THEN ImpQuin07
WHEN 8 THEN ImpQuin08
WHEN 9 THEN ImpQuin09
WHEN 10 THEN ImpQuin10
WHEN 11 THEN ImpQuin11
WHEN 12 THEN ImpQuin12
WHEN 13 THEN ImpQuin13
WHEN 14 THEN ImpQuin14
WHEN 15 THEN ImpQuin15
WHEN 16 THEN ImpQuin16
WHEN 17 THEN ImpQuin17
WHEN 18 THEN ImpQuin18
WHEN 19 THEN ImpQuin19
WHEN 20 THEN ImpQuin20
WHEN 21 THEN ImpQuin21
WHEN 22 THEN ImpQuin22
WHEN 23 THEN ImpQuin23
WHEN 24 THEN ImpQuin24
END As SumaSDOS
FROM HisMovimientosQnal
WHERE Anio = ' + CAST(@.Anio AS varchar) + ' AND NumeroEmpleado = ' +
CAST(@.NumeroEmpleado AS varchar)
+ ' AND TipoIncidencia = ''SDOS'')'
EXEC (@.Cadena) --Execute the select statement
--
Till this point, the result is shown correctly.
I would like to store that result into another variable.
How can I do it?Gonzalo Torres wrote:
> Hi
> I'm using a varchar variable (@.Cadena) to store a select statement.
> --
> declare @.cadena varchar(1000), @.quincena int, @.anio int, @.numeroempleado
> int, @.CadSDOS varchar(1000), @.sdos numeric(9,2)
> set @.quincena = 24
> set @.anio = 2004
> set @.numeroempleado = 589
> SET @.Cadena = '(SELECT CASE ' + CAST(@.Quincena AS varchar) + '
> WHEN 1 THEN ImpQuin01
> WHEN 2 THEN ImpQuin02
> WHEN 3 THEN ImpQuin03
> WHEN 4 THEN ImpQuin04
> WHEN 5 THEN ImpQuin05
> WHEN 6 THEN ImpQuin06
> WHEN 7 THEN ImpQuin07
> WHEN 8 THEN ImpQuin08
> WHEN 9 THEN ImpQuin09
> WHEN 10 THEN ImpQuin10
> WHEN 11 THEN ImpQuin11
> WHEN 12 THEN ImpQuin12
> WHEN 13 THEN ImpQuin13
> WHEN 14 THEN ImpQuin14
> WHEN 15 THEN ImpQuin15
> WHEN 16 THEN ImpQuin16
> WHEN 17 THEN ImpQuin17
> WHEN 18 THEN ImpQuin18
> WHEN 19 THEN ImpQuin19
> WHEN 20 THEN ImpQuin20
> WHEN 21 THEN ImpQuin21
> WHEN 22 THEN ImpQuin22
> WHEN 23 THEN ImpQuin23
> WHEN 24 THEN ImpQuin24
> END As SumaSDOS
> FROM HisMovimientosQnal
> WHERE Anio = ' + CAST(@.Anio AS varchar) + ' AND NumeroEmpleado = '
+
> CAST(@.NumeroEmpleado AS varchar)
> + ' AND TipoIncidencia = ''SDOS'')'
> EXEC (@.Cadena) --Execute the select statement
> --
> Till this point, the result is shown correctly.
> I would like to store that result into another variable.
> How can I do it?
Exec @.variable =(@.Cadena)
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)|||Look up the topic sp_ExecuteSQL in SQL Server Books Online. It has some
examples which shows how to assign the results to an output variable.
Also, just by glancing through the code, it seems like you might have a
better/easier way of retrieving the results without using Dynamic SQL. But
then without further knowledge of your table structures & business rules, it
is hard to tell.
Anith|||I don't know your table structure, but this thing looks suspiciously
de-normalized...
Anyways, you might look into sp_executesql instead of EXEC. Safer and
shouldn't require all the CASTs, plus you can get a little performance boost
out of it since it compiles the query plan for parameterized queries. Check
it out in BOL.
"Gonzalo Torres" <condormix2001@.yahoo.com.mx> wrote in message
news:O4nRqYCJFHA.1860@.TK2MSFTNGP15.phx.gbl...
> Hi
> I'm using a varchar variable (@.Cadena) to store a select statement.
> --
> declare @.cadena varchar(1000), @.quincena int, @.anio int, @.numeroempleado
> int, @.CadSDOS varchar(1000), @.sdos numeric(9,2)
> set @.quincena = 24
> set @.anio = 2004
> set @.numeroempleado = 589
> SET @.Cadena = '(SELECT CASE ' + CAST(@.Quincena AS varchar) + '
> WHEN 1 THEN ImpQuin01
> WHEN 2 THEN ImpQuin02
> WHEN 3 THEN ImpQuin03
> WHEN 4 THEN ImpQuin04
> WHEN 5 THEN ImpQuin05
> WHEN 6 THEN ImpQuin06
> WHEN 7 THEN ImpQuin07
> WHEN 8 THEN ImpQuin08
> WHEN 9 THEN ImpQuin09
> WHEN 10 THEN ImpQuin10
> WHEN 11 THEN ImpQuin11
> WHEN 12 THEN ImpQuin12
> WHEN 13 THEN ImpQuin13
> WHEN 14 THEN ImpQuin14
> WHEN 15 THEN ImpQuin15
> WHEN 16 THEN ImpQuin16
> WHEN 17 THEN ImpQuin17
> WHEN 18 THEN ImpQuin18
> WHEN 19 THEN ImpQuin19
> WHEN 20 THEN ImpQuin20
> WHEN 21 THEN ImpQuin21
> WHEN 22 THEN ImpQuin22
> WHEN 23 THEN ImpQuin23
> WHEN 24 THEN ImpQuin24
> END As SumaSDOS
> FROM HisMovimientosQnal
> WHERE Anio = ' + CAST(@.Anio AS varchar) + ' AND NumeroEmpleado = ' +
> CAST(@.NumeroEmpleado AS varchar)
> + ' AND TipoIncidencia = ''SDOS'')'
> EXEC (@.Cadena) --Execute the select statement
> --
> Till this point, the result is shown correctly.
> I would like to store that result into another variable.
> How can I do it?
>

Assembly name for SSIS built-in UITypeEditors?

I have a custom PipelineComponent that accepts a string of SQL. I don't have a custom UI, all I need is the Advanced Editor. Currently the SQL property is just the standard line of text that can be entered on the Advanced Editor. I would like to use the popup multi-line editor that the built-in components use for editing SQL. I was hoping it was the System.ComponentModel.Design.MultilineStringEditor but that is definitely not it (and that one is insufficient for entering more than a few lines of SQL). I'm assuming it must be a UITypeEditor that was shipped as part of SQL 2005, but I haven't been able to track down the qualified assembly name for it anywhere. I tried debugging in VS to get down to a IDTSCustomProperty90 that I could look at the UITypeEditor on, but no such like. I also tried using Reflector to see if I could dig up a string, but no luck there either as the pipeline components don't seem to have managed assemblies (I could've just missed them) and the control tasks (which happen to use the UITypeEditor I'm looking for too) seem to only be thin wrappers around COM interfaces. I scoured BOL and the WWW in general for a list of these assembly names, but looks like they're not out there either. Has anyone tried to find these before? Am I barking up the wrong tree, should I not be able to use these editors for copyright reasons?Using undocumented stuff like that could get messy, but most importantly for me I think most of the editors are rather poor. Simple things like support for Ctrl+A to select all text are missing. For what you want I would write my own, not too hard and you can make it much more user friendly. Not the answer you wanted I suspect, but really I think it would be probably faster and certainly better to write your own.

aspnetdb on host, connection string

Hi

I am having trouble connecting to aspnetdb on the shared host. On my local server this is my connection string which works fine:

<addname="MyLocalSqlServer"connectionString="Data Source=.\SQLExpress;Integrated Security=SSPI;Initial Catalog=aspnetdb;User Instance=false"

providerName="System.Data.SqlClient" />

But, this doesn't work on the host (just putting in host address):

<addname="MyLocalSqlServer"connectionString="190.40...232,1444;Integrated Security=SSPI;Initial Catalog=aspnetdb_booksale;User Instance=false"

providerName="System.Data.SqlClient" />,

and I get the error :

Login failed for user ''. The user is not associated with a trusted SQL Server connection.

so I put in the user id and password that i used when I created the aspnetdb_booksale db

<addname="MyLocalSqlServer"connectionString="Data Source=196...232,1444;Integrated Security=False;User ID=aspnetdbbooksale; password=amanda;Initial Catalog=aspnetdb_booksale;User Instance=false"

providerName="System.Data.SqlClient" />

and I get the error:

The ConnectionString property has not been initialized.

Any ideas for me?

Amanda

Hi

My problem is not the connection string, because the one below works when I use the add user wizard. However, I get the error when I use the "createuser.membership" method. Funny tho it worked on my local server...

<addname="MyLocalSqlServer"connectionString="Data Source=196...232,1444;Integrated Security=False;User ID=aspnetdbbooksale; password=amanda;Initial Catalog=aspnetdb_booksale;User Instance=false"

|||

So you mean you got the "The ConnectionString property has not been initialized" when you connect from remote machine using the connection string below?

addname="MyLocalSqlServer"connectionString="Data Source=196...232,1444;Integrated Security=False;User ID=aspnetdbbooksale; password=amanda;Initial Catalog=aspnetdb_booksale;User Instance=false"

Really strange. Every time you got the same error message?

aspnetdb connection string not found

Why I don't have connection string in web.config for aspnetdb.mdf database although the site works?Help plz?

You could have connection string in web.config,and reference it in your code,take gridview for example:

＜asp:SqlDataSource ID="SqlDataSourcel" Runat="server"ProviderName="System.Data.SqlClient"ConnectionString="Server=(local)\SQLExpress;Integrated Security=True;Database=Northwind;Persist Security Info=True"SelectCommand="SELECT [ProductID], [ProductName], [UnitPrice] FROM[Products]"＞＜/asp:SqlDataSource＞or "SqlDataSource1" runat="server" DataSourceMode="DataReader" ConnectionString="<%$ ConnectionStrings:MyNorthwind%>" SelectCommand="SELECT FirstName, LastName, Title FROM Employees">

In your soucecode you should find ConnectionString .|||The connection string for the database file created in your application is auto generated, you may take a look at this post:http://forums.asp.net/thread/1430985.aspx

aspnetdb connection string

Hello,
I'm getting up to speed with VS2005 and use SQL Server 2005. I'm using the login control in a test web app.

When I run the app I get this error:

Cannot open database "aspnetdb" requested by the login. The login failed.
Login failed for user 'UserID\ASPNET'.

The connection string I'm using is:

data source=localhost;Integrated Security=SSPI;Initial Catalog=aspnetdb;

The AspNetSqlProvider in the web administration tool connects to the database.

My question is, Is this a connection string issue, and user ID issue, a rights issue or is it something else?

Thanks,

Gaikhe

This is a permission issue on SQL, which indicates theUserID\ASPNETlogin dose not have sufficient permission to perform specific task(access in this case) on theaspnetdb database. You should add database mapping for this account to theaspnetdb database: open ManagementStudio->Explore the SQL instance->Security->Logins->view the properties of theUserID\ASPNETlogin->switch toUser Mapping tab-> add proper mapping and permission to the login.

ASP.NET,Gettting Error in the Program

Hi,

please have look into the code and let me know the solution plz.

string

ProID;try

{

using(SqlConnection conn=new SqlConnection(source))

{

conn.Open();

DataSet ds=

new DataSet();

DDLProject.Items.Clear();

SqlCommand cmd=

new SqlCommand("SP_ProjectSelect",conn);

cmd.CommandType=CommandType.StoredProcedure;

cmd.Parameters.Add("Name",SqlDbType.NChar,30,"@.Name");

cmd.UpdatedRowSource=UpdateRowSource.None;

cmd.Parameters["Name"].Value=DDLProductLine.SelectedItem;

SqlDataReader dr=cmd.ExecuteReader(); ---->>>> Here iam getting Following Error.

while(dr.Read())

{ Response.Write(dr["ID"].ToString());

}

}

}

catch(System.Exception ex)

{

Response.Write(ex);

}

Error:

System.InvalidCastException: Object must implement IConvertible. at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) at System.Data.SqlClient.SqlCommand.ExecuteReader() at MIS.UI.ResourceList.DDLProductLine_SelectedIndexChanged(Object sender, EventArgs e) in c:\inetpub\wwwroot\dotnet pgms\mis\ui\resourcelist.aspx.cs:line 121

Any suggestion plzz,where went wrong.

Thanks in advance

Regards

Mahesh Reddy

Is it possible your stored procedure "SP_ProjectSelect" is not returning a result set? That's what it sounds like.|||

I think your oder in parameter.add was not correct.

The order should be SQL parameter, DataType, and Input Value.

In your case, it looks like

cmd.Parameters.Add("@.Name",SqlDbType.NChar,30,Name);

Here, "@.Name" is SQL parameter name, Name is the valueable you want to input. You can use "Name" for test. In this case, you will insert "Name" into table.

Also, NChar is not the good datatype for string name. I suggest you change it to VarChar (You need change it in SQL SP too).

Hope it helps

ASP.NET SQLConnection string problem with ISP "oneandone.co.uk"

Appreciate this is probably one for the oneandone tech support team on Monday, but I'd like to avoid loosing a days development and deployment time by geting this sorted today if possible. Would really appreciate it if anyone can help?

When I try connecting to a SQL 2000 database supplied by my ISP I get an erro telling me the Server doesn't exist or I don't have acces. Am sure the problem lies with my connection string. Only problem is I've tried all possible variations using the "database name", "userid" and "password" supplied by oneandone. Their help pages give the following connection string example:

oConn = New System.Data.SQLClient.SQLConnection ("server=kundenmssql3.schlund.de; initial catalog=Northwind;uid=USERNAME;pwd=PASSWORD")

I've tried using my domain name for the server and the name they've provided in the example, all without any success. I can't find any information on what server name I should be using in my account maintenance area.

Does anyone else out there host with oneandone, and if so, can you point me in the right direction?

Thanks.you may need to specify the network library to use - try the page on this error at www.aspfaq.com

j

ASP.NET connection string problems with SQL Server 2000

My aspx page is trying to connect to a remote server with SQL Server 2000 installed on it, the ip of the server is 172.16.3.111 and the same is the instance of SQL Server, sql server is running in SQL Server Authentication mode, user id is "test" and password is "test123", name of database is myDB,the connection string which I am providing is as follows:

data source=172.16.3.111\172.16.3.111;user id=test;password=test123;initial catalog=myDB;"

But I am getting the following exception:

SQL Server does not exist or access denied.

even if I try the following connection string:
"data source=172.16.3.111;user id=test;password=test123;initial catalog=myDB;"

than also I get the same exception

even if provide the following connection strings:

"SERVER=172.16.3.111;UID=test;PWD=test123;DATABASE =myDB;"

"SERVER=172.16.3.111\172.16.3.111;UID=test;PWD=test123;DATABASE =myDB;"

than also I get the same exceptions

Can u please tell whats the problem,is the connection string correct,the DBA has also registered me "test" as a user on the SQL server.One thing which I want to mention is that through enterprise manager I can connect and use sql server without any problems,also please note that in VS.NET when I try connecting using server explorer and test the connection than I connect successfully, than why is problem occuring in connecting throug aspx page.

Try change the param "Server" like this:

\\ server name or server ip \ instance name

ASP.net Connection String

Hi I am new to asp.net and I am having problems connecting to a SQL Server database. My code is;

Dim myConnection As New SqlConnection("server='localhost'; user id='sa'; password='ExamResults'; Database='ExamResults'")
Dim myCommand As New SqlDataAdapter("select * from tblStudents", myConnection)

Dim rstStudents As New DataSet()
myCommand.Fill(rstStudents, "tblStudents")

I'm not too sure if this should be placed in the <script runat="server"> or just after declaring the namespaces.

Also, how do you reference the select statement in the code, currently I have;

<ASP: response.write rstStudents.fields("Surname") Response.write ("test")/
Any help would be greatly appreciatedYou should look at theServer-Side Data Access quickstart for a jump start.

All of the Quickstarts can be found here: http://www.asp.net/Tutorials/quickstart.aspx

Terri