Connect ASP.net To SQL server - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

Connect ASP.net To SQL server

Author

Topic

jhwong2610
Starting Member

8 Posts

Posted - 2004-04-16 : 10:54:40

i'm very new in ASP.net
i dont have any idea how to connect my web based system(in asp.net) to sql server. i tried to surf around to find any example, but too sad i cant find one....

i hope that the SQL team can help me in this matter.
if possible,give me the link or email me one simple example.

really needs help....URGENT

From jhwong2610

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2004-04-16 : 11:11:46

Do you have the .NET SDK documentation and samples ? It's all in there.

Damian

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-04-16 : 14:04:02

http://www.able-consulting.com/dotnet/adonet/Data_Providers.htm#SQLClientManagedProvider

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

TurdSpatulaWarrior
Starting Member

36 Posts

Posted - 2004-04-16 : 15:38:37

use the System.Data.SqlClient namespace

Dim strConnectionString As String="Data Source=servername; Initial Catalog=dbcatalog; User ID=userid; Password=password"

Dim objConn As New SqlConnection(strConnectionString)
objConn.Open()

' do things, use SqlCommand, SqlDataReader, DataSet, etc objects

objConn.Close() : objConn.Dispose()

jhwong2610
Starting Member

8 Posts

Posted - 2004-04-17 : 00:39:52

Dear Merkin...

I dont have the .Net SDK documentation...
can i have the link of it???

Micheal....
Thank for the web page link
i will try to look at it...

TurdSpatulaWarrior
thank for the coding that u gave...
again i will look at it and try it out

thank all of u

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2004-04-17 : 01:09:01

Have your tried www.msdn.com?
They have samples if you search for a class name (such as SqlConnection).

The nice thing about .NET is that your code to connect to a database is the same as it is in a Windows Form program.

Here's a quick sample

private void InsertItem()
{
SqlConnection connection = null;
SqlTransaction transaction = null;

try
{
connection = new SqlConnection(<Your connection string>);
SqlCommand command = connection.CreateCommand();

command.CommandText = "INSERT INTO tbSomeTable " +
"(col1, col2, col3) " +
"VALUES (" + col1Val + "," + col2Val + "," + col3Val + ")";

connection.Open();//opens connection to database.
transaction = connection.BeginTransaction();
command.Transaction = transaction;

command.ExecuteNonQuery();

transaction.Commit();
}

catch(Exception exception)
{
if(transaction != null)
transaction.RollBack();
}

finally
{
if(connection != null)
{
connection.Close();
connection.Dispose();
}

}
}

Dustin Michaels

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2004-04-17 : 01:23:56

quote:
Originally posted by jhwong2610

Dear Merkin...

I dont have the .Net SDK documentation...
can i have the link of it???

If you have Visual Studio or a compiler installed you should have the SDK on your machine. Are you sure you don't have it ?

Either way, get it here

http://www.microsoft.com/downloads/details.aspx?FamilyId=9B3A2CA6-3647-4070-9F41-A333C6B9181D&displaylang=en

Damian

jhwong2610
Starting Member

8 Posts

Posted - 2004-04-17 : 10:39:41

hello Dustin Michaels

the code that you gave me is it insert to the html code???
i really dont know anything about asp.net....
but some of my friend said that i can use web config to connect the asp.net to the SQL server...
is it correct???

i would like to hear more tips from you and all the SQL user ....
i need that very urgent....

From jhwong2610

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2004-04-17 : 10:47:30

There is a bit more to it than that. Go to www.asp.net and look at the quickstart samples, and the SDK docs from the link.

There is no quick answer to this one, you have to do some learning.

Damian

ying
Starting Member

4 Posts

Posted - 2004-05-12 : 23:15:53

gave you a sample,ask for a method of a sample from many friends:
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Globalization" %>
<%@ Import Namespace="Acme2" %>

<html>

<script language="VB" runat="server">

Dim MyConnection As SqlConnection

Sub Page_Load(Sender As Object, E As EventArgs)

myConnection = new SqlConnection("server=192.168.10.5;uid=news123;pwd=123;database=wsyy")

If Not (IsPostBack)
BindGrid()
End If
End Sub

Sub MyDataGrid_Edit(Sender As Object, E As DataGridCommandEventArgs)

