Showing posts with label update. Show all posts
Showing posts with label update. Show all posts

Tuesday, March 27, 2012

Converting data to be inserted into a database

Hi,

I am using web matrix, and I am trying to insert a data into a MSDE database. I have used webmatrix to generate the update code, and it is executed when a button is pressed on the web page. but when the code is executed I get the error:

Syntax error converting the varchar value 'txtAmountSold.text' to a column of data type int.

So I added the following code to try to convert the data, but i am still getting the same error, with txtAmountSold.text replaced with "test"

dim test as integer
test = Convert.ToInt32(txtAmountSold.text)

Here is the whole of the function I am using:

Function AddItemToStock() As Integer

dim test as integer
test = Convert.ToInt32(txtAmountSold.text)

Dim connectionString As String = "server='(local)\Matrix'; trusted_connection=true; database='HawkinsComputers'"
Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)

Dim queryString As String = "INSERT INTO [stock] ([Catagory], [Type], [Name], [Manufacturer], [Price], [Weight"& _
"], [Description], [image], [OnOffer], [OfferPr"& _
"ice], [OfferDescription], [AmountInStock], [AmountOnOrder], [AmountSold]) VALUES ('CatList.SelectedItem.text', 'txtType.text', 'txtname.text', 'txtmanufacturer.text'"& _
", convert(money,'txtPrice.text'), 'txtWeight.text', 'txtDescription.text', 'txtimage.text', 'txtOnOffer"& _
".text', convert(money,'txtOfferPrice.text'), 'txtOfferDescrip"& _
"tion.text', 'txtAmountInStock.text', 'txtAmountOnOrder.text', 'test')"
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection

Dim rowsAffected As Integer = 0
dbConnection.Open
Try
rowsAffected = dbCommand.ExecuteNonQuery
Finally
dbConnection.Close
End Try

Return rowsAffected
End Function

Any help in solving this problem would be greatly appreciated, as I am really stuck for where to go next.The probably is that you are trying to send the literal value 'test' as a parameter value.

