Newbie question on parameters to stored procedure

Hi All,
I had posted this question in the vb.net news group and don't seem to be
getting anywhere. This question may be more apt for this group, I guess. I
have pasted the post below.
****************************************
********************
I am trying to pass parameters to a stored procedure from vb.net code and
fails with the error that the variable is not a parameter to the stored
procedure
Here is the vb.net code
----
--
command = New SqlCommand("sp_updateProducts")
command.Connection = connection
command.CommandType = CommandType.StoredProcedure
command.Transaction = trans
command.Parameters.Add(New SqlParameter("@.pMacId",
SqlDbType.Char))
command.Parameters.Add(New SqlParameter("@.pProdDt",
SqlDbType.DateTime))
command.Parameters.Add(New SqlParameter("@.pProdInfo",
SqlDbType.VarChar))
command.Parameters(0).Direction = ParameterDirection.Input
command.Parameters(1).Direction = ParameterDirection.Input
command.Parameters(2).Direction = ParameterDirection.Input
command.Parameters(0).Value = machineID
command.Parameters(1).Value = updateDate
command.Parameters(2).Value = joinStr
command.ExecuteNonQuery()
----
--
Here is the stored procedure code:
----
--
CREATE PROCEDURE dbo.sp_updateProducts
(
@.pMachineId AS CHAR(6),
@.pProdDt AS DATETIME,
@.pProdinfo VARCHAR(4000)
)
AS
BEGIN
.....
.....
.....
END
GO
----
--
The error message occurs on ExecuteNonQuery() and says that @.pMacId is not a
prameter to the stored procedure sp_updateProducts
I may be missing something very naive! Could anybody suggest the cause of
the error?
Thanks
kd@.pMacId is not a parameter. Ther parameter is called @.pMachineId.
Do NOT use the "sp_" prefix for stored procs (unless you want to create
system procs in Master - something that I wouldn't recommend on a production
system).
"sp_" denotes a system proc and if you create procs with this name outside
Master they may not execute and their performance will suffer from recompile
s.
David Portas
SQL Server MVP
--|||Hi Kd -
The string you specify in the VB.Net call for the name of the parameter
should match the name of the parameter as specified in the stored
procedure.
In the VB.Net code you create a paramter called @.pMacId, but in the
procedure it's named @.pMachineId. Make sure they are given the same name.
BTW - considering changing your procedure name to something like
usp_UpdateProducts. With a prefix of sp_, SQL Server will look first to
the master database for the procedure - slowing your system down a bit.
HTH...
Joe Webb
SQL Server MVP
~~~
Get up to speed quickly with SQLNS
http://www.amazon.com/exec/obidos/t...il/-/0972688811
kd wrote:
> Hi All,
> I had posted this question in the vb.net news group and don't seem to be
> getting anywhere. This question may be more apt for this group, I guess. I
> have pasted the post below.
> ****************************************
********************
> I am trying to pass parameters to a stored procedure from vb.net code and
> fails with the error that the variable is not a parameter to the stored
> procedure
> Here is the vb.net code
> ----
--
> command = New SqlCommand("sp_updateProducts")
> command.Connection = connection
> command.CommandType = CommandType.StoredProcedure
> command.Transaction = trans
> command.Parameters.Add(New SqlParameter("@.pMacId",
> SqlDbType.Char))
> command.Parameters.Add(New SqlParameter("@.pProdDt",
> SqlDbType.DateTime))
> command.Parameters.Add(New SqlParameter("@.pProdInfo",
> SqlDbType.VarChar))
> command.Parameters(0).Direction = ParameterDirection.Input
> command.Parameters(1).Direction = ParameterDirection.Input
> command.Parameters(2).Direction = ParameterDirection.Input
> command.Parameters(0).Value = machineID
> command.Parameters(1).Value = updateDate
> command.Parameters(2).Value = joinStr
> command.ExecuteNonQuery()
> ----
--
> Here is the stored procedure code:
> ----
--
> CREATE PROCEDURE dbo.sp_updateProducts
> (
> @.pMachineId AS CHAR(6),
> @.pProdDt AS DATETIME,
> @.pProdinfo VARCHAR(4000)
> )
> AS
> BEGIN
> .....
> .....
> .....
> END
> GO
> ----
--
> The error message occurs on ExecuteNonQuery() and says that @.pMacId is not
a
> prameter to the stored procedure sp_updateProducts
> I may be missing something very naive! Could anybody suggest the cause of
> the error?
> Thanks
> kd
>|||kd
I think the problem is you are refering to @.pMacId as a parameter of the SP
but actually a name of parameter is @.pMachineId (see CREATE PROC ...)
Am I right?
"kd" <kd@.discussions.microsoft.com> wrote in message
news:C2C6C755-604B-4FED-B113-13D2F7D345C9@.microsoft.com...
> Hi All,
> I had posted this question in the vb.net news group and don't seem to be
> getting anywhere. This question may be more apt for this group, I guess. I
> have pasted the post below.
> ****************************************
********************
> I am trying to pass parameters to a stored procedure from vb.net code and
> fails with the error that the variable is not a parameter to the stored
> procedure
> Here is the vb.net code
> ----
--
> command = New SqlCommand("sp_updateProducts")
> command.Connection = connection
> command.CommandType = CommandType.StoredProcedure
> command.Transaction = trans
> command.Parameters.Add(New SqlParameter("@.pMacId",
> SqlDbType.Char))
> command.Parameters.Add(New SqlParameter("@.pProdDt",
> SqlDbType.DateTime))
> command.Parameters.Add(New SqlParameter("@.pProdInfo",
> SqlDbType.VarChar))
> command.Parameters(0).Direction = ParameterDirection.Input
> command.Parameters(1).Direction = ParameterDirection.Input
> command.Parameters(2).Direction = ParameterDirection.Input
> command.Parameters(0).Value = machineID
> command.Parameters(1).Value = updateDate
> command.Parameters(2).Value = joinStr
> command.ExecuteNonQuery()
> ----
--
> Here is the stored procedure code:
> ----
--
> CREATE PROCEDURE dbo.sp_updateProducts
> (
> @.pMachineId AS CHAR(6),
> @.pProdDt AS DATETIME,
> @.pProdinfo VARCHAR(4000)
> )
> AS
> BEGIN
> .....
> .....
> .....
> END
> GO
> ----
--
> The error message occurs on ExecuteNonQuery() and says that @.pMacId is not
a
> prameter to the stored procedure sp_updateProducts
> I may be missing something very naive! Could anybody suggest the cause of
> the error?
> Thanks
> kd
>|||Hi,
But, I thought @.pMacId is a value name, which could differ, in the call and
the definition, just like how it is with vb.net procedures and functions!
And thanks for the advice on the usage of "sp_"
kd
"David Portas" wrote:

