Solved

straneg MSSQL erorr using VB

Posted on 2011-09-03
8
184 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
Comment Utility
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
Comment Utility
Can you send the output from

EXECUTE sp_help Buddy_User
0
 
LVL 1

Author Comment

by:Chris Jones
Comment Utility
@ c1nmo: what were you needing there is a lot of values that are returned.
0
 
LVL 1

Author Comment

by:Chris Jones
Comment Utility
@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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 1

Author Comment

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

Expert Comment

by:Carlos Villegas
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

744 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now