MyDataGrid.EditItemIndex = CInt(E.Item.ItemIndex)
BindGrid()
End Sub

Sub MyDataGrid_Cancel(Sender As Object, E As DataGridCommandEventArgs)

MyDataGrid.EditItemIndex = -1
BindGrid()
End Sub

Sub MyDataGrid_Update(Sender As Object, E As DataGridCommandEventArgs)

Dim DS As DataSet
Dim MyCommand As SqlCommand

Dim UpdateCmd As String = "UPDATE dl_zongtable SET fist = @fist, definecode = @definecode, " & _
"pjname = @pjname, num = @num, dwname = @dwname, startime = @startime, " & _
"endtime = @endtime,waittime = @waittime where fist = @fist"

MyCommand = New SqlCommand(UpdateCmd, MyConnection)

MyCommand.Parameters.Add(New SqlParameter("@fist", SqlDbType.Int, 4))
MyCommand.Parameters.Add(New SqlParameter("@definecode", SqlDbType.Int, 4))
MyCommand.Parameters.Add(New SqlParameter("@pjname", SqlDbType.NVarChar, 50))
MyCommand.Parameters.Add(New SqlParameter("@num", SqlDbType.Int, 4))
MyCommand.Parameters.Add(New SqlParameter("@dwname", SqlDbType.NVarChar, 50))
MyCommand.Parameters.Add(New SqlParameter("@startime", SqlDbType.DateTime, 8))
MyCommand.Parameters.Add(New SqlParameter("@endtime", SqlDbType.DateTime, 8))
MyCommand.Parameters.Add(New SqlParameter("@waittime", SqlDbType.NVarChar, 50))

MyCommand.Parameters("@fist").Value = MyDataGrid.DataKeys(CInt(E.Item.ItemIndex))

Dim Cols As String() = {"@fist","@definecode","@pjname","@dwname","@dwname", _
"@startime","@endtime","@waittime"}

Dim NumCols As Integer = E.Item.Cells.Count
Message.InnerHtml = ""

Dim I As Integer
For I=2 To NumCols-1 'skip first, second and last column

Dim CurrentTextBox As System.Web.UI.WebControls.TextBox
CurrentTextBox = E.Item.Cells(I).Controls(0)
Dim ColValue As String = CurrentTextBox.Text

' check for invalid values
Select Case Cols(i-1)
Case "@definecode"
If Not InputValidator.IsValidFiveDigitZipCode(colvalue)
Message.InnerHtml &= "ERROR: Last Name - " & InputValidator.AnsiBasicZipCodeErrorString & "<br>"
End If
Case "@pjname"
If Not InputValidator.IsValidAnsiName(colvalue)
Message.InnerHtml &= "ERROR: First Name - " & InputValidator.AnsiNameErrorString & "<br>"
End If
Case "@num"
If Not InputValidator.IsValidFiveDigitZipCode(colvalue)
Message.InnerHtml &= "ERROR: num - " & InputValidator.AnsiBasicZipCodeErrorString & "<br>"
End If
Case "@dwname"
If Not InputValidator.IsValidAnsiName(colvalue)
Message.InnerHtml &= "ERROR: dwname - " & InputValidator.AnsiNameErrorString & "<br>"
End If
Case "@startime"
If Not InputValidator.IsValidAnsiDateTime(colvalue)
Message.InnerHtml &= "ERROR: startime - " & InputValidator.AnsiDateTimeErrorString & "<br>"
End If
Case "@endtime"
If Not InputValidator.IsValidAnsiDateTime(colvalue)
Message.InnerHtml &= "ERROR: endtime - " & InputValidator.AnsiDateTimeErrorString & "<br>"
End If
Case "@waittime"
If Not InputValidator.IsValidAnsiName(colvalue)
Message.InnerHtml &= "ERROR: waittime Code - " & InputValidator.AnsiNameErrorString & "<br>"
End If
End Select

' Check for null values in required fields
If I<6 And ColValue = ""
Message.InnerHtml &= "ERROR: Null values not allowed for " & Cols(i-1) & "<br>"
End If

MyCommand.Parameters(Cols(I-1)).Value = ColValue
Next

If Message.InnerHtml <> ""
Message.Style("color") = "red"
Return
End If

MyCommand.Connection.Open()

