Hi all,
I want to insert information like SQL Server Version, current sql server user etc into a form, How will I achieve this?
Thanks
Rudi Groenewald
Printable View
Hi all,
I want to insert information like SQL Server Version, current sql server user etc into a form, How will I achieve this?
Thanks
Rudi Groenewald
For SQL Server Version, you can run this query against sql server:
SELECT @@VERSION
I am not sure what you mean by "current sql server user" and "etc"
By current sql server user I mean the SCURRENT_USER() command. How will my code look if I wanted the version on a textbox on a windows form?
Thanks
Rudi
You can use the ExecuteScalar method of the SQLConnection class that will return a single value to you in a string.
Read:
http://msdn.microsoft.com/library/en...calartopic.asp
thanks
Please excuse my stupidity, but I'm still lost. What I wanna do is the following:
In my application, I will refer numerous times to sql server variables and static information. So what I want to do is create a class that can give me that static information.
One of these values I will use alot is the current user logged on at the sql server ("CURRENT_USER()" Command)
How will I go about to create this public class to get this value, is it possible that you could give me an example?
I've got like, a class already to specify system colors and stuff that exists out of the following:
VB Code:
Imports System.Data.SqlClient Module JMSGlobals Public JMSColor = Color.FromArgb(149, 178, 198) Public JMSConnection As New SqlConnection Public JMSColorLblsBack = Color.FromArgb(57, 106, 105) End Module
Is it possible to now make values like the current sql server user and sql server version available in the same class?
One other function that I'd like to add but I don't know how is eg: I've got a table (Tbl_Employees) on sql server which is my employees master. Now to get information about the current user and make it public in that same class as above. I was thinking on using something like "select ManagerName from Tbl_employees where Login = CURRENT_USER()"
and then make that value that's returned public. How will I do that?
Thanks alot, I really need to get this one figured out.
Regards,
Rudi Groenewald
When you say that you will refer to the information numerous times, will it be the same information? In other words, will there always be just ONE user logged in to the SQL Server database in the duration of your application run?
If what I just asked is true, then you can create a class quite similar to your coloring class, with the difference being that you query the database in the constructor (Sub New) of your class and hold the values in Public Properties. This way you can just keep referring to the object's properties without having to requery the database continously.
If, on the other hand, this information is dynamic, then you can make functions which return strings, instead of public properties in your class.
(Btw, your coloring "class" isn't really a class, it is a module, I just thought I should point that out)
So which one of the above two is it? And do you have a problem creating a class?
This will be very similar to the technique you implement for the post above. However, I have to question your architecture, and as to why you're using CURRENT_USER. Pardon me if it's a little too obvious to you.Quote:
Is it possible to now make values like the current sql server user and sql server version available in the same class?
One other function that I'd like to add but I don't know how is eg: I've got a table (Tbl_Employees) on sql server which is my employees master. Now to get information about the current user and make it public in that same class as above. I was thinking on using something like "select ManagerName from Tbl_employees where Login = CURRENT_USER()"
and then make that value that's returned public. How will I do that?
Hi Mendhak,
Yes ur right, There will only be one user logged in for the duration of the application's run, as the application will be open one instance per user. (will be open on numerous pc's, but only one user) so only one user will be "logged in" on my application. (Hope that makes sense)
This information will stay static, so yes, I'll would like it to be returned as static data. Even data that I get from the "Employee Master" like the user's manager's name, user's Shift he work's on's color, that kind of stuff. This information stays static in the database, but gets inserted into tables as the user uses the application. for eg: When the user does a purchasing transaction, the current user's login name gets inserted in the table. then I would use this class / module, to insert the current username. or I would filter records in a datagrid using the current username so that the user only sees his own records.. If that makes it a bit clearer to you.
Mostly, I would refer to the user's company employee number. so this is static aswell. I would get this value from the employees table in sql server, and refer to it each time I would filter records, insert new records into different tables, etc etc.
Thanks alot for all the help, please ask some more questions if you need to.
What you are describing is something similar to what most of us call a Data Access Layer. It is basically a class or a bunch of classes which you create to perform your database operations for you. The only thing you 'do' from your boundary class is to simply call the function or method.
So this is a good approach that you are taking. There are of course 99 different variations you can take to this approach, but you'll have to find out which one is best for you, and you will realize that as you code.
Now, you can start off (I'm showing you a very very basic DAL) with creating a class that represents your employee, give it some public properties like Employee Number, Manager ID, Address, Gender, Shift, Favorite Cold Drink (Default that to Mountain Dew ;)) and so on.
In this class, you can create your methods and functions to perform the database operations and querying, which would also set your properties to be accessed later.
How familiar are you with OOP?
uhhh... OOP? Sorry for the stupidity, I've only started studying mcad and mcsd last week, so please bare with me.
Allright. How would I start this off?
All the values, should use my connection called JMSConnection as stated in the public module JMSGlobals as so:
VB Code:
Module JMSGlobals Public JMSColor = Color.FromArgb(149, 178, 198) Public JMSConnection As New SqlConnection Public JMSColorLblsBack = Color.FromArgb(57, 106, 105) End Module
now the connection string for that connection gets set on the login form as follows:
VB Code:
Private Sub cmdLogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdLogin.Click If (RdoSQLAuth.Checked = True) Then JMSGlobals.JMSConnection.ConnectionString = "Data Source=" & Me.txtJMSServer.Text & ";Initial Catalog=MSTRIBOLOGY;User Id=" & Me.txtUsername.Text & ";Password=" & Me.txtPassword.Text & ";Application Name=" & Application.ProductName & " " & Application.ProductVersion & ";" Me.DialogResult = DialogResult.OK 'Me.Close() ElseIf (RdoNTAuth.Checked = True) Then JMSGlobals.JMSConnection.ConnectionString = "Data Source=" & Me.txtJMSServer.Text & ";Initial Catalog=MSTRIBOLOGY;Integrated Security=SSPI;workstation id=" & Environment.MachineName & ";Application Name=" & Application.ProductName & " " & Application.ProductVersion & ";" Me.DialogResult = DialogResult.OK 'Me.Close() End If End Sub
Right... Now I started out my class as:
VB Code:
Class JMSServerEmployees Dim EmployeeID As String Dim login As String End Class
Great start isnt it? hehe... I'm already lost...
I'm open for some learning...
Thanks alot for the help thusfar.
\\mendhak scores some more reputation points on this one... hehe
1. OOP = Object Oriented Programming
2. OK, I see where you are going with this. You want your connection object to remain visible to the entire project, in all forms.
So far, what you have done is to create the Employee class. Fine. But your database code is in your forms, which is... not good. What you need to do is to expand this class more:
VB Code:
Class JMSServerEmployees Dim EmployeeID As String Dim login As String Public Function DoDatabaseLogin(ByVal strServerName As String, ByVal strUserName As String, ByVal strPassword As String, ByVal NtAuth As Boolean) As DialogResult If NtAuth Then JMSGlobals.JMSConnection.ConnectionString = "Data Source=" & strServerName & ";Initial Catalog=MSTRIBOLOGY;User Id=" & strUserName & ";Password=" & strPassword & ";Application Name=" & Application.ProductName & " " & Application.ProductVersion & ";" Return DialogResult.OK Else JMSGlobals.JMSConnection.ConnectionString = "Data Source=" & Me.txtJMSServer.Text & ";Initial Catalog=MSTRIBOLOGY;Integrated Security=SSPI;workstation id=" & Environment.MachineName & ";Application Name=" & Application.ProductName & " " & Application.ProductVersion & ";" Return DialogResult.OK End If End Function End Class
I haven't edited all the code above, btw, that's for you to do...
Then you can call this function from the login button click:
VB Code:
Private Sub cmdLogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdLogin.Click If (RdoSQLAuth.Checked = True) Then MyEmpObj.DoDataBaseLogin(Me.txtUserName.Text, Me.txtPassword.Text.... blah blah) ElseIf (RdoNTAuth.Checked = True) Then End If End Sub
1. Aah ok I see, Oop... Why didn't I think of that.
2. Yup, want the connection string public, cause there's gonna be alotta forms, and some users login via sql server authentication and some via nt authentication.
I'm trying that code you've given me now... will get back in a sec. How would I do that class on the sql server variables?
Thanks for all the help thusfar. I battled a bit this morning cause I added more projects to my solution, (class library for all my forms) so that I don't end up with a 10 mb exe file.
SQL Server Variables? Care to explain?
Oh, and to add to your confusion, store your connection string in the application .CONFIG file.
lol... Yeah , that kinda get me over the edge of confusion. How would I save it in the config file? :confused:
Sql Server variables: The informatin that I wanted in the beginning, like the current username, The favourite pet name, mountain dew soft drink, shift color... that stuff.
btw, this is how far I've gotten now.
My class's name I've given where I will put all my classes and modules and functions and stuff that's got to do is called ServerOperations.
VB Code:
Public Class ServerOperations Public Function DoDatabaseLogin(ByVal strServerName As String, ByVal strUserName As String, ByVal strPassword As String, ByVal NtAuth As Boolean) As DialogResult If NtAuth = False Then JMSGlobals.JMSConnection.ConnectionString = "Data Source=" & strServerName & ";Initial Catalog=MSTRIBOLOGY;User Id=" & strUserName & ";Password=" & strPassword & ";Application Name=" & Application.ProductName & " " & Application.ProductVersion & ";" Return DialogResult.OK Else JMSGlobals.JMSConnection.ConnectionString = "Data Source=" & strServerName & ";Initial Catalog=MSTRIBOLOGY;Integrated Security=SSPI;workstation id=" & Environment.MachineName & ";Application Name=" & Application.ProductName & " " & Application.ProductVersion & ";" Return DialogResult.OK End If End Function End Class
Now on the click event of my login form's Login Button I've changed the code to this:
VB Code:
Private Sub cmdLogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdLogin.Click Dim LoginOp As New Object Try If (RdoSQLAuth.Checked = True) Then LoginOp.DoDatabaseLogin(Me.txtJMSServer, Me.txtUsername, Me.txtPassword, False) Me.DialogResult = DialogResult.OK ElseIf (RdoNTAuth.Checked = True) Then LoginOp.DoDatabaseLogin(Me.txtJMSServer, , , False) Me.DialogResult = DialogResult.OK End If Catch ex As Exception End Try End Sub
And just guess what.... Nothing happens when I click on the button now... grr... any ideas?
Thanks
Rudi
Dim LoginOp As New Object
should be
Dim LoginOp As New ServerOperations
oooooh... I c...doh..I feel so sheepish.
Ok... Roit. I've got that now.... the config thingy? and now we can move on to the sql server values?
Thanks for all the help thusfar... You're helping alot!
Rudi
In the application config file, add this:
<add key="MyConnectionString" value="Yourconnectionstring blah blah" />
And in your code, wherever you want to retrieve the value, use:
System.Configuration.COnfigurationSettings.AppSettings("MyConnectionString")
Ah ok, Thanks, I got that.
Now.... How would I go to work to retrieve information from sql server?
Thanks
Rudi
Do you know any ADO.NET?
A little bit, but I'm sure if I get a few hints, I'll be able to manage. I used ado in ms access... but that's about it.
Ok... I'm looking through that now...ummm yeah...
Yeah, I'm looking through it now.
Hmmm... yeah... looks good yeah....
Take your time. It'll be very useful for all your future apps. Well, most of them anyways. Post again when you get stuck somewhere.
Hi Mendhak....
Last night I tried to get only the current username from sql server, but failed miserably. Could you give me an example of how to get something from the server into that class I'm trying?
Thanks
Rudi
Show your code!
Did you use a statement like this?
Code:SELECT CONVERT(CHAR (30), CURRENT_USER) AS CurrentUserName
Hi Mendhak,
This is my class which I want to retrieve my values from:
VB Code:
Imports System.Data.SqlClient Imports System.Drawing Public Module JMSGlobals Public JMSColor = Color.FromArgb(149, 178, 198) Public JMSConnection As New SqlConnection Public JMSColorLblsBack = Color.FromArgb(57, 106, 105) Public Function CurrentUserName() Dim myDataReader As SqlDataReader Dim CurrentSQLUser As SqlCommand JMSConnection = JMSClasses.JMSConnection JMSConnection.ConnectionString = JMSClasses.JMSConnection.ConnectionString CurrentSQLUser = New SqlCommand("SELECT CONVERT(CHAR (30), CURRENT_USER) AS CurrentUserName", JMSConnection) JMSConnection.Open() myDataReader = CurrentSQLUser.ExecuteReader(CommandBehavior.CloseConnection) End Function End Module
It's called JMSGlobals.vb
This is how I referred to this code:
VB Code:
Private Sub FrmMenu_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load Me.lblWelcomeName.Text = JMSClasses.CurrentUserName
What am I doing wrong?
Thanks
Rudi
Don't use ExecuteReader. Use ExecuteScalar.
Also, in your function CurrentUserName, you need to specify a return type (string) and a return value.
I get index out of range exception when I changed my code to the following:
VB Code:
Imports System.Data.SqlClient Imports System.Drawing Public Module JMSGlobals Public JMSColor = Color.FromArgb(149, 178, 198) Public JMSConnection As New SqlConnection Public JMSColorLblsBack = Color.FromArgb(57, 106, 105) Public Function CurrentUserName() Dim myDataReader As SqlDataReader Dim RetrieveSQLUser As SqlCommand Dim CurrentSQLUser As String JMSConnection = JMSClasses.JMSConnection JMSConnection.ConnectionString = JMSClasses.JMSConnection.ConnectionString RetrieveSQLUser = New SqlCommand("SELECT CONVERT(CHAR (30), CURRENT_USER) AS CurrentUserName", JMSConnection) JMSConnection.Open() myDataReader = RetrieveSQLUser.ExecuteScalar(CommandBehavior.CloseConnection) CurrentSQLUser = myDataReader.Read End Function End Module
Anyone have any idea? I know I've almost got it. If I can just get this datareader to work......... puh puh puh puh puh please
Hey,
Ok... I've done the following, but I have the following questions:
VB Code:
Public Function CurrentUserName() Try Dim myDataReader As SqlDataReader Dim RetrieveSQLUser As SqlCommand Dim CurrentSQLUser As String JMSConnection = JMSClasses.JMSGlobals.JMSConnection JMSConnection.ConnectionString = JMSClasses.JMSGlobals.JMSConnection.ConnectionString RetrieveSQLUser.CommandText = "Select SUSER_SNAME() AS SQLLogin" JMSConnection.Open() RetrieveSQLUser.ExecuteScalar() JMSConnection.Close() Catch ex As Exception MsgBox(ex.ToString) End Try End Function
When running this code, I get like, a msgbox with nothing in it... and the value isnt displayed on the form.
#1: How do I specify the return type and return value?
#2: What am I doing wrong in my code?
Thanks
Rudi
Hi all,
Ok I never thought it would get down to this, but now I'm begging: :cry:
I changed my code to the following, but still no luck:
VB Code:
Imports System.Data.SqlClient Imports System.Drawing Namespace JMSGlobals Public Module JMSGlobals Public JMSColor = Color.FromArgb(149, 178, 198) Public JMSConnection As New SqlConnection Public JMSColorLblsBack = Color.FromArgb(57, 106, 105) Public Function CurrentUserName() Try Dim myDataReader As SqlDataReader Dim RetrieveSQLUser As New SqlCommand Dim CurrentSQLUser As String Dim SQLString As String Dim JMSConnection As New SqlConnection SQLString = "Select SUSER_SNAME() As SQLLogin" RetrieveSQLUser.Connection = JMSClasses.JMSGlobals.JMSConnection RetrieveSQLUser.Connection.Open() RetrieveSQLUser.CommandText = SQLString RetrieveSQLUser.ExecuteScalar().ToString() RetrieveSQLUser.Connection.Close() Catch ex As Exception MsgBox(ex.ToString) End Try End Function End Module End Namespace
I still can't get the value to get retrieved. The application runs like normal, but nothing happens (the value isnt inserted in the label)
Please Help!
Hi all,
Ok This is mind boggling...
I took the code that I've got in my class I'm creating. Pasted that in the form's code, referred to that to show in the label's text value, call the function on the form's load event......... and it works... it retrieves the value. :eek2:
now WHY OH WHY, does it work when the codes' inside the form, but when I refer to a publically available class, it doesn't get the value......
Someone PLEASE help me.