10/21/2008 3:52:23 AM

Hi Max,

First, thank you. I'm verry happy to be able to help fellow developers.

To be able to assign a value for a database to a text box there are many to do this. You can ether use a Out parameter or a DataReader.

I prefer the DataReader, well because I'm more use to it...

Here an example.

MySqlConnection mySql = new MySqlConnection();
mySql.CreateConn();
mySql.Command = mySql.Connection.CreateCommand();
mySql.Command.CommandText = Sring.Format("SELECT [dbo].[Users].[Name] FROM [dbo].[ZTABLE] WHERE [dbo].[Users].[UserId] = {0}", MyUserIdTextBox.Text);
mySql.DataReader = mySql.Command.ExecuteDataReader();
while(mySql.DataReader.Read())
{
MyUserNameTextBox.Text = Convert.ToString(mySql.DataReader[0]);
}
mySql.DataReader.Close();
mySql.Command.Dispose();
mySql.CloseConn();

Hope this helps. Let me know. If you need any clarifications, well, just post! ;)

Simon

Simon Deshaies

11/26/2008 11:02:03 PM

Christophe,

1- To move to code behind you would do something like this:
MySqlConnection.Open();
MySqlConnection.Command = MySqlConnection.Connection.CreateCommand();
//Your command
MySqlConnection.Command.CommandText = "INSERT INTO tbl_user (user_name, user_surname, user_email, user_pw, dpt_id, user_review) VALUES(@ctrl_user_name, @ctrl_user_surname, @ctrl_user_email, @ctrl_user_pw, @ctrl_user_dpt_id,@Ctrl_HF_userReview)";

//Your parameter
MySqlConnection.Command.Parameters.Add("@ctrl_user_name", SqlDbType.VarChar); //Parameter SQL Type
MySqlConnection.Command.Parameters["@ctrl_user_name"].Value = Txt_user_name.Text;

//For your condition
if (check1.Checked)
{
//your code
}

But this is not the best way to do this. I would suggest you create a stored procedure you can either create it in SQL this you make your code way more easy to maintain.

If you do not want to create a procedure in SQL Server you could do something like this:

MySqlConnection.Open();
MySqlConnection.Command = MySqlConnection.Connection.CreateCommand();

//Set all your parameter
MySqlConnection.Command.Parameters.Add("@ctrl_user_name", SqlDbType.VarChar); //Parameter SQL Type
MySqlConnection.Command.Parameters["@ctrl_user_name"].Value = Txt_user_name.Text;

//Your command
MySqlConnection.Command.CommandText = @"
IF @ctrl_user_role = 2 BEGIN

INSERT INTO tbl_user (user_name, user_surname, user_email, user_pw, dpt_id, user_review)
VALUES(@ctrl_user_name, @ctrl_user_surname, @ctrl_user_email, @ctrl_user_pw, @ctrl_user_dpt_id,@Ctrl_HF_userReview)

INSERT INTO tbl_user_role (user_id,role_id)
VALUES(IDENT_CURRENT('tbl_user'),@ctrl_user_role)

INSERT INTO tbl_dep_reviewers(dpt_id,user_id)
VALUES(@ctrl_user_dpt_id,IDENT_CURRENT('tbl_user'))

INSERT INTO tbl_dpt_users(dpt_id,user_id)
VALUES(@ctrl_sub_dpt,IDENT_CURRENT('tbl_user')); END

IF @ctrl_user_role = 1 AND @ctrl_user_review = 0 BEGIN

INSERT INTO tbl_user (user_name, user_surname, user_email, user_pw, dpt_id, user_review)
VALUES(@ctrl_user_name, @ctrl_user_surname, @ctrl_user_email, @ctrl_user_pw, @ctrl_user_dpt_id, @ctrl_user_review)

INSERT INTO tbl_user_role (user_id,role_id)
VALUES(IDENT_CURRENT('tbl_user'),@ctrl_user_role)

INSERT INTO tbl_dpt_users(dpt_id,user_id)
VALUES(@ctrl_sub_dpt,IDENT_CURRENT('tbl_user')); END


