Problem with sql2005 query/storedproc

I am working on the login portion of my app and am using my own setup for the moment so that I can learn more about how things work. I have 1 user setup in the db and am using a stored procedure to do the checking for me, here is the stored procedure code:

ALTER PROCEDUREdbo.MemberLogin(@.MemberNamenchar(20),

@.MemberPasswordnchar(15),

@.BoolLoginbit OUTPUT

)

AS

selectMemberPasswordfrommemberswheremembername = @.MemberNameandmemberpassword = @.MemberPassword

if@.@.Rowcount = 0

begin

selectBoolLogin = 0

return

end

selectBoolLogin=1

/* SET NOCOUNT ON */

RETURN

When I run my app, I continue to get login failed but no error messages. Can anybody help? Here is my vb code:

Dim MemberNameAsString

Dim MemberPasswordAsString

Dim BoolLoginAsBoolean

Dim DBConnectionAsNew Data.SqlClient.SqlConnection(MyCONNECTIONSTRING)

Dim SelectMembersAsNew Data.SqlClient.SqlCommand("MemberLogin", DBConnection)

SelectMembers.CommandType = Data.CommandType.StoredProcedure

MemberName = txtLogin.Text

MemberPassword = txtPassword.Text

Dim SelectMembersParameterAs Data.SqlClient.SqlParameter = SelectMembers.CreateParameter

'Name

SelectMembersParameter.ParameterName ="@.MemberName"

SelectMembersParameter.Value = MemberName

SelectMembers.Parameters.Add(SelectMembersParameter)

'Password

Dim SelectPasswordParameterAs Data.SqlClient.SqlParameter = SelectMembers.CreateParameter

SelectPasswordParameter.ParameterName ="@.MemberPassword"

SelectPasswordParameter.Value = MemberPassword

SelectMembers.Parameters.Add(SelectPasswordParameter)

Dim SelectReturnParameterAs Data.SqlClient.SqlParameter = SelectMembers.CreateParameter

SelectReturnParameter.ParameterName ="@.BoolLogin"

SelectReturnParameter.Value = BoolLogin

SelectReturnParameter.Direction = Data.ParameterDirection.Output

SelectMembers.Parameters.Add(SelectReturnParameter)

If BoolLogin =FalseThen

MsgBox("Login Failed")

ElseIf BoolLogin =TrueThen

MsgBox("Login Successful")

EndIf

EndSub

Thank you!!!

Perhaps its because of the nchar's you are using. CHAR is used for a fixed width string so if you send in a string which is less than the specified length it will be padded with extra spaces at the end. I would modify your code as follows:

ALTER PROCEDURE dbo.MemberLogin(@.MemberNamenvarchar(20),@.MemberPasswordnvarchar(15),@.BoolLoginbit OUTPUT)ASBEGINSET NOCOUNT ON-- @.BoolLogin =0 ==> Does not exist, @.BoolLogin=1 ==> ExistsSET @.BoolLogin =0IFEXISTS(select MemberPasswordfrom memberswhere membername = @.MemberNameand memberpassword = @.MemberPassword)SET @.BoolLogin = 1SET NOCOUNT OFFEND

|||

Hello all, I am still having problems and am very frustrated. I have looked and ready over a dozen articles on ado/asp/sql and cannot seem to figure this out. I have a sql db I added using the add components part of asp. It is local, IIS is active. As far as i can tell servername is 'local'. This is the code I am running:

visual basic code:
Imports System.DataImports System.Data.SqlClientPartialClass _DefaultInherits System.Web.UI.PagePrivateConst MyCONNECTIONSTRINGAsString = "Server=(local);Database=Vex;Trusted_Connection=True"ProtectedSub btnLogin_Click(ByVal senderAsObject,ByVal eAs System.EventArgs)Handles btnLogin.ClickDim MemberNameAsStringDim MemberPasswordAsStringDim BoolLoginAsBooleanDim testAsStringDim DBConnectionAsNew Data.SqlClient.SqlConnection(MyCONNECTIONSTRING)Dim SelectMembersAsNew Data.SqlClient.SqlCommand("MemberLogin", DBConnection) SelectMembers.CommandType = Data.CommandType.StoredProcedure 'open the connection to the db DBConnection.Open() MemberName = txtLogin.Text MemberPassword = txtPassword.Text 'NameDim SelectMembersParameterAs Data.SqlClient.SqlParameter = SelectMembers.CreateParameter SelectMembersParameter.ParameterName = "@.MemberName" SelectMembersParameter.Value = MemberName SelectMembers.Parameters.Add(SelectMembersParameter) 'PasswordDim SelectPasswordParameterAs Data.SqlClient.SqlParameter = SelectMembers.CreateParameter SelectPasswordParameter.ParameterName = "@.MemberPassword" SelectPasswordParameter.Value = MemberPassword SelectMembers.Parameters.Add(SelectPasswordParameter) 'Pass or Fail VariableDim SelectReturnParameterAs Data.SqlClient.SqlParameter = SelectMembers.CreateParameter SelectReturnParameter.ParameterName = "@.BoolLogin" SelectReturnParameter.Value = BoolLogin SelectReturnParameter.Direction = Data.ParameterDirection.Output SelectMembers.Parameters.Add(SelectReturnParameter) test = SelectMembers.ExecuteScalar()EndSubEndClass

I get this error when i try to login on the .open line:

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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

If i try to execute the .scalar w/o the line it tells me i need an open connection, but when I try to open the connection it throws this error. What am I doing wrong? Is there some setup piece(s) I am missing? I have gone through a basic install of vs2005 with no settings changes to sql05. Any help is greatly appreciated as I am at my wits end with this and I know it is going to be something simple......

as an added note, here is the connection string in the web.config file:

visual basic code:
<connectionStrings> <add name="csVex" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Vex.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" /> </connectionStrings>

I went into sql2005 surface configuration and made sure all protocols are enabled. I am an admin locally on my machine. I do not know what else to check or do at this point.

Thanks for your help....

|||

Just in case anybody else runs into this. I created a new project and added a datasource to that project pointing to my sql 2005 database. I then copied the connectionstring from that connection and pasted it in my CONST connectionstring. Error went away.

Good Luck!!

Problem with printing

Hello everybody,

i have used report viewer control in my win app, (c#, .net 2.0) and from msql i get data, fill necessary tableadapters and print report. Everything works fine on my computer, i generate report from template, push Print button in report viewer, Print dialog appears and i print the report by clicking OK.

The problem occurs in another computer, where i launch my app, i generate report, push Print button, and when appears print dialog happen strange things:

1. In print dialog i push "Print" button once, and nothing happens, Print dialog is still on top, then i push second time Print and then i can see that to pages is sended to printer (but my report is only one page). Printer does nothing, in printer schedulles there is no jobs.

2. So i press Print button in report viewer second time, Print dialog appears again, i click Print button nothing happens, then i push second time Print button i can see that 1 page is sended to printer, Print dialog disapear and now printer print my report correctly.

and this mesh is everytime i generate report and want to print it. Printer is installed correctly, i tried newest drivers for it. For example with ms office word everything works fine.

Could it be report viewer bug? what are the ways to find out what is wrong?

I have had the same problems in reporting services. Sometimes it doesn't print, sometimes it does. Sometimes it prints just the one page. Sometimes it prints multiple. I have tried just messing with the margins in the Report Properties menu button. I have found that SSRS doesn't print the report evenly on the page, too, so having compensated by setting the left margin .5 inches less then the right margin it now doesn't print more then one page as often. I don't know if that exact tweek will work for you, but maybe that will give you an idea what to look for. I would assume that most of these things are bugs, but they are not too hard to work around. Good luck!|||

What version of SSRS are you running? Go to http://<server>/ReportServer and note the number in the bottom right. 9.00.1399.00 is RTM (release to manufacturer) and it has known issues printing. There was a hotfix released for what might be your problem, but you should get the latest service pack (SP2) as it rolls up all of the fixes and has been through more testing.

Good luck,

Larry

|||I have version 9.00.1406.00 and it still has some problems with printing. Is this the latest version?|||

Here is a list of build numbers for the various versions of SQL Server 2005 and it indicates that you have a single patch installed (KB 932557) but that is all: 932557) but that is all: http://www.sqlservercentral.com/columnists/sjones/2960.asp

You may well find that applying SP2 will fix your issue.

Larry