ASP.NET
ASP.NET
Here's another often asked question :
(1) The first thing to do if you have datetime columns that can take NULLs is to import the name space SqlTypes.
<%@ Import Namespace="System.Data.SqlTypes" %>
and then the actual code
Dim myCommand As SqlCommand
Dim
myParam As SqlParameter
myCommand
= New SqlCommand()
myCommand.Connection
= objcon
myCommand.CommandText
= "usp_testproc"
myCommand.CommandType
= CommandType.StoredProcedure
myCommand.Parameters.Add(New SqlParameter("@ProductName",SqlDbType.VarChar,50))
myCommand.Parameters(
"@ProductName").Value = "New Product"
myCommand.Parameters.Add(New SqlParameter("@Quantity",SqlDbType.int))
myCommand.Parameters("@Quantity").Value = 100
myCommand.Parameters.Add(New SqlParameter("@ProductDescription",SqlDbType.VarChar,50))
myCommand.Parameters("@ProductDescription").Value = DBNull.Value
'Now here's a date column
myCommand.Parameters.Add(New SqlParameter("@OrderDate",SqlDbType.DateTime))
If txtorderdate.Text.Trim.Length = 0 Then
myCommand.Parameters(
"@OrderDate").Value = Sqldatetime.Null
Else
myCommand.Parameters("@OrderDate").Value = Convert.ToDate(txtorderdate.Text)
End If
Try
If objCon.State = 0 Then objCon.Open()
mycommand.ExecuteNonQuery()
Catch
exc As Exception
Response.Write(exc)
Finally
If objCon.State = ConnectionState.Open Then
objCon.Close()
End If
End
Try
And thats about it.
Happy Programming !!
I cannot stress enough the importance of using Parameteried queries in ASP.NET. Apart from not having to worry about escaping quotes and other xtra characters your app is saved from SQL Injection attack.
So knowing how important it is to use Parameterized queries I havent seen any sample article using SQL 2000. So I decided to write up one.
Dim
myCommand As SqlCommand
Dim
myParam As SqlParameter
myCommand
= New SqlCommand()
myCommand.Connection
= objcon
myCommand.CommandText
= "UPDATE Products SET ProductName=@ProductName AND Quantity=@Quantity WHERE Productid=@Productid"
myCommand.Parameters.Add(New SqlParameter("@ProductId",SqlDbType.Int))
myCommand.Parameters(
"@ProductId").Value = 25
myCommand.Parameters.Add(
New SqlParameter("@ProductName",SqlDbType.VarChar,50))
myCommand.Parameters(
"@ProductName").Value = "New Product"
myCommand.Parameters.Add(
New SqlParameter("@Quantity",SqlDbType.int))
myCommand.Parameters(
"@Quantity").Value = 100
Try
If objCon.State = 0 Then objCon.Open()
mycommand.ExecuteNonQuery()
Catch
exc As Exception
Response.Write(exc)
Finally
If objCon.State = ConnectionState.Open Then
objCon.Close()
End If
End
Try
(2) If you are using a stored proc and have any return values that you are expecting back from the stored proc in the form of OUTPUT parameters, you would also need to add the OUTPUT parameter in as :
Dim myCommand As SqlCommand
Dim
myParam As SqlParameter
myCommand
= New SqlCommand()
myCommand.Connection
= objcon
myCommand.CommandText
= "usp_testproc"
myCommand.CommandType
= CommandType.StoredProcedure
myCommand.Parameters.Add(New SqlParameter("@ProductName",SqlDbType.VarChar,50))
myCommand.Parameters(
"@ProductName").Value = "New Product"
myCommand.Parameters.Add(New SqlParameter("@Quantity",SqlDbType.int))
myCommand.Parameters("@Quantity").Value = 100
myCommand.Parameters.Add(New SqlParameter("@ProductId",SqlDbType.Int))
myCommand.Parameters.Direction = ParameterDirection.Output
Try
If objCon.State = 0 Then objCon.Open()
mycommand.ExecuteNonQuery()
Response.Write(Convert.ToInt16(mycommand.Parameters("@ProductId").Value))
Catch
exc As Exception
Response.Write(exc)
Finally
If objCon.State = ConnectionState.Open Then
objCon.Close()
End If
End
Try
Thats it.
Happy programming !!!
This article describes on how to generate a CR by running a query against an SQL Server 2000 Database. Add the CR object from the designer view.
1. The usual Imports
Imports
System.Data.SqlClient
Imports
CrystalDecisions.CrystalReports.Engine
Imports
CrystalDecisions.Shared
2. The actual Code
Dim repId as Integer
Dim myConnection As New SqlClient.SqlConnection()
myConnection.ConnectionString = "server=local;database=Northwind;Integrated Security=SSPI"
Dim MyCommand As New SqlClient.SqlCommand()
MyCommand.Connection = myConnection
MyCommand.CommandText = "Select CategoryId,CategoryName,DEscription from Categories order by CategoryID asc"
MyCommand.CommandType = CommandType.Text
Dim MyDA As New SqlClient.SqlDataAdapter()
MyDA.SelectCommand = MyCommand
Dim myDS As New DataSet()
'This is our DataSet created at Design Time
MyDA.Fill(myDS, "Categories")
'You have to use the same name as that of your Dataset that you created during design time
Dim oRpt As New CrystalReport2()
' This is the Crystal Report file created at Design Time
oRpt.SetDataSource(myDS)
' Set the SetDataSource property of the Report to the Dataset
CrystalReportViewer1.ReportSource = oRpt
' Set the Crystal Report Viewer's property to the oRpt Report object that we created
'Export the report into a html page
Dim CrReport As New ReportDocument()
Dim htmlOpts As New HTMLFormatOptions()
'Set the HTML format options
With htmlOpts
.HTMLBaseFolderName = "C:\Crystal\"
.HTMLFileName = "Testing" & now()& ".htm"
.HTMLEnableSeparatedPages =
False
.HTMLHasPageNavigator =
False
End With
oRpt.ExportOptions.FormatOptions = htmlOpts
'Export the report.
oRpt.ExportOptions.ExportDestinationType = ExportDestinationType.DiskFile
oRpt.ExportOptions.ExportFormatType = ExportFormatType.HTML40
' Trap any errors that occur on export
Try
'Export the report
oRpt.Export()
Catch err As Exception
MessageBox.Show(err.ToString())
End Try
myConnection .Close()
This article describes how to go through a bunch of HTML files in a folder and open each HTML file in a word document and print it.
-----------------------------------------------------------------------------
Sub PrintPages()
Dim path As String
Dim word_Server As Word.Application
Dim d() As String
path = "C:\Inetpub\wwwroot\newDir\" ' set the path
word_Server = New Word.Application()
With word_Server
.ChangeFileOpenDirectory(path)
End With
d = System.IO.Directory.GetFiles(path)
Dim en As System.Collections.IEnumerator
en = d.GetEnumerator
While en.MoveNext
word_Server.Documents.Open(_
filename:=CStr(en.Current),_
ConfirmConversions:=False,_
ReadOnly:=False, _
AddToRecentFiles:=False,_
PasswordDocument:="",_
PasswordTemplate:="",_
Revert:=False,_
WritePasswordDocument:="",_
WritePasswordTemplate:="",_
Format:=Word.WdOpenFormat.wdOpenFormatAuto)
word_Server.Application.PrintOut( _
filename:=CStr(en.Current), _
Range:=Word.WdPrintOutRange.wdPrintAllDocument, _
Item:= Word.WdPrintOutItem.wdPrintDocumentContent, _
Copies:=1, _
Pages:="", _
PageType:=Word.WdPrintOutPages.wdPrintAllPages, _
ManualDuplexPrint:=False, _
Collate:=True, _
Background:=True, _
PrintToFile:= False, _
PrintZoomColumn:=0, _
PrintZoomRow:=0, _
PrintZoomPaperWidth:=0, _
PrintZoomPaperHeight:=0)
End While
CType(word_Server, Word.ApplicationClass).Quit()
word_Server = Nothing
End Sub
web.config file is a part of every asp.net application and is a good place for storing information that may be needed over multiple web pages. It also saves you a lot of “maintenance nightmare” if you need to make any change in all of them. Storing the database connection string in the web.config is one such good example.
A part of the web.config file is shown below :
<?
xml version="1.0" encoding="utf-8" ?>
<configuration>
<appSettings>
</appSettings>
....
The connection string should be stored in the <appSettings> tag.
heres the complete example :
<?
xml version="1.0" encoding="utf-8" ?>
<
configuration>
<appSettings>
<add key="conn" value="Data Source=localhost;Integrated Security=SSPI; database=YourDB" />
</appSettings>
And we can access this connection string from the .aspx page from the key “conn” from the key-value pair. So in our .aspx page we would say..
Protected objCon As New SqlConnection(ConfigurationSettings.AppSettings("conn"))
Sub Someprocedure()
'...other code
Try
If objCon.State = 0 Then objCon.Open()
Catch ex as exception
Finally
objCon.Close()
End Try
End Sub
- Heres a small piece of code to force refresh the parent window from the child window after closing the child window, without having to press the refresh button.
window.opener.location=window.opener.location; window.close();
response.write the above JS code in the child window.
for example:
response.write(”<Script> window.opener...........close();" & Chr(60) & "/script >")
- If you just want to close the child window ( as in “cancel”) use window.close(); in the above example.
Here is a sample code to call a SQL Server UDF (User Defined Function) from ASP.NET
Sub GetNotes(contnum integer)
Protected objCon As New SqlConnection(ConfigurationSettings.AppSettings("conn"))
Dim cmdtrans As SqlCommand,notes as string
dim sqltrans as string ,popnotes as string
sqltrans= "select dbo.fnpop_notes(@contnum)"
cmdtrans=new sqlcommand(sqltrans,objCon)
cmdtrans.Parameters.Add(New SQLParameter("@contnum",contnum))
If objCon.State = 0 Then objCon.Open()
notes=cmdtrans.ExecuteScalar()
objCon.Close()
end sub
The code above is pretty much self xplanatory.
Based on a feedback I received from I am adding a link to another article that tells you another ( better) way to do this. Heres the link : http://www.ftponline.com/vsm/2003_09_14th/online/hottips/rehak/