Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2004
    Location
    Buenos Aires, Argentina
    Posts
    28

    Unanswered: Problem with " , " and " ' "

    I'm programming under VB, and I have a connection to a MSSQL Server 2000 database. How can I make a query work when a string contains "," and "'"? All I could think about is changing all querys to stored procedures. Is there any special character I could use to tell the server to include the coma as part of the string?

  2. #2
    Join Date
    Apr 2004
    Location
    LA, CA
    Posts
    125
    whether you use stored procedures or straight sql instructions from a VB client, you still need to pass parameters and if you need to pass a string parameter that contains a single quote (') insert another quote just next to it and it should be fine. As for commas, a string parameter containing a comma and delimited by two single quotes should work fine.

    try in QA:

    create table #temp(field1 varchar(500))
    insert into #temp(field1) values ('test1''')
    insert into #temp(field1) values ('test2 ,')
    select * from #temp
    drop table #temp

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    ....but use stored procedures anyway....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Feb 2004
    Location
    Bangalore, India
    Posts
    250

    Stored Proc

    is it nececessary to user storedprocedures for data inserting?
    cant we use

    sSql = "insert into tablename values (" & var1 & "," & var2 & ")"
    dbConn.Execute sSql

    cud u pl tell, if thers ne advantage in using sp for data insertion
    Cheers....

    baburajv

  5. #5
    Join Date
    Feb 2004
    Posts
    492
    Using an sp for data insertion can have the advantage of shielding the db layout from applications, so applications may not have to be modified, recompiled and distributed if changes are made. Some database administrators like to know all the update/insert statements that could be executed so they can tune the database. It might also help seperating business logic from your applications.

    I'm sure there are more, but these are the ones I can come up with.

    One thing I haven't mentioned is that the company you work for may have chosen for one type of approach (having all in vb or all in sp), which sort of overrules advantage/disadvantage.

  6. #6
    Join Date
    Feb 2004
    Location
    Bangalore, India
    Posts
    250

    using Sp

    apart from separating the business logic from applications, will there be an improved performance for large insert/update statements while using an sp

    i.e, for an insert statement like

    sSql = "insert into table1(field1,field2,.....fieldn) values ("
    & val1 & "," & val2 & "," ..... & "," & valn & ")"
    dbConn.Execute sSql

    pl post ur comments
    Cheers....

    baburajv

  7. #7
    Join Date
    Nov 2004
    Location
    Cairo
    Posts
    1

    Thumbs up Solve Single Quute Problem

    Quote Originally Posted by panchopp
    I'm programming under VB, and I have a connection to a MSSQL Server 2000 database. How can I make a query work when a string contains "," and "'"? All I could think about is changing all querys to stored procedures. Is there any special character I could use to tell the server to include the coma as part of the string?

    you can use this code to sole ur Problem

    Pvar_DataBase.Execute "insert into " & TablName _
    & "(Filed01,Filed02)" _
    & " Values('" & value01 & "','" & Single_Qute(Value02) & "')"



    Public Static Function Single_Qute(String_Value As String) As String
    Single_Qute= Replace(String_Value, "'", "''")
    Single_Qute= Replace(String_Value, ",", "''")
    End Function

    If u have any problem

    send me to
    tgamil@egysoft-it.com

    Best Regards

    Tarek Gamil

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •