Hey there, I have created an update form in a website and find that when calling for the current details, the recordset calls information from the database as a number. An example would be; Country = 1 rather than Country = Australia.

I beleive that this is because the property for the table cell is set as numberic. This is because the cell has a relationship with another table.

Is there anyway to call the information as text?

I have tried to change the update form property to text but this makes no differance. I have included some code below.

Also note, i tried to change all of the cell properties to text, but because of the relationships I got with other tables i continue to get the Type Mismatch error.

<%
Dim rsUser__MMColParam
rsUser__MMColParam = "1"
If (Session("MM_UserName") <> "") Then
rsUser__MMColParam = Session("MM_UserName")
End If
%>
<%
Dim rsUser
Dim rsUser_numRows
Set rsUser = Server.CreateObject("ADODB.Recordset")
rsUser.ActiveConnection = MM_connSeek_STRING
rsUser.Source = "SELECT * FROM tblSuppliers WHERE UserName = '" + Replace(rsUser__MMColParam, "'", "''") + "'"
rsUser.CursorType = 0
rsUser.CursorLocation = 2
rsUser.LockType = 1
rsUser.Open()
rsUser_numRows = 0
%>

<input name="Country" type="text" id="Country" value="<%=(rsUser.Fields.Item("idCountry").Value)% >" size="30">

If you need more info please ask.

Frustrated.

Mal.

I beleive that this is because the property for the table cell is set as numberic. This is because the cell has a relationship with another table

More likely that it is a normalised structure and you need to retrieve the text version of the country using a JOIN query.

something like

SELECT *.tblSuppliers, countrynameTable.countryname AS country from tblSuppliers JOIN countrynameTable ON tblSuppliers.idCountry = countrynameTable.ID WHERE etc

Guessing at table names and column names BTW

not tested

Dose this look like valid SQL?

SELECT *.tblSuppliers, idCountry.tblCountry
FROM tblSuppliers JOIN tblCountry ON tblSuppliers.idCountry = tblCountry
WHERE UserName = 'MMColParam'

Nope, there is a field missing in the join

= tblCountry.fieldname

is tblCountry.idCountry the text version of the country ? You may need to add an alias if idCountry appears in both tables

if it doesn't work post the table structure and an example of what data is in each field

Dose this look better?

SELECT *.tblSuppliers, idCountry.tblCountry
FROM tblSuppliers JOIN tblCountry ON tblSuppliers.idCountry = tblCountry.Country
WHERE UserName = 'MMColParam'


Fields I have are...

tblCountry - idCountry(Auto Number), Country(Text).

tblSuppliers - idCountry(Text)

Result I get with that is..



Microsoft JET Database Engineerror '80040e14'
Syntax error (missing operator) in query expression '*.tblSuppliers'. /html/advertiser-basic-details.asp, line 168

Someone suggested this, but the result is the same. Still calls the info as a number. No error though.

SELECT tblSuppliers.*, tblCountry.Country
FROM tblSuppliers INNER JOIN tblCountry ON tblSuppliers.iCountry = tblCountry.idCountry
WHERE UserName = 'MMColParam'

idCountry.tblSuppliers is a text field it should be a numeric type ( matching size and type with idCountry.tblCountry

it should be table.* my error

and are you still printing objRs("idcountry") from recordset because you should be sending out objRS("country")

I think this i it... Dose it look ok to you?

The following SQL should at least retreive the text version of the result Country (Australia) and Location (Victoria).

SELECT tblSuppliers.*, tblCountry.CountryName
FROM tblSuppliers S
INNER JOIN tblLocation L
ON (S.idLocation = L.idLocation)
INNER JOIN tblCountry C
ON (L.idCountry = c.idCountry)
WHERE UserName = 'MMColParam'

Tables.

.......tblSupplier
(A) idSupplier
(N) iLocation
(N) iCountry

.......tblLocation
(A) idLocation
(T) Location

.......tblCountry
(A) idCountry
(T) Country

(A) Auto Number
(N) Number
(T) Text

Location = Rome
Country = Italy

changed sections in blue

SELECT tblSuppliers.*, tblCountry.Country, tblLocation.location FROM tblSuppliers AS S INNER JOIN tblLocation AS L ON (S.idLocation = L.idLocation) INNER JOIN tblCountry AS C ON (L.idCountry = c.idCountry) WHERE UserName = '" & MMColParam & "' :"

{not tested}

the AS isn't strict in MS Access but other SQL servers can be, so if you port the application it could cause an error then.

I did try it. Came up with this.



Microsoft JET Database Engineerror '80040e14'
Syntax error (missing operator) in query expression '(S.idLocation = L.idLocation) INNER JOIN tblCountry AS C ON (L.idCountry = c.idCountry)'. /html/advertiser-basic-details.asp, line 187

<%
Dim Recordset2
Dim Recordset2_numRows
Set Recordset2 = Server.CreateObject("ADODB.Recordset")
Recordset2.ActiveConnection = MM_connSeek_STRING
Recordset2.Source = "SELECT tblSuppliers.*, tblCountry.Country, tblLocation.location FROM tblSuppliers AS S INNER JOIN tblLocation AS L ON (S.idLocation = L.idLocation) INNER JOIN tblCountry AS C ON (L.idCountry = c.idCountry) WHERE UserName = '" & " + Replace(Recordset2__MMColParam, "'", "''") + " & "' :" "
Recordset2.CursorType = 0
Recordset2.CursorLocation = 2
Recordset2.LockType = 1
Recordset2.Open() <--------------------------- 187
Recordset2_numRows = 0
%>

Thanks for your reply.

Mally.

remove the brackets from (L.idCountry = c.idCountry)

and move the brackets around in this

tblSuppliers AS S INNER JOIN tblLocation AS L ON (S.idLocation = L.idLocation)

to be

(tblSuppliers AS S INNER JOIN tblLocation AS L ON S.idLocation = L.idLocation)

you may also need to use LEFT JOIN in place of INNER JOIN

If you still have problems we are going to need the .mdb file to be able to debug properly.

Thanks, I looked every where for this post in the database forum. As you can tell, I couldnt find it.

Thanks.

Mally

BTW

Thanks for the access to the site you are creating. I have done something similar. Its almost working.

Mally.

I keep getting error messages. Is there any where I can post the database. Will save a lot fo time.

Mally.

attach it to a post

you can't do that in quick reply BTW

Heya, I have attached the file below.

SELECT S.*, C.Country, L.location FROM (( tblSuppliers AS S  INNER JOIN tblCountry AS C ON S.Country = c.idCountry) INNER JOIN tblLocation AS L ON L.idLocation = S.loc_id) WHERE S.UserName = 'gregs'

for some reason S.idLocations was not being recognised as part of the query. I added a numeric field S.loc_id and set it to the same number and the above query works.

That did the job. Thanks for your help & patience Chris.

Mally.