> @.pMacId is not a parameter. Ther parameter is called @.pMachineId.
> Do NOT use the "sp_" prefix for stored procs (unless you want to create
> system procs in Master - something that I wouldn't recommend on a producti
on
> system).
> "sp_" denotes a system proc and if you create procs with this name outside
> Master they may not execute and their performance will suffer from recompi
les.
> --
> David Portas
> SQL Server MVP
> --
>|||Hi David,
Changing the parameter name to @.pMachineId fixed the error.
Thanks
kd
"David Portas" wrote:

> @.pMacId is not a parameter. Ther parameter is called @.pMachineId.
> Do NOT use the "sp_" prefix for stored procs (unless you want to create
> system procs in Master - something that I wouldn't recommend on a producti
on
> system).
> "sp_" denotes a system proc and if you create procs with this name outside
> Master they may not execute and their performance will suffer from recompi
les.
> --
> David Portas
> SQL Server MVP
> --
>|||Hi Joe,
Thanks for the solution
kd
"Joe Webb" wrote:

> Hi Kd -
> The string you specify in the VB.Net call for the name of the parameter
> should match the name of the parameter as specified in the stored
> procedure.
> In the VB.Net code you create a paramter called @.pMacId, but in the
> procedure it's named @.pMachineId. Make sure they are given the same name.
> BTW - considering changing your procedure name to something like
> usp_UpdateProducts. With a prefix of sp_, SQL Server will look first to
> the master database for the procedure - slowing your system down a bit.
> HTH...
> Joe Webb
> SQL Server MVP
> ~~~
> Get up to speed quickly with SQLNS
> http://www.amazon.com/exec/obidos/t...il/-/0972688811
>
> kd wrote:
>|||Hi Uri,
Thanks for the solution
kd
"Uri Dimant" wrote:

> kd
> I think the problem is you are refering to @.pMacId as a parameter of the
SP
> but actually a name of parameter is @.pMachineId (see CREATE PROC ...)
> Am I right?
>
> "kd" <kd@.discussions.microsoft.com> wrote in message
> news:C2C6C755-604B-4FED-B113-13D2F7D345C9@.microsoft.com...
> --
> --
> --
> --
> a
>
>

Newbie question - disk defrag

Hi all
Please ignore my lack of knowledge, im new to sql...
I would like to defrag my sql databases and have found several sources on
the net that recommend using the command DBCC INDEXDEFRAG.
When I try and run this from the command prompt i get DBCC is not a
recognised command? Do I need something additional installed or do i not
run this from a command prompt?
Many thanks
This is a SQL Server command, not an operating system command. So you run it from, for instance,
Query analyzer, SQL Server Management Studio, OSQL, SQLCMD etc.
Also, make sure you read this first:
http://msdn.microsoft.com/library/en...server2000.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Si" <Si@.discussions.microsoft.com> wrote in message
news:B964ED96-1F06-4801-BBF9-43A7867D4D6B@.microsoft.com...
> Hi all
> Please ignore my lack of knowledge, im new to sql...
> I would like to defrag my sql databases and have found several sources on
> the net that recommend using the command DBCC INDEXDEFRAG.
> When I try and run this from the command prompt i get DBCC is not a
> recognised command? Do I need something additional installed or do i not
> run this from a command prompt?
> Many thanks
|||Si wrote:
> Hi all
> Please ignore my lack of knowledge, im new to sql...
> I would like to defrag my sql databases and have found several sources on
> the net that recommend using the command DBCC INDEXDEFRAG.
> When I try and run this from the command prompt i get DBCC is not a
> recognised command? Do I need something additional installed or do i not
> run this from a command prompt?
> Many thanks
DBCC is a T-SQL command, which you execute within Query
Analyzer/Management Studio...
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Newbie question - disk defrag

