Establish SQL Connection

Hi! (my first post)

I'm ASP.NET newbie and i´ve got anSQL connection error, i know why, ididn´t establish a connection...
my questions are: how do i establish the connection state?, and where in my project should i do it?

I added the connection section to my web.config, is that enough to establish a connection?

<connectionStrings>
<add name="buscomConnectionString" connectionString="Data Source=myserver.net;Initial Catalog=mycatalog;Persist Security Info=True;User ID=myuser;Password=mypassword"
providerName="System.Data.SqlClient" />
</connectionStrings>

The error i get is:

Object reference not set to an instance of an object.

Description:Anunhandled exception occurred during the execution of the current webrequest. Please review the stack trace for more information about theerror and where it originated in the code.

Exception Details:System.NullReferenceException: Object reference not set to an instance of an object.

Source Error:

Line 2443:
Line 2444://If connection is not open then attempt to open it.
Line 2445:if (connection.State == System.Data.ConnectionState.Closed)
Line 2446:{
Line 2447:try

Source File: d:\User\buscom\App_Code\PHP.cs Line: 2445

Thanks and cheers!

Bill Murphy (litio.net)

Welcome to ASP.NET Forums The section you added in web.config is just to store a conneciton string which then can be used in your application. You can create a connection using the connection string like:

SqlConnection conn= new SqlConnection(ConfigurationManager.ConnectionStrings["Access"].ConnectionString.ToString());

Then use Open method to open it:

Conn.Open();

Or you can create a SqlCommand with the connection:

SqlCommand cmd= new SqlCommand("SELECT * FROM sysobjects",conn);

Or do other things as you like. To learn various database connections in ASP .NET, you can start from here:

http://msdn2.microsoft.com/en-us/library/ms254937.aspx

However I suggest you use 'using' block to embed database operations so that the connection will be automatically disposed, so to avoid leaving unuseful open connections. That is:

using (SqlConnection conn= new SqlConnection(ConfigurationManager.ConnectionStrings["Access"].ConnectionString.ToString()))

{//add your code

}

To learn more about connection Close/Dispose in .NET, you can take a look at this article:

http://codebetter.com/blogs/sahil.malik/archive/2004/12/31/40036.aspx

|||

That was what I was looking for!! just didn´t seem to find it :D
Both link where very helpfull.

Thanks a lot!

|||

sorry, buy looking into that didn´t work out. i have to reopen my question...

I am using the PHPMigration assistant, so i went ahead and generated the connect command and got the following:

try {PHP.MSSQLSupport.Connect(host, user, password); }

catch(System.Exception) {Response.Write("Error, no server"); }

try {PHP.MSSQLSupport.ChangeDatabas
________________________________________________________

This is a duplicate of your other post:http://forums.asp.net/thread/1387576.aspx

Duplicate posts are not permitted on theASP.NET Forums. As a result, the duplicate post has been deleted. We trust however that you will receive helpful replies to your other post, linked above.

Please be aware that theASP.NET Forums are moderated. This means that there will always be a delay between submitting a message and seeing it appear on the forums. There is no need to submit a message multiple times.

Thanks,
ASP.NET Forums team

",0]);//-->e(bbdd);}

catch (System.Exception) { Response.Write( bbdd + " not found"); }

Inserted it into my page and the error above persists.

i am passing a null to PHP.MSSQLSupport.Query, that should reopen the last connection, so i guessI'm just not opening the connection as i should...

How should iestablish the connection with the PHPMigration assistant generatedcode? should i remove the info on the web. config or the above lines?

should i mention my connection string name ( buscomConnectionString )anywhere in the code? or should the above code be enough?

if the databese isn´t connecting properly, would i get a different error? (maybe Error, no server as i specified??)

|||This is what i meant to post:

sorry, buy looking into that didn´t work out. i have to reopen my question...

I am using the PHPMigration assistant, so i went ahead and generated the connect command and got the following:

try {PHP.MSSQLSupport.Connect(host, user, password); }

catch(System.Exception) {Response.Write("Error, no server"); }

try {PHP.MSSQLSupport.ChangeDatabase(bbdd);}

catch (System.Exception) { Response.Write( bbdd + " not found"); }

Inserted it into my page and the error above persists.

i am passing a null to PHP.MSSQLSupport.Query, that should reopen the last connection, so i guessI'm just not opening the connection as i should...

How should iestablish the connection with the PHPMigration assistant generatedcode? should i remove the info on the web. config or the above lines?

should i mention my connection string name ( buscomConnectionString )anywhere in the code? or should the above code be enough?

if the databese isn´t connecting properly, would i get a different error? (maybe Error, no server as i specified??)

Errors when attempting to use DataReader destination

Hi,
We have an ASP.Net app that consumes data from a SSIS package. I have proved that this works by building a noddy ASP.Net app against it on my local machine.

Unfortunately the same isn't true of our project-wide solution where we get error "Package failed to execute." In our package log I can see that the things that were failing were various property expressions that are in my package. The expressions are all on the SQLStatementSource property of various Exec SQL tasks in my package.

Here's an example of just such an error:
OnError,FR23011958,CT\aeqz,SQL UPDATE StreamHistory,{30BD52FA-8077-4D40-BFA7-248173C34000},{C84EF8C0-4B15-41FC-B205-9FB6B72884AC},26/10/2005 13:46:31,26/10/2005 13:46:31,-1073647612,0x,The result of the expression ""UPDATE dbo.StreamHistory
SET StreamEndDateID = (Year(GETDATE()) * 10000) + (Month(GETDATE()) * 100) + Day(GETDATE())
, StreamEndTimeID = (datepart(hh, GETDATE()) * 100) + datepart(mi, GETDATE())
, StreamEndTMS = GETDATE()
, StatusDetailID = 1
, Duration = DATEDIFF(ss, StreamStartTMS, GETDATE())
WHERE StreamHistoryID = " + TRIM((DT_STR, 4, 1252) @.[Metadata::StreamHistoryID])

" on property "SqlStatementSource" cannot be written to the property. The expression was evaluated, but cannot be set on the property.
Hopefully you can see that (in theory) this expression should get evaluated regardless of where the package is executed from. Remember, it is not the SQL query that fails to execute - it is the construction of the SQL statement inside an expression that fails.

So simply, has anyone experienced something similar? i.e. Expressions fail to get evaluated when a package is executed from an ASP.Net app (or elsewhere).

I'm wondering if this is a permissions thing...its damned annoying anyway.

ANY experiences at all of doing something similar would be useful.

Thanks
-Jamie

Jamie,

Would you like to share with me your package and ASP app?

BTW - I don't see how this relates to Data Reader destination. Do you mean to put another subject line instead?|||

Runying Mao wrote:

Jamie,

Would you like to share with me your package and ASP app?

BTW - I don't see how this relates to Data Reader destination. Do you mean to put another subject line instead?

Hi Runying,
Well it consumes data via a datareader. I actually wrote that initial post over the space of about an hour. At the beginning of that hour we figured the problem was in the datareader cos of the problems we've had with it lately (which you know all about ), by the end of the hour we'd learnt more about what it was - i.e. problems with expressions. I just forgot to change the thread title that's all.

We've found out more since. We've built a new windows forms app and copied the code from "Running an Existing Package using Visual Basic" in BOL into the app and used it to run our package. It worked fine.

We then copied and pasted that code line for line into a new project in our solution and it didn't work. Same code, same machine, same user credentials...different result. very very strange. So now we're pursuing the assumption that some sort of solution-wide configuration is causing a problem. Hopefully we'll find out more tomorrow but if not....I'l be on here pleading for help!!

-Jamie|||Hi, I'm working with Jamie on this problem and have found a little more about the problem, but am still no nearer a solution!

If I add a new Web site to our solution and copy in the code to call the SSIS package, everything runs correctly.

However, if I then reference one of the other assemblies in our solution the package fails to execute, even though we're not actually calling anything in the referenced assembly.|||Here's what Tim and I eventually found out.

http://blogs.conchango.com/jamiethomson/archive/2005/10/27/2315.aspx

Weird!!

-Jamie

Errors in SQL Server. How prevent them?

Im designing a dynamic website with Dreamweaver MX, ASP VBScript language, SQL 2000 Server and PWS as a server. The site is not available in Internet yet.

For the moment I have created all the main functions of the website. Almost all the actions that the site leads to term it does it with stored procedures (sp) on SQL Server. Those SP works well from the web application, and all the actions (to insert data, to modify data, to check data, etc.) works perfect. But I have been advised that I learn to capture the possible errors that SQL Server can return to user, and to avoid that the site fell. Someone could explain me what kind of errors can return me? And how can I solve it?

I can not understand why I need to know how to capture those errors because, if were managing to happen, the browser show us a message of error and the user try again the operation, as I have seen at many sites. After that, I should quarrel to discover why these errors happen, and then I try to solve it, or take the aid of an expert administrator. Or, can I really to prevent these potential errors and to minimize their effects?

In the other hand, I cant understand why my stored procedures are able to return errors or to bring problems to my web application, now it works well.

Thank you.

Here is an example of one of my stored procedures:

CREATE PROCEDURE check_mail_password_inscription
@.E-mail varchar(50), @.Password char(10),
@.Offer_num int, @.Date datetime, @.Motives_interest varchar(250)
As
Declare @.Buyer_num int

If Exists(select E-mail from Buyers where E-mail = @.E-mail
and Password = @.Password)
begin

select @.Buyer_num = Buyer_num
from Buyers
where E-mail = @.E-mail
and Password = @.Password

select @.Buyer_num AS Buyer_num, 1 AS Value
INSERT INTO Inscriptions_in_offers (Offer_num, Buyer_num, Date, Motives_interest)
VALUES (@.Offer_num, @.Buyer_num, @.Date, @.Motives_interest)
end
else
begin
select 0 AS Value
endAny data update can cause a deadlock and there is very little you can do to prevent it - you can minimise it but not prevent. Systems that are not designed with this in mind usually have a lot of problems when they get a few concurrent users.

Your system should always take account of the possibility of any operation failing.

In your SP what happens if the insert fails? to you still want to return a resultset?
And why return a single row resultset rather than output parameters which are much faster.
You are also returning a different shape resultset for the two paths which is a bit odd.

I would guess that you also need to read up on transaction control.|||What you recommend me to minimize the errors in my stored procedures? Now they are totally helpless. I need to use BEGIN TRANSACTION, COMMIT TRANSACTION, ROLLBACK TRANSACTION, @.@.ERROR variable, RAISERROR, RETURN statement to force an unconditional exit or sp_addmessage stored procedure? Or all together where this is possible?

If my sp fails and the action cannot be done, I need communicate it to my web application so that the user tries again the operation.

I only know this tools in general, never I used to. Would you mind give me a complete example with my stored procedure please?

CREATE PROCEDURE check_mail_password_inscription
@.E-mail varchar(50), @.Password char(10),
@.Offer_num int, @.Date datetime, @.Motives_interest varchar(250)
As
Declare @.Buyer_num int

If Exists(select E-mail from Buyers where E-mail = @.E-mail
and Password = @.Password)
begin

select @.Buyer_num = Buyer_num
from Buyers
where E-mail = @.E-mail
and Password = @.Password

select @.Buyer_num AS Buyer_num, 1 AS Value
INSERT INTO Inscriptions_in_offers (Offer_num, Buyer_num, Date, Motives_interest)
VALUES (@.Offer_num, @.Buyer_num, @.Date, @.Motives_interest)
end
else
begin
select 0 AS Value
end

I have a manual of SQL Server that says that exist two types of transactions: explicit and implicit. Explicit transaction means that begin with the BEGIN TRANSACTION statement and end with the COMMIT TRANSACTION statement. Implicit transaction means that, more or less, if you don t use the BEGIN TRANSACTION and COMMIT TRANSACTION statements, nor do you turn on implicit transactions, SQL Server autocommits the transaction. With this, I understand that is not necessary to write the statements begin tr and commit tr in my sp. It is like that?


Thank you,
Cesar

error:26 when connecting to sql server express...

I've got an ASP.net 2.0 application that I published and moved to a publicly available server. From my machine it works fine, but not from the production machine.

I'm attempting to connect to a machine that has sql server express installed.

The message in the browser:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

So, I went by the kb article and made sure tcpip is enabled. This uses port 3288. server browser is enabled and the machine accepts remote connections.My connection string is (minus the ip & user/pwd):

<add name="IRRMAConnectionString" connectionString="Data Source=<ip_address>\SQLEXPRESS,3288;Initial Catalog=IRRMA;User ID=<user>;password=<pwd>" providerName="System.Data.SqlClient"/>

I can ping the db server from my app server. On my dev machine it connects. Windows firewall is turned off on the db server.

This seems to be an issue with a firewall, I think?

Any direction would be appreciated.

Thanks,

Scott Jones

Scott@.fourindianajones.com

You should check this out and go from there.

http://blogs.msdn.com/sql_protocols/archive/2007/05/13/sql-network-interfaces-error-26-error-locating-server-instance-specified.aspx

I would say it has to do with the UDP 1434 or the sqlbrowser.exe in the exceptions. But once you follow all the steps you should get closer to the resolution.

error:26 when connecting to sql server express...

I've got an ASP.net 2.0 application that I published and moved to a publicly available server. From my machine it works fine, but not from the production machine.

I'm attempting to connect to a machine that has sql server express installed.

The message in the browser:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

So, I went by the kb article and made sure tcpip is enabled. This uses port 3288. server browser is enabled and the machine accepts remote connections.My connection string is (minus the ip & user/pwd):

<add name="IRRMAConnectionString" connectionString="Data Source=<ip_address>\SQLEXPRESS,3288;Initial Catalog=IRRMA;User ID=<user>;password=<pwd>" providerName="System.Data.SqlClient"/>

I can ping the db server from my app server. On my dev machine it connects. Windows firewall is turned off on the db server.

This seems to be an issue with a firewall, I think?

Any direction would be appreciated.

Thanks,

Scott Jones

Scott@.fourindianajones.com

You should check this out and go from there.

http://blogs.msdn.com/sql_protocols/archive/2007/05/13/sql-network-interfaces-error-26-error-locating-server-instance-specified.aspx

I would say it has to do with the UDP 1434 or the sqlbrowser.exe in the exceptions. But once you follow all the steps you should get closer to the resolution.

error:26 Error Locating Server/Instance Specified

I am having trouble connecting to Sql Server 2005 Express from asp.net
2.0 app on Windows Server 2003. I am using Sql Server Authentication,
and I tried the below article's suggestions a number of times. When I
login to to the machine, and login, I am able to get authenticated.
However, when I debug my application, I get the error below. Do I have
to give the sql server authenticaed account I created certain
permissions? Whould I set a port, and use the ipaddress rather than
the server name? Any suggestions would be greatly appreciated.
http://support.microsoft.com/kb/914277
My Error:
An error has occurred while establishing a connection to the server.
When connecting to SQL Server 2005, this failure may be caused by the
fact that under the default settings SQL Server does not allow remote
connections. (provider: SQL Network Interfaces, error: 26 - Error
Locating Server/Instance Specified)
My Connection String:
<add key="ConnectionString"
value="server=MyServerName;uid=MyUserName;pwd=MyPasswd;database=MyDB"/>On Feb 1, 5:20=A0pm, carlos <carlosubu...@.gmail.com> wrote:
> I am having trouble connecting to Sql Server 2005 Express from asp.net
> 2.0 app on Windows Server 2003. I am using Sql Server Authentication,
> and I tried the below article's suggestions a number of times. When I
> login to to the machine, and login, I am able to get authenticated.
> However, when I debug my application, I get the error below. Do I have
> to give the sql server authenticaed account I created certain
> permissions? Whould I set a port, and use the ipaddress rather than
> the server name? Any suggestions would be greatly appreciated.
> http://support.microsoft.com/kb/914277
> My Error:
> An error has occurred while establishing a connection to the server.
> When connecting to SQL Server 2005, this failure may be caused by the
> fact that under the default settings SQL Server does not allow remote
> connections. (provider: SQL Network Interfaces, error: 26 - Error
> Locating Server/Instance Specified)
> My Connection String:
> =A0<add key=3D"ConnectionString"
> value=3D"server=3DMyServerName;uid=3DMyUserName;pwd=3DMyPasswd;database=3D=MyDB"/>
Update:
I changed the connection string to use the ipaddress, and a port that
I set in the TCPIP settings, and now i'm getting the below message:
An error has occurred while establishing a connection to the server.
When connecting to SQL Server 2005, this failure may be caused by the
fact that under the default settings SQL Server does not allow remote
connections. (provider: TCP Provider, error: 0 - No connection could
be made because the target machine actively refused it.)
I'm thinking this may be a permission problem'|||Hi Carlos,
By default, SQL Server 2005 Express, Evaluation, and Developer editions
allow local client connections only. Enterprise, Standard, and Workgroup
editions also listen for remote client connections over TCP/IP.
you ned to enable Remote Connections option using
Surface Area Configuration for Services and Connections (Remote Connections)
- Database Engine
You must restart the Database Engine instance's service to apply changes.
Regards,
Amol R. Lembhe
"carlos" wrote:
> On Feb 1, 5:20 pm, carlos <carlosubu...@.gmail.com> wrote:
> > I am having trouble connecting to Sql Server 2005 Express from asp.net
> > 2.0 app on Windows Server 2003. I am using Sql Server Authentication,
> > and I tried the below article's suggestions a number of times. When I
> > login to to the machine, and login, I am able to get authenticated.
> > However, when I debug my application, I get the error below. Do I have
> > to give the sql server authenticaed account I created certain
> > permissions? Whould I set a port, and use the ipaddress rather than
> > the server name? Any suggestions would be greatly appreciated.
> >
> > http://support.microsoft.com/kb/914277
> >
> > My Error:
> >
> > An error has occurred while establishing a connection to the server.
> > When connecting to SQL Server 2005, this failure may be caused by the
> > fact that under the default settings SQL Server does not allow remote
> > connections. (provider: SQL Network Interfaces, error: 26 - Error
> > Locating Server/Instance Specified)
> >
> > My Connection String:
> > <add key="ConnectionString"
> > value="server=MyServerName;uid=MyUserName;pwd=MyPasswd;database=MyDB"/>
> Update:
> I changed the connection string to use the ipaddress, and a port that
> I set in the TCPIP settings, and now i'm getting the below message:
> An error has occurred while establishing a connection to the server.
> When connecting to SQL Server 2005, this failure may be caused by the
> fact that under the default settings SQL Server does not allow remote
> connections. (provider: TCP Provider, error: 0 - No connection could
> be made because the target machine actively refused it.)
> I'm thinking this may be a permission problem'
>

Error::while creating an alert in asp.net using C#

Hi,

I am creating operators,Alerts and jobs through asp.net using c#.
so some times while i am creating "Alerts" i am getting one error like this

[MicroSoft][ODBC ServerDriver][SQL Server] The specified @.Message_id('499') does not exist.

i am not understanding what its mean, any on can help me to do this

Thx,
Raj.Could you post a small standalone compileable reproduction of this issue, so we can take a closer look?

Error: Transaction context in use by another session

I am programming in ASP and SQL server.

I am using this tag <%Transaction=Required%> to do the transaction.

And it woks well, but the problem is that sometimes I get this error: "transaction context in use by another session"...

It's really weird because it only happens in the same row. If choose this row to be shown I get that message, but if choose another row I get result with no error. And if take out the tag <%Transaction... I don't get any error.

I've searched in other forums about this error, and one guy claims that there is a bug in the MTS and there is no way to solve the problem.

Could you please send the code of your transaction ?

Also, are you gettign GetContext Transaction ?

Regards-

|||

Actually, the transaction is done only by using this tag <%@. Language=VBScript Transaction=Required%> in the begnning of the code, and the MTS (microsoft transaction server) will work the transaction.

It works well... if I get any error in the middle of the code everything is cancelled. And if take this tag out of my code then there will be no transaction.

It looks to me that the problem is not in the code. It happened that even when I don't have any insert,update or delete I get this message. It seems that this "MTS" opens two sessions to execute the queries... I would like to know how could I use that to avoid this problem ? Is there any configuration needed, either in SQL Server or this MTS ?

error: Too many arguments specified

Error:Too Many Arguments Specified
Posted: 03-03-2005 05:03 AM
Hi,
I'm pretty new to ASP.NET so I'm having some problems
I'm trying to insert a users registration info into the DB using a Stored
Proc but for some reason it won't work
When i run the Sproc in query analyser it seems to be ok with it but when i
run the asp pages it lets me enter the info then when i submit it gives me
the Too many Arguments Specified error
here's the code for the proc
CREATE PROCEDURE [dbo].[Toregister]
(
@.firstname varchar(20),
@.lastname varchar(20),
@.address varchar(20),
@.town varchar(20),
@.county varchar(20),
@.postcode varchar(10),
@.telno varchar(10),
@.email varchar(20),
@.username varchar(20),
@.password varchar(20)
)
AS
insert into t_user
(firstname,lastname,address,town,county,
postcode,telno,email,username,passwo
rd)
values
(@.firstname,@.lastname,@.address,@.town,@.co
unty,@.postcode,@.telno,@.email,@.userna
me,@.password)
GO
and here's the code in my aspx file for adding the parameters
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Save.Click
SqlCommand1.Parameters.Add("@.firstname", txtFirstname.Text)
SqlCommand1.Parameters.Add("@.lastname", txtSurname.Text)
SqlCommand1.Parameters.Add("@.address", txtAddress.Text)
SqlCommand1.Parameters.Add("@.town", txtTown.Text)
SqlCommand1.Parameters.Add("@.county", txtCounty.Text)
SqlCommand1.Parameters.Add("@.postcode", txtPostcode.Text)
SqlCommand1.Parameters.Add("@.telno", txtTelno.Text)
SqlCommand1.Parameters.Add("@.email", txtEmail.Text)
SqlCommand1.Parameters.Add("@.username", txtUsername.Text)
SqlCommand1.Parameters.Add("@.password", txtPassword.Text)
SqlConnection1.Open()
SqlCommand1.ExecuteNonQuery()
End Sub
and just for good measure here's the result from the DB profiler when I run
the asp
exec [Toregister] @.firstname = default, @.lastname = default, @.address =
default, @.town = default, @.county = default, @.postcode = default, @.telno =
default, @.email = default, @.username = default, @.password = default,
@.firstname = N'Neil', @.lastname = N'Patel', @.address = N'28 my street, @.town
= N'mytown', @.county = N'mycounty', @.postcode = N'mypostcd', @.telno =
N'01234567890, @.email = N'neil_pat@.email.com', @.username = N'neilpat',
@.password = N'neil'
I really can't see where this is going wrong
Anny suggestions welcomeMaybe the parameters already exist in the SqlParameterCollection (Look at
where SqlCommand1 is created).
Have you tried... (Warning I am not a .net programmer so my syntax may be
off).
SqlCommand1.Parameters("@.firstname").Value = txtFirstname.Text
SqlCommand1.Parameters("@.lastname").Value = txtSurname.Text
SqlCommand1.Parameters("@.address").Value = txtAddress.Text
SqlCommand1.Parameters("@.town").Value = txtTown.Text
SqlCommand1.Parameters("@.county").Value = txtCounty.Text
SqlCommand1.Parameters("@.postcode").Value = txtPostcode.Text
SqlCommand1.Parameters("@.telno").Value = txtTelno.Text
SqlCommand1.Parameters("@.email").Value = txtEmail.Text
SqlCommand1.Parameters("@.username").Value = txtUsername.Text
SqlCommand1.Parameters("@.password").Value = txtPassword.Text
"neil_pat" wrote:

> Error:Too Many Arguments Specified
> Posted: 03-03-2005 05:03 AM
> Hi,
> I'm pretty new to ASP.NET so I'm having some problems
> I'm trying to insert a users registration info into the DB using a Stored
> Proc but for some reason it won't work
> When i run the Sproc in query analyser it seems to be ok with it but when
i
> run the asp pages it lets me enter the info then when i submit it gives me
> the Too many Arguments Specified error
> here's the code for the proc
> CREATE PROCEDURE [dbo].[Toregister]
> (
> @.firstname varchar(20),
> @.lastname varchar(20),
> @.address varchar(20),
> @.town varchar(20),
> @.county varchar(20),
> @.postcode varchar(10),
> @.telno varchar(10),
> @.email varchar(20),
> @.username varchar(20),
> @.password varchar(20)
> )
> AS
> insert into t_user
> (firstname,lastname,address,town,county,
postcode,telno,email,username,pass
word)
> values
> (@.firstname,@.lastname,@.address,@.town,@.co
unty,@.postcode,@.telno,@.email,@.user
name,@.password)
> GO
> and here's the code in my aspx file for adding the parameters
> Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
> System.EventArgs) Handles Save.Click
>
> SqlCommand1.Parameters.Add("@.firstname", txtFirstname.Text)
> SqlCommand1.Parameters.Add("@.lastname", txtSurname.Text)
> SqlCommand1.Parameters.Add("@.address", txtAddress.Text)
> SqlCommand1.Parameters.Add("@.town", txtTown.Text)
> SqlCommand1.Parameters.Add("@.county", txtCounty.Text)
> SqlCommand1.Parameters.Add("@.postcode", txtPostcode.Text)
> SqlCommand1.Parameters.Add("@.telno", txtTelno.Text)
> SqlCommand1.Parameters.Add("@.email", txtEmail.Text)
> SqlCommand1.Parameters.Add("@.username", txtUsername.Text)
> SqlCommand1.Parameters.Add("@.password", txtPassword.Text)
> SqlConnection1.Open()
> SqlCommand1.ExecuteNonQuery()
> End Sub
>
> and just for good measure here's the result from the DB profiler when I ru
n
> the asp
> exec [Toregister] @.firstname = default, @.lastname = default, @.address =
> default, @.town = default, @.county = default, @.postcode = default, @.telno =
> default, @.email = default, @.username = default, @.password = default,
> @.firstname = N'Neil', @.lastname = N'Patel', @.address = N'28 my street, @.to
wn
> = N'mytown', @.county = N'mycounty', @.postcode = N'mypostcd', @.telno =
> N'01234567890, @.email = N'neil_pat@.email.com', @.username = N'neilpat',
> @.password = N'neil'
> I really can't see where this is going wrong
> Anny suggestions welcome
>

error: too many arguments specified

Error:Too Many Arguments Specified
Posted: 03-03-2005 05:03 AM
Hi,
I'm pretty new to ASP.NET so I'm having some problems
I'm trying to insert a users registration info into the DB using a Stored
Proc but for some reason it won't work
When i run the Sproc in query analyser it seems to be ok with it but when i
run the asp pages it lets me enter the info then when i submit it gives me
the Too many Arguments Specified error
here's the code for the proc
CREATE PROCEDURE [dbo].[Toregister]
(
@.firstname varchar(20),
@.lastname varchar(20),
@.address varchar(20),
@.town varchar(20),
@.county varchar(20),
@.postcode varchar(10),
@.telno varchar(10),
@.email varchar(20),
@.username varchar(20),
@.password varchar(20)
)
AS
insert into t_user
(firstname,lastname,address,town,county,
postcode,telno,email,username,passwo
rd)
values
(@.firstname,@.lastname,@.address,@.town,@.co
unty,@.postcode,@.telno,@.email,@.userna
me,@.password)
GO
and here's the code in my aspx file for adding the parameters
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Save.Click
SqlCommand1.Parameters.Add("@.firstname", txtFirstname.Text)
SqlCommand1.Parameters.Add("@.lastname", txtSurname.Text)
SqlCommand1.Parameters.Add("@.address", txtAddress.Text)
SqlCommand1.Parameters.Add("@.town", txtTown.Text)
SqlCommand1.Parameters.Add("@.county", txtCounty.Text)
SqlCommand1.Parameters.Add("@.postcode", txtPostcode.Text)
SqlCommand1.Parameters.Add("@.telno", txtTelno.Text)
SqlCommand1.Parameters.Add("@.email", txtEmail.Text)
SqlCommand1.Parameters.Add("@.username", txtUsername.Text)
SqlCommand1.Parameters.Add("@.password", txtPassword.Text)
SqlConnection1.Open()
SqlCommand1.ExecuteNonQuery()
End Sub
and just for good measure here's the result from the DB profiler when I run
the asp
exec [Toregister] @.firstname = default, @.lastname = default, @.address =
default, @.town = default, @.county = default, @.postcode = default, @.telno =
default, @.email = default, @.username = default, @.password = default,
@.firstname = N'Neil', @.lastname = N'Patel', @.address = N'28 my street, @.town
= N'mytown', @.county = N'mycounty', @.postcode = N'mypostcd', @.telno =
N'01234567890, @.email = N'neil_pat@.email.com', @.username = N'neilpat',
@.password = N'neil'
I really can't see where this is going wrong
Anny suggestions welcomeActually there are only 10 parameters to be passed.
exec [Toregister] @.firstname = default, @.lastname = default, @.address =
default, @.town = default, @.county = default, @.postcode = default,
@.telno
= default, @.email = default, @.username = default, @.password = default,
@.firstname = N'Neil', @.lastname = N'Patel', @.address = N'28 my street,
@.town = N'mytown', @.county = N'mycounty', @.postcode = N'mypostcd',
@.telno = N'01234567890, @.email = N'neil_...@.email.com', @.username =
N'neilpat', @.password = N'neil'
You passed more than 10
Madhivanan|||Look at the profiler trace. It is passing all the parameters as default
folowed by all the parameters with values - hence 20 parameters instead of 1
0.
I suspect when you build the command you are getting the parameters for the
sp then adding them again.
"neil_pat" wrote:

> Error:Too Many Arguments Specified
> Posted: 03-03-2005 05:03 AM
> Hi,
> I'm pretty new to ASP.NET so I'm having some problems
> I'm trying to insert a users registration info into the DB using a Stored
> Proc but for some reason it won't work
> When i run the Sproc in query analyser it seems to be ok with it but when
i
> run the asp pages it lets me enter the info then when i submit it gives me
> the Too many Arguments Specified error
> here's the code for the proc
> CREATE PROCEDURE [dbo].[Toregister]
> (
> @.firstname varchar(20),
> @.lastname varchar(20),
> @.address varchar(20),
> @.town varchar(20),
> @.county varchar(20),
> @.postcode varchar(10),
> @.telno varchar(10),
> @.email varchar(20),
> @.username varchar(20),
> @.password varchar(20)
> )
> AS
> insert into t_user
> (firstname,lastname,address,town,county,
postcode,telno,email,username,pass
word)
> values
> (@.firstname,@.lastname,@.address,@.town,@.co
unty,@.postcode,@.telno,@.email,@.user
name,@.password)
> GO
> and here's the code in my aspx file for adding the parameters
> Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
> System.EventArgs) Handles Save.Click
>
> SqlCommand1.Parameters.Add("@.firstname", txtFirstname.Text)
> SqlCommand1.Parameters.Add("@.lastname", txtSurname.Text)
> SqlCommand1.Parameters.Add("@.address", txtAddress.Text)
> SqlCommand1.Parameters.Add("@.town", txtTown.Text)
> SqlCommand1.Parameters.Add("@.county", txtCounty.Text)
> SqlCommand1.Parameters.Add("@.postcode", txtPostcode.Text)
> SqlCommand1.Parameters.Add("@.telno", txtTelno.Text)
> SqlCommand1.Parameters.Add("@.email", txtEmail.Text)
> SqlCommand1.Parameters.Add("@.username", txtUsername.Text)
> SqlCommand1.Parameters.Add("@.password", txtPassword.Text)
> SqlConnection1.Open()
> SqlCommand1.ExecuteNonQuery()
> End Sub
>
> and just for good measure here's the result from the DB profiler when I ru
n
> the asp
> exec [Toregister] @.firstname = default, @.lastname = default, @.address =
> default, @.town = default, @.county = default, @.postcode = default, @.telno =
> default, @.email = default, @.username = default, @.password = default,
> @.firstname = N'Neil', @.lastname = N'Patel', @.address = N'28 my street, @.to
wn
> = N'mytown', @.county = N'mycounty', @.postcode = N'mypostcd', @.telno =
> N'01234567890, @.email = N'neil_pat@.email.com', @.username = N'neilpat',
> @.password = N'neil'
> I really can't see where this is going wrong
> Anny suggestions welcome
>|||Could you just explain that? iDo you mean that its counting each one
seprately i.e. 20 instead of 10?
How do i rectify this?
"Madhivanan" wrote:

