VWD 2005 Express. I need to retrieve a value from a SQL database from the code behind a page and assign it to a variable. In Microsoft Access I can do this using the DLookup function. What I need to do is get the data that results from the following query into a variable:
SELECT [SystemUserId] FROM [SystemUser] WHERE ([Username] = @.Username)
The name of the data source is SqlDataSource2
Also, in Access I can create a recordset from a query and then process through the recordset. Can that be done in VB code in VWD 2005 Express?
I wouldn't use a SqlDataSource for this. I would use ADO.NET code and ExecuteScalar to obtain one value.
http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executescalar.aspx
Their are two potential equivalents to a RecordSet. One is using a DataReader, for forward only, read only access, and the other is a DataSet, in which you can move forwards and backwards. DataReader is the more common approach, but it depends on what you want to do.
http://msdn2.microsoft.com/EN-US/library/system.data.sqlclient.sqldatareader.aspx
|||
Thanks Mike. I looked at the link. The MSDN info is so cryptic to me that I cannot discern what to do. Could you provide a real VB code example of what you are talking about? Thanks for the help.
|||
Dim SystemUserId As Int32 = 0
Dim sql As String = "SELECT [SystemUserId] FROM [SystemUser] WHERE ([Username] = @.Username)"
Using conn As New SqlConnection(connString)
Dim cmd As New SqlCommand(sql, conn)
cmd.Parameters.AddWithValue("@.UserName", TxtUserName.Text)
Try
conn.Open()
SystemUserId = Convert.ToInt32(cmd.ExecuteScalar())
Catch ex As Exception
'Do whatever with Exceptions
End Try
End Using
In the line that starts cmd.Parameters.AddWithValue, I have assumed that the value for the @.Username will come from a TextBox called TxtUserName. Of course, you would need to adjust this to reflect the actual source. Also, connString is a variable of type String that contains your connection string. You would obviously have to suuply this value as well.
|||
Mike. I tried the following using a code example from the link you provided. I get errors saying that types SqlConnection and SqlCommand are not defined. What do I need to do here? Thanks.
ProtectedFunction GetSystemUserId(ByVal UsernameAsString)AsString
Dim connStringAsString ="<%$ ConnectionStrings:GoodNews_IntranetConnectionString %>"
Dim UserIDAsString
Dim sqlAsString ="SELECT [SystemUserId] FROM [SystemUser] WHERE ([Username] = '" + Username +"')"
Using connAsNew SqlConnection(connString)
Dim cmdAsNew SqlCommand(sql, conn)
Try
conn.Open()
UserID = Str(cmd.ExecuteScalar())
Catch exAs Exception
Console.WriteLine(ex.Message)
EndTry
EndUsing
Return UserID
EndFunction
|||
You need to add
Imports System.Data.SqlClient
at the top of the page. That makes the classes relating to connections and commands within the System.Data.SqlClient available to that page. The alternative is to use the full reference:
Using conn as new System.Data.SqlClient.SqlConnection
etc. Imports statements save a lot of typing in the long run.
|||
Thanks loads Mike!!! After I added the "Imports" at the top, all I had to do was modify my connection string (just copied from my web.config file) and she worked. You have now moved me from novice level 1 to novice level 1.1. Thanks and God bless.
|||
Novice level 1.1's will use:
Dim Conn as new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("ConnectionString").ToString)
and keep the connection string in the connection string section of the web.config file.
|||
I would be interested on the information on what you called "DataSet." Can you provide a link?
|||
Public Function GetDataSet(ByVal SQLString As String) As DataSet
Dim cmd As New SqlCommand(SQLString)
Return GetDataSet(cmd)
End Function
Public Function GetDataSet(ByVal cmd As SqlCommand) As DataSet
NullifyParameters(cmd)
Dim DS As New DataSet
Dim MyCommand As SqlDataAdapter
OpenConn(cmd)
cmd.CommandTimeout = m_CommandTimeout
MyCommand = New SqlDataAdapter(cmd)
'MyCommand.SelectCommand.CommandTimeout = m_CommandTimeout -- Change if you want
MyCommand.Fill(DS, "DS")
CloseConn(cmd)
Return DS
End Function
Private Sub NullifyParameters(ByVal cmd As SqlCommand)
For Each p As SqlParameter In cmd.Parameters
If p.Value Is Nothing OrElse (TypeOf p.Value Is String AndAlso p.Value.Length = 0 AndAlso (p.SqlDbType = SqlDbType.DateTime OrElse p.SqlDbType = SqlDbType.Int OrElse p.SqlDbType = SqlDbType.Money OrElse p.SqlDbType = SqlDbType.Real OrElse p.SqlDbType = SqlDbType.Float OrElse p.SqlDbType = SqlDbType.Decimal OrElse p.SqlDbType = SqlDbType.BigInt OrElse p.SqlDbType = SqlDbType.UniqueIdentifier OrElse p.SqlDbType = SqlDbType.TinyInt)) Then
p.Value = DBNull.Value
End If
Next
End Sub
I can't really provide the OpenConn/CloseConn functions, but they do what you would expect them to basically. They just have transaction support in them, which then needs more functions, etc. So here are some ones that will do what you want:
Private OpenConn(cmd as SqlCommand)
dim conn as new SqlConnection(System.Configuration.ConfigurationManager("ConnectionString").ToString)
conn.open
cmd.Connection=conn
end Sub
Private CloseConn(cmd as SqlCommand)
cmd.Connection.Close
end sub
|||
Add those functions to any code file you want to use them in (or make them part of a class library), and then you can do things like:
Dim MyDataSet as dataset = GetDataSet("SELECT * FROM MyTable")
then you can iterate through the dataset rows like
For each dr as datarow in MyDataSet.tables(0).Rows
if dr("Column1")= ... then
' Do something here
end if
next
|||
I placed your code in a class module. I got the following errors. Any help in clearing these up would be appreciated. Thanks.
The following code generates the error, "Type 'DataSet not defined."
PublicFunction GetDataSet(ByVal SQLStringAsString)As DataSet
Dim cmdAsNew SqlCommand(SQLString)
Return GetDataSet(cmd)
EndFunction
The following code generates the error, "m_CommandTimeout not declared."
PublicFunction GetDataSet(ByVal cmdAs SqlCommand)As DataSet
NullifyParameters(cmd)
Dim DSAsNew DataSet
Dim MyCommandAs SqlDataAdapter
OpenConn(cmd)
cmd.CommandTimeout = m_CommandTimeout
MyCommand =New SqlDataAdapter(cmd)
'MyCommand.SelectCommand.CommandTimeout = m_CommandTimeout -- Change if you want
MyCommand.Fill(DS,"DS")
CloseConn(cmd)
Return DS
EndFunction
The following code generates the error, "SqlDbType is not declared."
PrivateSub NullifyParameters(ByVal cmdAs SqlCommand)
ForEach pAs SqlParameterIn cmd.Parameters
If p.ValueIsNothingOrElse (TypeOf p.ValueIsStringAndAlso p.Value.Length = 0AndAlso (p.SqlDbType = SqlDbType.DateTimeOrElse p.SqlDbType = SqlDbType.IntOrElse p.SqlDbType = SqlDbType.MoneyOrElse p.SqlDbType = SqlDbType.RealOrElse p.SqlDbType = SqlDbType.FloatOrElse p.SqlDbType = SqlDbType.DecimalOrElse p.SqlDbType = SqlDbType.BigIntOrElse p.SqlDbType = SqlDbType.UniqueIdentifierOrElse p.SqlDbType = SqlDbType.TinyInt))Then
p.Value = DBNull.Value
EndIf
Next
EndSub
The following code generates the error, "Error 14 'ConfigurationManager' is a type in 'Configuration' and cannot be used as an expression."
Dim connAsNew SqlConnection(System.Configuration.ConfigurationManager("ConnectionString").ToString)
|||
Add
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.Configuration
to the top of the file containing these methods.
Comment out the Timout line. The default value is good for most scenarios. You will know if you have to increase it, because you get Timeout errors.
|||
The changes that mike suggested should fix the errors. If they don't, please post again.
|||
Mike:
I put the routines you gave me into a class module and I defined them as Shared so that I may call them from other modules. However, when I change the code as shown below (adding the Shared) I get the error that follows. Also I am confused as to how you can have two routines called "GetDataSet." How does the code know which one you are calling?
Cannot refer to an instance member of a class from within a shared method or shared member initializer without an explicit instance of the class.
Public SharedFunction GetDataSet(ByVal SQLStringAsString)As DataSet
Dim cmdAsNew SqlCommand(SQLString)
Return GetDataSet(cmd)
EndFunction
PrivateFunction GetDataSet(ByVal cmdAs SqlCommand)As DataSet
NullifyParameters(cmd)
Dim DSAsNew DataSet
Dim MyCommandAs SqlDataAdapter
OpenConn(cmd)
'cmd.CommandTimeout = m_CommandTimeout
MyCommand =New SqlDataAdapter(cmd)
'MyCommand.SelectCommand.CommandTimeout = m_CommandTimeout -- Change if you want
MyCommand.Fill(DS,"DS")
CloseConn(cmd)
Return DS
EndFunction