Hi all
Please ignore my lack of knowledge, im new to sql...
I would like to defrag my sql databases and have found several sources on
the net that recommend using the command DBCC INDEXDEFRAG.
When I try and run this from the command prompt i get DBCC is not a
recognised command? Do I need something additional installed or do i not
run this from a command prompt?
Many thanksThis is a SQL Server command, not an operating system command. So you run it
from, for instance,
Query analyzer, SQL Server Management Studio, OSQL, SQLCMD etc.
Also, make sure you read this first:
http://msdn.microsoft.com/library/e...000.a
sp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Si" <Si@.discussions.microsoft.com> wrote in message
news:B964ED96-1F06-4801-BBF9-43A7867D4D6B@.microsoft.com...
> Hi all
> Please ignore my lack of knowledge, im new to sql...
> I would like to defrag my sql databases and have found several sources on
> the net that recommend using the command DBCC INDEXDEFRAG.
> When I try and run this from the command prompt i get DBCC is not a
> recognised command? Do I need something additional installed or do i no
t
> run this from a command prompt?
> Many thanks|||Si wrote:
> Hi all
> Please ignore my lack of knowledge, im new to sql...
> I would like to defrag my sql databases and have found several sources on
> the net that recommend using the command DBCC INDEXDEFRAG.
> When I try and run this from the command prompt i get DBCC is not a
> recognised command? Do I need something additional installed or do i no
t
> run this from a command prompt?
> Many thanks
DBCC is a T-SQL command, which you execute within Query
Analyzer/Management Studio...
Tracy McKibben
MCDBA
http://www.realsqlguy.comsql

Newbie question - disk defrag

Hi all
Please ignore my lack of knowledge, im new to sql...
I would like to defrag my sql databases and have found several sources on
the net that recommend using the command DBCC INDEXDEFRAG.
When I try and run this from the command prompt i get DBCC is not a
recognised command? Do I need something additional installed or do i not
run this from a command prompt?
Many thanksThis is a SQL Server command, not an operating system command. So you run it from, for instance,
Query analyzer, SQL Server Management Studio, OSQL, SQLCMD etc.
Also, make sure you read this first:
http://msdn.microsoft.com/library/en-us/dnsql2k/html/intlfeaturesinsqlserver2000.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Si" <Si@.discussions.microsoft.com> wrote in message
news:B964ED96-1F06-4801-BBF9-43A7867D4D6B@.microsoft.com...
> Hi all
> Please ignore my lack of knowledge, im new to sql...
> I would like to defrag my sql databases and have found several sources on
> the net that recommend using the command DBCC INDEXDEFRAG.
> When I try and run this from the command prompt i get DBCC is not a
> recognised command? Do I need something additional installed or do i not
> run this from a command prompt?
> Many thanks|||Si wrote:
> Hi all
> Please ignore my lack of knowledge, im new to sql...
> I would like to defrag my sql databases and have found several sources on
> the net that recommend using the command DBCC INDEXDEFRAG.
> When I try and run this from the command prompt i get DBCC is not a
> recognised command? Do I need something additional installed or do i not
> run this from a command prompt?
> Many thanks
DBCC is a T-SQL command, which you execute within Query
Analyzer/Management Studio...
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Newbie question

I'm new to XML and .NET in general and I'm lost in the maze of all the
various Xml classes. What I want to do is programatically create an xml
structure and store it in a database column, for example :-
<FlavourID>0</FlavourID>
<FormID>Form1</FormID>
<ItemKey>EmployeeName</ItemKey>
<PropertyKey>PropertyDataHere</PropertyKey>
<Value>ValueHere</Value>
is one such structure I might want to store in a database column. The reason
I dont store this in 5 columns is that the structure itself is variable. My
problems are :-
1) I dont know how to programatically create a structure - I'm confused with
XmlDocument, XmlDataDocument, XmlNode, XmlTextWriter etc. I basically want
to end up with one single datatype that can be stored in a single column in
a database, which brings me to the second problem:
2) I dont know the best way to store this in a database column. I know there
is some Xml support in SqlServer 2000 and more in 2005, but with something
as simple and small as this maybe converting and storing as Text or Varchar
would be sufficient.
Any help very gratefully appreciated !
"JezB" <jezbroadsword@.blueyonder.co.uk> wrote in message
news:Oxp2glBaEHA.524@.TK2MSFTNGP09.phx.gbl...
[snip]
> 1) I dont know how to programatically create a structure - I'm confused
> with
> XmlDocument, XmlDataDocument, XmlNode, XmlTextWriter etc. I basically want
> to end up with one single datatype that can be stored in a single column
> in
> a database, which brings me to the second problem:
Here are two good articles on Xml in .Net:
http://msdn.microsoft.com/library/de...ml03172004.asp
http://support.softartisans.com/kbview.aspx?ID=673