> Actually there are only 10 parameters to be passed.
> exec [Toregister] @.firstname = default, @.lastname = default, @.address =
> default, @.town = default, @.county = default, @.postcode = default,
> @.telno
> = default, @.email = default, @.username = default, @.password = default,
> @.firstname = N'Neil', @.lastname = N'Patel', @.address = N'28 my street,
> @.town = N'mytown', @.county = N'mycounty', @.postcode = N'mypostcd',
> @.telno = N'01234567890, @.email = N'neil_...@.email.com', @.username =
> N'neilpat', @.password = N'neil'
> You passed more than 10
> Madhivanan
>|||Sorry I'm a bit of a beginer.
Which bit of my code do i need to edit to stop this?
"Nigel Rivett" wrote:
> Look at the profiler trace. It is passing all the parameters as default
> folowed by all the parameters with values - hence 20 parameters instead of
10.
> I suspect when you build the command you are getting the parameters for th
e
> sp then adding them again.
> "neil_pat" wrote:
>|||Have a look at
http://www.mindsdoor.net/DOTNET/DotNetDBAccess.html
It gives a database access layer and includes creation of all the objects.
I suspect you are binding sqlcommand1 to the sp before adding the parameters
.
"neil_pat" wrote:
> Sorry I'm a bit of a beginer.
> Which bit of my code do i need to edit to stop this?
> "Nigel Rivett" wrote:
>|||Remove all the fields with default values
Madhivanan|||I managed to sort it out now.
VS.net was adding default parameters automatically which i removed and it
works fine now
Thanks
"neil_pat" wrote:

