CODEWITHSUNDEEP
sqlvb.netvisual-studio-2012
Phil J Fry
Phil J Fry

Reputation: 293

SQL and Visual Studios - Passing Variables

I need help with passing variables from my Visual Basic application into SQL queries to update my database and retrieve data depending on what variable I am using.

What is the correct way to do this :

My database is storing an appName, appLink and clickCount. Every time I click the associated app, it launches the link and then I would like it to increment the clickCount by one. to keep track of the most frequently used links.

I was thinking, 

UPDATE myTable
SET clickCount = clickCount + 1
WHERE appName = (variable I am passing | link user clicked on)

but how do I get the variable from VB into my SQL statement? I am going to be doing numerous functions in my VB app and will need to openly pass data to and from my SQL server. What is the best way to accomplish this?

Side Note... When should I use stored procedures or functions in SQL rather than a function in VB?

Finally, the only language I really know is Python, so I am learning both SQL and VB to complete this project. In Python, using SQLite, the code is all in the same place. I can write one function that does all of the above. Can I do that in VB?

Upvotes: 3

Views: 5731

Answers (2)

Steve
Steve

Reputation: 216293

Your question doesn't contains any actual code, so I will give you just a pseudocode. First I define a method called UpdateClickCount that receives the application name and the linkname

 Public Sub UpdateClickCount(ByVal appName as String, ByVal linkName as String)
     Using sqlCon = new SqlConnection(connectionString)
          sqlCon.Open()
          Dim sqlText = "UPDATE myTable SET clickCount = clickCount + 1 " & _
                        "WHERE appName = @name AND link = @link"
          Dim cmd = new SqlCommand(sqlText, sqlCon)
          cmd.Parameters.AddWithValue("@name", appName)
          cmd.Parameters.AddWithValue("@link", linkName)
          cmd.ExecuteNonQuery()
     End Using
 End Sub

Inside the method the first thing to do is open the SqlConnection using a connectionstring that contains all the information to reach the database.
Next the command text to update the database is defined and assigned to a string.
This command contains two parameters placeholders (@name and @link).
Now we can define the SqlCommand with the string and the connection.
The last thing to do before executing the command, is to define the two parameters and their values.

The ExecuteNonQuery submits everything to the database and your data is updated.
Generally this is the way to go for every command that update the database.

Upvotes: 4

Raj
Raj

Reputation: 1770

You can use the sqlcommand object to build your sql statement and use parameter placeholders to assign the varable.

Upvotes: 0

Related Questions