Join 136,182 VB Programmers for FREE! Get instant access to thousands of VB experts, tutorials, code snippets, and more! There are 2,052 people online right now. Registration is fast and FREE... Join Now!
I have a code here for login but using MS Access as backend, can you help me convert it to SQL as my back end
here it is:
CODE
Option Strict Off Option Explicit On Imports VB = Microsoft.VisualBasic Friend Class frmLogin Inherits System.Windows.Forms.Form
Private Sub Command2_Click()
End Sub
Private Sub cmdCancel_Click(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles cmdCancel.Click Me.Close() End Sub
Private Sub cmdOk_Click(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles cmdOk.Click Dim db_file As String Dim statement As String Dim conn As ADODB.Connection Dim rs As ADODB.Recordset
' Open the database. db_file = My.Application.Info.DirectoryPath If VB.Right(db_file, 1) <> "\" Then db_file = db_file & "\" db_file = db_file & "data.mdb"
' Open a connection. conn = New ADODB.Connection conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & db_file & ";" & "Persist Security Info=False" conn.Open()
' Look up the user name/password. statement = "SELECT COUNT (*) FROM Users WHERE " & "UserName='" & Replace(txtUserName.Text, "'", "''") & "' AND " & "Password='" & Replace(txtPassword.Text, "'", "''") & "'" rs = conn.Execute(statement)
' See if we got anything. If CInt(rs.Fields(0).Value) < 1 Then ' There is no match. ' Do not allow the login. Me.Close() MsgBox("Invalid user name/password.") Else ' There is a match. ' Display the program's main form. Form1.Show() Me.Close() End If
rs.Close() conn.Close() End Sub End Class
thank you
This post has been edited by enteng: 4 Sep, 2007 - 05:35 AM
First of all, in the future you might want to post in the VB.NET forum so you can get a more accurate answer in a timely manner. This forum is for VB previous to .NET.
As for your question, the conversion is not that hard if you know where to start looking for the information. What you will want to check out is a "SQLDataAdapter", "SQLConnection", and possibly "DataReaders" which will allow you to connect and read with little editing of this code. Below I have put a link to a great site that explains a few of the parts and provides some sample code that you can adapt to your particular situation.
If I'm not mistaken, you should still be able to use ADODB objects to query a sql database. All you should have to do is change the connection string. Do a google search for 'sql connection string' and you should get quite a few examples.
when i used the code from the link you had given me, there are errors appear
Error 1 Type 'System.Data.SqlClient.SqlDataReader' has no constructors. C:\Documents and Settings\Ericson Ramos\Local Settings\Application Data\Temporary Projects\WindowsApplication1\Form1.vb 8 9 WindowsApplication1
Error 2 Identifier expected. C:\Documents and Settings\Ericson Ramos\Local Settings\Application Data\Temporary Projects\WindowsApplication1\Form1.vb 11 83 WindowsApplication1 Error 3 Syntax error. C:\Documents and Settings\Ericson Ramos\Local Settings\Application Data\Temporary Projects\WindowsApplication1\Form1.vb 12 1 WindowsApplication1 Error 4 Variable 'e' hides a variable in an enclosing block. C:\Documents and Settings\Ericson Ramos\Local Settings\Application Data\Temporary Projects\WindowsApplication1\Form1.vb 32 15 WindowsApplication1
Public Class Form1 Inherits System.Windows.Forms.Form Dim myConnection As SqlConnection Dim myCommand As SqlCommand Dim dr As New SqlDataReader() 'declaring the objects
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs)_ Handles MyBase.Load myConnection = New SqlConnection("server=localhost;uid=sa;pwd=;database=pubs") 'establishing connection. you need to provide password for sql server Try myConnection.Open() 'opening the connection myCommand = New SqlCommand("Select * from discounts", myConnection) 'executing the command and assigning it to connection dr = myCommand.ExecuteReader() While dr.Read() 'reading from the datareader MessageBox.Show("discounttype" & dr(0).ToString()) MessageBox.Show("stor_id" & dr(1).ToString()) MessageBox.Show("lowqty" & dr(2).ToString()) MessageBox.Show("highqty" & dr(3).ToString()) MessageBox.Show("discount" & dr(4).ToString()) 'displaying the data from the table End While dr.Close() myConnection.Close() Catch e As Exception End Try End Sub
Put a space between the )_ on line 11 to take care of the first two errors
CODE
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs)_ Handles MyBase.Load
becomes
CODE
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) _ Handles MyBase.Load
Next change the variable in the catch block from e to ex so that you don't have two variables named e. (one for the event arguments and one for the exception)
Error 1 Type 'System.Data.SqlClient.SqlDataReader' has no constructors. C:\Thesis\WindowsApplication1\WindowsApplication1\Form1.vb 8 9 WindowsApplication1
Public Class Form1 Inherits System.Windows.Forms.Form Dim myConnection As SqlConnection Dim myCommand As SqlCommand Dim dr As New SqlDataReader() 'declaring the objects
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) _ Handles MyBase.Load myConnection = New SqlConnection("server=localhost;uid=sa;pwd=;database=pubs") 'establishing connection. you need to provide password for sql server Try myConnection.Open() 'opening the connection myCommand = New SqlCommand("Select * from discounts", myConnection) 'executing the command and assigning it to connection dr = myCommand.ExecuteReader() While dr.Read() 'reading from the datareader MessageBox.Show("discounttype" & dr(0).ToString()) MessageBox.Show("stor_id" & dr(1).ToString()) MessageBox.Show("lowqty" & dr(2).ToString()) MessageBox.Show("highqty" & dr(3).ToString()) MessageBox.Show("discount" & dr(4).ToString()) 'displaying the data from the table End While dr.Close() myConnection.Close() Catch ex As Exception End Try End Sub
Try changing the line Dim dr As New SqlDataReader() to Dim dr As SqlDataReader instead and see if that helps. You don't need to create a new object of datareader there, it just needs to be of that type for when you use the ExecuteReader later on the command object.
That should help.
Damn, beaten to the punch! and btw keep those parenthesis off if you are going to use the ExecuteReader later. Parenthesis are only needed for new objects. If you keep them on, it will start flagging your executereader line as bogus.
This post has been edited by Martyr2: 4 Sep, 2007 - 10:48 AM
after removing the Imports System.Data.SqlClient.SqlDataReader and change the line dr As New ... to dr As Sql...
i encounter dis error
Error 1 Value of type 'System.Data.SqlClient.SqlDataReader' cannot be converted to '1-dimensional array of System.Data.SqlClient.SqlDataReader'. C:\Thesis\WindowsApplication1\WindowsApplication1\Form1.vb 20 18 WindowsApplication1
Error 2 'Read' is not a member of 'System.Array'. C:\Thesis\WindowsApplication1\WindowsApplication1\Form1.vb 21 19 WindowsApplication1
Error 3 'Close' is not a member of 'System.Array'. C:\Thesis\WindowsApplication1\WindowsApplication1\Form1.vb 30 13 WindowsApplication1
CODE
Imports System.Data.SqlClient
Public Class Form1 Inherits System.Windows.Forms.Form Dim myConnection As SqlConnection Dim myCommand As SqlCommand Dim dr As SqlDataReader() 'declaring the objects
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) _ Handles MyBase.Load myConnection = New SqlConnection("server=localhost;uid=sa;pwd=;database=pubs") 'establishing connection. you need to provide password for sql server Try myConnection.Open() 'opening the connection myCommand = New SqlCommand("Select * from discounts", myConnection) 'executing the command and assigning it to connection dr = myCommand.ExecuteReader() While dr.Read() 'reading from the datareader MessageBox.Show("discounttype" & dr(0).ToString()) MessageBox.Show("stor_id" & dr(1).ToString()) MessageBox.Show("lowqty" & dr(2).ToString()) MessageBox.Show("highqty" & dr(3).ToString()) MessageBox.Show("discount" & dr(4).ToString()) 'displaying the data from the table End While dr.Close() myConnection.Close() Catch ex As Exception End Try End Sub
Read the edits I had made to my last post. Drop those parenthesis on the dim line for sqldatareader. The parenthesis make it think you are trying to establish a new object.
4 Sep, 2007 - 05:26 AM
| 