Hi all,

I am newbie in .NET tech.

I have been trying to insert records into the DB using data adapter. This is the code I wrote which excutes but doesnot do anything.

string path = "server=(local);database=peopleDB;integrated security=SSPI;";
SqlConnection connstring = new SqlConnection(path);
connstring.Open();
string query = "Insert into users(username,password) values(username,paswd)";
SqlCommand cmd = new SqlCommand(query,connstring);
cmd.CommandType = CommandType.Text;
SqlDataAdapter da= new SqlDataAdapter(query,connstring);
cmd.Parameters.Add(new SqlParameter("username",SqlDbType.Char,10));
cmd.Parameters.Add(new SqlParameter("paswd",SqlDbType.Char, 10));
cmd.Parameters["username"].Value = tbxUsername.Text;
cmd.Parameters["paswd"].Value =tbxPassword.Text;
da.InsertCommand=cmd;

I tried the same thing using stored procedure also:

            SqlCommand cmd = new SqlCommand("spEnterUserDetails",connstring);
            cmd.CommandType = CommandType.StoredProcedure;

            SqlDataAdapter da=new SqlDataAdapter("select * from users", connstring);

            SqlParameter p1 = new SqlParameter("@Username",SqlDbType.Char,20);
            p1.Value=username;
            cmd.Parameters.Add(p1);

            SqlParameter p2 =new SqlParameter("@Fname",SqlDbType.Char,25);
            p2.Value=fname;
            cmd.Parameters.Add(p2);

            SqlParameter p3= new SqlParameter("@Lname",SqlDbType.Char,25);
            p3.Value=lname;
            cmd.Parameters.Add(p3);

            SqlParameter p4 = new SqlParameter("@Email",SqlDbType.Char,25);
            p4.Value=email;
            cmd.Parameters.Add(p4);

            SqlParameter p5 =new SqlParameter("@Paswd",SqlDbType.Char,10);
            p5.Value=paswd;
            cmd.Parameters.Add(p5);

            da.InsertCommand = cmd;


Even this doesnot work...
Please let me know if something is wrong with my code.

I really need help!

Thanks,
Favor

You don't have an "Execute" anywhere in your code. So even though you are setting up the queries to run, you've never actually running them.

Usually this entails calling one of the execute methods of the command object:

<command>.ExecuteNonQuery()

-Peter

When I used
cmd.ExecuteNonQuery();
An error pops up!
Error messg:

Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 'username'. The name 'username' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.

Source Error:


Line 92: cmd.Parameters["paswd"].Value =tbxPassword.Text;
Line 93: da.InsertCommand=cmd;
Line 94: cmd.ExecuteNonQuery();

Did you get this resolved?

You need to change the query a bit. MSSQL Parameters start with @:

string query = "Insert into users(username,password) values(@username,@paswd)";
...
cmd.Parameters.Add(new SqlParameter("@username",SqlDbType.Char,10));
cmd.Parameters.Add(new SqlParameter("@paswd",SqlDbType.Char, 10));

-Peter

Insert records into the DB using data adapter.

Design a form using .NET environment and place two textbox controls on a form.
Design and create a table using SQL Server 2000.

Name the Database as FinAccounting.
Name the Table as AccountsTable.
Name the form as Form1
Name the controls on the form as Textbox1 and Textbox2.


Tasks:

1. Establish the connection with the database using Connection object.
2. Instantiate the Command object.
3. Instantiate Data Adapter
4. Set Data Adapter command properties
5. Instantiate DataSet
6. Populate the DataSet using the DataAdapter and the data will be displayed in the textboxes.

Imports System.Data.SqlClient
Public Class Form1
Inherits System.Windows.Forms.Form
Dim myConnection As SqlConnection = New SqlConnection(“Data Source=VSDOTNET;Integrated Security=SSPI;Initial Catalog=FinAccounting”)
Dim str_sql_account_select As String = “SELECT * FROM AccountsTable”
Dim comAccountSelect As SqlCommand ‘command for Account select
Dim myAccountAdapter As SqlDataAdapter
Dim myAccountDataset As DataSet

Private Sub frmAccounts_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
    Dim i, j As Integer
    Dim vbn As Boolean
    vbn = True
    myConnection.Open()

    â€˜Instantiate the commands
    comAccountSelect = New SqlCommand(str_sql_account_select, myConnection)

    â€˜Instantiate data adapter
    myAccountAdapter = New SqlDataAdapter(str_sql_account_select, myConnection)
    â€˜Set data adapter command properties
    myAccountAdapter.SelectCommand = comAccountSelect

    â€˜Instantiate the datasets
    myAccountDataset = New DataSet()

    â€˜Populate the dataset
    myAccountAdapter.Fill(myAccountDataset, “AccountsTable”)
    j = myAccountDataset.Tables(“AccountsTable”).Rows. Count()
    For i = 0 To (j - 1)
        TextBox1.Text = myAccountDataset.Tables(“AccountsTable”).Rows( i)(0)
        TextBox2.Text = myAccountDataset.Tables(“AccountsTable”).Rows( i)(1)
    Next
    End Sub
    End Class

Also, try to refer this book Titled:"Database programming using vb.net and SQL server 2000"

Regards
Bhar