String Maddness!

[invitro]

Ok, well this is an easy questions... just takes a careful eye.

Heres the query:

strSQL = "INSERT INTO tblUser ";
	
	strSQL += "VALUES (\\'" + strGuid + "\\',\\'" + strUser + "\\',\\'" + pHash + "\\',\\'" + strEmail + "\\',\\'"; 
	
	strSQL += strFullname + "\\',\\'" + strAddress + "\\',\\'" + strCity + "\\',\\'" + strCountry + "\\',\\'" + strZip + "\\',\\'" + strPhone + "\\',\\'" + strWPhone;
	
	strSQL += "\\',\\'" + strWPhone + "\\',\\'" + strCPhone + "\\',\\'" + strFax + "\\',\\'" + strActivation + "\\'," + Date() + ") ";

when I try to run this, I get this error

[Microsoft][ODBC SQL Server Driver][SQL Server]Unclosed quotation mark before the character string '{048DB038-76B8-4139-805A-862BA2710DC3}'.

You might be thinking I have a " somewhere in the input, but I dont... i checked, there is no " or ' anywhere in the input. And if you look, the string "s are fine. What is going on here?

[blindlizard]

Print out strSQL and look at it that way. Much easier to identify

[Ecniv]

strSQL = "INSERT INTO tblUser ";
strSQL += "VALUES (\'" + strGuid + "\',\'" + strUser;
strSQL += "\',\'" + pHash + "\',\'" + strEmail + "\',\'";
strSQL += strFullname + "\',\'" + strAddress + "\',\'" ;
strSQL += strCity + "\',\'" + strCountry + "\',\'" + strZip + "\',\'";
strSQL += strPhone + "\',\'" + strWPhone;
strSQL += "\',\'" + strWPhone + "\',\'";
strSQL += strCPhone + "\',\'" + strFax + "\',\'";
strSQL += strActivation + "\',#" + Date() + "#) ";

I take it the above is either .Net or javascript?

Hashes around the date.
You have strWPhone in twice.


Vince

[invitro]

Yes, I printed out the SQL statement and it looks something like this...