> 2) I dont know the best way to store this in a database column. I know
> there
> is some Xml support in SqlServer 2000 and more in 2005, but with something
> as simple and small as this maybe converting and storing as Text or
> Varchar
> would be sufficient.
In SQL Server 2000 you would store it as a text column.
Bryant
sql

Newbie question

I have built a database and VB.net app and it's working great...
The question is how to accomplish that number 1 is shown like this 0001
(with zeros in front) in the databse.
Thanks.
That's really a job for the front-end. However, you can try:
select
replace (str (MyCol, 4), ' ', '0')
from
MyTable
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
..
"Morx" <morx@.gawab.com> wrote in message news:cgche5$lv4$1@.ls219.htnet.hr...
I have built a database and VB.net app and it's working great...
The question is how to accomplish that number 1 is shown like this 0001
(with zeros in front) in the databse.
Thanks.

Newbie question

I have built a database and VB.net app and it's working great...
The question is how to accomplish that number 1 is shown like this 0001
(with zeros in front) in the databse.
Thanks.That's really a job for the front-end. However, you can try:
select
replace (str (MyCol, 4), ' ', '0')
from
MyTable
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Morx" <morx@.gawab.com> wrote in message news:cgche5$lv4$1@.ls219.htnet.hr...
I have built a database and VB.net app and it's working great...
The question is how to accomplish that number 1 is shown like this 0001
(with zeros in front) in the databse.
Thanks.

Newbie question

I have built a database and VB.net app and it's working great...
The question is how to accomplish that number 1 is shown like this 0001
(with zeros in front) in the databse.
Thanks.That's really a job for the front-end. However, you can try:
select
replace (str (MyCol, 4), ' ', '0')
from
MyTable
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Morx" <morx@.gawab.com> wrote in message news:cgche5$lv4$1@.ls219.htnet.hr...
I have built a database and VB.net app and it's working great...
The question is how to accomplish that number 1 is shown like this 0001
(with zeros in front) in the databse.
Thanks.sql

Newbie Q setting up MSDE

Hello. I've installed MSDE (Win2k machine, running asp.net on localhost),
and it says 'Not Connected' when I mouseover the icon in the system tray. I
opened the SQL Server service manager, and there are two empty fields-
Server and Services. Please, what do I put in these fields? Also, what
connection string would I use with MSDE. Thanks a lot
Justin Dutoit
hi Justin,
"Justin Dutoit" <anon@.anon.com> ha scritto nel messaggio
news:%23Gr48BqqEHA.3988@.tk2msftngp13.phx.gbl
> Hello. I've installed MSDE (Win2k machine, running asp.net on
> localhost), and it says 'Not Connected' when I mouseover the icon in
> the system tray. I opened the SQL Server service manager, and there
> are two empty fields- Server and Services. Please, what do I put in
> these fields? Also, what connection string would I use with MSDE.
> Thanks a lot
> Justin Dutoit
please verify, using the services applet (control panel->performance and
maintenance->administrative tools->services [on XP] , control
panel->administrative tools->services [on Win2k]) that your MSDE instance is
currently up and running...
you can then try typing in the service name of your MSDE instance to manage
it...
MSDE installs by default disabling network protocols and this can be an
issue for some ODBC related functions and, sometime, the SQL Server
instances are not enlisted by the Service Manager, as reported in
http://support.microsoft.com/default...b;EN-US;814132
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Newbie needing help with joining 2 fields

I have a sql 2005 dev ed running an application developed in vs.net 2005 C# I have several gridviews which have a field call first name and a field called lastname I need to put both the firstname and lastname in the same cell or colum row. I do not now how to join these. Can some one help me with a SQL query string that will do this for me.You could do SELECT FirstName + ' ' + LastName FROM YourTable|||SELECT FirstName + ' ' + LastName AS FullName
FROM Employees

Newbie needing help ASAP with width issue in sql report.

I am building a Sql Report in VS.net 2006 I have run into an issue I have 258 colums it am using the wizard table generator in design mode. the problem is the screen will on allow 160 inches wide I need to mak this larger to 300 inches the purpose of the report is to sort data and them save to csv format is there a better way or can some on tell me how to increase the size of the width the properties tab will not allow me to explain pass 160 inches. Some one please help me thanksWhy not tackle the job another way ? Have a look at http://www.codeproject.com/useritems/filehelpers.asp "An easy to use .NET library to read/write strong typed data from fileswith fixed length or delimited records (CSV). Also has support toimport/export data from different data storages (Excel, Acces,SqlServer, MySql)"|||The issue I am having is a task was given to me to pull data from an invoice module out of SQL and creat a 288 field output of data in a csv format. The problem is we on have about 100 fields data the rest have to be blank and all this has to be in an order so . I was going to build a report in th sql reports in my business intell create the headers for all 288 files and create the expression in the correct placement create several sort paramerters then I was going to just save as csv format this would create the csv file with the correct order and blank fields separated by the commas. the problem is I dont know how to creat empty fields on an output to csv.|||