Please use this approach instead:
Dim queryString As String = "INSERT INTO [stock] ([Catagory], [Type], [Name], [Manufacturer], [Price], [Weight"& _
"], [Description], [image], [OnOffer], [OfferPr"& _
"ice], [OfferDescription], [AmountInStock], [AmountOnOrder],[AmountSold]) VALUES (@.CatList, @.Type,@.name, @.manufacturer, @.Price, @.Weight, @.Description, @.image, @.OnOffer, @.OfferPrice, @.OfferDescription, @.AmountInStock, @.AmountOnOrder, @.test)"
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
dbCommand.Parameters.Add("@.CatList,SqlDbType.VarChar,99).Value =
CatList.SelectedItem.text
dbCommand.Parameters.Add("@.Type,SqlDbType.VarChar,50).Value =txtType.text
dbCommand.Parameters.Add("@.Name,SqlDbType.VarChar,30).Value =txtmanufacturer.text
' add all parameters in this manner
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection

Note that for each parameter you will need to use the appropriate SqlDbType. And if it's a character data type you will also need to specify the length.

Here are some links which should help you with using parameters:
Using Parameterized Query in ASP.NET, Part 1
Using Parameterized Query in ASP.NET, Part 2
Using Parameterized Queries in ASP.Net
How To: Protect From SQL Injection in ASP.NET|||Thanks alot for the reply, Those links will help alot aswell.

Tuesday, March 20, 2012

ConvertEmptyStringToNull

I have a DB table with a varchar field that doesn't allow nulls, but the SqlDataSource I use to update the row wants to put a null in in exchange of an empty string when the field is empty.

<asp:Parameter Name="comment" ConvertEmptyStringToNull="false" Type="string" /
I even tried settting ConvertEmptyStringToNull to "false" but got nowhere.

I can do this by directly by handling the Updating event of the SqlDataSource, but that's a lot of code. Is there a better way?

I don't know whether this is what you are looking for. Use a space for the DefaultValue.

<UpdateParameters>

<asp:ParameterName="comment"DefaultValue=" "Type="String"/></UpdateParameters>|||

You have to set to ConvertEmptyStringToNull="false" on both the Parameter, and the BoundField control.

For example:

<

asp:BoundFieldDataField="locationName"HeaderText="Location Name"SortExpression="locationName"ConvertEmptyStringToNull="false"/>

<

asp:ParameterName="locationName"Type="String"ConvertEmptyStringToNull="false"/>|||

Hi,

This was a great help, I have been teaching myself ASP.NET and it was the first stumbling block. I was following "SAMS Teach Yourself ASP.NET (24Hrs)" and was on the datagrid view section. It was advising me to set the column value (which was set to not allow nulls) to still be able to except a blank entry. This was supposedly achieved by editing the value via the smart tag and then setting the ConvertEmptyStringToNull, which I did. But still failed with the error "Null expected".

I followed the manual method described above and edited the source code and it worked!

My question is if this is a bug with Visual Studio Express? That is the smart tag function is not updating the Parameter. I had to manually edit the source code in order to get it to work as above. Are there other areas that Visual Studio does not via Design view update the source code side of things?

Regards X Smile

|||

Hi exup,

There are a lot of demos showing how complex web pages can be done without digging into code. The problem is that, unless you want your pages to perform exactly like the demos, you will need to dig into code.

"Teach yourself asp.net in 24 hours" as a book title sets up a somewhat unrealistic expectation. Asp.net is complicated, and needs time to master. I'd give yourself a few months to get comfortable with all of the quirks.

John

Monday, March 19, 2012

Convert varbinary(16) to nvarchar

Hi,

Basically. I need to convert a varbinary(16) to a varchar in a sorted procedure (to update a table). [in query analyser key looks like 0x00000000000000000001]

Background:
I'm having to output replication commands from the distributor DB and want to store the sequence I'm upto to not have to repeat the same commands on the next export.

The stored proc for performing the export accepts an nvarchar(22) for the min key A(and this is how I've stored it). The records are then exported with varbinary(16) datatyped keys

I need to then store this key back but cannot seem to convert it.

Any thoughts are much appreciated.
Gaz

I've run into similar issues.

Try converting to a BigInt and storing that way. If the datatype is VarBin(16) and you shove in a value of 16, it will convert it. Usually, if an external application is expected a binary type, and you feed it 16 (without the 0x0 in the front) it will do it's own conversion.

So, you have varbin(16) type, value: 0x00000000000000000001

convert Hex value to Bigint (which, in this case would be = 1)

convert the bigint to a string and store the string (in this case "1"). Later when your app gets the string "1", it might just convert it appropriately. In any event, it's pretty easy to test.

Code Snippet

insert into MyTable (MyStringField)

select convert(varchar(32),convert(bigint,<MyHexValue>))

convert update from access to sql and have an error.

I am trying to convert code I have working for access to work with SQL.
fldName, fldEmail, ID are the names in the database. recNum does have the value of the record that I want to edit. Here is the error I am getting.
System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near '?'.

And here is the stack trace (which I don't know how to read except for the line the error is on)
 [SqlException: Line 1: Incorrect syntax near '?'.]
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) +723
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +194
goodellweb.adm_contact.editNow_Click(Object sender, EventArgs e) in C:\Inetpub\wwwroot\webroot\goodellweb\adm\adm_contacts.aspx.vb:306
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +57
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain() +1315

here is my code.

Dim editSQL As String = "Update tbEmail Set fldName=?, fldEmail=? Where ID=?"
Dim SqlConn As New SqlConnection(ConnStr)
Dim Cmd As New SqlCommand(editSQL, SqlConn)
Cmd.Parameters.Add(New SqlParameter("@.fldName", nameEdit.Text))
Cmd.Parameters.Add(New SqlParameter("@.fldEmail", emailEdit.Text))
Cmd.Parameters.Add(New SqlParameter("@.recNum", recNum))

SqlConn.Open()
Try
Cmd.ExecuteNonQuery()
Finally
SqlConn.Close()
End Try
Response.Write("recNum " & recNum & " <br>")

Thanks
MichaelSQL uses named parameters. Try:

Dim editSQL As String = "Update tbEmail Set fldName=@.fldName, fldEmail=@.fldEmail Where ID=@.recNum"

Dim SqlConn As New SqlConnection(ConnStr)

Dim Cmd As New SqlCommand(editSQL, SqlConn)

Cmd.Parameters.Add(New SqlParameter("@.fldName", nameEdit.Text))

Cmd.Parameters.Add(New SqlParameter("@.fldEmail", emailEdit.Text))

Cmd.Parameters.Add(New SqlParameter("@.recNum", recNum))

|||hanks douglas
That did take care of the error, but it is not updating. Maybe it is updating its just updating what was already in the database and now the new data. Is there a way to see if the new data in the textbox is present? When I do a
 Response.Write("email " & emailEdit.Text & " <br>")
at the end of the code it shows the data in the database. Should it be the new data?
Thanks
Michael|||Just tried this and it updated.

Cmd.Parameters.Add(New SqlParameter("@.fldName", "test"))

So my new data is not getting to the right place.
So what am i missing?
Thanks
Michael|||You need to have your data binding only take place when IsPostback is false:

If IsPostback=false then
' data bind ONLY here
End If

What you do otherwise is re-read the data from the database and "update" with the data from the database.

Thursday, March 8, 2012

Convert SQL in ASP 3.0 site to UPDATE ntext

Greetings...
I have an ASP 3.0 website that I am usizing from an MS Access DB to MS SQL
server 2000. While most of my SQL seems to be working perfectly, my memo
fields (ntext) it appears require updating using WRITETEXT.
The following is the sub I tried to use, however, was asked to declare the
pointer variable. After many attempts I'm stuck...
sub db_update_add_note
sql ="SELECT @.ptrval = TEXTPTR(ProjectDesc) " & _
"FROM pmProjects " & _
"WHERE ProjectId = " & ProjectId & " " & _
"WRITETEXT newnote @.ptrval"
response.write sql
on error resume next
cn.execute(sql)
if err.number <> 0 then
b_error = true
error_list.add "db_update_add_note" & err.Number ,"The database update
failed: " & err.Description
else
end if
on error goto 0
end sub
As I'm new to the differences between MS Access SQL and T-SQL I could use
some help.
Thanks...
Message posted via http://www.droptable.com
Sorry... This is the code I attempted to use:
sub db_update_add_note
sql = "DECLARE @.ptrval CURSOR " & _
"SELECT @.ptrval = TEXTPTR(ProjectDesc) " & _
"FROM pmProjects " & _
"WHERE ProjectId = " & ProjectId & " " & _
"WRITETEXT newnote @.ptrval"
response.write sql
on error resume next
cn.execute(sql)
if err.number <> 0 then
b_error = true
error_list.add "db_update_add_note" & err.Number ,"The database update
failed: " & err.Description
else
end if
on error goto 0
end sub
I know I'm missing something here...
Kev
Message posted via http://www.droptable.com
|||There are a number of problems with your SQL script. @.ptrval needs to be a
binary(16) rather than CURSOR. Also, you have not specified a column value
in the WRITETEXT statement.
If your ntext data are reasonably sized, you might consider using a regular
UPDATE statement instead like the example below. In any case, use command
parameters to prevent SQL injection.
myCommand.CommandText = "UPDATE pmProjects SET ProjectDescription = ? WHERE
ProjectId = ?"
Set projectDescriptionParameter = myCommand.CreateParameter( _
"@.ProjectDescription", _
adLongVarWChar, _
adParamInput, _
Len(ProjectDescription))
myCommand.Parameters.Append projectDescriptionParameter
projectDescriptionParameter.Value = ProjectDescription
Set projectIdParameter = myCommand.CreateParameter( _
"@.ProjectIdParameter", _
adInteger, _
adParamInput)
myCommand.Parameters.Append projectIdParameter
projectIdParameter.Value = ProjectId
myCommand.Execute
Hope this helps.
Dan Guzman
SQL Server MVP
"Kevin Dearinger via droptable.com" <forum@.droptable.com> wrote in message
news:cbbeac9230f74b41929c9ef12396c26a@.droptable.co m...
> Sorry... This is the code I attempted to use:
> sub db_update_add_note
> sql = "DECLARE @.ptrval CURSOR " & _
> "SELECT @.ptrval = TEXTPTR(ProjectDesc) " & _
> "FROM pmProjects " & _
> "WHERE ProjectId = " & ProjectId & " " & _
> "WRITETEXT newnote @.ptrval"
> response.write sql
> on error resume next
> cn.execute(sql)
> if err.number <> 0 then
> b_error = true
> error_list.add "db_update_add_note" & err.Number ,"The database update
> failed: " & err.Description
> else
> end if
> on error goto 0
> end sub
> I know I'm missing something here...
> Kev
> --
> Message posted via http://www.droptable.com

Convert SQL in ASP 3.0 site to UPDATE ntext

Greetings...
I have an ASP 3.0 website that I am usizing from an MS Access DB to MS SQL
server 2000. While most of my SQL seems to be working perfectly, my memo
fields (ntext) it appears require updating using WRITETEXT.
The following is the sub I tried to use, however, was asked to declare the
pointer variable. After many attempts I'm stuck...
sub db_update_add_note
sql = "SELECT @.ptrval = TEXTPTR(ProjectDesc) " & _
"FROM pmProjects " & _
"WHERE ProjectId = " & ProjectId & " " & _
"WRITETEXT newnote @.ptrval"
response.write sql
on error resume next
cn.execute(sql)
if err.number <> 0 then
b_error = true
error_list.add "db_update_add_note" & err.Number ,"The database update
failed: " & err.Description
else
end if
on error goto 0
end sub
As I'm new to the differences between MS Access SQL and T-SQL I could use
some help.
Thanks...
Message posted via http://www.droptable.comSorry... This is the code I attempted to use:
sub db_update_add_note
sql = "DECLARE @.ptrval CURSOR " & _
"SELECT @.ptrval = TEXTPTR(ProjectDesc) " & _
"FROM pmProjects " & _
"WHERE ProjectId = " & ProjectId & " " & _
"WRITETEXT newnote @.ptrval"
response.write sql
on error resume next
cn.execute(sql)
if err.number <> 0 then
b_error = true
error_list.add "db_update_add_note" & err.Number ,"The database update
failed: " & err.Description
else
end if
on error goto 0
end sub
I know I'm missing something here...
Kev
Message posted via http://www.droptable.com|||There are a number of problems with your SQL script. @.ptrval needs to be a
binary(16) rather than CURSOR. Also, you have not specified a column value
in the WRITETEXT statement.
If your ntext data are reasonably sized, you might consider using a regular
UPDATE statement instead like the example below. In any case, use command
parameters to prevent SQL injection.
myCommand.CommandText = "UPDATE pmProjects SET ProjectDescription = ? WHERE
ProjectId = ?"
Set projectDescriptionParameter = myCommand.CreateParameter( _
"@.ProjectDescription", _
adLongVarWChar, _
adParamInput, _
Len(ProjectDescription))
myCommand.Parameters.Append projectDescriptionParameter
projectDescriptionParameter.Value = ProjectDescription
Set projectIdParameter = myCommand.CreateParameter( _
"@.ProjectIdParameter", _
adInteger, _
adParamInput)
myCommand.Parameters.Append projectIdParameter
projectIdParameter.Value = ProjectId
myCommand.Execute
Hope this helps.
Dan Guzman
SQL Server MVP
"Kevin Dearinger via droptable.com" <forum@.droptable.com> wrote in message
news:cbbeac9230f74b41929c9ef12396c26a@.dr
optable.com...
> Sorry... This is the code I attempted to use:
> sub db_update_add_note
> sql = "DECLARE @.ptrval CURSOR " & _
> "SELECT @.ptrval = TEXTPTR(ProjectDesc) " & _
> "FROM pmProjects " & _
> "WHERE ProjectId = " & ProjectId & " " & _
> "WRITETEXT newnote @.ptrval"
> response.write sql
> on error resume next
> cn.execute(sql)
> if err.number <> 0 then
> b_error = true
> error_list.add "db_update_add_note" & err.Number ,"The database update
> failed: " & err.Description
> else
> end if
> on error goto 0
> end sub
> I know I'm missing something here...
> Kev
> --
> Message posted via http://www.droptable.com

Convert SQL in ASP 3.0 site to UPDATE ntext

Greetings...
I have an ASP 3.0 website that I am usizing from an MS Access DB to MS SQL
server 2000. While most of my SQL seems to be working perfectly, my memo
fields (ntext) it appears require updating using WRITETEXT.
The following is the sub I tried to use, however, was asked to declare the
pointer variable. After many attempts I'm stuck...
sub db_update_add_note
sql = "SELECT @.ptrval = TEXTPTR(ProjectDesc) " & _
"FROM pmProjects " & _
"WHERE ProjectId = " & ProjectId & " " & _
"WRITETEXT newnote @.ptrval"
response.write sql
on error resume next
cn.execute(sql)
if err.number <> 0 then
b_error = true
error_list.add "db_update_add_note" & err.Number ,"The database update
failed: " & err.Description
else
end if
on error goto 0
end sub
As I'm new to the differences between MS Access SQL and T-SQL I could use
some help.
Thanks...
--
Message posted via http://www.sqlmonster.comSorry... This is the code I attempted to use:
sub db_update_add_note
sql = "DECLARE @.ptrval CURSOR " & _
"SELECT @.ptrval = TEXTPTR(ProjectDesc) " & _
"FROM pmProjects " & _
"WHERE ProjectId = " & ProjectId & " " & _
"WRITETEXT newnote @.ptrval"
response.write sql
on error resume next
cn.execute(sql)
if err.number <> 0 then
b_error = true
error_list.add "db_update_add_note" & err.Number ,"The database update
failed: " & err.Description
else
end if
on error goto 0
end sub
I know I'm missing something here...
Kev
--
Message posted via http://www.sqlmonster.com|||There are a number of problems with your SQL script. @.ptrval needs to be a
binary(16) rather than CURSOR. Also, you have not specified a column value
in the WRITETEXT statement.
If your ntext data are reasonably sized, you might consider using a regular
UPDATE statement instead like the example below. In any case, use command
parameters to prevent SQL injection.
myCommand.CommandText = "UPDATE pmProjects SET ProjectDescription = ? WHERE
ProjectId = ?"
Set projectDescriptionParameter = myCommand.CreateParameter( _
"@.ProjectDescription", _
adLongVarWChar, _
adParamInput, _
Len(ProjectDescription))
myCommand.Parameters.Append projectDescriptionParameter
projectDescriptionParameter.Value = ProjectDescription
Set projectIdParameter = myCommand.CreateParameter( _
"@.ProjectIdParameter", _
adInteger, _
adParamInput)
myCommand.Parameters.Append projectIdParameter
projectIdParameter.Value = ProjectId
myCommand.Execute
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Kevin Dearinger via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:cbbeac9230f74b41929c9ef12396c26a@.SQLMonster.com...
> Sorry... This is the code I attempted to use:
> sub db_update_add_note
> sql = "DECLARE @.ptrval CURSOR " & _
> "SELECT @.ptrval = TEXTPTR(ProjectDesc) " & _
> "FROM pmProjects " & _
> "WHERE ProjectId = " & ProjectId & " " & _
> "WRITETEXT newnote @.ptrval"
> response.write sql
> on error resume next
> cn.execute(sql)
> if err.number <> 0 then
> b_error = true
> error_list.add "db_update_add_note" & err.Number ,"The database update
> failed: " & err.Description
> else
> end if
> on error goto 0
> end sub
> I know I'm missing something here...
> Kev
> --
> Message posted via http://www.sqlmonster.com

Friday, February 24, 2012

convert ms access sql staement to oracle sql statement

Hi,

I currently have a ms access update query that runs
perfectly well and quicly in access however I now need to add this query
to convert this qeryu to oracles equivelant sql syntax and add it to the end
of an oracle sql script.

Unfortunately Im not having much success although i seem to be able to
convert it to a working oracle sql. it takes hours to run the statement in
oracle where as in access it runs in seconds

any help is appreciated.

Ms Access sql :

UPDATE (PRO_STY_TPRICES INNER JOIN PRO_STYLE_COLOURS ON PRO_STY_TPRICES.
STY_ID = PRO_STYLE_COLOURS.STY_ID) INNER JOIN PRO_TST_RV3X_RPT_WRK ON
(PRO_STYLE_COLOURS.SEASON = PRO_TST_RV3X_RPT_WRK.SEASON) AND
(PRO_STYLE_COLOURS.STY_NUM = PRO_TST_RV3X_RPT_WRK.STY_NUM) AND
(PRO_STYLE_COLOURS.STY_QUAL = PRO_TST_RV3X_RPT_WRK.STY_QUAL) AND
(PRO_STYLE_COLOURS.BF_MAT_CHAR_VAL = PRO_TST_RV3X_RPT_WRK.BF_MAT_CHAR_VAL)
SET PRO_TST_RV3X_RPT_WRK.MKD_DATE = pro_sty_tprices.new_active_date,
PRO_TST_RV3X_RPT_WRK.MKD_PRICE = pro_sty_tprices.new_tprice
WHERE (((PRO_STY_TPRICES.NEW_ACTIVE_DATE) Is Not Null));

Oracle SQL :
update pro.tst_rv3x_rpt_wrk x
set(x.mkd_date, x.mkd_price) =
(Select a.new_active_date, a.new_tprice
from pro.sty_tprices a, pro.style_colours b
where a.sty_id=b.sty_id
and b.bf_mat_char_val = x.bf_mat_char_val
and b.season = x.season
and b.sty_num = x.sty_num
and b.sty_qual = x.sty_qual
and a.new_active_date is not null
)ian m via SQLMonster.com (forum@.SQLMonster.com) writes:
> I currently have a ms access update query that runs perfectly well and
> quicly in access however I now need to add this query to convert this
> qeryu to oracles equivelant sql syntax and add it to the end of an
> oracle sql script.
> Unfortunately Im not having much success although i seem to be able to
> convert it to a working oracle sql. it takes hours to run the statement in
> oracle where as in access it runs in seconds

Without knowledge about the tables, their sizes and the and indexes it is
impossible to tell.

But, stop! don't post that information as a reply to this post. There is
one more thing that is wrong with your post: you are posting to a newsgroup
for SQL Server. What is truth on SQL Server may not be good on Oracle.
So try comp.databases.oracle instead.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Convert money columns to update?

Hi Guys

I need your help again, I am try to update several columns and the data type is 'money'.

Below is the code I have used:

UPDATE CAT_Products
SET

UnitCost ='10.00',
UnitCost2 = '10.00',
UnitCost3 = '10.00',
UnitCost4 = '10.00',
UnitCost5 = '10.00',
UnitCost6 = '10.00'

WHERE ProductCode = '0008'

But it will not update, instead I get this error:

-----------------------------------------------------------

>[Error] Script lines: 1-9 --------
Disallowed implicit conversion from data type varchar to data type money, table 'dbo.CAT_Products', column 'UnitCost'. Use the CONVERT function to run this query.

More exceptions ... Disallowed implicit conversion from data type varchar to data type money, table '.dbo.CAT_Products', column 'UnitCost2'. Use the CONVERT function to run this query.

-----------------------------------------------------------

The error message indicates that I need to use the convert function. But the columns data type is set at 'money' not 'varcher' . So do I need to convert data type to 'varcher' in order to update and convert back to data type 'money' when update complete? Or do I need to indicate in the update statement that data type is already 'money'? I am not sure how I would either.

Thanks

try:

UPDATE CAT_Products
SET

UnitCost =convert(money,'10.00'),
UnitCost2 = convert(money,'10.00'),
UnitCost3 = convert(money,'10.00'),
UnitCost4 =convert(money,'10.00'),
UnitCost5 = convert(money,'10.00'),
UnitCost6 = convert(money,'10.00')

WHERE ProductCode = '0008'

|||

Hi jpazgier

Thank you for your reply and code.

I am pleased to say that your code worked first time.

A great help.

Cheers.

Tuesday, February 14, 2012

Convert getdate to week and day of week

I have a query that run every day to update a summary table which has week number and day of week. what I currently do is delete all records from the summary table and then summarize all the data availabe from four tables adn then populate the table daily. I want to know if I can run the update query to run only for the week number and day of week depending on getdate. Can I do this?

Please help..Look up the DATEPART() function.

Sunday, February 12, 2012

Convert DB2 "FOR UPDATE" in SQL Server

Hello,
In DB2, we can do FOR UPDATE selection, in order to lock the records until the transaction is completed. e.g.
select field1, field2, field3
from table
FOR UPDATE;
How can I perform the equivalent feature in SQL Server?
Thanks!This is an exceptionally bad way to do things. It allows the "lunchtime lock" where a user has data locked for ages, preventing others from getting any real work done. The best way to simulate this feature in SQL Server is to retrieve the data, then turn off or unplug the network cable from the server.

-PatP|||Pat...Pat...Pat...

You've worked on big iron before....|||You've worked on big iron before....I know, I'll admit it... but I keep trying to prevent those bad habits from spreading!

-PatP|||I sincerely need helps for this question. And I am not fooling around here.

Friday, February 10, 2012

Convert character to NULL

I am loading a flat file to a table but I also need to scrub the data a bit before the data hits the table. The main update required is converting a dot (.) character to a null value. The source file is using this character to indicate a blank. I know I can use the Dervived Column Transformation, but I have quite a few columns which will take a while to manually configure. Is there another transformation option that anyone can point me to?

Thanks

Under this scenario, is that the only value in the column? Or are you searching/replacing (.) with NULLs?|||

I am using a conditional evaluation in the derived column transformation:

[Coulmn1] == "." ? (DT_STR,50,1252)NULL(DT_STR,50,1252) : [Column1]

The columns would never have a value that contains a dot, only values or the dot

|||

crancilio wrote:

I am loading a flat file to a table but I also need to scrub the data a bit before the data hits the table. The main update required is converting a dot (.) character to a null value. The source file is using this character to indicate a blank. I know I can use the Dervived Column Transformation, but I have quite a few columns which will take a while to manually configure. Is there another transformation option that anyone can point me to?

Thanks

Not that I am awre of. I am afraid you have to do the same thing for every column affected by that logic. Perhaps, you could try to use an script component where you could use the magic of copy and paste...

|||Let's use a script component instead of a derived column. You'll be very happy with the following solution:

Instead of the derived column, add a script component, set it to be a transformation.

Select the columns you wish to work with. Set their usage types to "ReadWrite." ONLY select the columns you wish to process with this logic.

Below is the script. I don't understand it fully, and I've hacked something that our forum user, jaegd wrote:

Code Snippet

Imports System
Imports System.Data
Imports System.Math
Imports System.Text
Imports System.Collections.Generic
Imports Microsoft.SqlServer.Dts.Pipeline
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
'Note: this code was originally written/posted by the SSIS forum user, jaegd. http://forums.microsoft.com/MSDN/User/Profile.aspx?UserID=133544&SiteID=1
'Credit has been given where credit is due
'Original post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=864401&SiteID=1

Public Class ScriptMain
Inherits UserComponent

Private inputBuffer As PipelineBuffer
Private cols As Dictionary(Of Int32, ColumnInfo) = New Dictionary(Of Int32, ColumnInfo)
Private currentColumnInfo As ColumnInfo = New ColumnInfo

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
'Setup control counter
Dim counter As Integer = 0
'Loop through segments
'MsgBox(currentColumnInfo.colIndex.ToString)
For Each currentcolumn As KeyValuePair(Of Int32, ColumnInfo) In cols
'MsgBox(inputBuffer.GetString(currentcolumn.Key))
If (inputBuffer.GetString(currentcolumn.Key)) = "." Then
inputBuffer.SetString(currentcolumn.Key, Chr(0))
End If
Next

End Sub

Public Overrides Sub ProcessInput(ByVal InputID As Integer, ByVal Buffer As Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer)
' Get the Pipeline Buffer for subsequent ordinal column access
inputBuffer = Buffer
MyBase.ProcessInput(InputID, Buffer)
End Sub

Public Overrides Sub PreExecute()
BuildColumnDictionary()
End Sub

Private Sub BuildColumnDictionary()
Dim indexes() As Integer
Dim input As IDTSInput90
Dim col As IDTSInputColumn90
Dim offset As Integer = 0

input = Me.ComponentMetaData.InputCollection(0)
'presumes GetColumnIndexes order matches iterator order
'as BufferManager is not available to my knowledge in ScriptComponent
indexes = Me.GetColumnIndexes(input.ID)
For Each col In input.InputColumnCollection
Dim columnStructure As New ColumnInfo
With columnStructure
.colName = col.Name
.colLength = col.Length
.colIndex = indexes(offset)
'Normally, BufferManager would be used, but its not exposed in Script Component
.colPrecision = col.Precision
.colScale = col.Scale
.colType = col.DataType
End With
cols.Add(indexes(offset), columnStructure)
offset += 1
Next
End Sub

Public Structure ColumnInfo
Dim colName As String
Dim colType As DataType
Dim colIndex As Int32
Dim colLength As Int32
Dim colPrecision As Int32
Dim colScale As Int32
End Structure

End Class


A screenshot of the results: HERE|||Note: I would LOVE it if someone would come in and simplify the code above. I just don't have the understanding of the SSIS programming model to do anything BUT hack code together. Not yet anyway.

Also, the work happens in the Input0_ProcessInputRow sub. Periods are replaced with Chr(0) which is null. Give it a shot, tweak it to however you need, etc...

You may want to trim() the columns first, before going into this transformation.|||This is great - thanks! I will let you know how it goes|||

Phil - thank you so much for your help!

This code was exactly what I was looking for. The only tweak I had to make was to use SetNull() instead of SetString() - the Chr(0) actually added an empty string rather than a null.

Thanks again!

|||

Phil Brammer wrote:

Note: I would LOVE it if someone would come in and simplify the code above. I just don't have the understanding of the SSIS programming model to do anything BUT hack code together. Not yet anyway.

Also, the work happens in the Input0_ProcessInputRow sub. Periods are replaced with Chr(0) which is null. Give it a shot, tweak it to however you need, etc...

You may want to trim() the columns first, before going into this transformation.

How's this?

Note - this is really only useful for generically accessing each column, and has no type safety, so the code only works for string columns. jaegd's original code provided a lot more information about the columns, including type information, which would allow you to add conditional logic to use the appropriate accessor (GetString, GetInt32, etc). I've also used the System.Reflection to access the buffer, but I need to test the performance a bit more before pushing that as a solution.

Code Snippet

'Note: this code was originally written/posted by the SSIS forum user, jaegd. http://forums.microsoft.com/MSDN/User/Profile.aspx?UserID=133544&SiteID=1

'Credit has been given where credit is due

'Original post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=864401&SiteID=1

'Trimmed to smaller set of code by jwelch

Imports Microsoft.SqlServer.Dts.Pipeline

PublicClass ScriptMain

Inherits UserComponent

Private inputBuffer As PipelineBuffer

PublicOverridesSub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim counter AsInteger = 0

For counter = 0 To inputBuffer.ColumnCount - 1

If (inputBuffer.GetString(counter)) = "."Then

inputBuffer.SetString(counter, Chr(0))

EndIf

Next

EndSub

PublicOverridesSub ProcessInput(ByVal InputID AsInteger, ByVal Buffer As Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer)

' Get the Pipeline Buffer for subsequent ordinal column access

inputBuffer = Buffer

MyBase.ProcessInput(InputID, Buffer)

EndSub

EndClass

|||

jwelch wrote:


How's this?

Yep, I like that MUCH better. Knew jaegd had much more stuff in there, I just didn't quite know where to begin to trim it down... Ran out of time too... The golf course beckoned. Wink