Connecting variable to database field

Greetings

what I need to do is access tables in a database, clean up one of the fields and return it to another table in the database. Im using a script component for the transformation whick looks for , or : and replaces : with a decimal point and removes the comma completely. My problem comes with the varaible-- how do I set this up so that the variable read matches the field in the database?

here is the script itself

thanks

km

Public Function AddList(list As String) As String
Dim total As Double
total = 0

If Len(list) > 0 Then
Dim s As Object
s = Split(list, ",")

Dim i As Integer
For i = LBound(s) To UBound(s)
s(i) = Replace(s(i), ":", ".")
total = total + CDbl(s(i))
Next
End If

AddList = total
End Function

km6589,

This would be better served in a stored procedure or function on the database side. That would solve your problem. Just run the SP in an Execute SQL Task.

If you don't care about overhead you could do this:

1) Drop a data flow task in the design.

2) Open the data flow task and add an OLE DB Source.

3) Open the OLE DB Source and add a connection if it isn't already there.

4) For the data access mode, choose SQL Command.

5) Add your SQL statement that lists the field you are trying to cleanup.

6) Add a Recordset Destination and feed the recordset into a variable of type object.

7) Access the variable for your recordset.

|||

thanks

went ahead and wrote some sql to clean this up on the DB side

km

Connecting variable to database field

Greetings

what I need to do is access tables in a database, clean up one of the fields and return it to another table in the database. Im using a script component for the transformation whick looks for , or : and replaces : with a decimal point and removes the comma completely. My problem comes with the varaible-- how do I set this up so that the variable read matches the field in the database?

here is the script itself

thanks

km

Public Function AddList(list As String) As String
Dim total As Double
total = 0

If Len(list) > 0 Then
Dim s As Object
s = Split(list, ",")

Dim i As Integer
For i = LBound(s) To UBound(s)
s(i) = Replace(s(i), ":", ".")
total = total + CDbl(s(i))
Next
End If

AddList = total
End Function

km6589,

This would be better served in a stored procedure or function on the database side. That would solve your problem. Just run the SP in an Execute SQL Task.

If you don't care about overhead you could do this:

1) Drop a data flow task in the design.

2) Open the data flow task and add an OLE DB Source.

3) Open the OLE DB Source and add a connection if it isn't already there.

4) For the data access mode, choose SQL Command.

5) Add your SQL statement that lists the field you are trying to cleanup.

6) Add a Recordset Destination and feed the recordset into a variable of type object.

7) Access the variable for your recordset.

|||

thanks

went ahead and wrote some sql to clean this up on the DB side

km

Connecting to the SQL Database

Hi all,

I am new to this field. I am trying to Connect to the database using the following code. The code does not give an error but gives an empty grid view.

Can somebody figure out the mistake?

protectedvoid Page_Load(object sender,EventArgs e)

{

if (!IsPostBack)

BindGridView();

}

void BindGridView()

{

System.Data.SqlClient.SqlConnection sqlconnect =new System.Data.SqlClient.SqlConnection("");//added connection string here

String command ="SELECT * from table";

System.Data.SqlClient.SqlCommand sqlcommand =new System.Data.SqlClient.SqlCommand();

sqlcommand.CommandText = command;

sqlcommand.Connection = sqlconnect;

System.Data.SqlClient.SqlDataAdapter da =new System.Data.SqlClient.SqlDataAdapter();

da.SelectCommand = sqlcommand;

DataSet ds =newDataSet();

sqlconnect.Open();

sqlcommand.ExecuteNonQuery();

da.Fill(ds);

GridView1.DataSource = ds;

GridView1.DataBind();

sqlconnect.Close();

}

First, make sure the SQL does return data by executing it like it query analyser or management studio.

Try this code in stead

System.Data.SqlClient.SqlConnection sqlconnect =new System.Data.SqlClient.SqlConnection("");//added connection string hereString command ="SELECT * from table";System.Data.SqlClient.SqlCommand sqlcommand =new System.Data.SqlClient.SqlCommand();sqlcommand.CommandText = command;sqlcommand.Connection = sqlconnect;System.Data.SqlClient.SqlDataAdapter da =new System.Data.SqlClient.SqlDataAdapter();da.SelectCommand = sqlcommand;DataSet ds =new DataSet();da.Fill(ds);GridView1.DataSource = ds;GridView1.DataBind();

|||

hi,

I have already checked the query in query builder and it works absolutely fine. The connection also works when i use the SQLDataSource control from the toolbox. However, it does not work with the code. Also, the code that you suggested gives the same output as before.

|||

Hi,

are you mapping the columns in dataview by manually or allow auto generated Columns.

System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(" ");
System.Data.DataSet ds = new System.Data.DataSet();
System.Data.SqlClient.SqlDataAdapter adapter = new System.Data.SqlClient.SqlDataAdapter();
conn.Open();
adapter.SelectCommand = new System.Data.SqlClient.SqlCommand("Select * From Table", conn);
adapter.Fill(ds, "Authors");
conn.Close();

GridView1.DataSource = ds;
GridView1.DataBind();

Hope this Work

|||

sjm:

I have already checked the query in query builder and it works absolutely fine. The connection also works when i use the SQLDataSource control from the toolbox. However, it does not work with the code. Also, the code that you suggested gives the same output as before.

The code should execute fine and as you say there are no errors.

Can you just break through and step over all the code just to make sure no exceptions are being thrown, and also inspect the dataset object after it has been filled to see if datatables/datarows exist in it.

|||

Hey

This does not work either. I've tried mapping the columns manually as well as Allowing auto generate, both don't give the required output.

Do i need to create a new table manually for allowing display of grid view?

if yes, how should i do that?

|||

sjm:

Do i need to create a new table manually for allowing display of grid view?

There is no need. The gridview can be bound to a dataset and it will auto generate the bound fields based on the datatable in the dataset.

Did you step through the code to make sure no errors and thrown and can inspect the dataset?

|||

place ur code in try catch block.

and also check ds.tables[0].rows.count by debug. is should return > 0.

|||

Thanks to all of you who replied...

I could solve the Problem...

The problem with the code was that the columns in the grid were not binded to the data.

I did that nd the code worked..

thank you.