IF @ctrl_user_role = 1 AND @ctrl_user_review = 1 BEGIN

INSERT INTO tbl_user (user_name, user_surname, user_email, user_pw, dpt_id, user_review, user_reviewer)
VALUES(@ctrl_user_name, @ctrl_user_surname, @ctrl_user_email, @ctrl_user_pw, @ctrl_user_dpt_id, @ctrl_user_review, @ctrl_user_reviewer)

INSERT INTO tbl_user_role (user_id,role_id)
VALUES(IDENT_CURRENT('tbl_user'),@ctrl_user_role)

INSERT INTO tbl_dpt_users(dpt_id,user_id)
VALUES(@ctrl_sub_dpt,IDENT_CURRENT('tbl_user')); END";
MySqlConnection.Command.ExecuteNonQuery();

For the DropDownList you can either set them wil the DataReader
MySqlConnection.Open();
MySqlConnection.Command = MySqlConnection.Connection.CreateCommand();
MySqlConnection.Command.CommandType = CommandType.Text;
MySqlConnection.Command.CommandText = "SELECT something FROM [dbo].[sometable] WHERE [template] = 0 ORDER BY something";
MySqlConnection.DataReader = MySqlConnection.Command.ExecuteReader();
while (MySqlConnection.DataReader.Read())
{
raceDropDownList.Items.Add(Convert.ToString(MySqlConnection.DataReader["something"]));
}
MySqlConnection.DataReader.Close();

or a DataTable as DataSource

DataTable MyAliasDataTable = new DataTable();
SqlDataAdapter MyAliasDataAdapter = new SqlDataAdapter("SELECT [userID],[alias] FROM [dbo].[users] ORDER BY [alias]", MySqlConnection.Connection);
MyAliasDataAdapter.Fill(MyAliasDataTable);
AliasDropDownList.DataSource = MyAliasDataTable;
AliasDropDownList.DataTextField = "alias";
AliasDropDownList.DataValueField = "userID";
AliasDropDownList.DataBind();
MyAliasDataTable.Dispose();
MyAliasDataAdapter.Dispose();

Regards,

Simon

Simon Deshaies

12/2/2008 12:41:15 AM

Jessica,
I did not have the chance to use the Device Emulator in Visual Studio, so unfortunately I can’t help you.

Natasja,
A simple class would look like this… Well it’s complicated I cannot in all good conscience give you a piece of code that I think is not wordy of a production environment.
But here’s a class that will accomplish what you want to do and its rather simple. You can from it expand and modify it to fit your needs.
using System;
using System.Data;
using System.Data.SqlClient;

public static class SimpleSqlClass
{
private static String ConnectionString = "server=SERVERNAMEORIP;database=DATABASE;User ID=LOGIN;Password=PASSWORD;Trusted_Connection=False;";
private static SqlConnection PConnection;

public static SqlConnection Open()
{
if (PConnection == null) { PConnection = new SqlConnection(); }
if (String.IsNullOrEmpty(PConnection.ConnectionString) || PConnection.State != ConnectionState.Open)
{
try
{
PConnection.ConnectionString = "Min Pool Size=5;Max Pool Size=40;Connect Timeout=4;" + ConnectionString + ";";
PConnection.Open();
}
catch (Exception)
{
if (PConnection.State != ConnectionState.Closed)
{
PConnection.Close();
}
PConnection.ConnectionString = "Pooling=false;Connect Timeout=45;" + ConnectionString + ";";
PConnection.Open();
}
return PConnection;
}
return PConnection;
}
public static void Close()
{
if (PConnection != null)
{
if (PConnection.State == ConnectionState.Open)
{
PConnection.Close();
}
PConnection.Dispose();
}
}
}

class TestClass
{
void TestMethod()
{
try
{
SimpleSqlClass.Open();
//Your code.
}
catch (Exception ex)
{
//Your exception handling.
}
finally
{
SimpleSqlClass.Close();
}
}
}

Simon Deshaies