Try
MyCommand.ExecuteNonQuery()
Message.InnerHtml = "<b>Record Updated</b><br>" & UpdateCmd.ToString()
MyDataGrid.EditItemIndex = -1
Catch Exp As SQLException
If Exp.Number = 2627
Message.InnerHtml = "ERROR: A record already exists with the same " & _
"primary key"
Else
Message.InnerHtml = "ERROR: Could not update record, please " & _
"ensure the fields are correctly filled out"
End If
Message.Style("color") = "red"
End Try

MyCommand.Connection.Close()

BindGrid()
End Sub

Sub BindGrid()

Dim DS As DataSet
Dim MyCommand As SqlDataAdapter
MyCommand = new SqlDataAdapter("select * from dl_zongtable", MyConnection)

DS = new DataSet()
MyCommand.Fill(DS, "dl_zongtable")

MyDataGrid.DataSource=DS.Tables("dl_zongtable").DefaultView
MyDataGrid.DataBind()
End Sub

</script>

<body style="font: 10pt verdana">

<form runat="server">

<h3><font face="Verdana">Updating a Row of Data</font></h3>

<span id="Message" EnableViewState="false" style="font: arial 11pt;" runat="server"/><p>

<ASP:DataGrid id="MyDataGrid" runat="server"
Width="800"
BackColor="#ccccff"
BorderColor="black"
ShowFooter="false"
CellPadding=3
CellSpacing="0"
Font-Name="Verdana"
Font-Size="8pt"
HeaderStyle-BackColor="#aaaadd"
OnEditCommand="MyDataGrid_Edit"
OnCancelCommand="MyDataGrid_Cancel"
OnUpdateCommand="MyDataGrid_Update"
DataKeyField="fist"
>

<Columns>
<asp:EditCommandColumn EditText="Edit" CancelText="Cancel"
UpdateText="Update" ItemStyle-Wrap="false"/>
</Columns>

</ASP:DataGrid>

</form>

</body>
</html>

SQL SERVER

Updating a Row of Data
ERROR: Could not update record, please ensure the fields are correctly filled out
fist definecode pjname num dwname startime endtime waittime
Update Cancel 21 1008 name 100 fjkk 2004-5-12 19:54:49 2004-5-14 19:54:49 2days0hours0minutes
//-----------------------------------------------------------------------
// This file is part of the Microsoft .NET SDK Code Samples.
//
// Copyright (C) Microsoft Corporation. All rights reserved.
//
//This source code is intended only as a supplement to Microsoft
//Development Tools and/or on-line documentation. See these other
//materials for detailed information regarding Microsoft code samples.
//
//THIS CODE AND INFORMATION ARE PROVIDED AS IS WITHOUT WARRANTY OF ANY
//KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
//IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
//PARTICULAR PURPOSE.
//-----------------------------------------------------------------------

