sql query problem

proper sql queries but getting wrong data in program

Page 1 of 1

1 Replies - 1183 Views - Last Post: 28 September 2008 - 05:13 PM Rate Topic: -----

#1 DjVw321  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 13
  • Joined: 25-June 08

sql query problem

Posted 28 September 2008 - 03:13 PM

I am trying to make a login form that would store a users clearance and difficulty into two separate variables. I have a data set created with the following data
 
StudentID, StudentName, TeacherID, Clearance, Difficulty
2, Dan, 1, 1, 1
3, Jodi, 2, 2, 2
4, Karen, 3, 3, 3
5, Jhonnie, 2, 1, 3
6, Timmy, 1, 4, 2



In my form there are two combo boxes which contain the students names and the teachers names. When the teachers name is selected the students in that class are put into the second list box and it is enabled so that the student can select their name from that classroom. When the student selects their name and hits the login button i want it to put their values for their difficulty and clearance into variables. Then, for debugging purposes i have it assign the variables to three different labels' text property. I have the sql queries made and they work fine when i execute them in the data preview window and sql query builder. Here they are:
SELECT		Clearance
FROM			Student
WHERE		(StudentID = @Param2)

SELECT		Difficulty
FROM			Student
WHERE		(StudentName = @Param2)

SELECT		StudentID
FROM			Student
WHERE		(StudentName = @Param2)

SELECT		StudentName, StudentID, TeacherID, Clearance, Difficulty
FROM			Student
WHERE		(TeacherID = @Param1)




When I run the program, select the student and teacher names and press the button I get an incorrect value for my clearance. For example if i have Timmy selected i should get a number 4 assigned to my clearance but instead i get 1. Here is my code for the form:
Public Class Form1

	Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
		'TODO: This line of code loads data into the 'StudentLoginDataSet.Student' table. You can move, or remove it, as needed.
		Me.StudentTableAdapter.Fill(Me.StudentLoginDataSet.Student)
		'TODO: This line of code loads data into the 'StudentLoginDataSet.Teacher' table. You can move, or remove it, as needed.
		Me.TeacherTableAdapter.Fill(Me.StudentLoginDataSet.Teacher)

	End Sub

	Private Sub TeacherNameComboBox_SelectedIndexChanged_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TeacherNameComboBox.SelectedIndexChanged
		Try
			Me.StudentTableAdapter.StudentName(Me.StudentLoginDataSet.Student, CType(TeacherNameComboBox.SelectedValue, Integer))
		Catch ex As System.Exception
			System.Windows.Forms.MessageBox.Show(ex.Message)
		End Try
		StudentNameComboBox.Enabled = True
	End Sub

	Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
		End
	End Sub

	Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
		studentname = StudentNameComboBox.Text
		studentid = CType(StudentNameComboBox.SelectedValue, Integer) '' Student ID is the selected value for studentnamecombobox
		clearance = StudentTableAdapter.Clearance(StudentLoginDataSet.Student, studentid)

		Label1.Text = studentname
		Label2.Text = studentid
		Label3.Text = clearance

	End Sub
End Class


Like i stated before i get a correct value in the query builder and data preview but when i execute the program something is wrong. The student ID shows up properly however if i use my sql query to get that data from the table it will not give right value for that either.

Is This A Good Question/Topic? 0
  • +

Replies To: sql query problem

#2 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5929
  • View blog
  • Posts: 12,851
  • Joined: 16-October 07

Re: sql query problem

Posted 28 September 2008 - 05:13 PM

You, um, already posted the identical question five days ago: http://www.dreaminco...wtopic64836.htm

I didn't answer because I don't really enjoy VB.NET. However, is broad sense, the answer is this.

Make a typed dataset for the table Student. ( Please don't ask how to make a typed dataset in Visual Studio, google is your friend. ) The default Fill method will look like this:
select StudentID, StudentName, TeacherID, Clearance, Difficulty from Student



Add the following methods to the TableAdapter: FillByStudentID, FillByStudentName, FillByTeacherID, FillByClearance, FillByDifficulty. The code for each of these should be obvious.

select StudentID, StudentName, TeacherID, Clearance, Difficulty from Student where StudentID=@StudentID
select StudentID, StudentName, TeacherID, Clearance, Difficulty from Student where StudentName=@StudentName
select StudentID, StudentName, TeacherID, Clearance, Difficulty from Student where TeacherID=@TeacherID
select StudentID, StudentName, TeacherID, Clearance, Difficulty from Student where Clearance=@Clearance
select StudentID, StudentName, TeacherID, Clearance, Difficulty from Student where Difficulty=@Difficulty



The point is, don't worry about picking and choosing the exact fields you need returned, just fill your DataTable as required. Good luck.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1