INSERT INTO tblUser VALUES ('{5D9F4500-6D97-4FD2-A95C-9D71BE91886F}

It ends right there... for no reason. I've checked that spot 20 times now and see nothing wrong with it... so I have no idea why its terminating there. I'll go and check it again i suppose

thanks for the replies

oh yeah and thats jscript
sorry i keep forgeting to specify

Also, dont you need the \ before the ' since its an escape character? I tried doing it your way and tried specifying the colums into which to inser the data... but no go.

strSQL = "INSERT INTO tblUser ";
	
strSQL += "(guidUserID, charUsername, ";
strSQL += "charPHash, charEmail, charFullname, ";
strSQL += "charAddress, charCity, charCountry, ";
strSQL += "charPostal, charPhone, charWPhone, ";
strSQL += "charCPhone, charFax, charActivation, ";
strSQL += "blnActivated, charDate) ";

strSQL += "VALUES ( '" + strGuid + "','" + strUser;
strSQL += "','" + pHash + "','" + strEmail + "','";
strSQL += strFullname + "','" + strAddress + "','" ;
strSQL += strCity + "','" + strCountry + "','" + strZip + "','";
strSQL += strPhone + "','" + strWPhone + "','";
strSQL += strCPhone + "','" + strFax + "','";
strSQL += strActivation + ",\'0\'," + "',#" + Date() + "#) ";

Exact same error

Also, why do i need the # around the date object?

[invitro]

Heres what it gives me if I print out the new string.

INSERT INTO tblUser (guidUserID, charUsername, charPHash, charEmail, charFullname, charAddress, charCity, charCountry, charPostal, charPhone, charWPhone, charCPhone, charFax, charActivation, blnActivated, charDate) VALUES('{1269FDE5-7EE8-459C-8E15-60EC97848656}

Why is it stopping right after the guid, i dont get it.

[Danial]

What is the value of strGUID?

Try passing a normal string as strGUID (e.g a test value of abcd) and see if that works.

[invitro]

Yes it did work... theres something wierd about guids...

heres what im using to make a guid

In order for a guid to become a string that works, apperently i have to cut off 2 characters at the end... ummmm, yeah... im as puzzled as anyone.

strGuid = strGuid.substr(1,strGuid.length -2);

heres my create guid function

function sCart_getGuid()
{
	var TypeLib = Server.CreateObject("Scriptlet.TypeLib")
	var getGuid = TypeLib.Guid
	return getGuid;
}

Is there something I dont know about guids? .....wait, it looks as if it has a problem with the } bracket. When i snip it off, it works great... bah

Why would that be?

[invitro]

I dont know what is going on but I cut the guid by 2 characters and it works fine... no idea...

anyhow, now im getting this message.

Microsoft OLE DB Provider for ODBC Drivers error '80040e57'

[Microsoft][ODBC SQL Server Driver][SQL Server]String or binary data would be truncated.

looks like i cant win... i checked all my values, the only one that I think might be a problem is datetime

When I load up Enterprise manager, it tells me the length can be 8... however, when I go find SQL help it sais the datetime can be passed in as a string in this format yyyy-mm-dd hh:mm:ss

So, my date looks something like
'2004-7-27 22:13:2'

is this what is causing the problem?
If it is, how can i insert a date... since doing 2004-7-27 gives me the same problem... and if i take out the dashes in the date, it sais error converting date from string...


*bash head into keyboard now until unconscious*

[Danial]

Very intersting!

You can print out any string containing "{", thats not a problem. But problem i suspect is, the GUID function is returning something that might not be String Object.

I tried converting it to string many ways, but it seems like you cant concatonate the varaible once you fill with GUID.

I dont use JavaScript much in ASP so i cant explain this. I would suggest you string out the two { & } and then store it.

Hope this helps. Atleast you now know where the problem is.

Here is my test code : check it out


Notice that even though the response.write is printing out same value for the variable s, the length of the variable is not the same.

<%@LANGUAGE="JavaScript"%>

<%

var TypeLib = Server.CreateObject("Scriptlet.TypeLib")
var s = new String(TypeLib.Guid);


Response.write("Length before :" + s.length + "<br>");
Response.write("String Before :" + s + "<br>");

s+="hello";

Response.write("<br>Length after: " + s.length + "<br>");

Response.write ("String After :" + s + "<br>");

var t;

t="{E75F3580-BCFE-4653-82A4-2DCD9BD43DDF}";

t+="hello";

Response.write("<br>Normal String :" + t + "<br>");


%>

Output :

Length before :40
String Before :{CB7C0C03-2595-4BC4-994F-2DE75141ED2B}
Length after: 45
String After :{CB7C0C03-2595-4BC4-994F-2DE75141ED2B}
Normal String :{E75F3580-BCFE-4653-82A4-2DCD9BD43DDF}hello

[Danial]

Originally posted by invitro
I dont know what is going on but I cut the guid by 2 characters and it works fine... no idea...

anyhow, now im getting this message.


looks like i cant win... i checked all my values, the only one that I think might be a problem is datetime

When I load up Enterprise manager, it tells me the length can be 8... however, when I go find SQL help it sais the datetime can be passed in as a string in this format yyyy-mm-dd hh:mm:ss

So, my date looks something like
'2004-7-27 22:13:2'

is this what is causing the problem?
If it is, how can i insert a date... since doing 2004-7-27 gives me the same problem... and if i take out the dashes in the date, it sais error converting date from string...


*bash head into keyboard now until unconscious*

First of all Define the datefield as DateTime not SmallDate Time.

Secondly do not wrap the date with '#' like Enciv indicated, thats for Access Database, i think he assumed you are using Access.

Use single quote instead

Finally why are you using GUID as Primary Key, why are you not using Identity(Autonumber of Access)?

One final comment : You should never execute SQL query from front end, the whole Idea of using SQL server is to move away from things like that. Try to start using Stored Procedure instead.

[invitro]

1). I am not using smallDatetime. I specificaly specified for the SQL database to use DateTime. Even now, when i go back, it sais DateTime not smallDatetime.

2). Yes it was giving me bad errors with # so i took them out and it works fine... well, except for that problem.

3). I am not using autonumber because I was told guids are much better for that kind of stuff. Is this not true? It was something about when you have to merge databases in the future guids will make this much easier without ID clashes.

4). Why am i executing SQL commands from front end? I dont think i quite understand what you mean. The code above is embeded into my ASP websites... what it does is inserts a user when he\she signs up into the website. Is there a better way to do this that I dont know about?

Again, thanks again... i dont think i will ever need to merge databases in the future so perhaps i will revert to autonumber.

[Danial]

1). I am not using smallDatetime. I specificaly specified for the SQL database to use DateTime. Even now, when i go back, it sais DateTime not smallDatetime.

Thats fine, now first type a valid date in sql server and see what format it is stored, and then pass it in that format, eg "/" or "-" as devider.

2). Yes it was giving me bad errors with # so i took them out and it works fine... well, except for that problem.

Thats good.

3). I am not using autonumber because I was told guids are much better for that kind of stuff. Is this not true? It was something about when you have to merge databases in the future guids will make this much easier without ID clashes.