Suppose your table FRED has three fields A, C and D and you want to output 5, use:

SELECT A, ' ' AS B, C, ' ' AS D, E FROM FRED

That will create 2 empty fields - you can readily extend the technique to create 188 blank fields.

|||

Thank youy this makes sense. The only question is I may need it to look like this

"john", "Smith","","","","","","","404","555-5555","123 Main Street","","completed",

If I under stand the your code I can select A,B,' ' AS C,' ' AS D,' ' AS E, ' ' AS F, ' ' AS G, ' ' AS H,I,J,K,' ' AS L,M FROM TABLE

|||

>>The only question is I may need it to look like this: "john", "Smith","","","","","","","404","555-5555","123 Main Street","","completed",
The quoting takes places aroung each value, whether it is occupied or empty.

>>If I under stand the your code I can select A,B,' ' AS C,' ' AS D,' ' AS E, ' ' AS F, ' ' AS G, ' ' AS H,I,J,K,' ' AS L,M FROM TABLE

Yes! I put a space between the quote marks for clarity; you will not need to do this.

Newbie login failed

Oh great ASP.NET gods I <grovel>beseech</grovel> ye...

I'm sorry if this has been asked and answered elsewhere. I'm a complete newbie and I need a bit of hand holding...

I've tried setting up a basic databound datagrid using the MSDE SQL server. I get a login Failed error when I try to display the page in the browser inside VS.NET 2003. I don't get any errors until I actually try to preview the page in the browser. Sorry for the long post below... which is the full error message. What, from what you see, can I do to fix this?

Please be forewarned that I have never used MSDE before or SQL server for that matter (I'm a MySQL guy making the switch!). For that reason I am a little confused about this error because I thought everything was ok because in Visual Studio the DataAdapter previewed with no errors and there were no build errors. I was able to drag the authors table over from the Northwind db, create a dataset and fill it from its dataAdapter.

Server Error in '/asp-practice' Application.

Login failed for user 'PHILIP\ASPNET'.

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: Login failed for user 'PHILIP\ASPNET'.

Source Error:

Line 134: 'Put user code to initialize the page hereLine 135: If Not Page.IsPostBack ThenLine 136: SqlDataAdapter1.Fill(AuthorsDS1)Line 137: DataBind()Line 138:

Source File: c:\inetpub\wwwroot\asp-practice\WebForm1.aspx.vb Line: 136

Stack Trace:

[SqlException: Login failed for user 'PHILIP\ASPNET'.] System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction) +474 System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) +372 System.Data.SqlClient.SqlConnection.Open() +384 System.Data.Common.DbDataAdapter.QuietOpen(IDbConnection connection, ConnectionState& originalState) +44 System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +304 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +77 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) +38 asp_practice.WebForm1.Page_Load(Object sender, EventArgs e) in c:\inetpub\wwwroot\asp-practice\WebForm1.aspx.vb:136 System.Web.UI.Control.OnLoad(EventArgs e) +67 System.Web.UI.Control.LoadRecursive() +35 System.Web.UI.Page.ProcessRequestMain() +750

Version Information: Microsoft .NET Framework Version:1.1.4322.2032; ASP.NET Version:1.1.4322.2032your connection string appears to be set to use integrated authentication (i.e. the credentials of the current user)

while developing, the current user is you: 'PHILIP\yourLogin'

when the web page runs however, the user account that is used is ASPNET
ie: ('PHILIP\ASPNET')

the ASPNET account needs to be given access to your SQL database.|||I have the same error, but I use SQLExpress Server.
It hasn't any GUI.
How can I set ASPNET user for SQLExpress server and which password have I to use?

Newbie Installation Problem

