?
Solved

straneg MSSQL erorr using VB

Posted on 2011-09-03
8
Medium Priority
?
201 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
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 2000 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

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

777 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