Sql Update command: string

Showing posts with label string. Show all posts

Friday, March 30, 2012

Problems ith Connection string to SQL Express

My application is usningtraditional connection string:

addkey="DbConnection"value="driver={SQL Native Driver}; provider=SQLNCLI;server=localhost;trusted_connection=yes;database=ebay" />

It works fine with database attached to SQL Server, how do I make this working with SQL Express attachable file in data directory?

I have tried this, but it does not work:

Provider=SQLNCLI;Server=RAF001\SQLExpress;AttachDbFilename=..\TestSite\App_Data\ebay.mdf;Database=ebay;Trusted_Connection=Yes;

Wednesday, March 28, 2012

problems in editing a record... why? help pls....

can someone help me why it produces an error...

error is: ERROR 22001 Microsoft ODBC SQL Server Driver SQL Server STRING or data of BINARY was cut short.
ERROR 01000 Microsoft ODBC SQL Server Driver SQL Server statement was ended.

This is my code here for editing a record...

Protected Sub Button_save2_Click1(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button_save2.Click
'||||| Create string connection
Dim StrConn As String = "Dsn=MS_PKG01;UID=emiline;APP=Microsoft? Visual Studio? 2005;WSID=MSHNP200603;DATABASE=MS_PKG01;Trusted_Connection=Yes"
'||||| Create connection object
Dim MyConn As Odbc.OdbcConnection = New Odbc.OdbcConnection(StrConn)

'||||| Open connection
MyConn.Open()

'|||| Create odbcCommand object
Dim Update_record As New Odbc.OdbcCommand("UPDATE TM0001 SET TM0001.syain_name = ?, TM0001.syain_pass = ?, TM0001.office_id = ?, TM0001.birth_date = ?, TM0001.empl_date = ?, TM0001.user_iden = ? ", MyConn)

Dim hireYear As String
Dim hireMonth As String
Dim hireDay As String
Dim date_hire As String

hireYear = DropDownList_hire_yr.Text
hireMonth = DropDownList_hire_mo.Text
hireDay = DropDownList_hire_day.Text

date_hire = hireYear + "/" + hireMonth + "/" + hireDay

'|||| Add command parameters
Update_record.Parameters.Add("@.P1", OdbcType.Char, 8).Value = TextBox_id.Text
Update_record.Parameters.Add("@.P2", OdbcType.Char, 20).Value = TextBox_name.Text
Update_record.Parameters.Add("@.P3", OdbcType.Char, 20).Value = TextBox_pswd.Text
Update_record.Parameters.Add("@.P3", OdbcType.Char, 40).Value = DropDownList_office.SelectedValue
Update_record.Parameters.Add("@.P3", OdbcType.Char, 2).Value = date_hire
Update_record.Parameters.Add("@.P3", OdbcType.Char, 10).Value = TextBox_bday.Text
Update_record.Parameters.Add("@.P3", OdbcType.Char, 1).Value = DropDownList_iden.SelectedValue

'|||| Execute command
Update_record.ExecuteNonQuery()

'|||| Close connection
MyConn.Close()
End Sub
End Class

I modify some codes already but still same error...

my code:

'||||| Create connection object
Dim MyConn As Odbc.OdbcConnection = New Odbc.OdbcConnection(StrConn)

id_user = TextBox_id.Text
name = TextBox_name.Text
pass = TextBox_pswd.Text
office_id = DropDownList_office.SelectedValue
bday = TextBox_bday.Text
iden = DropDownList_iden.SelectedValue

hireYear = DropDownList_hire_yr.Text
hireMonth = DropDownList_hire_mo.Text
hireDay = DropDownList_hire_day.Text

date_hire = hireYear + "/" + hireMonth + "/" + hireDay

'|||| Add command parameters

Update_record.Parameters.Add("@.P1", OdbcType.Char).Value = name
Update_record.Parameters.Add("@.P1", OdbcType.Char).Value = pass
Update_record.Parameters.Add("@.P1", OdbcType.Char).Value = office_id
Update_record.Parameters.Add("@.P1", OdbcType.Char).Value = date_hire
Update_record.Parameters.Add("@.P1", OdbcType.Char).Value = bday
Update_record.Parameters.Add("@.P1", OdbcType.Char).Value = iden
Update_record.Parameters.Add("@.P1", OdbcType.Char).Value = id_user

'||||| Open connection
MyConn.Open()

'|||| Execute command
Update_record.ExecuteNonQuery()

'|||| Close connection
MyConn.Close()

|||

Hi,

i think a textfield has more data as permitted, so the update command crashed. You can inspect the prepared SQLCommand inside the Update_record object and try to execute this command direct in the database (Query Analyzer or Database Command Prompt Interface).

Regards
Marc Andre

|||

hmmm i already check it and there is nothing wrong...

and whenever i input a new record it always satisfy the condition in the data type...

but still i dont why when i edit it generates error in my code...

hmmm another thing that i observe in my code is that when

i click the save button when editing it doesnt change anything...but the first record or the initial record always shows...

i tried putting is postback but nothing happens...

everytime i press the save button to saved my changes in the data the initial entry shows...

coz in my pageload the initial entry or the first record in the dabse is shown...

hmmm what do u think?

Wednesday, March 7, 2012

Problem with XQuery...

Hi all,

I've a little problem to read from a XML string using T-Sql, someone can help me please?
I use this XML string:
DECLARE @.x xml
SET @.x = '
<Envelope xmlns="urn:aerospace: dataschema: DocumentBusinessInformationEntitySchemaModule" xmlns:asram="urn: aerospaceataschema: ReusableAggregateBusinessInformationEntitySchemaModule">
<ROOT>
<asram:a>111</asram:a>
</ROOT>
</Envelope>
'

And this select :
SELECT @.x.query('/Envelope/ROOT/asram:a/text()') AS LastResult

Whow I can get de “111”?

Tks

In your query you need to make sure to declare the namespaces:

Code Snippet

DECLARE @.x xml;

SET @.x = '

<ROOT>

<asram:a>111</asram:a>

</ROOT>

</Envelope>;

SELECT @.x.query('

declare default element namespace "urn:aerospace: dataschema: DocumentBusinessInformationEntitySchemaModule";

declare namespace asram="urn: aerospace dataschema: ReusableAggregateBusinessInformationEntitySchemaModule";

/Envelope/ROOT/asram:a/text()

') AS LastResult;

|||Thank, that works,

But if i change the <Envelope> like that (
<Envelope xmlns="urn:aerospace: dataschema: documentBusinessInformationEntitySchemaModule" xmlns:asram="urn:aerospace: dataschema:ReusableAggregateBusinessInformationEntitySchemaModule" xmlns:ccts="urn:unece:uncefact: dataschema: draft:CoreComponentTypesSchemaModule:0:3:4" xmlns:udt="urn:unece:uncefact: dataschema: draft:UnqualifiedDataTypesSchemaModule:0:3:4" xmlns:mime="urn:unece:uncefact: dataschema: draft:codeList:MIME:MIME_Media_Types:2003::IANA" xmlns:rec20="urn:unece:uncefact: dataschema: draft:codeList:Rec20:Codes_for_Units_of_Measure:2001:6:UNECE" xmlns:iso4217="urn:unece:uncefact: dataschema: draft:identifierData:ISO_4217:Currency_Codes:2001:5:ISO" xmlns:iso639="urn:unece:uncefact: dataschema: draft:identifierData:ISO_639:Language_Code:1988:3:ISO" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsichemaLocation="urn:aerospace: dataschema: documentBusinessInformationEntitySchemaModule
\\Srci-gestion\SRCI\SRCI_Projets\Associations\CAPEMP~1\Norme_CapEmploi\R1.13\CapEmploi_Envelope_BIE_R1.13.xsd">
)
how I can get again the “111”|||

The only change seems to be in the namespac URNs so you need to adapt the XQuery namespace declarations as in this example:

Code Snippet

DECLARE @.x xml;

SET @.x = '

<ROOT>

<asram:a>111</asram:a>

</ROOT>

</Envelope>;

SELECT @.x.query('

declare default element namespace "urn:aerospace: dataschema: documentBusinessInformationEntitySchemaModule";

declare namespace asram="urn:aerospace: dataschema:ReusableAggregateBusinessInformationEntitySchemaModule";

/Envelope/ROOT/asram:a/text()

') AS LastResult;

|||It works, thanks a lot.
(Merci beaucoup)

problem with xml data source

hi,

i have a problem:

i got a report to which i pass a xml string like

<xml>

value

</somemoredata>

</somedata>

</xml>

in the visual studio it works just the way its supposed to be, but if i upload it on a report server and execute it there nothing happens. even no exception is thrown. the screen remains blank.

anyone got a solution or atleast a hint?

tia

found the problem:

the string i pass is only allowed to be about 1800 bytes large

Saturday, February 25, 2012

Problem with variable in T-SQL

Hi,

I want to find out the project names, for the PROJ table and I know
the Project IDs as a string (comma seperated).

I tried this:

--------
DECLARE @.proj_ids varchar(500)

SET @.proj_ids='(100,200)'

SELECT PROJ_NAME FROM PROJ WHERE PROJ_ID IN + @.proj_ids
--------

But this throws the error:
"Incorrect syntax near '+'"

in Query Analyzer.

Could someone please point, what I am doing wrong?
I know, I must be doing something very silly!Hi

Check out the excellent article at:
http://www.algonet.se/~sommar/dynamic_sql.html

John

"Surajit Laha" <s_laha@.rediffmail.com> wrote in message
news:ba8ee108.0310140529.3af4dd96@.posting.google.c om...
> Hi,
> I want to find out the project names, for the PROJ table and I know
> the Project IDs as a string (comma seperated).
> I tried this:
> --------
> DECLARE @.proj_ids varchar(500)
> SET @.proj_ids='(100,200)'
> SELECT PROJ_NAME FROM PROJ WHERE PROJ_ID IN + @.proj_ids
> --------
> But this throws the error:
> "Incorrect syntax near '+'"
> in Query Analyzer.
> Could someone please point, what I am doing wrong?
> I know, I must be doing something very silly!|||[posted and mailed, please reply in news]

Surajit Laha (s_laha@.rediffmail.com) writes:
> I want to find out the project names, for the PROJ table and I know
> the Project IDs as a string (comma seperated).
> I tried this:
> --------
> DECLARE @.proj_ids varchar(500)
> SET @.proj_ids='(100,200)'
> SELECT PROJ_NAME FROM PROJ WHERE PROJ_ID IN + @.proj_ids
> --------
> But this throws the error:
> "Incorrect syntax near '+'"
> in Query Analyzer.
> Could someone please point, what I am doing wrong?
> I know, I must be doing something very silly!

Yes, you are inventing your own syntax. SQL Server is not clever enough
to understand that.

Although, John recommended one article on my web site, I like to
recommend another. Look at
http://www.algonet.se/~sommar/array...ist-of-integers
to see a function that will help you out.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks a lot to you people!!!

The article at the URL pointed, was fabulous!
I really liked it and solved a lot of confusions of mine...

Thanks guys.

-surajit

problem with using String Variable in IN Function

Hellow !!
My problem is that I want to use a string variable in the 'where' clause Like this.I am using VB.6

dim ID as string
ID = "('1','2')"

Select coursecode from student
where studentid in ID

Now tell me how to solve this problem. or is there any other way to solve this problem.
Plz help me out.One solution would be:

dim ID as string
ID = ".1.2."

Select coursecode from student
where ID LIKE '%.' || studentid || '.%';|||Hellow !!

First i will say thanks. Tell me some other solution, this one is not working. Let me tell you i am using SQLSever 2000.
Let me explaing my problem with some more details.

recordSet.open" select coursecode from course where student id='1'" , connectionobject

Now save the recordset data in an array or in a string

let suppose
array(0)= "Eng"
array(1)="Phy"
(OR)

dim str as string
str="('eng','phy')"

now how i will use this array or this string in another select statement like

recordset1.open "select studentid from student where coursecode = ???",connectionObject,adopenstatic

Plz dont tell to use sunquery
I will be waiting for the reply Thanks|||You can use IN:

select studentid from student where coursecode in ('eng','phy')|||Hellow :
Thanks for the reply. Actually the result of the first query is not fixed.like

recordSet.open" select coursecode from course where student id='1'" , connectionobject

'Consider the result of this query will be different ever time when ever it runs.
' Like it may give 4 records,10 records may be 15 in the recordset
'now i have two ways to store the result of the query , i can store it either in a "string variable" or in an "Array".
' if string variable is used like

dim str as string
str = "('phy','chem','math')" or str = ('eng','oop','db','oose')

(forget this how i saved the data from the query in this string like this )
the only problem with using the string in the query is the outer quots " " . like

recordset1.open "select studentid from student where coursecode = "' & str & '" ",connectionObject,adopenstatic

Now if i use an array for storing the result of the first query. Tell me the way how that array will be use in the second query like

recordset1.open "select studentid from student where coursecode = array ",connectionObject,adopenstatic

Problem with using SQLExpress on the server.

I have a small ASP.NET 2.0 that uses an instanse of SQLExpress. The connection string looks like this:

</connectionStrings>

It works fine when I use it in file system. When I copy it to the server ( IIS6 on Windows Server ) it gives me an error:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

I tried changing the path to the local path on the server but it did not help.

Any help would be greatly appreciated.

With the way that you have the connection string configured you are using a user instance. What you sould do is make sure that sql express is installed on the server. Configure your web application directories and then install your application. On the server inside your web directories you should hava folder called app_data (The same as your local machine). Next I would perform an IISRESET once the app is loaded and the start the application up. You might find that the first time the app might time out on the database... just restart the browser session and it should fire up. You do not need to move the database to a different directory on the server as the connection string uses the datadirectory with is the app_data directory from your web solutions.