using System;
using System.Text.RegularExpressions;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace Acme2
{
public class InputValidator
{
// ***
// Regular Expression useful in Validation
//

// Accepts only digits. No spaces before or after are
// allowed. No monetary symbol is allowed. No decimal
// separator is allowed.
public static string AnsiDateTimeExpressionString = "^[\\d\\s\\:\\-]*$";
public static string AnsiDateTimeErrorString = "Limit characters to ANSI digits,space,and these symbols :-";
public static Regex AnsiDateTimeExpression = new Regex(AnsiDateTimeExpressionString, RegexOptions.Compiled);

public static string AnsiNumericExpressionString = "^\\d*$";
public static string AnsiNumericErrorString = "Limit characters to ANSI digits";
public static Regex AnsiNumericExpression = new Regex(AnsiNumericExpressionString, RegexOptions.Compiled);

// Accepts only basic ANSI letters, digits, the period
// character, and space characters. Requires that at
// least one character exist to be validated.
public static string AnsiNameExpressionString = "^[\\w\\d\\.\\s\\,\\']*$";
public static string AnsiNameErrorString = "Limit characters to alphanumeric, spaces, and these symbols .,'";
public static Regex AnsiNameExpression = new Regex(AnsiNameExpressionString, RegexOptions.Compiled);

// Looks for characters that can be considered unsafe text.
// Items to watch out for include quotes, apostrophes,
// semi-colons, and the less than and greater than signs.
// Unknown malicious characters can easily bypass the
// IsSafeText expression.
public static string SafeTextExpressionString = "[\\\"\\'\\;<>]";
public static string SafeTextErrorString = "String cannot contain these symbols <>;'\"";
public static Regex SafeTextExpression = new Regex(SafeTextExpressionString, RegexOptions.Compiled);

// Accepts only valid email characters.
// Valid email characters are limited to
// Alphanumeric, periods, underscores, and the
// at symbol.
public static string AnsiEmailExpressionString = "^[\\w\\d\\.\\@_]*$";
public static string AnsiEmailErrorString = "Limit characters to alphanumeric and these symbols _.@";
public static Regex AnsiEmailExpression = new Regex(AnsiEmailExpressionString, RegexOptions.Compiled);

// Accepts only valid telephone number characters.
// This restricts the input to digits, hyphens, parentheses,
// periods, commas, space characters, the plus symbol,
// and ansi letters.
// This expression can be customized to add better parsing
// and to limit input types.
//
// Examples of valid input
// 111 111 1111
// (111) 111-1111
// (111) 111-1111 ext. 555
public static string AnsiPhoneExpressionString = "^[\\w\\d\\.\\s\$\$\\+\\,\\-]*$";
public static string AnsiPhoneErrorString = "Limit characters to ANSI alphanumerics, spaces, and these symbols .,+-()";
public static Regex AnsiPhoneExpression = new Regex(AnsiPhoneExpressionString, RegexOptions.Compiled);

// Accepts only valid address input characters.
// This restricts the input to ansi letters, digits,
// commas, hyphens, periods, semi-colons, and space
// characters.
public static string AnsiAddressExpressionString = "^[\\w\\d\\.\\s\\;\\,\\-]*$";
public static string AnsiAddressErrorString = "Limit characters to ANSI alphanumerics, spaces, and these symbols .,-;";
public static Regex AnsiAddressExpression = new Regex(AnsiAddressExpressionString, RegexOptions.Compiled);

// Accepts only two character states
// This can be extended to actually check against a valid
// listing of states. This is a first tier approach that
// can be much easier to implement.
public static string AnsiTwoCharacterStateExpressionString = "^[\\w]{2}$";
public static string AnsiTwoCharacterStateErrorString = "Limit to 2 ANSI alpha characters";
public static Regex AnsiTwoCharacterStateExpression = new Regex(AnsiTwoCharacterStateExpressionString, RegexOptions.Compiled);

// Accepts only alpha state or city names with periods.
// This can be extended to check against actual
// state names. This is a first tier approach that
// can be much easier to implement.
public static string AnsiCityStateExpressionString = "^[\\w\\s\\.\\'\\,]*$";
public static string AnsiCityStateErrorString = "Limit characters to ANSI alpha characters, spaces, and these symbols .,'";
public static Regex AnsiCityStateExpression = new Regex(AnsiCityStateExpressionString, RegexOptions.Compiled);

// Accepts only 5 character zip codes
public static string AnsiBasicZipCodeExpressionString = "^[\\d]*$";
public static string AnsiBasicZipCodeErrorString = "Limit to digits";
public static Regex AnsiBasicZipCodeExpression = new Regex(AnsiBasicZipCodeExpressionString, RegexOptions.Compiled);

// Accepts only 9 character zip codes
// with a hyphen separator between digits
// 5 and 6
public static string AnsiExtendedZipCodeExpressionString = "^\\d{5}\\-\\d{4}$";
public static string AnsiExtendedZipCodeErrorString = "Limit to 5 ANSI digits, a hyphen, and 4 more ANSI digits";
public static Regex AnsiExtendedZipCodeExpression = new Regex(AnsiExtendedZipCodeExpressionString, RegexOptions.Compiled);

// ***
// Regular Expression useful in string stripping and conversion
//
public static string StripHtmlTagsExpressionString = "\\<\\.\\*\\>";
public static Regex StripHtmlTagsExpression = new Regex(StripHtmlTagsExpressionString, RegexOptions.Compiled);

public static string StripHtmlTagCharactersExpressionString = "[\\<\\>]";
public static Regex StripHtmlTagCharactersExpression = new Regex(StripHtmlTagCharactersExpressionString, RegexOptions.Compiled);

public static string EscapeApostrophesExpressionString = "[\\']";
public static Regex EscapeApostrophesExpression = new Regex(EscapeApostrophesExpressionString, RegexOptions.Compiled);

public static string EscapeNewLinesToHtmlExpressionString = "(?:\\r\\n|\\n\\r|\\r|\\n)";
public static Regex EscapeNewLinesToHtmlExpression = new Regex(EscapeNewLinesToHtmlExpressionString, RegexOptions.Compiled);

// ***
// String Normalization Methods
//

public static string StripHtmlTags(string text)
{
return StripHtmlTagsExpression.Replace(text, "");
}

// This method is multi input hack safe. This means
// that tags spread over multiple inputs won't miss
// being stripped. For instance:
//
// input 1: <script
// input 2: language=javascript>alert('me');
//
// This would get parsed out because the tag
// begin and end symbols get ripped. Another
// stripping approach could be to strip the tag
// characters plus whatever is inside, but this
// doesn't work when input is spread over multiple
// controls.
public static string StripHtmlTagsSafe(string text)
{
return StripHtmlTagCharactersExpression.Replace(text, "");
}

public static string EscapeApostrophes(string text)
{
return EscapeApostrophesExpression.Replace(text, "''");
}

// If you are going to use this method then call
// StripHtmlTags first, then call this method. Else
// you'll strip out the BR's.
public static string EscapeNewLinesToHtml(string text)
{
return EscapeNewLinesToHtmlExpression.Replace(text, "<BR>");
}

// ***
// Numeric Validator Methods
//

// Parses valid ansi numbers
public static bool IsValidAnsiDateTime(string number)
{
return AnsiDateTimeExpression.IsMatch(number);
}
public static bool IsValidAnsiNumber(string number)
{
return AnsiNumericExpression.IsMatch(number);
}

// Parses 5 digit zip codes
public static bool IsValidFiveDigitZipCode(string zip)
{
return AnsiBasicZipCodeExpression.IsMatch(zip);
}

// Parses 9 digit zip codes
public static bool IsValidNineDigitZipCode(string zip)
{
return AnsiExtendedZipCodeExpression.IsMatch(zip);
}

// Attempts to parse the number based on the current
// locale. This can be used for international support.
public static bool IsParseableNumber(string number)
{
try
{
Int32.Parse(number);
return true;
}
catch
{
}

return false;
}

// ***
// String Data Validators
//

// Processes a name value
public static bool IsValidAnsiName(string name)
{
return AnsiNameExpression.IsMatch(name);
}

// Processes phone number values
public static bool IsValidAnsiPhoneNumber(string name)
{
return AnsiPhoneExpression.IsMatch(name);
}

// Processes address values
public static bool IsValidAnsiAddress(string address)
{
return AnsiAddressExpression.IsMatch(address);
}

// Processes two character state strings
public static bool IsValidAnsiTwoCharacterState(string state)
{
return AnsiTwoCharacterStateExpression.IsMatch(state);
}

// Processes city or state strings
public static bool IsValidAnsiCityOrState(string citystate)
{
return AnsiCityStateExpression.IsMatch(citystate);
}

// Processes safe text
public static bool IsSafeText(string text)
{
return SafeTextExpression.Matches(text).Count == 0;
}
}
}

squib
Starting Member

1 Post

Posted - 2004-06-21 : 14:56:35

Hey there, I am just learning asp.net as well and am trying to figure out how exactly you apply the InputValidator code. I've tried including the file that is in the SDK. But have been unable to get it to work correctly. Right now I have it as...


<%@ Page Language="VB" %>
<%@ Import nameSpace="system.data"%>
<%@ Import nameSpace="system.data.SQLClient"%>
<%@ Import nameSpace="Acme"%>
...
<tr>
<td>First Name : </td>
<td>
<input type="text" id="au_fname" value="John" runat="server"/><br/>
<asp:regularExpressionValidator
id="au_fnameValidator"
controlToValidate="au_fname"
validationExpression="<%# InputValidator.AnsiNameExpressionString%>"
display="dynamic"
errorMessage="<%# InputValidator.AnsiNameErrorString%>"
runat="server"/>
</td>
</tr>
...


Plus I have the InputValidator.cs file in the same folder as the .aspx file. Any help in how to use the InputValidator functionality would be appreciated.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources