Solved

straneg MSSQL erorr using VB

Posted on 2011-09-03
8
187 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
 
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

895 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

20 Experts available now in Live!

Get 1:1 Help Now