Solved

straneg MSSQL erorr using VB

Posted on 2011-09-03
8
195 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

734 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