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!

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 :)

AMAZING THANK YOU!

Very helpful thank you sir.

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.

Wunderbar!!!!

🌹🌹🌹

hi nice that you did for my ideas just keep on moving O.E.I

Great!!' I` m almost absolute beginner but i have an opportunity to cache "how the engine work"

still watching :)

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! :)

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.

@VBToolbox

3 жыл бұрын

At first glance, it looks like you might be trying to run your parameters through the query engine, instead of the AddParam method. 🙂

@VBToolbox

3 жыл бұрын

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 🙂

@clw7777

3 жыл бұрын

crap, I need to sleep more! I totally missed that. blonde moment! thank you so much!

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

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?

@VBToolbox

3 жыл бұрын

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. 🙂

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.

@diglife

7 жыл бұрын

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.

@VBToolbox

7 жыл бұрын

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

@diglife

7 жыл бұрын

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.

gr8. hi can you help me to use it in stored procedure with return parameters

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.

@GemSky-bc8lc

4 жыл бұрын

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?

@bb7276

3 жыл бұрын

Have you found the cause because mine is doing the same thing.

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.

@VBToolbox

7 жыл бұрын

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.

@aubreylomax

10 ай бұрын

Give me back my fuck in idwntity

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.

@VBToolbox

2 жыл бұрын

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?

@user-ym7wm2qw9u

2 жыл бұрын

@@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

@VBToolbox

2 жыл бұрын

@@user-ym7wm2qw9u 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.

@user-ym7wm2qw9u

2 жыл бұрын

@@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.

@VBToolbox

2 жыл бұрын

@@user-ym7wm2qw9u 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.

How to use Stored Procedures in this tutorial

@VBToolbox

7 жыл бұрын

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. :-)

@DixitSoliya

7 жыл бұрын

Thanks...

@DixitSoliya

7 жыл бұрын

It works, but I am getting an error that parameter wasn't provided but I already did provide the parameters for procedure.

@VBToolbox

7 жыл бұрын

Is it the SQLControl parameter or the Stored Procedure parameter that is failing?

@DixitSoliya

7 жыл бұрын

Actually I have stored procedure with parameters. but error is coming from execQuery. I think.

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

@VBToolbox

7 жыл бұрын

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 Using stored procedures is preferred as it is cleaner, more flexible, and you can validate within the same scope and session as the insert.

@RTKN197

7 жыл бұрын

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