Newbie question VB.net to SQL server insert and delete data

Newbie question VB.net to SQL server insert and delete data

Post by Visha » Mon, 27 Mar 2006 16:15:33



Hello there,

I am new to .net and have built a connection with my backend SQL
server.I can view data but now I want to insert from the front end into
the tables in the back end.

after establishing the connection string whats the next step.Im working
at a firm as an intern and am kind of stuck.....

-Vishal

 
 
 

Newbie question VB.net to SQL server insert and delete data

Post by Bradley, Pete » Tue, 28 Mar 2006 16:04:49


Something like this (assuming a MySQL database - use SqlCommand and
SqlConnection if you're using SQL Server):

/***********************************************************************
*

try
{
        // Create a command object ...
        MySqlCommand cmd = new MySqlConnection ("Your Connection string
goes here").CreateCommand();

        // Formulate your query - e.g. something like ...
        string sqlInsertCmd = "INSERT INTO theTableName(theFieldNames)
VALUES(theFieldValues)";

        // Assign the query to the command                              
        cmd.CommandText = sqlInsertCmd;

        // Open the connection
        cmd.Connection.Open();

        // Execute the query
        int i = cmd.ExecuteNonQuery();

        // Check that the number of rows affected is correct
        // i.e. 1 in this case, e.g. ...
        if (i != 1)
                throw new Exception("Unexpected number of rows
inserted");

        ...

Quote:}

catch (Exception ex)
{
        // Do whatever you want with the exception, e.g. ...
        throw new Exception(ex.Message + ": Cannot insert new record");      
Quote:}

finally
{
        cmd.Connection.Close();

Quote:}

************************************************************************
***/

Obviously you should replace things like connection strings, table and
field names and so on with your own values where I've just put in
placeholders.  Production code would have more specific error handling
as well.

Peter

-----Original Message-----


Sent: 26 March 2006 08:16
To: DotNetDevelopment, VB.NET, C# .NET, ADO.NET, ASP.NET, XML, XML Web
Services,.NET Remoting
Subject: [DotNetDevelopment] Newbie question VB.net to SQL server insert
and delete data

Hello there,

I am new to .net and have built a connection with my backend SQL
server.I can view data but now I want to insert from the front end into
the tables in the back end.

after establishing the connection string whats the next step.Im working
at a firm as an intern and am kind of stuck.....

-Vishal


 
 
 

Newbie question VB.net to SQL server insert and delete data

Post by Bradley, Pete » Tue, 28 Mar 2006 16:22:10


Sorry to follow up my own post, but I should have said two other things:

*  Validate any incoming values before inserting them into your query.
Never, ever use values from outside the method doing the insert without
checking first for (at least) SQL injection and cross scripting
attempts.

*  A better way to do an insert is to use a stored procedure, but I just
gave you the most straightforward way as a starter.

HTH

Peter

-----Original Message-----


Sent: 27 March 2006 08:05

Subject: [DotNetDevelopment] Re: Newbie question VB.net to SQL server
insert and delete data

Something like this (assuming a MySQL database - use SqlCommand and
SqlConnection if you're using SQL Server):

/***********************************************************************
*

try
{
        // Create a command object ...
        MySqlCommand cmd = new MySqlConnection ("Your Connection string
goes here").CreateCommand();

        // Formulate your query - e.g. something like ...
        string sqlInsertCmd = "INSERT INTO theTableName(theFieldNames)
VALUES(theFieldValues)";

        // Assign the query to the command                              
        cmd.CommandText = sqlInsertCmd;

        // Open the connection
        cmd.Connection.Open();

        // Execute the query
        int i = cmd.ExecuteNonQuery();

        // Check that the number of rows affected is correct
        // i.e. 1 in this case, e.g. ...
        if (i != 1)
                throw new Exception("Unexpected number of rows
inserted");

        ...

}
catch (Exception ex)
{
        // Do whatever you want with the exception, e.g. ...
        throw new Exception(ex.Message + ": Cannot insert new record");      
}
finally
{
        cmd.Connection.Close();
}

************************************************************************
***/

Obviously you should replace things like connection strings, table and
field names and so on with your own values where I've just put in
placeholders.  Production code would have more specific error handling
as well.

Peter

-----Original Message-----


Sent: 26 March 2006 08:16
To: DotNetDevelopment, VB.NET, C# .NET, ADO.NET, ASP.NET, XML, XML Web
Services,.NET Remoting
Subject: [DotNetDevelopment] Newbie question VB.net to SQL server insert
and delete data

Hello there,

I am new to .net and have built a connection with my backend SQL
server.I can view data but now I want to insert from the front end into
the tables in the back end.

after establishing the connection string whats the next step.Im working
at a firm as an intern and am kind of stuck.....

-Vishal

 
 
 

Newbie question VB.net to SQL server insert and delete data

Post by Visha » Wed, 29 Mar 2006 00:30:41


Thank you very very much Peter this is of great help to me. It is just
perfect.
:)

-Vishal.

 
 
 

Newbie question VB.net to SQL server insert and delete data

Post by Bradley, Pete » Wed, 29 Mar 2006 00:35:09


You're welcome

Peter

-----Original Message-----


Sent: 27 March 2006 16:31
To: DotNetDevelopment, VB.NET, C# .NET, ADO.NET, ASP.NET, XML, XML Web
Services,.NET Remoting
Subject: [DotNetDevelopment] Re: Newbie question VB.net to SQL server
insert and delete data

Thank you very very much Peter this is of great help to me. It is just
perfect.
:)

-Vishal.