I cant seem to accomplish this. It returns nothing. Please help.
TIA,
Stue
<code>
Function Get_AttendID(ByVal strAttendIDAsString)As SqlDataReader
Dim connStringAsString = ConfigurationSettings.AppSettings("ClassDB")
Dim sqlConnAsNew SqlConnection(connString)
Dim sqlCmdAs SqlCommand
Dim drAs SqlDataReader
sqlConn.Open()
Dim strSQLAsString = "Select AttendID from attendees Where FirstName=@.FirstName and LastName=@.LastName and classbegdt = @.classbegdt and survey = '0'"
sqlCmd =New SqlCommand(strSQL, sqlConn)
sqlCmd.Parameters.Add("@.FirstName", SqlDbType.VarChar, 50)
sqlCmd.Parameters("@.FirstName").Value = tbFirstName.Text
sqlCmd.Parameters.Add("@.LastName", SqlDbType.VarChar, 50)
sqlCmd.Parameters("@.LastName").Value = tbLastName.Text
sqlCmd.Parameters.Add("@.classbegdt", SqlDbType.DateTime, 8)
sqlCmd.Parameters("@.classbegdt").Value = calBegDate.SelectedDate.ToShortDateString
dr = sqlCmd.ExecuteReader()
dr.Close()
sqlConn.Close()
Return dr
EndFunction
</code>
The best way would be to use executescalar method and return the value. excuse the sample code because it is C#
publicstring AttendID()
{
SqlConnection myConnection =new SqlConnection(ConfigurationSettings.AppSettings("ClassDB"));
string strSQL = "Select AttendID from attendees Where FirstName=@.FirstName and LastName=@.LastName and classbegdt = @.classbegdt and survey = '0'";
SqlCommand myCommand =new SqlCommand(strSQL, myConnection);
myCommand.Parameters.Add("@.FirstName", SqlDbType.VarChar, 50);
myCommand.Parameters("@.FirstName").Value = tbFirstName.Text;
myCommand.Parameters.Add("@.LastName", SqlDbType.VarChar, 50);
myCommand.Parameters("@.LastName").Value = tbLastName.Text;
myCommand.Parameters.Add("@.classbegdt", SqlDbType.DateTime, 8);
myCommand.Parameters("@.classbegdt").Value = calBegDate.SelectedDate.ToShortDateString();
return myCommand.ExecuteScalar().ToString();
}
|||Thanks Mansoorl! I tried that and it worked. In response to your question about the datareader, the reason I went this route is because I have another function wich requires pulling 2 values. So i was in that mindset. I didnt know about the ExecuteScalar though so thanks for educating me.
Do you mind explaining how i might go about returning 3 values via the data reader if:
Select FirstName, LastName, Company from TBClassSurvey Where AttendID=@.AttendID and SchedID=@.SchedID and survey = '0'";
Thanks again,
Stue
|||publicvoidAttendID()
{
SqlConnection myConnection =new SqlConnection(ConfigurationSettings.AppSettings("ClassDB"));
string strSQL = "Select AttendID from attendees Where FirstName=@.FirstName and LastName=@.LastName and classbegdt = @.classbegdt and survey = '0'";
SqlCommand myCommand =new SqlCommand(strSQL, myConnection);
myCommand.Parameters.Add("@.FirstName", SqlDbType.VarChar, 50);
myCommand.Parameters("@.FirstName").Value = tbFirstName.Text;
myCommand.Parameters.Add("@.LastName", SqlDbType.VarChar, 50);
myCommand.Parameters("@.LastName").Value = tbLastName.Text;
myCommand.Parameters.Add("@.classbegdt", SqlDbType.DateTime, 8);
myCommand.Parameters("@.classbegdt").Value = calBegDate.SelectedDate.ToShortDateString();
SqlDataReader myReader = myCommand.ExecuteReader();
myReader.Read();
string FirstName = myReader["FirstName"].ToString();
string LastName = myReader["LastName"].ToString();
string Company = myReader["Company"].ToString();
myReader.Close();
myConnection.Close();
}
The above code assumes you got something back in result of the query. If there is a possiblity for blank records make sure you use if (myReader.Read()) constuct.
Cheers,|||Thanks again mansoorl! I appretiate you educating me.
Take care,
Stue