Yes if you plan to merge the Database then GUID will remove the chance of key conflits.

4). Why am i executing SQL commands from front end? I dont think i quite understand what you mean. The code above is embeded into my ASP websites... what it does is inserts a user when he\she signs up into the website. Is there a better way to do this that I dont know about?

Yes there much better way, which is considerably faster, safer and easy to maintain and i can go on.

What i am talking about is Stored procedure, bascially it is similar to Access's saved query. Stored Procudure are saved in the SQL Server and are called from ASP code. You can pass parameters and it will execute the query and pass you the recordset. Since it is compiled it will run faster then your embeded SQL query.

Here is a simple example of a stored procudure(they can do some quite complex things).

Create Procedure GetProducts
          @ProductID int

AS
         Select * From Products Where ProductID=@ProductID

return

Search for VB and stored procedure in google, you will see hundreds of tutorial and example. If not then let me know i will post some example.

[invitro]

D,

I looked into some stored procedure code but i cant find a good tutorial.

I understand how it works, allthough i still have to work on the syntax... but, once I have my stored procedure function, how do i execute it in ASP to get a record?

Is there any good tutorials you found that explain this from step 1 to the end? Most of the things i found online just kind of jump right into the middle... which is usualy fine...

wait, for all of you out there who are interested in stored proc. heres a good link
http://www.planet-source-code.com/vb...d=127&lngWId=5

Ok, I think i understand them now... COOL! thanks a lot you make my life easy man

[invitro]

Bah this stored procedure bs just added another load to learn... sheesh Danial i blame it all on you!

Ok, previously what I did in order to get all rows from SQL I would do something like this:

function getTable(tblName)
{
	strSQL = "SELECT * ";
	strSQL += "FROM " + tblName + " ";
	
	return this.GetRecordset(strSQL);
}

Now, what that would do is return a recordset. So, in to call that I would simply do

objMyTable = getTable('users');

then, I would cycle through records by using objMyTable('charUsername')

Is it possible to return a recordset like this through stored procs?

[Danial]

Bah this stored procedure bs just added another load to learn... sheesh Danial i blame it all on you!

Well if only you knew the difference between Stored Proc and embeded SQL Queries, you would kill yourself for using embeded SQL query ! Before you know it you will think of upgrading to .Net

Ok, previously what I did in order to get all rows from SQL I would do something like this:

function getTable(tblName)
{
	strSQL = "SELECT * ";
	strSQL += "FROM " + tblName + " ";
	
	return this.GetRecordset(strSQL);
}

Never use Select * even if a table contain 1/2 fields. The performance difference is significant and its very bad practice.

Now, what that would do is return a recordset. So, in to call that I would simply do

objMyTable = getTable('users');

then, I would cycle through records by using objMyTable('charUsername')

Is it possible to return a recordset like this through stored procs?

Ofcourse, stored procedure uses (TSQL short for Transact SQL), it is a language in itself.

When you have a "Select ID, FirstName from Contact" in the stored procedure, that query actually is returning a Recordset which you can use in your VB.

Give me few miniute i will post a simple example of using stored procedure in VB6(havent programmed in VB6 for a while so might just take a bit more time then usual).

[Danial]

Here is the example :

    Create Procedure SerachContact
    	@FieldName varchar(100),
    	@Value varchar(100)
    AS
    	Select * From Contact Where @FieldName =@Value
    
    return

Dim CMD As New ADODB.Command
    Dim Conn As New ADODB.Connection
    
    Dim rsOrg As Recordset
    Dim Param As ADODB.Parameter
    
 
        
    Conn.ConnectionString = gConnectionString
    Conn.Open
    
    Set CMD.ActiveConnection = Conn
    
    CMD.CommandText = "SearchContact"
    CMD.CommandType = adCmdStoredProc
    
    strFldName="your field name"
    strValue="your value"
    
    Set rsOrg = CMD.Execute(, Array(strFldName, strValue))
    
    
 
    Dim i As Integer
    i = 1
    
    
 
 Do While Not rsOrg.EOF
     .ListItems.Add i, , i
 
     .ListItems(i).ListSubItems.Add , , rsOrg("Con_Id")
     .ListItems(i).ListSubItems.Add , , rsOrg("Con_Name")
     .ListItems(i).ListSubItems.Add , , rsOrg("Org_Abbr")
     .ListItems(i).ListSubItems.Add , , rsOrg("Org_Address")
     .ListItems(i).ListSubItems.Add , , rsOrg("Org_Postcode")
 
      i = i + 1
 rsOrg.MoveNext
 Loop