Tag Archives: SQL Server

Using Grid.MVC in MVC4 using VB.NET

There are numerous example for C3 but I had to spend quite some time to adapt it to my project using VB.NET

 

Hope it can be of help to you.

I assume that you have

already added the GridMVC package from NUGET.

Here are step by step guide

Controller

No change needed , see a sample below,

Function Index() As ActionResult
Return View(db.Categories.ToList())
End Function

View

Add the following import at the top of your view

@imports  GridMVC.html

 

You would need to reference the following also

 

Add the stylesheets (check the location after you install the GridMVC package)

<link href=”~/Content/Gridmvc.css” rel=”stylesheet” />
    <link href=”~/Content/gridmvc.datepicker.min.css” rel=”stylesheet” />
For your paging support

Install the PagedList package also

Once you have installed this package then add the following reference to your view, this will help you format your paging buttons

properly.

 <link href=”~/Content/PagedList.css” rel=”stylesheet” />

Add the following code for your Grid to appear – In the following examples my Model has three fields – CatId, CatName

and ParentCatId. Make changes to suit your needs.

@Html.Grid(Model).Columns(Function(columns)
                                   
                              columns.Add(Function(o) o.CatID).Titled(“Cat #“).SetWidth(40)
                                 
                              columns.Add(Function(o) o.CatName).Titled(“Category“).SetWidth(100)
                                  
                              columns.Add(Function(o) o.ParentCatID).Titled(“Parent Cat #“).SetWidth(40)
                          
                          End Function
).WithPaging(10).Sortable(True)

 

 

 

A potentially dangerous Request.Form value was detected from the client – error in MVC4 in vb.net

In your Create Actions in a Controller in MVC4, if you try to save text with html tags then you get this annoying error.

A potentially dangerous Request.Form value was detected from the client

Description: ASP.NET has detected data in the request that is potentially dangerous because it might include HTML markup or script. The data might represent an attempt to compromise the security of your application, such as a cross-site scripting attack. If this type of input is appropriate in your application, you can include code in a web page to explicitly allow it. For more information, see http://go.microsoft.com/fwlink/?LinkID=212874.

This is a great protection if you do not want your client to save html text.

Here is how to get rid of this error

In the following example we have a create action which saves a new record. MVC4 controller will have two functions.

The first function is for GET ( this is the one which will render the page to save the new record

 

The second function is for POST. This function is responsible to receive the model
which has been populated by the data which the user has filled in on the form. We have to add the following text

<ValidateInput(False)>

 

for C# add

[ValidateInput(false)]

 

 

 

 

‘ GET: /admin/ManageAds/Create

Function Create() As ActionResult
Return View()
End Function

 

 

‘ POST: /admin/ManageAds/Create
<HttpPost()> _
<ValidateAntiForgeryToken()> _
<ValidateInput(False)>
Function Create(ByVal ad As Ad) As ActionResult
If ModelState.IsValid Then
db.Ads.Add(ad)
db.SaveChanges()
Return RedirectToAction(“Index”)
End If

Return View(ad)
End Function

 

How to fill a ComboBox using a DataTable in VB.NET

A Combo box has two properties

 

1.One which displays the items in the combo box called the DISPLAY MEMBER

2. Another one which stores the underlying ID for the Item which is being Displayed.

 

eg your table may have EmployeeId and EmployeeName.

You would like to display the EmployeeName in the ComboBox and when a user selects an employee name you would like to save the EmployeeID.

Function Fillcombo(ByVal SQL As String, ByVal CBO As ComboBox, ByVal DisplayMember As String, ByVal ValueMember As String)
Dim dt As New DataTable                  'Create a New DataTable
Dim dc As New SqlCommand              'Create a New SQLCommand
Dim da As New SqlDataAdapter         ' Create a New SQL Data Adapter
dc.CommandTimeout = 0
dc.CommandText = sql
da.SelectCommand = dc
dc.Connection = cn

da.Fill(dt)    ‘ Use the DataAdapter to fill the DataTable with the records
Try
CBO.DataSource = dt
CBO.DisplayMember = DisplayMember   ‘ This is the item which will be displayed on the combo box  eg Employee Name
CBO.ValueMember = ValueMember       ‘ This is the value which is stored eg. employeeID

Catch
MsgBox(“Unable to Fill the Combobox:” & CBO.Name & ” with values” & “:” & Err.Description)
End Try
End Function

 

 How to use the above function

Fillcombo(“Select EmployeeID,EmployeeName from Employees”, Me.EmployeeCombo, “EmployeeName”, “EmployeeID”)

How to connect to a SQL Server in VB.NET

Connection to a SQL Server database canbe achieved in vb.net quite easily.
The Windows Forms based applications as well as ASP.NET based applications can connect to the SQL Server database in a similar way. But the way the ConnectionStrings are stored is slightly different.
1. Windows Forms Applications – How to make a connection in a Windows Forms based application
I normally use a module in which I have the following two functions – one for opening a connection and one for closing the connection.
Step 1:
At the top of the module write the following line

Imports System.Data.SqlClient

Step 2

Also declare a public string in which you can store the connection string as follows
( well there are different other ways to store the connection string but I will not discuss them here)

Public gsConnString As String = "Data Source=100.200.300.400;Initial Catalog=testdatabase;
User Id=testuser;Password=testpassword;"

Step 3

Function OpenConnection(ByVal cn As SqlConnection) As Boolean
Try
If cn.State <> ConnectionState.Closed Then
cn.Close()
End If
cn.ConnectionString = gsConnString
cn.Open()
Return True ' If the connection is made then it returns true
Catch
MsgBox("Unable to Connect to the database" & Err.Description, vbCritical)
Return False ' If the connection is not made then it returns false
End Try

End Function

Function CloseConnection(ByVal cn As SqlConnection) As Boolean

Try
If cn.State <> ConnectionState.Closed Then
cn.Dispose()
cn.Close()
End If

Return True
Catch
End Try

End Function

Now that you have written the functions and the connection string for your SQL Server . Now you are ready to open the connection in your form or module
Step 1
write the following line at the top of the form or the module

Imports System.Data.SqlClient

Step 2
You can use the openconnection and the close connection in your programs the following way.
This is a useful generic function which you can use to fill and return a datatable. You simply pass the sql statement to it and then you can get a datatable with the results
Once you get the datatable you can use it eg. use it in a datagrid

Function GetDataTable(ByVal sql As String) As DataTable
Dim dt As New DataTable
Dim dc As New SqlCommand
Dim cn As New SqlConnection
dc.CommandTimeout = 0
Dim da As New SqlDataAdapter
OpenConnection(cn)
da.SelectCommand = dc
dc.Connection = cn
dc.CommandText = sql
Try
da.Fill(dt)
Return dt
Catch
MsgBox("Error encountered, please try again:(" & sql & ")" & Err.Description, MsgBoxStyle.Critical)
Finally
dt.Dispose()
dc.Dispose()
CloseConnection(cn)
End Try

End Function

In the following function we are just passing the SQL string to the GetDataTable function which returns the datatable filled with the results. and the Datagrid is being populated with this table.

Private Sub btnFetchData_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFetchData.Click
Dim sql As String
sql = "SELECT UserID,SectionID, PostID, ThreadID, PostAuthor, PostLevel,PostDate FROM dbo.cs_Posts order by PostID Desc"
Me.DataGridView1.DataSource = GetDataTable(sql)

End Sub

In ASP.NET the Connection string is normally stored in the web.config file

Following example shows a connection string for a sql server 2008 database.



This is how you would read the connection string from the web.config file

Public gsConnString As String = ConfigurationSettings.ConnectionStrings("vbnet_ConnectionString").connectionstring

They you can open the connection using the Openconnection function as described above.

How to execute a SQL Server Procedure in VB.NET

How to execute a Backend Procedure in SQL Server using VB.NET

Here is a function which allows you to execute a SQL Server Procedure in VB.NET

Function ExecuteProcedure_DeleteRecord() as boolean

Dim cn As New SqlConnection
Dim cmd As New SqlCommand
Dim sql As String

sql = "cs_Post_Delete"
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@SectionID", "200") ' string then enclose it in the double quotes
cmd.Parameters.AddWithValue("@SettingsID", 1000) ' numeric value then you can put it without any quotes
cmd.Parameters.AddWithValue("@PostID", Me.TextBox1.Text) ' Here we are picking a value from a textbox
cmd.Parameters.AddWithValue("@ResetStatistics", 1)
cmd.Parameters.AddWithValue("@DeletedBy", 2109)
cmd.Parameters.AddWithValue("@Reason", "Deleted due to SPAM")
cmd.CommandText = sql
cmd.Connection = cn

Try
'Call Open database - connect to the database
OpenConnection(cn) ' I use a seperate function to open a new connection
dc.ExecuteNonQuery()

Catch
MsgBox("Unable to Delete the record:," & Err.Description)
'Finally
da.Dispose()
dc.Dispose()
dcB.Dispose()
CloseConnection(cn) ' I use a seperate function to close an existing connection
MsgBox("Record Deleted")
End Try

End Sub

Fix the SQL Single quote problems in SQL in vb.net

When the sql contains the single quotes then they have to be properly escaped otherwise it will give errors

just pass the field value which you want to check and the following function will return you the properly escaped string.

eg. o’brien will be returned as o”brien and this will not cause your sql to break.

Function FixSql(ByVal sStr As String) As String

FixSql = Replace(sStr, “‘”, “””)

End Function