I'm using Visual Studio.Net and have been unable to access SQL Server
Databases from the Server Explorer. Under SQL Servers I did see one from the
Office Business Contact Manager (BCM). So I down loaded the MSDE Sp3a file
and installed it from a command prompt, setup SAPWD="AStrongPassword"
(substituting an actual password within the quotes). The setup seemed to
proceed smoothly and then just ended. Now in VS.Net Server Exporer I see a
second SQL Server with simply the computer name, and when I click on the "+"
to expand it, a Login Box pops up with my username under Login. If have
tried typing the password I entered after the SAPWD parameter with my user
name and the following login names: sa, SA, Admin, Administrator, all to no
effect. I've entered the password with and without quotes. Each time I get
the server does not exist or access denied message. The only way I have been
able to tell if the MSDE is actually running is by looking under processes in
the task manager. There I do see an SQL2000 process. I've looked at the
knowledge base describing connection problems, and most of those seemed to be
related to access from other computers, and it really isn't clear to me which
of the problems they describe might apply to a local machine by itself. Is
there a different login name I should be using? Many of the wizards in
Visual Studio only work with MS SQL, so its a real hassle not having it
installed. Any suggestions would be greatly appreciated.
hi,
JerryKogan wrote:
> I'm using Visual Studio.Net and have been unable to access SQL Server
> Databases from the Server Explorer. Under SQL Servers I did see one
> from the Office Business Contact Manager (BCM). So I down loaded the
> MSDE Sp3a file and installed it from a command prompt, setup
> SAPWD="AStrongPassword" (substituting an actual password within the
> quotes). The setup seemed to proceed smoothly and then just ended.
> Now in VS.Net Server Exporer I see a second SQL Server with simply
> the computer name, and when I click on the "+" to expand it, a Login
> Box pops up with my username under Login. If have tried typing the
> password I entered after the SAPWD parameter with my user name and
> the following login names: sa, SA, Admin, Administrator, all to no
> effect. I've entered the password with and without quotes. Each
> time I get the server does not exist or access denied message. The
> only way I have been able to tell if the MSDE is actually running is
> by looking under processes in the task manager. There I do see an
> SQL2000 process. I've looked at the knowledge base describing
> connection problems, and most of those seemed to be related to access
> from other computers, and it really isn't clear to me which of the
> problems they describe might apply to a local machine by itself. Is
> there a different login name I should be using? Many of the wizards
> in Visual Studio only work with MS SQL, so its a real hassle not
> having it installed. Any suggestions would be greatly appreciated.
MSDE installs by default allowing only tusted WinNT authenticated
connections.. you have to provide the additional
SECURITYMODE=SQL
parameter to the setup.exe boostrap installer to allow SQL Server (standard)
authenticated connections or, after install, to modify the Windows registry
as foillowing:
(named instance)
HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\InstanceName\MSSQLServer
LoginMode=2
(default instance, your case)
HKLM\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer
LoginMode=2
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Newbie Install question

HI,
I am new to Sql Reporting Service and I have a Newbie question. I have VS.Net installed on one Computer and SQL Server installed on a server. When I install SQL RS, Do I install it on both, my development computer and the SQL Server (windows Server)? And what VER can I put on both computers (if I have to install it on both)? Should I put Enterprise on the Server (Sql server and windows server) and Developer on the VS.Net computer? Or can I put Enterprise on both?
Thank you,
AndreWell, that depends on what you want to do. :) If you want the design app
then you have to install RS on the machine with VS. For the server
components you can install on any machine that has access to a SQL machine.
It is not a requirement that SQL and RS be on the same box.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"andre@.online.nospam" <andreonlinenospam@.discussions.microsoft.com> wrote in
message news:7D4A0AF2-17B5-447A-9042-FF56434C6353@.microsoft.com...
> HI,
> I am new to Sql Reporting Service and I have a Newbie question. I have
VS.Net installed on one Computer and SQL Server installed on a server. When
I install SQL RS, Do I install it on both, my development computer and the
SQL Server (windows Server)? And what VER can I put on both computers (if I
have to install it on both)? Should I put Enterprise on the Server (Sql
server and windows server) and Developer on the VS.Net computer? Or can I
put Enterprise on both?
> Thank you,
> Andre
>|||Thanks.
Is there any reason that I would need the Server components on my VS.Net machine? Or installing the Server components on the SQL server will do the trick?
FYI
I have both a Sql server running windows server 2003 and IIS, and a Development machine.|||There is no need to put the server components on the VS.Net machine. Some
companies will not allow IIS and SQL on the same box so they will put RS
server components on another machine.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"andre@.online.nospam" <andreonlinenospam@.discussions.microsoft.com> wrote in
message news:EAF2855F-B8D7-4074-999F-BB8EB7BE1E36@.microsoft.com...
> Thanks.
> Is there any reason that I would need the Server components on my VS.Net
machine? Or installing the Server components on the SQL server will do the
trick?
> FYI
> I have both a Sql server running windows server 2003 and IIS, and a
Development machine.
>

NewBie Here: Need Help Please (SQLCommand)

Hi Guyz, im currently studying asp.net, i need a code that retrive data from SQL database and post it using Label (web from), i dont know how to do it, need help... Thanks in advance !!!!

Hi There,

First of all import sqlclient namespace

using System.Data.SqlClient;

Read data from sql database and set value to Label1 and Label2

protectedvoid Page_Load(object sender,EventArgs e)

{

// Create database connection

SqlConnection connection =newSqlConnection("YourConnectionString");

// Create instance of command

SqlCommand command =newSqlCommand("Select Field1, Fields2 From TableName", connection);

// Open database connection

command.Connection.Open();

// Execute command and get datareader

System.Data.SqlClient.SqlDataReader datareader = command.ExecuteReader();

// Check whether or not datareader has soemthing

if (datareader.Read())

{

Label1.Text = datareader.GetString(0);// get Field1

Label2.Text = datareader.GetString(1);// get Field2

}

// Close database connection

command.Connection.Close();

}

Newbie here with a newbie error - Getting Database ... already exists.

