Solved

straneg MSSQL erorr using VB

Posted on 2011-09-03
8
189 Views
Last Modified: 2012-05-12
Hello

i have this code that worked 2 days ago but now its not working and nothing has been chaged dealing with the code as well as the data.

here is the eror i get

UserID
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.IndexOutOfRangeException: UserID

Source Error:


Line 33:         dr = sql.ExecuteReader()
Line 34:         If dr.Read() Then
Line 35:             TempuserID = dr("UserID")
Line 36:             conn = D.NewSQLConnectionWebApp()
Line 37:             sqlUpdate.Connection = conn
 



HERE IS THE CODE
Private Sub verifyuser(ByVal MID As String)
        Dim sql As New SqlCommand
        Dim sqlUpdate As New SqlCommand
        Dim dr As SqlDataReader
        Dim conn As SqlConnection
        Dim TempuserID As String

        conn = D.NewSQLConnectionWebApp()
        sql.Connection = conn
        sql.Parameters.AddWithValue("@ID", MID)
        sql.CommandText = "SELECT * FROM Buddy_User WHERE Code = @ID"
        dr = sql.ExecuteReader()
        If dr.Read() Then
            TempuserID = dr("UserID")
            conn = D.NewSQLConnectionWebApp()
            sqlUpdate.Connection = conn
            sqlUpdate.Parameters.AddWithValue("@ID", MID)
            sqlUpdate.CommandText = "UPDATE Buddy_User SET Active ='Y' WHERE Code = @ID"
            sqlUpdate.ExecuteNonQuery()
            Response.Redirect("Home.aspx")
        End If
        dr.Close()
        sql.Dispose()
        sqlUpdate.Dispose()
        conn.Close()

        '  A.SaveImage("./UserData/placeholder.jpg", TempuserID)
end sub

Open in new window

0
Comment
Question by:Chris Jones
8 Comments
 
LVL 17

Expert Comment

by:Carlos Villegas
ID: 36478046
Hello, are you sure that this query:
SELECT * FROM Buddy_User WHERE Code = @ID

Open in new window

Is returning a column named UserID ? be sure of that.
0
 
LVL 6

Expert Comment

by:c1nmo
ID: 36478050
Can you send the output from

EXECUTE sp_help Buddy_User
0
 
LVL 1

Author Comment

by:Chris Jones
ID: 36478106
@ c1nmo: what were you needing there is a lot of values that are returned.
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 1

Author Comment

by:Chris Jones
ID: 36478110
@yv989c: here is my table structure


USE [cjones12_Wedding]
GO
/****** Object:  Table [defjam903].[Buddy_User]    Script Date: 09/03/2011 10:50:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [defjam903].[Buddy_User](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Username] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Password] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Email] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[School] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Code] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Active] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Buddy_User_Active]  DEFAULT ('N'),
	[MemberSince] [datetime] NULL CONSTRAINT [DF_Buddy_User_MemberSince]  DEFAULT (getdate())
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

Open in new window

0
 
LVL 1

Author Comment

by:Chris Jones
ID: 36478111
oh no wait a moment there is no column name UserID WOW!!
0
 
LVL 17

Expert Comment

by:Carlos Villegas
ID: 36478127
Hello, that is the problem, the [Buddy_User] table don't has a column named UserID, I think that correct column name is ID, but I can't be sure, so try change this line (#14 of your example):
TempuserID = dr("UserID")

Open in new window

to:
TempuserID = dr("ID")

Open in new window


I hope this help.
0
 
LVL 8

Accepted Solution

by:
Andrei Fomitchev earned 500 total points
ID: 36480062
1. SELECT * FROM Buddy_User WHERE Code = @ID

to get @ID by ID ??? - you already have @ID.

2. ByVal MID As String - ""String" what is MID in the table? What is the big picture? Let me guess...

The user entered the name/password and the task is to flag the user as active (MID is the string).
Then UserName = @param not Id = @param.

3. Instead of reader you can use ID =  sql.ExecuteScalar() - it returns 1st row 1st column counting from 1.

4. Doesn't it look strange to you - getting ID by MID and then update by ID? You can update by MID and you don't need "select part" at all.



This is all you need in your sub for SQL (Image is separate):
... Sub ...
DIM ...
            conn = D.NewSQLConnectionWebApp()
            sqlUpdate.Connection = conn
            sqlUpdate.Parameters.AddWithValue("@ID", MID)
            sqlUpdate.CommandText = "UPDATE Buddy_User SET Active ='Y' WHERE Code = @ID"
            sqlUpdate.ExecuteNonQuery()
end sub

Code = @ID (where @ID is MID) maybe UserName = @ID should be here...

Open in new window

0
 
LVL 1

Author Closing Comment

by:Chris Jones
ID: 36815655
Thanks
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question