> Error:Too Many Arguments Specified
> Posted: 03-03-2005 05:03 AM
> Hi,
> I'm pretty new to ASP.NET so I'm having some problems
> I'm trying to insert a users registration info into the DB using a Stored
> Proc but for some reason it won't work
> When i run the Sproc in query analyser it seems to be ok with it but when
i
> run the asp pages it lets me enter the info then when i submit it gives me
> the Too many Arguments Specified error
> here's the code for the proc
> CREATE PROCEDURE [dbo].[Toregister]
> (
> @.firstname varchar(20),
> @.lastname varchar(20),
> @.address varchar(20),
> @.town varchar(20),
> @.county varchar(20),
> @.postcode varchar(10),
> @.telno varchar(10),
> @.email varchar(20),
> @.username varchar(20),
> @.password varchar(20)
> )
> AS
> insert into t_user
> (firstname,lastname,address,town,county,
postcode,telno,email,username,pass
word)
> values
> (@.firstname,@.lastname,@.address,@.town,@.co
unty,@.postcode,@.telno,@.email,@.user
name,@.password)
> GO
> and here's the code in my aspx file for adding the parameters
> Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
> System.EventArgs) Handles Save.Click
>
> SqlCommand1.Parameters.Add("@.firstname", txtFirstname.Text)
> SqlCommand1.Parameters.Add("@.lastname", txtSurname.Text)
> SqlCommand1.Parameters.Add("@.address", txtAddress.Text)
> SqlCommand1.Parameters.Add("@.town", txtTown.Text)
> SqlCommand1.Parameters.Add("@.county", txtCounty.Text)
> SqlCommand1.Parameters.Add("@.postcode", txtPostcode.Text)
> SqlCommand1.Parameters.Add("@.telno", txtTelno.Text)
> SqlCommand1.Parameters.Add("@.email", txtEmail.Text)
> SqlCommand1.Parameters.Add("@.username", txtUsername.Text)
> SqlCommand1.Parameters.Add("@.password", txtPassword.Text)
> SqlConnection1.Open()
> SqlCommand1.ExecuteNonQuery()
> End Sub
>
> and just for good measure here's the result from the DB profiler when I ru
n
> the asp
> exec [Toregister] @.firstname = default, @.lastname = default, @.address =
> default, @.town = default, @.county = default, @.postcode = default, @.telno =
> default, @.email = default, @.username = default, @.password = default,
> @.firstname = N'Neil', @.lastname = N'Patel', @.address = N'28 my street, @.to
wn
> = N'mytown', @.county = N'mycounty', @.postcode = N'mypostcd', @.telno =
> N'01234567890, @.email = N'neil_pat@.email.com', @.username = N'neilpat',
> @.password = N'neil'
> I really can't see where this is going wrong
> Anny suggestions welcome
>|||I've sorted it now
VS.Net was adding default parameters automatically. I've removed them and it
works fine now
"Nigel Rivett" wrote:
> Have a look at
> http://www.mindsdoor.net/DOTNET/DotNetDBAccess.html
> It gives a database access layer and includes creation of all the objects.
> I suspect you are binding sqlcommand1 to the sp before adding the paramete
rs.
> "neil_pat" wrote:
>