Hi there

I sorry if I have placed this query in the wrong place.

I'm getting to grips with ASP.net 2, slowly but surely!

When i try to access my site which uses a Sql Server 2005 express DB i am receiving the following error:

Server Error in '/jarebu/site1' Application.

---

Database 'd:\hosting\member\asanga\App_Data\ASPNETDB.mdf' already exists.
Could not attach file 'd:\hosting\member\jarebu\site1\App_Data\ASPNETDB.MDF' as database 'ASPNETDB'.

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: Database 'd:\hosting\member\asanga\App_Data\ASPNETDB.mdf' already exists.
Could not attach file 'd:\hosting\member\jarebu\site1\App_Data\ASPNETDB.MDF' as database 'ASPNETDB'.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException (0x80131904): Database 'd:\hosting\member\asanga\App_Data\ASPNETDB.mdf' already exists.Could not attach file 'd:\hosting\member\jarebu\site1\App_Data\ASPNETDB.MDF' as database 'ASPNETDB'.] System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +735075 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838 System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +33 System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +628 System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170 System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +359 System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28 System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424 System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66 System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105 System.Data.SqlClient.SqlConnection.Open() +111 System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +121 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +137 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83 System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1770 System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +17 System.Web.UI.WebControls.DataBoundControl.PerformSelect() +149 System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70 System.Web.UI.WebControls.GridView.DataBind() +4 System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82 System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +69 System.Web.UI.Control.EnsureChildControls() +87 System.Web.UI.Control.PreRenderRecursiveInternal() +41 System.Web.UI.Control.PreRenderRecursiveInternal() +161 System.Web.UI.Control.PreRenderRecursiveInternal() +161 System.Web.UI.Control.PreRenderRecursiveInternal() +161 System.Web.UI.Control.PreRenderRecursiveInternal() +161 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1360

---

Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210

This is the connection string that I am using:

<connectionStrings>

<addname="ConnectionString"connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;Integrated Security=True;Initial Catalog=ASPNETDB;User Instance=True"providerName="System.Data.SqlClient"/>

</connectionStrings>

The database is definitly in the folder that the error message relates to.

What I'm finding confusing is that the connection string seems to be finding "aranga"s database.

Is it something daft?

Many thanks.

James

If you used the ASP.NET configiration manager it will of created the ASPNETDB.MDF database automatically in your App_Data folder but it will be hidden, so either un-hide and delete the previous one or rename your new DB and change your connection string.

Newbie help with joins

I am learning vb.net and ms sql (2000 evaluation version); I can't get my joins to work in Tsql:

select m.lname,m.fname,m.idno,m.payorno,p.payorno.p.payor name
from dbtemp..d_mbrdata m join dbtemp..d_payors p
on (m.payorno = p.payorno)

inner join query returns no records at all. left outerjoin returns the mbrdata fields, but the payors fields show as null. payorno in both tables is type integer and both tables definitely have matching records. I created index for both tables on payorno.

Does anyone have an idea what i am doing wrong here? Thanks.There is nothing wrong with query. If left join returns some data - problem is in second table (matching records).

Just run this query (without join):

select *
from dbtemp..d_mbrdata
where payorno in (select distinct payorno from dbtemp..d_payors)|||Yeah, from the way u'r describing u'r results, it seems there is no m.payorno that is equal to p.payorno.

Maybe u can try debuggin further by inserting a new record into both this tables with a "confirm" identical payorno and do your select join statement again.
It should return 1 joined record.|||Try running this and see what key values are returned:
select m.payorno, p.payorno
from dbtemp..d_mbrdata m
full outer join dbtemp..d_payors p on (m.payorno = p.payorno)
where mpayrono is null or p.payorno is null

blindman

Newbie Connection Problem

I'm having a problem writing aconnection string in .NET. I'm trying to use:

SqlConnection myConnection = new SqlConnection("Provider=SQLOLEDB;Data Source=mssql;Initial Catalog= cat;UserId=ddd;Password=ddd;");

which unfortunately is giving me:

SQL Server does not exist or access denied.
Description: An unhandled exception occurred during the execution ofthe current web request. Please review the stack trace for moreinformation about the error and where it originated in the code.

Can someone tell me what I'm doing wrong? Thanks in advance!

do you only have Windows Authentication setup for your SQL server?

or - if not -does the user you put in the connection string actually have an account in SQL Server?

|||We're not using Windows Authentication and there is a valid account being referenced in the SQL Server (I just put letters in my post to show what I was using)|||

mtarby:

SqlConnection myConnection = new SqlConnection("Provider=SQLOLEDB;Data Source=mssql;Initial Catalog= cat;UserId=ddd;Password=ddd;");

May be a silly question: can Provider key word be used in SqlConnection object? How about remove the Provider attribute from the connection string?

|||Without wanting to come across as too condescending, i'm taking it that your server is called mssql (and it's the only instance on your machine) and that the username and password are correct...?|||Yes, those are just placeholders I put in my post. The server, user name and password are all correct in my actual code|||

