Help In Coding  
Author Message
arcliner





PostPosted: Visual Basic Express Edition, Help In Coding Top

I had the following code, how do i actually pass the value from the select statement to the Contactlbl.Text Thanks

Protected Sub GoBtn_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles GoBtn.Click

Dim objConn As New SqlConnection

Dim objCmd As New SqlCommand

Dim Value As String = EventCmb.SelectedItem.ToString()

objConn.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename='C:\Documents and Settings\HP\My Documents\Visual Studio 2005\WebSites\FYP2\App_Data\Event.mdf';Integrated Security=True;Connect Timeout=30;User Instance=True"

objCmd.Connection = objConn

objCmd.CommandType = CommandType.Text

objCmd.CommandText = "SELECT EventTel FROM Event WHERE (EventID = Value)"

objConn.Open()

ContactLbl.Text =

objConn.Close()

End Sub

End Class



Visual Studio Express Editions19  
 
 
MarcD





PostPosted: Visual Basic Express Edition, Help In Coding Top

Try this code instead. Basically your code was not getting the value into the query. To be secure we used a Named parameter and youwill see where I modified your SQL Query just a bit. The next bit is we ExecuteScalar which returns one value. We check to make sure something was returned and if something was we put it in your label.


Dim objConn As New SqlConnection
Dim objCmd As New SqlCommand
Dim Value As String = EventCmb.SelectedItem.ToString()
objConn.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename='C:\Documents and Settings\HP\My Documents\Visual Studio 2005\WebSites\FYP2\App_Data\Event.mdf';Integrated Security=True;Connect Timeout=30;User Instance=True"
Try
objConn.Open()
objCmd.Connection = objConn
objCmd.CommandType = CommandType.Text


Dim RetVal As Object = objCmd.ExecuteScalar() ' This returns the First Column of the first row regardless of how much data is returned.
If Not ((RetVal Is Nothing) Or (RetVal Is DBNull.Value)) Then
ContactLbl.Text = RetVal.ToString()
Else
' noting was returned
End If
Catch ex As Exception
Throw ex
Finally
objConn.Close()
End Try



Hope this helps.

 
 
Bernard Pang





PostPosted: Visual Basic Express Edition, Help In Coding Top

Thanks MarcD! I learn something new!

However, there's an error for in line "Dim RetVal As Object = objCmd.ExecuteScalar() "

Error Message is as follow "Conversion failed when converting the nvarchar value 'LTA' to data type int."

It is due to "ExecuteScalar() " can only store int  I just need to display one data, and Value data comes form a combo box "EventCmb", which i wanted to find the selected String, compared to the "Event" database to get the "EventTel" data How do i solved this problem, any advice Thanks!


 
 
MarcD





PostPosted: Visual Basic Express Edition, Help In Coding Top

It sounds like you're not executing the same query that you gave me as there is a column named LTA, I'm guessing. If you are not using the same query then you will need to provide the query you are using.l You can also try debugging it on your own by using the instrucitons below:
if you change
Dim RetVal As Object = objCmd.ExecuteScalar() '
to
Dim RetVal As Object' = objCmd.ExecuteScalar() '
and instead do a
objCmd.ExecuteNonQuery()

do you still get the same exception

 
 
Bernard Pang





PostPosted: Visual Basic Express Edition, Help In Coding Top



Maybe i shd show you my Database Table, and data stored (Currrently). The "Dim RetVal As Object = objCmd.ExecuteScalar()" error had been debugged.

However, another error arise, the "Finally" just before "objconn.close()" had this error, "Conversion failed when converting the nvarchar value 'LTA' to data type int."

Any idea how to resolve Thks again!

Event

EventID                    int  -> Primary Key      3
EventName             nvarchar(50)                LTA      
EventTel                  nvarchar(50)                12345678

EventStartDate      nvarchar(50)                6/30/2006
EventEndDate       nvarchar(50)                6/30/2006
EventStartTime      varchar(10)                 01:00
EventEndTime       varchar(50)                  02:00
EventVenue           nvarchar(50)                 LTA
EventDescription  nvarchar(4000)            LtA Testing

 
 
Bernard Pang





PostPosted: Visual Basic Express Edition, Help In Coding Top

Thanks MarcD! Problem solved thanks to code provided. MVP indeed.