Thumbs Ups for you Sir, Excellent! Work, Save me a lot. I am doing my Final Project in VB.Net with SQL Server Database. Kindly upload a video for OOPs like Inheritance, Aggregation, Association and Composition - Many Thanks :)
Great video. I'm new to SQL server, but one way to help prevent injections would to write some code that would not allow any characters in the username text box other than letters or numbers. You could maybe allow certain characters for email addresses.
Your tutorials have been a lifesaver. I've built a whole interface for my managers using access, but now I'm trying to build an interface for me using Sql and I'm hitting a wall. I keep getting the error "too many arguments to Public sub execQuery(Query as string)" When I add in the optional part, it freaks out saying stuff isn't Boolean. I don't really need that part of the code as I'm the only person who will be in here so I was skipping it. When I get the Too many arguments error, it's pointing to the SqlControl file, but I've got the angry red squiggles showing up under my txt fields in the form code. so where I'm adding the parameters, the txt's at the end are angry and are what are throwing the error, but the error points to the control. 'Create Parameters Sql.ExecQuery("@FName", txtFName.Text) Sql.ExecQuery("@MName", txtMName.Text) Sql.ExecQuery("@FLName", txtFLName.Text) Any help to get me unstuck would be greatly appreciated.
Sorry, just to clarify: You'll want to load up your params with your Sql.AddParam method instead of Sql.ExecQuery. Once loaded, then you can execute your full SQL statement with the Sql.ExecQuery. Reference: 10:00 in video 🙂
Regardiing to getting the retrieving the added record... since you wan to get ITS ID why can't u just re querry with the sql command : select ID from tablename where name=@name and pass=@pass since you have those 2 values right there?. Wouldn't it be safest assuming the record is added successfully?
Sometimes you'll run into timing problems, where the VB code is actually executing faster than the database services are updating records, so there's a chance that the subsequent pull will misfire. There is a special command for safely grabbing the ID of the most recent record from the current session. I believe I have this implemented in a SQLControl class somewhere. If I can find it I can share the Dropbox link. 🙂
Hello, I'm having an error: must declare the scalar variable @id and I can't figure it out, I search the online explanations are not helping me. I'm using your SQL Class code. It works fine for every other method, except inserting profiles. I have three relational tables link each other. Thanks in advance Sr.
I'm not entirely certain why that might be occurring unless the param wasn't properly defined, or possibly @id is a reserved SQL keyword. An easy test would be to rename your parameter and see if it likes the new one better. Also, are you trying to insert a value into an auto number column?
@@VBToolbox Please take a look at my code. I'm willing to pay you for your time: If m_ValidateID = True Then m_ValidateID = False : Exit Sub If m_BuscarID = False Then m_EmpIDInsert = EmpID ' The EmpID string variable is Public ReadOnly Property EmpID As String, Return TxtEmpID.Text.Trim m_SQL.AddParam("@EmpID", m_EmpIDInsert) Else m_EmpIDInsert = EmpID TxtEmpID.Text = m_EmpIDInsert m_SQL.AddParam("@EmpID", m_EmpIDInsert) End If m_SQL.AddParam("@FName", LName) m_SQL.AddParam("@LName", FName) m_SQL.AddParam("@UInitials", Initials) m_SQL.AddParam("@Email", UserEmails) m_SQL.AddParam("@PhoneNumb", PhoneNumb) m_SQL.AddParam("@Status", Status) SearchDptID() m_SQL.AddParam("@Dept", m_DeptID) SearchJobID() m_SQL.AddParam("@JobTitle", m_JobID) SearchPermissionID() m_SQL.AddParam("@Permission", m_PermissionID) m_SQL.AddParam("@TransDates", Dates) If m_Loging = String.Empty Then m_IDGK = CInt(m_CredentialNewID) Else m_IDGK = Variables.m_IDLogin End If m_SQL.AddParam("@TransMadeBy", m_IDGK) m_SQL.ExecQuery("INSERT INTO Employees (EmpID, LName, FName, Initial, Email, PhoneNumber, Status, DepartmentID, JobID, PermissionID, TransDate, TransMadeBy)" & "VALUES(@EmpID, @LName, @FName, @UInitials, @Email, @PhoneNumb, @Status, @Dept, @JobTitle, @Permission, @TransDates, @TransMadeBy);") 'If m_SQL.HasException(True) Then Exit Sub If m_SQL.HasException(True) Then m_SQL.AddParam("@JID", m_JobID) m_SQL.ExecQuery("Delete From Jobs Where JobID = @JID ") m_SQL.AddParam("@DID", m_DeptID) m_SQL.ExecQuery("Delete From Department Where DepartmentID = @DID ") m_SQL.AddParam("@UID", m_IDGK) m_SQL.ExecQuery("Delete From GateKeeper Where LoginCID = @UID ") Exit Sub End If If m_CheckUsers = False Then If m_UserNames String.Empty Then Variables.UserProperties() End If End If LoadUsers() m_Zero = False m_BuscarID = False LblMessages.Text = "User " & UserNames & " was created!" Tiempo.Start() m_Commit = True End If Please, let me know if you can help me. wararque@epbfi.com
@@Puerto_Ayacucho_VE One thing that may be an issue is the concatenation in the ExecQuery between your INSERT and VALUES. There is no space between those strings, so maybe add a space after TransMadeBy) or directly in front of VALUES. It's difficult to read through it all on my phone, but perhaps later I can fire up the generator and get on my computer and take a closer look.
@@VBToolbox I fixed the concatenation space and I'm still getting error: must declare the scalar variable "@EmpID" Also, you can contact me at waraque@epbfi.com Thank you! Sr.
@@Puerto_Ayacucho_VE I tried to email you but got "recipient rejected" bounce. I need the structure of your Employees table and the variables you're sending to your params.
Thank you so much Sir! But how about checking duplicate data before inserting record? Or, getting the login username or id whenever you login? Or, login first before inserting a data. Thank you in advance sir! :)
hi sir , when i make setup for project include database access and install on another computer .display massage that cannot know the path of database ....thanks with best regard
In your table design, under the Properties for the ID column, there is a property group called "Identity Specification". Under that is a property called (Is Identity). Set that property to "Yes" and it will populate the Identity Increment and Identity Seed properties. Changing the Primary Key may require a rebuild of the table.
You can simply execute the Stored Procedure from the SQL.ExecQuery. Example 1: SQL.ExecQuery("exec sp_query_users;") Example 2: SQL.ExecQuery("exec sp_add_user 'MyName', 'MyPassword';") Example 3: SQL.AddParam("@user", txtUser.Text) SQL.AddParam("@pass", txtPass.Text) SQL.ExecQuery("exec sp_add_user @user,@pass;") I hope that helps. :-)
Hi, this tutorial is great, thank you for taking the time to create. The only issue i have now is @ the end when i run i get ExecQuery Error Could not find stored procedure 'True". What am i missing? Again, thanks these tutorials are fantastic.
It worked fine until i got to the part to test @ 36:06. If i change SQL.ExecQuery("INSERT INTO TESTTABLE (username,password,active,admin,joindate) " & "VALUES (@user,@pass,@active,@admin,GETDATE());", True) to false it works but with true i get the above error.
It sounds to me like there may be an error in the Optional ReturnIdentity section that we build @ 27:36 If it's "False" it will skip the added section and work for you, but if it's true it will execute the subsequent query. Check the second query to ensure that it's correct: If ReturnIdentity = True Then Dim ReturnQuery As String = "SELECT @@IDENTITY As LastID;" DBCmd = New SqlCommand(ReturnQuery, DBCon) DBDT = New DataTable DBDA = New SqlDataAdapter(DBCmd) RecordCount = DBDA.Fill(DBDT) End If
Thanks, in the line right below Select @@IDENTITY i had DBCmd = New SqlCommand(ReturnIdentity, DBCon) and not ReturnQuery. Thanks for your help, I am totally new to Visual Basic and your tutorials have been very helpful. Keep up the great work.
hello, Video is so great, Thank you for that. When I execute Insert Query to my remote sql server , I dont get any error and get message for successful insertion of data in the sql server. But when I check in SQL management studio, I cant see the data in the table. Can you guide me for that? Thank you in advance.
Mine too. I checked everything what might I missed - but it looks every seems okay. I was thinking the SQLControl - but it works on my datagridview how come not on insert?
Just a comment on how to use it with SCOPE_IDENTITY(). It dosn't work as in the video sicnce (as I understand) this command must be in the same query with INSERT command (within the same command string). What I did to make it work is: Public Sub ExecQuery(Query As String, Optional ReturnIdentity As Boolean = False) 'RESET QUERY STAT RecordCount = 0 Exception = "" Try DBCon.Open() ' CREATE DB COMMAND If ReturnIdentity Then Query += "SELECT SCOPE_IDENTITY() As LastID;" DBCmd = New SqlCommand(Query, DBCon) ' LOAD PARAMS INTO DB COMMAND Params.ForEach(Sub(p) DBCmd.Parameters.Add(p)) ' CLEAR PARAM LIST Params.Clear() ' EXECUTE COMMAND & FILL DATASET DBDT = New DataTable DBDA = New SqlDataAdapter(DBCmd) RecordCount = DBDA.Fill(DBDT) If ReturnIdentity Then If DBDT.Rows.Count > 0 Then Dim r As DataRow = DBDT.Rows(0) MsgBox("last identity = " & r("LastID").ToString) End If End If Catch ex As Exception ' CAPRTURE ERRORS Exception = "ExecQuery Error: " & vbNewLine & ex.Message Finally ' CLOSE CONNECTION If DBCon.State = ConnectionState.Open Then DBCon.Close() End Try End Sub
There are a few different ways that you can check for an existing value in the database. First off, SQL Server will automatically check the uniqueness of Primary Keys and indexes that demand unique values. If you attempt to insert duplicate records into these columns, which are also likely to be the ones you want to validate in the first place, SQL will return an exception. If prefer not to rely on these exceptions, then you must do a query on the fields that you wish to validate before you execute your insert. You can do this via a simple query in VB, a SQL Stored Procedure, or a SQL Function. Example of simple VB Test & Insert: SQL.AddParam("@value", MyTextBox.Text) SQL.ExecQuery("SELECT validateColumn FROM yourTable WHERE validateColumn=@value;") If SQL.RecordCount < 1 Then InsertYourNewRecord() Using stored procedures is preferred as it is cleaner, more flexible, and you can validate within the same scope and session as the insert.
![SQL Tutorial for Beginners [Full Course]](/img/n.gif)
Great video, can’t thank you enough. It’s resolved a lot of issues I’ve been working on, as well as clearing things up.
This is an excellent video series. Each video has been easy to follow and informative.
Another great video that has taught me so much about good, clean, efficient coding. Thank you.
nice, its easy to unterstand! thx for this ... wait for more!
Very helpful thank you sir.
Thumbs Ups for you Sir, Excellent! Work, Save me a lot. I am doing my Final Project in VB.Net with SQL Server Database. Kindly upload a video for OOPs like Inheritance, Aggregation, Association and Composition - Many Thanks :)
Great video. I'm new to SQL server, but one way to help prevent injections would to write some code that would not allow any characters in the username text box other than letters or numbers. You could maybe allow certain characters for email addresses.
Your tutorials have been a lifesaver. I've built a whole interface for my managers using access, but now I'm trying to build an interface for me using Sql and I'm hitting a wall.
I keep getting the error "too many arguments to Public sub execQuery(Query as string)"
When I add in the optional part, it freaks out saying stuff isn't Boolean. I don't really need that part of the code as I'm the only person who will be in here so I was skipping it.
When I get the Too many arguments error, it's pointing to the SqlControl file, but I've got the angry red squiggles showing up under my txt fields in the form code. so where I'm adding the parameters, the txt's at the end are angry and are what are throwing the error, but the error points to the control.
'Create Parameters
Sql.ExecQuery("@FName", txtFName.Text)
Sql.ExecQuery("@MName", txtMName.Text)
Sql.ExecQuery("@FLName", txtFLName.Text)
Any help to get me unstuck would be greatly appreciated.
At first glance, it looks like you might be trying to run your parameters through the query engine, instead of the AddParam method. 🙂
Sorry, just to clarify:
You'll want to load up your params with your Sql.AddParam method instead of Sql.ExecQuery.
Once loaded, then you can execute your full SQL statement with the Sql.ExecQuery.
Reference: 10:00 in video
🙂
crap, I need to sleep more! I totally missed that. blonde moment! thank you so much!
AMAZING THANK YOU!
Regardiing to getting the retrieving the added record... since you wan to get ITS ID why can't u just re querry with the sql command : select ID from tablename where name=@name and pass=@pass since you have those 2 values right there?. Wouldn't it be safest assuming the record is added successfully?
Sometimes you'll run into timing problems, where the VB code is actually executing faster than the database services are updating records, so there's a chance that the subsequent pull will misfire. There is a special command for safely grabbing the ID of the most recent record from the current session.
I believe I have this implemented in a SQLControl class somewhere. If I can find it I can share the Dropbox link. 🙂
Hello, I'm having an error: must declare the scalar variable @id and I can't figure it out, I search the online explanations are not helping me. I'm using your SQL Class code. It works fine for every other method, except inserting profiles. I have three relational tables link each other. Thanks in advance Sr.
I'm not entirely certain why that might be occurring unless the param wasn't properly defined, or possibly @id is a reserved SQL keyword. An easy test would be to rename your parameter and see if it likes the new one better. Also, are you trying to insert a value into an auto number column?
@@VBToolbox Please take a look at my code. I'm willing to pay you for your time:
If m_ValidateID = True Then m_ValidateID = False : Exit Sub
If m_BuscarID = False Then
m_EmpIDInsert = EmpID ' The EmpID string variable is Public ReadOnly Property EmpID As String, Return TxtEmpID.Text.Trim
m_SQL.AddParam("@EmpID", m_EmpIDInsert)
Else
m_EmpIDInsert = EmpID
TxtEmpID.Text = m_EmpIDInsert
m_SQL.AddParam("@EmpID", m_EmpIDInsert)
End If
m_SQL.AddParam("@FName", LName)
m_SQL.AddParam("@LName", FName)
m_SQL.AddParam("@UInitials", Initials)
m_SQL.AddParam("@Email", UserEmails)
m_SQL.AddParam("@PhoneNumb", PhoneNumb)
m_SQL.AddParam("@Status", Status)
SearchDptID()
m_SQL.AddParam("@Dept", m_DeptID)
SearchJobID()
m_SQL.AddParam("@JobTitle", m_JobID)
SearchPermissionID()
m_SQL.AddParam("@Permission", m_PermissionID)
m_SQL.AddParam("@TransDates", Dates)
If m_Loging = String.Empty Then
m_IDGK = CInt(m_CredentialNewID)
Else
m_IDGK = Variables.m_IDLogin
End If
m_SQL.AddParam("@TransMadeBy", m_IDGK)
m_SQL.ExecQuery("INSERT INTO Employees (EmpID, LName, FName, Initial, Email, PhoneNumber, Status, DepartmentID, JobID, PermissionID, TransDate, TransMadeBy)" & "VALUES(@EmpID, @LName, @FName, @UInitials, @Email, @PhoneNumb, @Status, @Dept, @JobTitle, @Permission, @TransDates, @TransMadeBy);")
'If m_SQL.HasException(True) Then Exit Sub
If m_SQL.HasException(True) Then
m_SQL.AddParam("@JID", m_JobID)
m_SQL.ExecQuery("Delete From Jobs Where JobID = @JID ")
m_SQL.AddParam("@DID", m_DeptID)
m_SQL.ExecQuery("Delete From Department Where DepartmentID = @DID ")
m_SQL.AddParam("@UID", m_IDGK)
m_SQL.ExecQuery("Delete From GateKeeper Where LoginCID = @UID ")
Exit Sub
End If
If m_CheckUsers = False Then
If m_UserNames String.Empty Then
Variables.UserProperties()
End If
End If
LoadUsers()
m_Zero = False
m_BuscarID = False
LblMessages.Text = "User " & UserNames & " was created!"
Tiempo.Start()
m_Commit = True
End If
Please, let me know if you can help me. wararque@epbfi.com
@@Puerto_Ayacucho_VE
One thing that may be an issue is the concatenation in the ExecQuery between your INSERT and VALUES. There is no space between those strings, so maybe add a space after TransMadeBy) or directly in front of VALUES.
It's difficult to read through it all on my phone, but perhaps later I can fire up the generator and get on my computer and take a closer look.
@@VBToolbox I fixed the concatenation space and I'm still getting error: must declare the scalar variable "@EmpID"
Also, you can contact me at waraque@epbfi.com
Thank you! Sr.
@@Puerto_Ayacucho_VE I tried to email you but got "recipient rejected" bounce. I need the structure of your Employees table and the variables you're sending to your params.
Wunderbar!!!!
Thank you so much Sir!
But how about checking duplicate data before inserting record?
Or, getting the login username or id whenever you login?
Or, login first before inserting a data.
Thank you in advance sir! :)
🌹🌹🌹
hi sir , when i make setup for project include database access and install on another computer .display massage that cannot
know the path of database ....thanks with best regard
Great!!' I` m almost absolute beginner but i have an opportunity to cache "how the engine work"
still watching :)
hi
nice that you did for my ideas just keep on moving
O.E.I
Hey, if i have primary key ID in my table how to make it to auto increase it like you do? so i dont have to write new id when i insert new user.
In your table design, under the Properties for the ID column, there is a property group called "Identity Specification". Under that is a property called (Is Identity). Set that property to "Yes" and it will populate the Identity Increment and Identity Seed properties.
Changing the Primary Key may require a rebuild of the table.
Give me back my fuck in idwntity
How to use Stored Procedures in this tutorial
You can simply execute the Stored Procedure from the SQL.ExecQuery.
Example 1:
SQL.ExecQuery("exec sp_query_users;")
Example 2:
SQL.ExecQuery("exec sp_add_user 'MyName', 'MyPassword';")
Example 3:
SQL.AddParam("@user", txtUser.Text)
SQL.AddParam("@pass", txtPass.Text)
SQL.ExecQuery("exec sp_add_user @user,@pass;")
I hope that helps. :-)
Thanks...
It works, but I am getting an error that parameter wasn't provided but I already did provide the parameters for procedure.
Is it the SQLControl parameter or the Stored Procedure parameter that is failing?
Actually I have stored procedure with parameters. but error is coming from execQuery. I think.
Hi, this tutorial is great, thank you for taking the time to create. The only issue i have now is @ the end when i run i get ExecQuery Error Could not find stored procedure 'True". What am i missing? Again, thanks these tutorials are fantastic.
It worked fine until i got to the part to test @ 36:06. If i change SQL.ExecQuery("INSERT INTO TESTTABLE (username,password,active,admin,joindate) " &
"VALUES (@user,@pass,@active,@admin,GETDATE());", True) to false it works but with true i get the above error.
It sounds to me like there may be an error in the Optional ReturnIdentity section that we build @ 27:36
If it's "False" it will skip the added section and work for you, but if it's true it will execute the subsequent query. Check the second query to ensure that it's correct:
If ReturnIdentity = True Then
Dim ReturnQuery As String = "SELECT @@IDENTITY As LastID;"
DBCmd = New SqlCommand(ReturnQuery, DBCon)
DBDT = New DataTable
DBDA = New SqlDataAdapter(DBCmd)
RecordCount = DBDA.Fill(DBDT)
End If
Thanks, in the line right below Select @@IDENTITY i had
DBCmd = New SqlCommand(ReturnIdentity, DBCon) and not ReturnQuery.
Thanks for your help, I am totally new to Visual Basic and your tutorials have been very helpful. Keep up the great work.
hello, Video is so great, Thank you for that.
When I execute Insert Query to my remote sql server , I dont get any error and get message for successful insertion of data in the sql server. But when I check in SQL management studio, I cant see the data in the table. Can you guide me for that?
Thank you in advance.
Mine too. I checked everything what might I missed - but it looks every seems okay. I was thinking the SQLControl - but it works on my datagridview how come not on insert?
Have you found the cause because mine is doing the same thing.
gr8. hi can you help me to use it in stored procedure with return parameters
Just a comment on how to use it with SCOPE_IDENTITY(). It dosn't work as in the video sicnce (as I understand) this command must be in the same query with INSERT command (within the same command string). What I did to make it work is:
Public Sub ExecQuery(Query As String, Optional ReturnIdentity As Boolean = False)
'RESET QUERY STAT
RecordCount = 0
Exception = ""
Try
DBCon.Open()
' CREATE DB COMMAND
If ReturnIdentity Then Query += "SELECT SCOPE_IDENTITY() As LastID;"
DBCmd = New SqlCommand(Query, DBCon)
' LOAD PARAMS INTO DB COMMAND
Params.ForEach(Sub(p) DBCmd.Parameters.Add(p))
' CLEAR PARAM LIST
Params.Clear()
' EXECUTE COMMAND & FILL DATASET
DBDT = New DataTable
DBDA = New SqlDataAdapter(DBCmd)
RecordCount = DBDA.Fill(DBDT)
If ReturnIdentity Then
If DBDT.Rows.Count > 0 Then
Dim r As DataRow = DBDT.Rows(0)
MsgBox("last identity = " & r("LastID").ToString)
End If
End If
Catch ex As Exception
' CAPRTURE ERRORS
Exception = "ExecQuery Error: " & vbNewLine & ex.Message
Finally
' CLOSE CONNECTION
If DBCon.State = ConnectionState.Open Then DBCon.Close()
End Try
End Sub
sir how to validate value exists
There are a few different ways that you can check for an existing value in the database.
First off, SQL Server will automatically check the uniqueness of Primary Keys and indexes that demand unique values. If you attempt to insert duplicate records into these columns, which are also likely to be the ones you want to validate in the first place, SQL will return an exception.
If prefer not to rely on these exceptions, then you must do a query on the fields that you wish to validate before you execute your insert. You can do this via a simple query in VB, a SQL Stored Procedure, or a SQL Function.
Example of simple VB Test & Insert:
SQL.AddParam("@value", MyTextBox.Text)
SQL.ExecQuery("SELECT validateColumn FROM yourTable WHERE validateColumn=@value;")
If SQL.RecordCount < 1 Then InsertYourNewRecord()
Using stored procedures is preferred as it is cleaner, more flexible, and you can validate within the same scope and session as the insert.
thanks for this sir . wait for the upcoming tutorial . sir make a tutorial for the remote desktop using vb.net
Please see your emails..:)
huevos de pascua