have you tried:

SqlConnection myConnection = new SqlConnection("Server= YourSrvr; Database=YourDB;UserId=ddd;Password=ddd;");|||I ended up adding an entry to my web.config file and its working fine now. Thanks for the help!

newbie confusion: file vs server

caution: this is not doubt a stupid newbie question...

In creating vs.net 2005 website, I can add a sql database to my project and a mdf file is created. I can create data providers against this file, etc, just as though it were a database in a sql server instance. I can deploy this dbf file to my finished web site.

Also, I can attach to a running instance of sql server 2005 express, and do exactly the same thing.

I can also take my mdf file created in step 1 above, and attach it to a running instance of sql server express.

Now, I have delt with access databases, and sql server 2000 databases, so this dual nature of sql server 2005 express confuses me a little.

Why would I ever need to use a server instance of sql server 2005 when I can use a file based data file in my web apps? Is there an advantage to one or the other?

I had a thought that when using the file based method, I was actually still using the server based stuff, which would explain why the sql server express notification bubble pops up when I debug on the dev machine.

In any case could someone explain the difference and should I install sql server 2005 express on my deployment server?

thanks.

Access is a database SQL Server Express is a SQL Server 2005 edition which is RDBMS(relational database management systems) without a SQL Server instance you don't have a database engine to run your MDF(Microsoft data file) it is just one of at least two files you need to run your database. Run a search for file groups in SQL Server BOL(books online). Hope this helps.

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

|||

In order to use a *.mdf (SQL Server database file), you must have a SQL Server installed and running.

That could be SQL Server 2005 Express.

|||

Thanks for the replies. I have a clearer picture but have some additional questions...

I add a mdf file to my asp.net website. This file requires some version of SQL server, in this case

Express, to provide the RDMS system. Great. So how is it that I can just deploy the app to the web server

and it works without installing SQL server Express on the web server?

Is SQL Server Express built into the ASP.Net 2.0 runtime?

If so, the file must somehow be dynamically attached to this instance of SQL Server Express?

How will this type of deployment be upgraded to a higher version of SQL Server? If I use the "file" based

technique, will my app always use the embeded SQL Server Express and not any fuller version of SQL Server

I may install?

So to migrate to a higher version of SQL Server, will I need to attach the mdf file to SQL Server and change my

connection string to point to the new version of SQL Server?

This is my last little stumbling block to grasping the SQL Server Express concept, thanks for your replies!

|||

Perhaps this will clear some things up for you.

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

|||

yes, perfect. Thanks.

No doubt I over think these things.

While I appreciate the ease of use, I also like knowing what's going on, so the documentation is there, of course, but in such great volume one questions the point of reading massive amounts of documentation on an easy to use feature. ;-) I digress...thanks again.

Newbie advise

Hi
I am going to begin my first sql server app with vb.net front end. Is there
somewhere I can read on good practices on developing sql server apps, or is
there a sample app that I can see as an example?
Thanks
Regards
I'd check the "How Do I" Videos from MSDN if you're just starting out.
This link is specific to Visual Basic.NET
http://msdn2.microsoft.com/en-us/vbasic/bb466226.aspx
"John" <info@.nospam.infovis.co.uk> wrote in message
news:eHk$nX8iIHA.5280@.TK2MSFTNGP02.phx.gbl...
> Hi
> I am going to begin my first sql server app with vb.net front end. Is
> there somewhere I can read on good practices on developing sql server
> apps, or is there a sample app that I can see as an example?
> Thanks
> Regards
>
|||Here are the samples and starter kits for SQL Server 2005 Express with have
plenty of examples in VB.NET:
http://msdn2.microsoft.com/en-us/express/bb403187.aspx
http://msdn2.microsoft.com/en-us/express/aa718396.aspx
HTH,
Plamen Ratchev
http://www.SQLStudio.com
|||My latest book is a good match for you. See www.betav.com for details or
just visit
http://www.amazon.com/Hitchhikers-Guide-Visual-Studio-Server/dp/0321243625.
It walks you through how SQL Server works in terms anyone can understand and
uses VB.NET for all of the examples from simple to far more sophisticated
approaches.
__________________________________________________ ________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
__________________________________________________ __________________________________________
"John" <info@.nospam.infovis.co.uk> wrote in message
news:eHk$nX8iIHA.5280@.TK2MSFTNGP02.phx.gbl...
> Hi
> I am going to begin my first sql server app with vb.net front end. Is
> there somewhere I can read on good practices on developing sql server
> apps, or is there a sample app that I can see as an example?
> Thanks
> Regards
>
|||On Mar 21, 10:39Xpm, "John" <i...@.nospam.infovis.co.uk> wrote:
> Hi
> I am going to begin my first sql server app with vb.net front end. Is there
> somewhere I can read on good practices on developing sql server apps, or is
> there a sample app that I can see as an example?
>
While it's actually a Visual Studio.Net reference, it has a lot of
material that covers data access in your .Net applications:
http://www.learnvisualstudio.net
Good luck!
Richard Carpenter